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

Home
SQL / MySQL
1.Aggregate Functions
2.Backup Load
3.Command MySQL
4.Cursor
5.Data Type
6.Database
7.Date Time
8.Engine
9.Event
10.Flow Control
11.FullText Search
12.Function
13.Geometric
14.I18N
15.Insert Delete Update
16.Join
17.Key
18.Math
19.Procedure Function
20.Regular Expression
21.Select Clause
22.String
23.Table Index
24.Transaction
25.Trigger
26.User Permission
27.View
28.Where Clause
29.XML
SQL / MySQL » Select Clause » Exists 
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(50NOT 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,NameVALUES (1,'Joe Wang');
INSERT INTO Student (StudentID,NameVALUES (2,'Henry Al');
INSERT INTO Student (StudentID,NameVALUES (3,'Amma Zee');
INSERT INTO Student (StudentID,NameVALUES (4,'Lili Lee');
INSERT INTO Student (StudentID,NameVALUES (5,'Sam Jun');
INSERT INTO Student (StudentID,NameVALUES (6,'Dianna Wang');

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

INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,CommentsVALUES (1,1,55,1,'Satisfactory');
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,CommentsVALUES (1,2,73,1,'Good result');
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,CommentsVALUES (2,3,44,1,'Scraped through');
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,CommentsVALUES (2,5,39,0,'Failed, and will need to retake this one later in the year');
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassedVALUES (2,6,63,1);
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,CommentsVALUES (3,4,78,1,'Excellent result');
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,CommentsVALUES (3,7,82,1,'Great result!');
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,CommentsVALUES (4,8,65,1,'Adequate performance');
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,CommentsVALUES (4,11,72,1,'Good result');
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,CommentsVALUES (4,5,53,1,'Below expectations');
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,CommentsVALUES (5,1,26,0,'Very poor performance. Recommend this student drop this module');
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,CommentsVALUES (5,9,68,1,'Good result');
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,CommentsVALUES (5,2,62,1,'Good result');
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,CommentsVALUES (6,3,78,1,'Excellent work');
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassedVALUES (6,6,69,1);
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,CommentsVALUES (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
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.