SQL Exists Keyword

Posted on Friday, 10 March 2023 11:10 AM

The SQL EXISTS keyword is used to check whether a subquery returns any rows or not. It is often used in combination with a correlated subquery to check for the existence of a certain condition.

Exists Syntax

SELECT column1, column2, …
FROM table1
WHERE EXISTS (
  Subquery
);

For example,

SELECT *
FROM student
WHERE EXISTS (
  SELECT 1
  FROM exam
  WHERE exam.student_id = student.id
);

OUTPUT

In the given example,

Let's say we want to find all the students who have taken at least one exam.

This query first selects all the rows from the student table. Then it checks for the existence of a row in the exam table that has the same student_id as the current row in the student table.

If a row exists, the EXISTS condition is true and the corresponding row from the student table is returned in the result set.

Note that the subquery in the EXISTS clause does not need to return any specific columns. We can use any constant value instead (in this case, 1) because we are only interested in whether the subquery returns any rows or not.

K-Learn