EXISTS and not EXISTS in SQL

Source: Internet
Author: User

A subquery with the EXISTS operator does not return any data, producing only logical truth ' true ' or logical false value ' false '. Subqueries with the EXISTS operator are correlated subqueries.

Correlated subqueries: The conditions of a subquery depend on the parent query.

EXISTS: If the inner query result is not empty, the outer WHERE clause returns TRUE, outputting the outer query result.

Not EXISTS: If the inner query result is empty, the outer WHERE clause returns TRUE, outputting the outer query result .

Sample Table
CREATE TABLEStudent (SnoCHAR(9)PRIMARY KEY,--School NumberSnameCHAR( -)UNIQUE,--name);Create TableSC (SnoChar(9),--School NumberCnoChar(4),--Course Number Primary Key(SNO,CNO),Foreign Key(SNO)Referencesstudent (Sno));
View Code

/*Check the name of a student who has at least one course not selected*/SELECTsname fromStudentWHERE EXISTS(SELECT *               fromCourseWHERE  not EXISTS(SELECT *                                fromSCWHERESno=Student.sno andCno=course.cno));--outer layer to have query results middle-level query results to non-empty inner query results to be empty = At least one course is not selected/*Check the names of students who have not been selected in all classes*/SELECTsname fromStudentWHERE  not EXISTS(SELECT *                   fromCourseWHERE  EXISTS(SELECT *                                  fromSCWHERESno=Student.sno andCno=course.cno));--outer layer to have query results middle-level query results to empty inner query results to be empty = All classes are not selected/*Check the name of the student who has chosen at least one course*/SELECTsname fromStudentWHERE  EXISTS(SELECT *                fromCourseWHERE  EXISTS(SELECT *                               fromSCWHERESno=Student.sno andCno=course.cno));--outer layer to have query results middle-level query results to non-empty inner query results to be non-null = selected at least one course/*Check the names of students who have selected all classes*/SELECTsname fromStudentWHERE  not EXISTS(SELECT *                   fromCourseWHERE  not EXISTS(SELECT *                                    fromSCWHERESno=Student.sno andCno=course.cno));--outer layer to have query results middle-level query results to empty inner query results to be non-NULL = selected All Classes /*with GROUP by*/SELECTsname fromStudent Left JOINSc onStudent.sno=Sc.snoGROUP  bySname having COUNT(CNO)<=(SELECT COUNT(CNO) fromCourse)-1; SELECTsname fromStudent Left JOINSc onStudent.sno=Sc.snoGROUP  bySname having COUNT(CNO)= 0;SELECTsname fromStudent Left JOINSc onStudent.sno=Sc.snoGROUP  bySname having COUNT(CNO)>= 1;SELECTsname fromStudent Left JOINSc onStudent.sno=Sc.snoGROUP  bySname having COUNT(CNO)=(SELECT COUNT(CNO) fromCourse);

/*Enquiry at least 201215122 elective courses for students*/SELECT DISTINCTSno fromSC xWHERE  not EXISTS(SELECT *                    fromSC YWHEREY.sno=201215122  and  not EXISTS(SELECT *                                                        fromSC ZWHEREZ.sno=X.sno andZ.cno=y.cno));--outer layer to have query results middle query results to empty inner query results to be non-null = at least 201215122 elective coursesSELECTCNO intoCopy fromSCWHERESno=201215122;--Copy the 201215122 selected class number to the Copy tableSELECT DISTINCTSno fromCopy Left OUTER JOINSc onSc.cno=Copy.cno--use copy table as all rowsGROUP  bySno having COUNT(SC.CNO)=(SELECT COUNT(CNO)--sort only select the Sno of one or several courses in the Copy table                        fromcopy); 

EXISTS and not EXISTS in SQL

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.