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

This article mainly introduces "how to use exists and except in SQLServer". In daily operation, I believe many people have doubts about how to use exists and except in SQLServer. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "how to use exists and except in SQLServer". Next, please follow the editor to study!

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.

Exists: emphasize whether to return a 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 a 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.

It performs better than inner join,exists, and when it finds the first eligible record, it 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:namemazeyrabbit1.3 intersect/2017-07-21

The function of intersect is similar to exists.

-- intersect--SQL:select name from family_member where group_level > 0intersectselect name from family_grade where grade > 90--result: Namecherre II, except2.1 description

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

Except automatically repeats, but 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

A new rabbit is added to the family_member when verifying the except de-repetition function.

-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 to this The study on "how to use exists and except in SQLServer" is over. I hope I can solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.