SQL EXISTS

by

SQL EXISTS is used to test if a subquery returns a row or not. If yes, then the outer query proceeds, if not, the outer query stops and no row returned.

SQL EXISTS Syntax

SELECT column1, column2, ... 
FROM Table1
WHERE EXISTS
  (
    SELECT *
    FROM Table2
    WHERE contion
  )

SQL EXISTS Example

Table: Employees

EmployeeId FirstName LastName Department Salary
203 Mazojys Fxoj Finance 78000
204 Bhkki Hkooia Finance 45800
205 Uisaoo Dfaafk Finance 57000
206 Qidfa Srlkrt Finance 62000
302 Williaa Bdnaa Development 75000
303 Zkiil Errllov Development 55000
304 Uillaw Osk Development 49000

Table: EmpAccess

EmployeeId AccessLevel
203 1
204 2
205 2
206 2
302 1
303 2
304 2

Example 1
We want to select all the employees which AccessLevel is 1:

SELECT EmployeeId, FirstName, LastName
FROM Employees
WHERE EXISTS
  (
      SELECT EmployeeId
      FROM EmpAccess
      WHERE EmployeeId = Employees.EmployeeId
                AND AccessLevel = 1
  )

The result will look like:

EmployeeId FirstName LastName
203 Mazojys Fxoj
302 Williaa Bdnaa

Example 2 (Example of SQL NOT EXISTS)
If we want to select all the employees which AccessLevel is NOT 1:

SELECT EmployeeId, FirstName, LastName
FROM Employees
WHERE NOT EXISTS
  (
      SELECT EmployeeId
      FROM EmpAccess
      WHERE EmployeeId = Employees.EmployeeId
                AND AccessLevel = 1
  )

The result will look like:

EmployeeId FirstName LastName
204 Bhkki Hkooia
205 Uisaoo Dfaafk
206 Qidfa Srlkrt
303 Zkiil Errllov
304 Uillaw Osk

Leave a Comment