Subquery: Using EXIST Operator : Exists « Select Clause « SQL / MySQL





Subquery: Using EXIST Operator

  
/*
mysql> SELECT StudentID, Name FROM Student s
    -> WHERE EXISTS (
    ->    SELECT StudentID FROM StudentExam e
    ->    WHERE Mark < 40 AND e.StudentID = s.StudentID);
+-----------+----------+
| StudentID | Name     |
+-----------+----------+
|         2 | Henry Al |
|         5 | Sam Jun  |
+-----------+----------+
2 rows in set (0.02 sec)


*/
Drop table Student;
Drop table StudentExam;
Drop table Exam;

CREATE TABLE Student (
   StudentID INT NOT NULL PRIMARY KEY,
   Name      VARCHAR(50) NOT NULL
)TYPE = InnoDB;


CREATE TABLE StudentExam (
   StudentID  INT NOT NULL,
   ExamID     INT NOT NULL,
   Mark       INT,
   IfPassed   SMALLINT,
   Comments   VARCHAR(255)
)TYPE = InnoDB;


CREATE TABLE Exam (
   ExamID      INT NOT NULL PRIMARY KEY,
   CourseID    INT NOT NULL,
   ProfessorID INT NOT NULL,
   SustainedOn DATE,
   Comments    VARCHAR(255),

   INDEX       examcourse_index(CourseID),
   CONSTRAINT  FK_ExamCourse FOREIGN KEY (CourseID)
               REFERENCES Course(CourseID),

   INDEX       examprof_index(ProfessorID),
   CONSTRAINT  FK_ExamProf FOREIGN KEY (ProfessorID)
               REFERENCES Professor(ProfessorID)
)TYPE = InnoDB;


INSERT INTO Student (StudentID,Name) VALUES (1,'Joe Wang');
INSERT INTO Student (StudentID,Name) VALUES (2,'Henry Al');
INSERT INTO Student (StudentID,Name) VALUES (3,'Amma Zee');
INSERT INTO Student (StudentID,Name) VALUES (4,'Lili Lee');
INSERT INTO Student (StudentID,Name) VALUES (5,'Sam Jun');
INSERT INTO Student (StudentID,Name) VALUES (6,'Dianna Wang');

INSERT INTO Exam (ExamID,CourseID,ProfessorID,SustainedOn,Comments) VALUES (1,1,1,'2003-03-12','A difficult test that should last an hour');
INSERT INTO Exam (ExamID,CourseID,ProfessorID,SustainedOn,Comments) VALUES (2,2,1,'2003-03-13','A simple two hour test');
INSERT INTO Exam (ExamID,CourseID,ProfessorID,SustainedOn,Comments) VALUES (3,3,2,'2003-03-11','1 hour long');
INSERT INTO Exam (ExamID,CourseID,ProfessorID,SustainedOn) VALUES (4,4,3,'2003-03-18');
INSERT INTO Exam (ExamID,CourseID,ProfessorID,SustainedOn,Comments) VALUES (5,5,2,'2003-03-19','2 hours long');
INSERT INTO Exam (ExamID,CourseID,ProfessorID,SustainedOn) VALUES (6,6,3,'2003-03-25');

INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,1,55,1,'Satisfactory');
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,2,73,1,'Good result');
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (2,3,44,1,'Scraped through');
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (2,5,39,0,'Failed, and will need to retake this one later in the year');
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed) VALUES (2,6,63,1);
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (3,4,78,1,'Excellent result');
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (3,7,82,1,'Great result!');
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (4,8,65,1,'Adequate performance');
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (4,11,72,1,'Good result');
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (4,5,53,1,'Below expectations');
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (5,1,26,0,'Very poor performance. Recommend this student drop this module');
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (5,9,68,1,'Good result');
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (5,2,62,1,'Good result');
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (6,3,78,1,'Excellent work');
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed) VALUES (6,6,69,1);
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (6,10,58,1,'Adequate performance');



SELECT StudentID, Name FROM Student s
WHERE EXISTS (
   SELECT StudentID FROM StudentExam e
   WHERE Mark < 40 AND e.StudentID = s.StudentID);

           
         
    
  










Related examples in the same category

1.Use the NOT EXISTS operator in the same way as the EXISTS operator, only the opposite results are returned.
2.Sub query with EXISTS command
3.Sub query with NOT EXISTS command
4.Subquery with EXISTS
5.Exists and subquery
6.Not exists and subquery of constant value