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

This article mainly introduces how to use exists and not exists in sql, which has a certain reference value, and interested friends can refer to it. I hope you will gain a lot after reading this article.

Exists:

The emphasis is on whether there is a return set, and you don't need to know exactly what is returned, such as:

SELECT * FROM customer WHERE not EXISTS (SELECT 0 FROM customer_goods WHERE customer_id = 1)

This condition is true as long as the clause guided by exists has a result set returned. The returned field is always 0. If you change it to 1, it will always return 1, so exists does not

Care about what is returned, only about whether a result set is returned.

The difference between exists and in

The biggest difference between the two is that only one field value can be returned with in

SELECT * FROM customer c WHERE c.id not in (SELECT customer_id FROM customer_goods WHERE customer_id = 1)

However, exists allows multiple fields to be returned.

Not in and not exists are the opposite of in and exists, respectively.

Exists (true result set returned by sql)

Not exists (true result set is not returned by sql)

Not exists details:

Table customer:

Table customer_goods:

The relationship between the two: customer_goods.customer_id = customer.id

(1) Enquiry:

SELECT * FROM customer c WHERE NOT EXISTS (SELECT * FROM customer_goods cg WHERE cg.customer_id = 1)

Results:

No result returned

(2) Enquiry:

SELECT * FROM customer c WHERE NOT EXISTS (SELECT * FROM customer_goods cg WHERE c.id = 1)

Results:

(3) Analysis:

It is found that the only difference between the two is whether the query condition of the not exists sentence query is related to the external query condition. If the not query subquery has its own query condition, so as long as there is data returned in the subquery, it is proved to be false, and the result will have no return value in the overall execution. Once associated with the external query, the data can be accurately found.

And that's the problem I have.

After analysis, I think that once associated with the outer query, the table of the external query will be scanned. However, once the relationship between the two is not added, it will only be judged based on whether there is a result set returned by not exists, which is why once the subquery has data, it cannot find all the data.

Attached case analysis

Let's see how not exists or exists works.

# Student form CREATE TABLE `Student` (`sid` VARCHAR (20), `sname` VARCHAR (20) NOT NULL DEFAULT'', `ssex` VARCHAR (20) NOT NULL DEFAULT'', `ssex` VARCHAR (10) NOT NULL DEFAULT', PRIMARY KEY (`sid`); # course schedule CREATE TABLE `Course` (`cid`Course` (20), `cname` VARCHAR (20) NOT NULL DEFAULT'', `tid` VARCHAR (20) NOT NULL, PRIMARY KEY (`cid`) # teacher table CREATE TABLE `Teacher` (`tid` VARCHAR (20), `tname` VARCHAR (20) NOT NULL DEFAULT', PRIMARY KEY (`tid`)); # transcript CREATE TABLE `Score` (`sid` VARCHAR (20), `cid` VARCHAR (20), `sscore` INT (3), PRIMARY KEY (`sid`, `cid`)) # insert student table test data insert into Student values ('01',' Zhao Lei', '1990-01-01', 'male'); insert into Student values ('02',' Qian Dian', '1990-12-21',' male'); insert into Student values ('03',' Sun Feng', '1990-05-20',' male') Insert into Student values ('04',' Li Yun', '1990-08-06', 'male'); insert into Student values ('05',' Zhou Mei', '1991-12-01', 'female'); insert into Student values ('06',' Wu Lan', '1992-03-01', 'female'); insert into Student values ('07',' Zheng Zhu', '1989-07-01', 'female') Insert into Student values ('08',' Wang Ju', '1990-01-20',' female'); # Curriculum schedule test data insert into Course values ('01',' Chinese', '02'); insert into Course values (' 02', 'math', '01'); insert into Course values (' 03', 'English', '03') # teacher's table test data insert into Teacher values ('01',' Zhang San'); insert into Teacher values ('02',' Li Si'); insert into Teacher values ('03', Wang Wu'); # score table test data insert into Score values ('01',' 01', 80); insert into Score values ('01',' 02', 90); insert into Score values ('01',' 03', 99) Insert into Score values ('02',' 01', 70); insert into Score values ('02',' 02', 60); insert into Score values ('02',' 03', 80); insert into Score values ('03',' 01', 80); insert into Score values ('03',' 02', 80); insert into Score values ('03',' 03', 80) Insert into Score values ('04',' 01', 50); insert into Score values ('04',' 02', 30); insert into Score values ('04',' 03', 20); insert into Score values ('05',' 01', 76); insert into Score values ('05',' 02', 87); insert into Score values ('06',' 01', 31) Insert into Score values ('06',' 03', 34); insert into Score values ('07',' 02', 89); insert into Score values ('07',' 03', 98)

The topic is to inquire about the information of other students who are exactly the same course as the students on "01". It is really troublesome to do it directly. We can do this first: check the information of the students who have taken all the courses.

The information of the students who have taken all the courses is that none of these students have not taken any courses.

Select * from Student st where not exists (select * from Course c where not exists (select * from Score sc where sc.c_id = c.c_id and sc.s_id = st.s_id))

Then let's go back to this question and replace all the courses with those taken by students in 01.

Select * from Student st where not exists (select * from (select s2.c_id as c_id from Student S1 inner join Score S2 on s1.s_id = s2.s_id where s1.s_id = 01) t where not exists (select * from Score sc where sc.c_id = t.c_id and sc.s_id = st.s_id and st.s_id! = 01))

Thank you for reading this article carefully. I hope the article "how to use exists and not exists in sql" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.