25th Jun 2024 10 minutes read The SQL EXISTS Operator Ignacio L. Bisso learn sql sql basics Table of Contents EXISTS Operator Syntax Example Database: Luxury Boats and Cars Examples of the SQL EXISTS Operator Example 1: Finding Products Sold Example 2: Using the NOT EXISTS Operator Example 3: Using EXISTS with NOT EXISTS Practicing the EXISTS Operator Exercise 1: Old Orders Exercise 2: Boat Engine Buyers Exercise 3: Never Bought Continue Expanding Your SQL EXISTS Operator Skills Using the SQL EXISTS clause allows us to create complex queries in a simple way. Learn the pros and cons of the EXISTS operator in this article. In SQL, the EXISTS operator helps us create logical conditions in our queries. Essentially, it checks if there are any rows in a subquery. We’ll show you EXISTS syntax, provide some usage examples, and then give you several exercises to practice on. If you’re just getting started with SQL or need a SQL refresher, I recommend the course SQL Basics. It includes SQL topics ranging from basic SELECTs to complex topics like joins, aggregations, and subqueries. It’s a great way to build, refresh, or expand your SQL skills. EXISTS Operator Syntax You’re probably familiar with SQL operators like =, >, <, and LIKE. All these operators can be used to create logical conditions that will return TRUE or FALSE. Common condition examples include: WHERE Employee_id = 10345 WHERE Price < 1000 WHERE Name LIKE ‘John%’ The operators =, <, and LIKE compare two elements or operands. This is the reason why they are called binary operators. WHERE EXISTS ( subquery ) Now let’s see the entire SQL query to determine where the EXISTS condition can be placed: SELECT columns FROM table1 WHERE EXISTS (SELECT columns FROM table2); The EXISTS operator is used to create boolean conditions to verify if a subquery returns row(s) or an empty set. When its subquery returns at least one row, EXISTS returns TRUE. It doesn’t matter how many rows are returned or how many columns are in the subquery SELECT list. Only when the subquery returns 0 rows does EXISTS return FALSE. It is important to note that none of the rows returned by the subquery are shown in the final result. Example Database: Luxury Boats and Cars Let’s examine several example queries based on a sample database of a company that sells luxury cars and boats. The company keeps track of the cars and boats in the table product, which has the columns product_id, product_name, product_price, product_type. product_idproduct_nameproduct_priceproduct_type 100Ferrari F203000000Car 101Lamborghini AX3600000Car 102Pagani Zonda4300000Car 200VanDutch 582100000Boat 201Lamborghini B93400000Boat 202VanDutch 561800000Boat 300Boat Engine Yamei 1001000000Boat The database has also a Client table with the columns client_id, client_name and country. client_idclient_namecountry 10John F.United States 11Samid A,Kuwait 12Majal H.Brunei 13Pierre B.France 14Abdul E.Kuwait Finally, there is the sale table with the columns product_id, client_id, sale_date. (For simplicity’s sake, I omit the idea of having a table for purchase orders; in this kind of company, one customer normally buys just one product at a time.) client_idproduct_idcountry 102002020-03-05 101012024-04-05 112022023-03-05 121022021-03-07 121002023-03-05 122022024-04-09 133002022-03-05 103002020-07-19 133002023-11-25 Examples of the SQL EXISTS Operator Example 1: Finding Products Sold For the first example query, suppose the marketing manager wants to know which cars and boats were sold between April 1 and April 15 of 2024. The query below does that: SELECT product_name FROM product p1 WHERE EXISTS ( SELECT * FROM sale s WHERE s.product_id = p1.product_id AND s.sale_date >= ‘2024-04-01’ AND s.sale_date <= ‘2024-04-15’ ) Results: Product_name Lamborghini AX VanDutch 56 There are other ways to write this query without using the EXISTS operator. However, in order to explain how the EXISTS operator works, this is a good entry-level example. The main SELECT is very easy; it goes to the table product to obtain the product_name column. However we don’t want all the product_names in the table. We want only those products which returns TRUE for the following condition: WHERE EXISTS ( SELECT * FROM sale s WHERE s.product_id = p1.product_id AND s.sale_date >= ‘2024-04-01’ AND s.sale_date <= ‘2024-04-15’ ) If we analyze the condition, the subquery returns all the sales records for a given product (p1.product_id) and for a specific time period (2024-04-01 to 2024-04-15). If the subquery returns records, then EXISTS will return TRUE. If the subquery returns no records, then EXISTS will return FALSE. Note that the subquery is executed many times – once for each row read by the external query. These kinds of subqueries that are executed one time for each row processed in the main query are called “correlated subqueries” and they have their particularities. If you want to go deeper on this topic, I suggest the article Correlated Subquery in SQL: A Beginner’s Guide. Example 2: Using the NOT EXISTS Operator Now suppose the manager wants the names of the vehicles that didn’t sell during the same period. This type of query – where we search for records that do not exist in the database – is excellent for the NOT EXISTS operator. After all, we are searching for elements that do not exist in the database. The query is as follows: SELECT * FROM product p1 WHERE p1.product_type = ‘Car’ AND NOT EXISTS ( SELECT * FROM sale s WHERE s.product_id = p1.product_id AND s.sale_date >= ‘2024-04-01’ AND s.sale_date <= ‘2024-04-15’ ) Results: Product_idProduct_nameProduct_priceProduct_type 100Ferrari F203000000Car 102Pagani Zonda4300000Car Once we understand how the EXISTS operator works in SQL, understanding NOT EXISTS is very simple; it’s the opposite. If EXISTS returns TRUE, then NOT EXISTS returns FALSE and vice versa. In this example, the main query has a WHERE clause with two conditions. The first condition is to ask for products of the type ‘vehicle’. The second condition uses NOT EXISTS to keep only the products which did not have sales during the period 2024-04-01 to 2024-04-15. In other words, we look for products for which the subquery returns an empty result set – i.e. for which there are no sales in that period. In some cases, we can solve this kind of query using NOT IN with a subquery. In this particular query, we can put in this WHERE clause: WHERE p1.product_type = ‘Car’ AND p1.product_id NOT IN (SELECT s.product_id FROM sale s WHERE s.sale_date >= ‘2024-04-01’ AND s.sale_date <= ‘2024-04-15’ ) However, there is a difference in the way the query is executed internally in the database. The NOT IN subquery condition is executed once, while the NOT EXISTS subquery condition is executed one time per each row. That’s right; NOT EXISTS is a correlated subquery. Read the articles 5 SQL Subquery Examples and SQL IN Operator for more on these operators. Example 3: Using EXISTS with NOT EXISTS Next, we need a list of those clients who didn’t buy a boat during the summer 2023 but did buy a boat during the preceding winter (i.e. December 2022 to March 2023). The query to obtain that report is: SELECT * FROM client c1 WHERE EXISTS ( SELECT * FROM sale s1 JOIN product p1 ON p1.product_id = s1.product_id WHERE c1.client_id = s1.client_id AND p1.product_type = 'Boat' AND s1.sale_date >= '2022-12-21' AND s1.sale_date <= '2023-03-20' -- winter ) AND NOT EXISTS ( SELECT * FROM sale s2 JOIN product p1 ON p1.product_id = s2.product_id WHERE c1.client_id = s2.client_id AND p1.product_type = 'Boat' AND s2.sale_date >= '2023-6-21' AND s2.sale_date <= '2023-09-20' -- summer ) ; Results: client_idClient_nameCountry 11Samid A.Kuwait After the previous two examples, this example should not be too difficult to understand; it is the combination of both. The idea is to select the entire record (SELECT *) from the client table and then use an EXISTS to check that a boat has been purchased last winter. After that, we use NOT EXISTS to check that a boat has not been purchased in the past summer. Note that both subqueries have a JOIN between the sale and product tables because we need to use the columns product_type and sale_date in the WHERE conditions. At this point, I would like to clarify something about correlated subqueries. We’ve mentioned that correlated subqueries are executed one time per each row candidate. This fact can impact the performance of the whole query, especially when we work with large tables. In summary, the EXISTS operator (and correlated subqueries) is a powerful SQL resource for certain kinds of queries. However, we should avoid correlated subqueries if we are working with big tables. Practicing the EXISTS Operator As many other computer languages, learning SQL by doing exercises is the one of the best ways to gain skills. So in this section, I will show three EXISTS operator exercises of different complexities. Exercise 1: Old Orders Exercise: The owner of the company wants to know which products (cars or boats) were not ordered in the last 365 days. Solution: SELECT p1.product_name FROM product p1 WHERE NOT EXISTS ( SELECT 1 FROM sale s WHERE s.product_id = p1.product_id AND s.sale_date >= CURRENT_DATE - 365 ); Results: Product_name Ferrari F20 Pagani Zonda Lamborghini B9 VanDutch 58 Explanation: The main query uses the table product to obtain the product_name column. The WHERE clause of this query is the key part. It has a NOT EXISTS condition which evaluates to TRUE for those products that have not been sold in the last year. The condition s.sale_date >= CURRENT_DATE - 365 in the subquery obtains all the rows of the last year. Note the subquery returns a constant ( 1 ). This is because the important fact about the subquery is how many rows it returns, not the content of the returned rows; thus, we can put a column or a constant like 1. Exercise 2: Boat Engine Buyers Exercise: Our company is famous for manufacturing an engine for boats and we have some clients who only buy this product. The marketing department wants to identify clients who only buy boat engines so they can target these clients in a marketing campaign. Solution: SELECT * FROM client c1 WHERE EXISTS ( SELECT * FROM sale s1 -- they buy boat engines JOIN product p1 ON p1.product_id = s1.product_id WHERE c1.client_id = s1.client_id AND p1.product_name = 'Boat engine' ) AND NOT EXISTS ( SELECT * FROM sale s2 -- they never buy other product JOIN product p2 ON p2.product_id = s2.product_id WHERE c1.client_id = s2.client_id AND p2.product_name <> 'Boat engine' ); Results: client_idClient_nameCountry 13Pierre B.France 14Abdul E.Kuwait Explanation: This exercise has a WHERE clause with two conditions. The first condition uses an EXISTS operator to verify that the customer selected by the outer query has purchased a boat engine. The second WHERE condition uses NOT EXISTS to verify that the same customer (the customer selected by the outer query) has never purchased any other type of product. If you want to explore more subquery exercises, I recommend the article SQL Subquery Practice: 15 Exercises with Solutions. Exercise 3: Never Bought Exercise: The owner of the company wants a Never Bought report. The report should have only two columns: client_name and product_name. The content of the report should be the complement of the sale table. In other words, if a client never bought a product, then the pair formed by this client_name and this product_name must be in the report. Solution: There are two approaches to solving this exercise: Using NOT EXISTS. Using the set operator EXCEPT (or MINUS). We’ll show both approaches. Solution using NOT EXISTS: SELECT c1.client_name, p1.product_name FROM client c1, product p1 WHERE NOT EXISTS ( SELECT 1 FROM sale s WHERE c1.client_id = s.client_id AND p1.product_id = s.product_id ) Partial Query Results: Client_nameProduct_name John F.Ferrari F20 John F.Pagani Zonda John F.Lamborghini B9 John F.VanDutch 56 Samid A.Lamborghini AX Samid A.Pagani Zonda Samid A.VanDutch 56 Samid A.Lamborghini B9 Samid A.Boat Engine Yamei 100 Samid A.Ferrari F20 Explanation: The first point to understand is the result of this part of the query: SELECT c1.client_name, p1.product_name FROM client c1, product p1 As we are not properly joining the tables client and product, the result is a cartesian product – a set of all the possible <client_name, product_name> pairs. Once we have all the possible pairs, we proceed to discard those pairs in the sale table using the NOT EXISTS operator. Solution using EXCEPT: SELECT c1.client_name, p1.product_name FROM client c1, product p1 EXCEPT SELECT client_name, product_name FROM sale s JOIN product p ON p.product_id = s.product_id JOIN client c ON c.client_id = s.client_id Explanation: The first part of this approach is similar to the previous solution; we create all possible client–product pairs. Then, using the EXCEPT operator, we remove the pairs which are in the sale table. This obtains the pairs we are looking for. Here’s the difference: In the second part of the EXCEPT, we need to JOIN the sale table with the product and client tables. Continue Expanding Your SQL EXISTS Operator Skills In this article, we covered the EXISTS and NOT EXISTS operators with several examples. We also explained the pros and cons of correlated subqueries. For readers who want to increase their SQL skills, I recommend the SQL Practice track. You will find hundreds of exercises on SQL topics like JOINs, GROUP BY, HAVING, subqueries and even the EXISTS operator. If you are just getting started with SQL or need a refresher, I recommend our SQL Basics course. When you increase your SQL skills, you invest in you! Tags: learn sql sql basics