In PostgreSQL, the EXISTS operator is used to test for the existence of rose in a subquery.It is generally used with correlated subqueries. If the subquery returns at least one row, the result of EXISTS is true. In case the subquery returns no row, the result is of EXISTS is false.
Syntax: EXISTS (subquery)
For the sake of this article we will be using the sample DVD rental database, which is explained here and can be downloaded by clicking on this link in our examples.
Here we will query for customers who have at least one payment whose amount is greater than 9 USD using the “customer” and “payment” tables of our sample database.
SELECT first_name, last_name FROM customer c WHERE EXISTS (SELECT 1 FROM payment p WHERE p.customer_id = c.customer_id AND amount > 9 ) ORDER BY first_name, last_name;
Here we will query for films that are not available in the inventory using the “film” and “inventory” tables of our sample database.
SELECT title FROM film f WHERE NOT EXISTS (SELECT 1 FROM inventory i WHERE f.film_id = i.film_id ) ORDER BY title;
- PostgreSQL - NOT IN operator
- PostgreSQL - IN operator
- PostgreSQL - NOT LIKE operator
- PostgreSQL - ILIKE operator
- PostgreSQL - IS NULL operator
- PostgreSQL - LIKE operator
- PostgreSQL - NOT BETWEEN operator
- PostgreSQL - BETWEEN operator
- PostgreSQL - UNION operator
- PostgreSQL - INTERSECT Operator
- PostgreSQL - EXCEPT Operator
- PostgreSQL - ANY Operator
- PostgreSQL - SOME Operator
- PostgreSQL - ALL Operator
- PostgreSQL - Create Database
- PostgreSQL - COALESCE
- PostgreSQL - CASE Statement
- PostgreSQL - IF Statement
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to firstname.lastname@example.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.