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