PostgreSQL: Using where EXISTS clause
When any SQL query is used inside another SQL query then it is called a subquery. The complicated query can be written easily by dividing a large query into multiple subqueries. EXISTS clause is used with a subquery in a SQL statement. The output of EXISTS depends on the numbers of records returned by the subquery but does not depends on the values of the records. The result of EXISTS will be true if the associated subquery returns at least one row. How you can use EXISTS and NOT EXISTS clause in select, insert, update and delete statements are shown in this tutorial using different examples.
EXISTS Clause Syntax:
FROM table_name1
WHERE EXISTS( SELECT 1
FROM
table_name2
WHERE col1 = table_name1.col1);
Create three tables named company, items and customers and insert some data. Run the following select query to show the contents of these tables.
SELECT * from items;
SELECT * from customers;
Example-1: Using EXISTS clause in SELECT query
(a) EXISTS
The following query will retrieve records from items table based on company table. The subquery searches all records from company table where company_id of company table is equal to company_id of items table and the value of phone field is not empty. There is one record in company table that does not have phone number and this record will be omitted from the output.
FROM items
WHERE EXISTS (SELECT 1
FROM company
WHERE items.company_id = company.company_id and company.phone<>'');
Output:
The record of LG has no phone entry in company table. So it is not appeared in the output.
(b)NOT EXISTS
NOT EXISTS clause is opposite of EXISTS clause. The following query will retrieve those records from items table when the subquery returns false.
FROM items
WHERE NOT EXISTS (SELECT 1
FROM company
WHERE items.company_id = company.company_id and company.website_url is NULL);
Output:
There is no record in company table where website_url is NULL. So the output of subquery is false for all records and all records of the items table are retrieved.
Example-2: Using EXISTS clause in INSERT query
According to the following insert query, the data will be inserted in items table when company_id of both company and customers tables are equal.
(company_id)
SELECT company_id
FROM customers
WHERE EXISTS (sELECT 1
FROM company,customers
WHERE customers.company_id = company.company_id);
Output:
There are three entries for company_id in customers table with values are 1 and 2. So the subquery will return true for three times and three records will be inserted.
Run the select query to check the content of items table.
Example-3: Using EXISTS clause in UPDATE query
The following query will update the records of items table where company_id is 1 and the subquery returns true.
SET name='Nokia'
WHERE company_id=1 and EXISTS (SELECT 1
FROM items
WHERE name='Not Assign');
Output:
Here, the subquery will return true for three times and company_id is 1 for three records. Three records will be updated after executing the update query.
Run the select query to check the content of items table.
Example-4: Using EXISTS clause in DELETE query
The following query will delete those records of items table where company_id is 1 and the subquery returns true.
WHERE company_id=1 and EXISTS (SELECT 1
FROM items
WHERE name='Not Assign');
Output:
There are three records with value 1 of company_id and one record has name value ‘Not Assign’. So, the condition will true for three times and delete three records from items table.
Run the select query to check the content of items table.
Hope, you have get a clear idea of using EXISTS clause in SQL query after reading this tutorial.
