RSS Feed

Subqueries in WHERE Clause - Examples

Subqueries in WHERE Clause - Examples

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.