exists and except usage in SQL Server

I. Description of exists1.1

The return value of the EXISTS (including not EXISTS) clause is a bool value. There is a subquery inside the exists (SELECT ... From ...), which I call an inner query statement for exist. The inner query statement returns a result set. The EXISTS clause returns a Boolean value based on the result set of its query statement, either empty or non-empty. Link

Exists: The emphasis is on whether to return the result set and not to know what to return, such as: select name from student where sex = ' m ' and mark exists (select 1 from grade where ...), only To exists the BOOT clause has a result set return, then exists this condition is set up, we notice that the returned field is always 1, if you change to "Select 2 from Grade where ...", then the returned field is 2, this number is meaningless. So the EXISTS clause does not care what is returned, but rather whether there is a result set returned. EXISTS = in, the meaning is the same but the syntax is a little bit different, as if using in efficiency to almost, should not be the reason for indexing. Link

Performance is better relative to inner join,exists, and when she finds the first qualifying record, it stops the search immediately and returns True.

1.2 Example

--exists--sql:select name from Family_memberwhere group_level > 0and EXISTS (select 1 from Family_grade where Family_mem Ber.name = Family_grade.nameand grade >)--result:namecherrie--not exists--sql:select name from Family_memberwhere Group_level > 0and exists (select 1 from family_grade where family_member.name = Family_grade.nameand Grade > 90) --result:namemazeyrabbit

Ii. Description of except2.1

The query results on except = not exists,intersect = EXISTS, but Except/intersect's "query cost" is much larger than not exists/exists.

Except automatically to repeat, not in/not exists will not.

2.2 Example

--except--sql:select name from Family_memberwhere group_level > 0except (select name from Family_grade)--result: Namerabbit--not exists--sql:select name from Family_memberwhere group_level > 0and no EXISTS (select name from Family_g Rade where family_member.name = family_grade.name)--result:namerabbitrabbit

Third, test data

-- ------------------------------ Table structure for family_grade-- ----------- -----------------Drop table [mazeytop]. [Family_grade] Gocreate table [mazeytop]. [family_grade]  ([Id] int not null ,[name] varchar ()  NULL ,[grade]  int NULL ) go-- ------------------------------ records of family_grade--  ----------------------------Insert into [mazeytop]. [family_grade]  ([Id], [name], [grade])  VALUES  (N ' 1 ',  n ' Mazey ',  n ' 70 ') Gogoinsert into [mazeytop]. [family_grade]  ([Id], [name], [grade])  VALUES  (N ' 2 ',  n ' Cherrie ',  n ' 93 ') gogo-- ------------------------------ Table structure for family_member-- ------ ----------------------Drop table [mazeytop]. [Family_member] Gocreate table [mazeytop]. [family_member]  ([Id] int not&nbsP Null ,[name] varchar ( null ,[sex] varchar)  NULL ,[age] int  null ,[group_level] int null ) go-- ------------------------------ Records  of family_member-- ----------------------------Insert into [mazeytop]. [family_member]  ([Id], [name], [sex], [age], [group_level])  VALUES  (N ' 1 ',  n ' Mazey ',  n ' male ',  n ' all ',  n ' 1 ') gogoinsert into [mazeytop]. [family_member]  ([Id], [name], [sex], [age], [group_level])  VALUES  (N ' 2 ',  n ' Cherrie ',  n ' female ',  n ' a ',  n ' 2 ') gogoinsert into [mazeytop]. [family_member]  ([Id], [name], [sex], [age], [group_level])  VALUES  (N ' 3 ',  n ' rabbit ',  n ' female ',  n ' ",  n ' 3 ') gogoinsert into [mazeytop]. [family_member]  ([id], [name], [sex], [age], [group_level])  Values  (N ' 4 ',  n ' rabbit ',  n ' female ',  n ',  n ' 3 ') gogo-- -------------------------- ---- Table structure for family_part-- ----------------------------drop table  [mazeytop]. [Family_part] Gocreate table [mazeytop]. [family_part]  ([id] int not null ,[group] int null ,[group_name]  varchar ( NULL ) go-- ------------------------------ records of family_part--  ----------------------------Insert into [mazeytop]. [family_part]  ([Id], [group], [group_name])  VALUES  (N ' 1 ',  n ' 1 ',  n ' father ') Gogoinsert into [mazeytop]. [family_part]  ([Id], [group], [group_name])  VALUES  (N ' 2 ',  n ' 2 ',  n ' mother ') Gogoinsert into [mazeytop]. [family_part]  ([Id], [group], [group_name])  VALUES  (N ' 3 ',  n ' 3 ',  n ' daughter ') gogo-- ------------------------------ indexes structure for table family_grade-- ------------------------------ - ----------------------------- primary key structure for table family_grade--  ----------------------------Alter table [mazeytop]. [family_grade] add primary key  ([id]) go-- ------------------------------ indexes  structure for table family_member-- ------------------------------ ------------ ------------------ Primary Key structure for table family_member-- -------- --------------------Alter table [mazeytop]. [family_member] add primary key  ([id]) go-- ------------------------------  indexes structure for table family_part-- ------------------------------ ------- ----------------------- Primary Key structure for table family_part-- ----- -----------------------Alter table [mazeytop]. [family_part] add primary key  ([id]) GO

exists and except usage in SQL Server

This article from "Do not know not to ask" blog, please be sure to keep this source http://mazey.blog.51cto.com/12997993/1947482

exists and except usage in SQL Server