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 |
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 |