Subqueries in WHERE Clause

Subqueries in WHERE Clause

Below are examples of subqueries in the WHERE clause of an SQL Server statement.

CREATE TABLE teacher
(
Id int,
Name varchar(255),
Salary int,
Dept varchar(255)
);

insert into teacher values (1, 'A', 100, 'D1') 
insert into teacher values (2, 'B', 50, 'D5')
insert into teacher values (3, 'C', 500, 'D3')
insert into teacher values (4, 'D', 200, 'D7')
insert into teacher values (5, 'E', 400, 'D1')

CREATE TABLE subjects
(
Subject_Id varchar(50),
Title varchar(255),
Time varchar(255),
Instructor_Id int
);

insert into subjects values ('S1', 'Maths', 'Summer', 1)
insert into subjects values ('S1', 'English', 'Winter', 2)
insert into subjects values ('S1', 'Science', 'Summer', 3)
insert into subjects values ('S1', 'Language', 'Spring', 5)

Select * from teacher;
Select * from subjects;

teacher Table

  

    
Id
    
Name
 
    
Salary
    
Dept
  
  

    
1
    
A
    
100
    
D1
  
  

    
2
    
B
    
50
    
D5
  
  

    
3
    
C
    
500
    
D3
  
    

    
4
    
D
    
200
    
D7
  
    

    
5
    
E
    
400
    
D1
  


subjects Table

  

    
Subject_Id
    
Title
 
    
Time
    
Instructor_Id
  
  

    
S1
    
Maths
    
Summer
    
1
  
  

    
S1
    
English
    
Winter
    
2
  
  

    
S1
    
Science
    
Summer
    
3
  
    

    
S1
    
Language
    
Spring
    
5
  

Example 1 of Subqueries in WHERE Clause

SELECT
  Id,
  Name, Salary
FROM
  teacher
WHERE
  Id =
  (
    SELECT Instructor_Id
    FROM Subjects
    WHERE Title = 'Science'
  );


  

    
Id
    
Name
 
    
Salary
  
  

    
3
    
C
    
500
  
  

The subquery finds the Instructor_Id where Title is Science. This Instructor_Id is used by outer query to find the row from teacher table.

Example 2 of Subqueries in WHERE Clause (using NOT IN)

SELECT
  Id,
  Name, Salary
FROM
  teacher
WHERE
  Id NOT IN
  (
    SELECT Instructor_Id
    FROM Subjects
    WHERE Title = 'Science'
  );


  

    
Id
    
Name
 
    
Salary
  
  

    
1
    
A
    
100
  
  

    
2
    
B
    
50
  
  

    
4
    
D
    
200
  
    

    
5
    
E
    
400
  
 

The subquery finds the Instructor_Id where Title is Science. Outer query finds rows from the teacher table where Id is Not 3 (3 is the Instructor_Id returned by subquery).

Example 3 of Subqueries in WHERE Clause (using NOT EXISTS)

SELECT
  Id,
  Name, Salary
FROM
  teacher
WHERE NOT EXISTS
  (
    SELECT Instructor_Id
    FROM Subjects
    WHERE Title = 'Arabic'
  );


  

    
Id
    
Name
 
    
Salary
  
  

    
1
    
A
    
100
  
  

    
2
    
B
    
50
  
  

    
3
    
C
    
500
  
    

    
4
    
D
    
200
  
  

    
5
    
E
    
400
  

NOT EXISTS works like EXISTS, except the WHERE clause in which it is used is satisfied if no rows are returned by the subquery. Same way, no rows are returned by the subquery, satisfying the WHERE clause, in the above example, so the outer query returns all the rows from the teacher table.

In below example, subquery returns a row, therefore outer query does not return any rows because the WHERE clause is not satisfied.

Example 3A of Subqueries in WHERE Clause (using NOT EXISTS)

SELECT
  Id,
  Name, Salary
FROM
  teacher
WHERE NOT EXISTS
  (
    SELECT Instructor_Id
    FROM Subjects
    WHERE Title = 'Language'
  );


  

    
Id
    
Name
 
    
Salary

Example 4 of Subqueries in WHERE Clause (using EXISTS)

SELECT
  Id,
  Name, Salary
FROM
  teacher
WHERE EXISTS
  (
    SELECT Instructor_Id
    FROM Subjects
    WHERE Title = 'Arabic'
  );
  

  

    
Id
    
Name
 
    
Salary

EXISTS specifies a subquery to test for the existence of rows. Example 3 and 3A uses NOT EXISTS and this example 4 and 4A use EXISTS. As with Example 3 and 3A, this example 4 and 4A also shows no records in the result or all records in the result based on whether WHERE clause is satisfied by the subquery or not.

Example 4A of Subqueries in WHERE Clause (using EXISTS)

SELECT
  Id,
  Name, Salary
FROM
  teacher
WHERE EXISTS
  (
    SELECT Instructor_Id
    FROM Subjects
    WHERE Title = 'Language'
  );


  

    
Id
    
Name
 
    
Salary
  
  

    
1
    
A
    
100
  
  

    
2
    
B
    
50
  
  

    
3
    
C
    
500
  
    

    
4
    
D
    
200
  
  

    
5
    
E
    
400
  

Example 5 of Subqueries in WHERE Clause (using ALL)

SELECT
  Id,
  Name, Salary
FROM
  teacher
WHERE
  Id > ALL
  (
    SELECT Instructor_Id
    FROM Subjects
    WHERE Title in ('Maths', 'Science')
  );


  

    
Id
    
Name
 
    
Salary
  
    

    
4
    
D
    
200
  
  

    
5
    
E
    
400
  

>ALL means greater than every value. In other words, it means greater than the maximum value. Subquery returns two Instructor_Ids i.e. 1 and 3. Outer query fetches all the rows with Instructor_Ids greater than 1 and 3 (or you can say greater than 3).

Example 6 of Subqueries in WHERE Clause (using ANY)

SELECT
  Id,
  Name, Salary
FROM
  teacher
WHERE
  Id > ANY
  (
    SELECT Instructor_Id
    FROM Subjects
    WHERE Title in ('Maths', 'Science')
  );


  

    
Id
    
Name
 
    
Salary
  
  

    
2
    
B
    
50
  
  

    
3
    
C
    
500
  
    

    
4
    
D
    
200
  
  

    
5
    
E
    
400
  

>ANY means greater than at least one value, that is, greater than the minimum, hence the above result.

Example 7 of Subqueries in WHERE Clause (using SOME)

SELECT
  Id,
  Name, Salary
FROM
  teacher
WHERE
  Id > SOME
  (
    SELECT Instructor_Id
    FROM Subjects
    WHERE Title in ('Maths', 'Science')
  );


  

    
Id
    
Name
 
    
Salary
  
  

    
2
    
B
    
50
  
  

    
3
    
C
    
500
  
    

    
4
    
D
    
200
  
  

    
5
    
E
    
400
  

SOME and ANY are equivalent.