SQL EXISTS is use to check the existence of the result of subquery return. SQL EXISTS will test whether the subquery will return at least a single record, and if there is no data returned the operator returns 'FALSE' and it will not return any records with stop the execution.
However, the selected list in SQL EXISTS subquery is not used to execute the outer query and it will list out all the records of table selected as long as subquery return at least a single record.
The SQL syntax for the EXISTS operator is:
SELECT [COLUMN NAME] FROM [TABLE NAME]
WHERE EXISTS (
SELECT [COLUMN NAME] FROM [TABLE NAME]
WHERE [CONDITION])
EXAMPLE :
Table GameScores
PlayerName | Department | Scores |
Jason | IT | 3000 |
Irene | IT | 1500 |
Jane | Marketing | 1000 |
David | Marketing | 2500 |
Paul | HR | 2000 |
James | HR | 2000 |
Table PlayerGender
PlayerName | Gender |
Jason | Male |
Irene | Female |
Jane | Female |
David | Male |
Paul | Male |
James | Male |
SQL statement :
SELECT * FROM GameScores
WHERE EXISTS (SELECT * FROM PlayerGender WHERE Gender ='Male')
Result:
PlayerName | Department | Scores |
Jason | IT | 3000 |
Irene | IT | 1500 |
Jane | Marketing | 1000 |
David | Marketing | 2500 |
Paul | HR | 2000 |
James | HR | 2000 |