SQL Tutorial SQL Advanced SQL Database SQL References

SQL EXISTS Keyword



The SQL EXISTS Keyword is used to test the existence of records from a subquery. It returns true if the subquery returns one or more records, else returns false.

Syntax

The syntax for using EXISTS keyword is given below:

SELECT column1, column2, column3, ...
FROM table_name
WHERE EXISTS 
(SELECT column_name FROM table_name
WHERE condition);

Example:

Consider a database containing tables called Employee and Contact_Info with the following records:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

Table 2: Contact_Info table

Phone_NumberEmpIDAddressGender
+1-80XXXXX0002XXX, Brooklyn, New York, USAF
+33-14XXXXX013XXX, Grenelle, Paris, FranceM
+31-20XXXXX194XXX, Geuzenveld, Amsterdam, NetherlandsF
+86-10XXXXX4586XXX, Yizhuangzhen, Beijing, ChinaM
+65-67XXXXX47XXX, Yishun, SingaporeM
+81-35XXXXX728XXX, Koto City, Tokyo, JapanM

  • The below SQL statement returns TRUE and records of female employees from Employee table.

    SELECT * FROM Employee
    WHERE EXISTS 
    (SELECT EmpID FROM Contact_Info 
    WHERE Employee.EmpID = Contact_Info.EmpID AND Gender = 'F');
    

    This will produce the result as shown below:

    EmpIDNameCityAgeSalary
    2MarryNew York242750
    4KimAmsterdam303100
  • The below mentioned SQL statement returns TRUE and records of male employees from Employee table.

    SELECT * FROM Employee
    WHERE EXISTS 
    (SELECT EmpID FROM Contact_Info 
    WHERE Employee.EmpID = Contact_Info.EmpID AND Gender = 'M');
    

    This result of above SQL code will be:

    EmpIDNameCityAgeSalary
    3JoParis272800
    6HuangBeijing282800

❮ SQL Keywords

5