EXISTS and not EXISTS in SQL

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