Shulou(Shulou.com)06/01 Report--

1. Exists1.1 description

The return value of the EXISTS (including NOT EXISTS) clause is a Bool value. There is a subquery statement (SELECT...) inside EXISTS. FROM...), which I call the inner query statement of EXIST. The query statement in it returns a result set. The EXISTS clause returns a Boolean value based on whether the result set of the query statement in it is empty or not. Link

Exists: emphasize whether to return the result set, and do not need to know what to return, for example: select name from student where sex ='m 'and mark exists (select 1 from grade where...). As long as the clause guided by exists returns the result set, then the exists condition is true. Note that the returned field is always 1, and if it is changed to "select 2 from grade where...", then the returned field is 2, which is meaningless. So the exists clause doesn't care about what is returned, but about whether a result set is returned. EXISTS = IN, the meaning is the same, but there is a slight difference in syntax, as if using IN is less efficient, which should be the reason why the index will not be executed. Link

The performance is better than inner join,exists, and when she finds the first eligible record, she immediately stops searching and returns TRUE.

1.2 example-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:namecherrie--NOT EXISTS--SQL:select name from family_memberwhere group_level > 0and not exists (select 1 from family_grade where family_member.name = family_grade.nameand grade > 90)-result: NamemazeyrabbitII, except2.1 description

EXCEPT = NOT EXISTS,INTERSECT = EXISTS on the query result, but the "query overhead" of EXCEPT / INTERSECT is much higher than that of NOT EXISTS / EXISTS.

Except automatically repeats, not in / not exists does 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 not exists (select name from family_grade where family_member.name = family_grade.name)-- result:namerabbitrabbit III. Test data-Table structure for family_grade---DROP TABLE [mazeytop]. [family_grade] GOCREATE TABLE [mazeytop]. [family_grade] ([id] int NOT NULL [name] varchar (20) NULL, [grade] int NULL) GO-- Records of family_grade-- INSERT INTO [mazeytop]. [family_grade] ([id], [name], [grade]) VALUES Numb70') GOGOINSERT INTO [mazeytop]. [family_grade] ([id], [name], [grade]) VALUES Numb93') GOGO-- Table structure for family_member-- DROP TABLE [mazeytop]. [family_member] GOCREATE TABLE [mazeytop]. [family_member] ([id] int NOT NULL, [name] varchar (20) NULL, [sex] varchar (20) 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 GOGOINSERT INTO [mazeytop]. [family_member] ([id], [name], [sex], [age], [group_level]) VALUES (Numb2, Nickel, female, Nissan, Numb22, Numb2') GOGOINSERT INTO [mazeytop]. [family_member] ([id], [name], [sex], [age], [group_level]) VALUES GOGOINSERT INTO [mazeytop]. [family_member] ([id], [name], [sex], [age], [group_level]) VALUES Numb3') 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 (20) NULL) GO-- Records of family_part-- INSERT INTO [mazeytop]. [family_part] ([id], [group], [group_name]) VALUES N`Father') GOGOINSERT INTO [mazeytop]. [family_part] ([id], [group], [group_name]) VALUES (Nong2mom, Nong2fang, N`Mother') GOGOINSERT INTO [mazeytop]. [family_part] ([id], [group], [group_name]) VALUES 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

The usage of exists and except in SQLServer

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.