HkEndless
- 浏览: 92098 次
- 性别:
- 来自: 上海
-
文章分类
- 全部博客 (133)
- jQuery (11)
- XML (3)
- 组件 (1)
- JAVA (20)
- WEB (3)
- SPRING (6)
- HIBERNATE (5)
- AJAX (2)
- JS (1)
- JAVA webservice (1)
- Ditu (1)
- WEBSITE (1)
- HIBERNATE ANNOTATION (1)
- 排序 (1)
- TCP_NODELAY (1)
- ConvertUtils (1)
- Logistics (1)
- SQL SERVER 中identity (4)
- sql server (35)
- MYSQL (1)
- Eclipse (6)
- ORACLE (6)
- FLEX (4)
- notepad++ (0)
- UNION ALL (1)
- JUnit (3)
- SQL 异常处理 (1)
- @@trancount (1)
- IOS (1)
- ORA-02266 (1)
- REMOTE DESKTOP (0)
- HTML 优化 (1)
- CRLF (1)
- SQL Server Sequence (1)
最新评论
-
zjuttsw：
看的舒服
重要的hashcode equals转载
EXISTS或者NOT EXISTS是把主查询的字段传到后边的查询中作为条件，返回值是TRUE或者FALSE。EXISTS TRUE，那么就是查询条件成立，结果会显示出来。NOT EXISTS TRUE，则为FALSE，查询连接条件不成立。
select * from course where not exists(select * from grade where grade.课程代号=course.课程代号)
这个语句，是查询course表中课程代号在grade中没有出现的数据。
看看grade表，课程编号有K01到K06，而COURSE表，有K01到K07，那么K07在GRADE表是不存在的，那么，是符合条件的。
同样select * from course where exists(select * from grade where grade.课程代号=course.课程代号)
则是查询COURSE的记录条件为编号在GRADE中存在。那么很明显，结果是K01到K06的数据。
另外，EXISTS和NOT EXISTS的作用可以用IN或NOT IN实现，但是，效率要高。
因为EXISTS和NOT EXISTS返回的结果是TRUE或者FALSE，那么则在子查询中，遇到第一个符合条件的结果，就会退出查询，而不会进行全表的检索。而NOT IN或者IN，要把子查询中的SELECT字句全部查询出来才行。
select * from course where not exists(select * from grade where grade.课程代号=course.课程代号)
这个语句，是查询course表中课程代号在grade中没有出现的数据。
看看grade表，课程编号有K01到K06，而COURSE表，有K01到K07，那么K07在GRADE表是不存在的，那么，是符合条件的。
同样select * from course where exists(select * from grade where grade.课程代号=course.课程代号)
则是查询COURSE的记录条件为编号在GRADE中存在。那么很明显，结果是K01到K06的数据。
另外，EXISTS和NOT EXISTS的作用可以用IN或NOT IN实现，但是，效率要高。
因为EXISTS和NOT EXISTS返回的结果是TRUE或者FALSE，那么则在子查询中，遇到第一个符合条件的结果，就会退出查询，而不会进行全表的检索。而NOT IN或者IN，要把子查询中的SELECT字句全部查询出来才行。
分享到：
发表评论
-
WITH (NOLOCK)
2014-09-28 11:56 522http://www.cnblogs.com/henw/arc ... -
SQL Server Profiler使用方法
2014-05-21 16:55 545http://lyt7599.blog.163.com/blo ... -
SQL Server Error Message
2014-05-20 20:20 1248http://www.sql-server-helper.co ... -
SQL锁的概述
2014-05-16 14:39 356http://blog.csdn.net/htl258/art ... -
SET...
2013-12-31 11:16 268http://msdn.microsoft.com/en-us ... -
DATE
2013-12-30 15:23 385http://blog.csdn.net/dba_huangz ... -
DATE AND TIME
2013-12-30 14:22 487SELECT (CAST((SELECT SUBSTRING( ... -
Microsoft SQL Server, Error:9002
2013-09-25 17:48 976一般在建立一个database之后，就应该手动设置其日志的增长 ... -
SQL 2005 with(nolock)详解
2013-08-26 15:46 535http://hi.baidu.com/gabriel/ite ... -
is invalid in the select list because it is not contained in either an aggregate
2013-07-11 10:37 901当select 语句块中出现function与常列时，需要在w ... -
A interesting article for printing statement in sql
2013-07-09 11:57 351http://bytes.com/topic/sql-serv ... -
Good net website blog for Database
2013-07-09 11:53 693http://www.bluegecko.net/mysql/ ... -
how to search a function or sto in database
2013-07-08 14:02 634SELECT * FROM sysobjects WHERE ... -
Good SQL WEBSITE 细说Sql Server中的视图(上)(更新)
2013-07-08 11:38 461http://www.cnblogs.com/xbf321/a ... -
SQLServer : EXEC和sp_executesql的区别
2013-07-08 11:36 484http://www.cnblogs.com/xbf321/a ... -
Search for a stored procedure
2013-07-08 11:27 579September 3, 2007 by Pinal Dave ... -
TRUNCATE TABLE
2013-06-27 10:43 438默认情况下，IDENTITY_INSER就是off 这种情况下 ... -
SELECT INTO 和 INSERT INTO SELECT 两种表复制语句
2013-06-18 14:08 486http://www.cnblogs.com/freshman ... -
安装了SQL以后，编写SQL语句没有提示
2013-06-18 09:55 491安装了SQL以后，编写SQL语句没有提示解决办法： 工具— ... -
局部临时表VS全局临时表
2013-06-13 14:37 3583http://blog.csdn.net/wu_qiongle ...
相关推荐
在sql语名中，if not exists 即如果不存在... 代码如下:if not exists (select * from sysobjects where id = object_id(‘table_name’) and OBJECTPROPERTY(id, ‘IsUserTable’) = 1) c，判断列不存在 代码如下:if
uploadQuickStartsee for more detail.Development$ npm i$ npm run dev$ open http://localhost:7001/Deploy$ npm start$ npm stopnpm scriptsUse npm run lint to check code style.Use npm test to run unit test...
if exists(select 1 from sys.sysforeignkey where role='FK_CHOOSE_C_CHOOSE_CO_COURSES') then alter table choose_course delete foreign key FK_CHOOSE_C_CHOOSE_CO_COURSES end if; if exists(select 1 from...
IF NOT EXISTS(SELECT 1 FROM sys.views WHERE name='Report_IndividualTicket') BEGIN create view Report_IndividualTicket as SELECT Ticket.TicketNumber, Ticket.TicketID, GisProcess.StageName, Content....
combines to show students, in lectures and in study, not only how techniques are implemented, but also how and why they work with an explicit relation to conventional teaching material.
select sc.* from sc where not exists( select cno from c where( sc.cno!='c1' and sc.cno!='c2' ) ) --其中not exists 中的 select cno from c where 是没有用的 --等价于 select sc.* from sc where cno not in...
The potential for deployment of such networks exists in many scenarios ranging from civil and construction engineering, and disaster relief to sensor networks and military applications. The IEEE 802....
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbUser]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tbUser] GO if exists (select * from dbo.sysobjects where ...
-domain model, we showthat in a widely used DLL configuration,jitter peaking always exists and high-frequency jitter does not get attenuated as previous analyses suggest. This is true even in a first ...
Such methods do not represent the n-D data fully and do not allow the restoration of the n-D data completely from their 2-D representation. Respectively, our abilities to discover the n-D data ...
In a few places, where such code is not readily available, we have included Matlab code in the body of the text. Layout. The layout and typography has been revised. Examples and Matlab code now ...
CCP4 exists to produce and support a world-leading, integrated suite of programs that allows researchers to determine macromolecular structures by X-ray crystallography, and other biophysical ...
we also include cases where no intensity edge exists at the location of the motion boundary, or when no parametric motion model can describe the data. Finally, we describe psychophysical experiments ...
can be used to force mksquashfs to not compress inodes/directories and data respectively. Giving both options generates an uncompressed filesystem. The -le and -be options can be used to force ...
本篇文章是对PHP中empty is_null和isse的测试进行了详细的分析介绍，需要的朋友参考下
method: the spatial structure and statistics are not preserved during the procedure of model calibration and history matching. While the spatial structure and statistics of models may be one of the ...
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Borrow_Info_Book_Info]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Borrow_Info] DROP CONSTRAINT FK_Borrow...
包含：in、not in exists、not exists 范围：between…and、not between….and 匹配测试：like、not like Null测试：is null、is not null 布尔链接：and、or、not 通配符： 在where子句中，...
In practical robot motion planning, robots usually do not have full models of their surrounding, and hence no complete and correct plan exists for the robots to be executed fully. In most real-world ...