|
There are two other keywords that can be used in a similar way: EXISTS and NOT EXISTS.
The SQL EXISTS Keyword
The SQL EXISTS keyword is used to check if at least one value is found in a subquery.
It doesn’t work with a literal list of values like the IN keyword does. So this clause won’t work: WHERE column EXISTS (value1, value2). It needs to be a subquery.
WHERE EXISTS (subquery);
The subquery is a SELECT query that aims to return at least one value.
If the subquery returns at least one value, EXISTS evaluates to TRUE and the record is shown.
If the subquery returns no values, then EXISTS evaluates to FALSE and the record is not shown.
Let’s see an example.
SELECT id, first_name, last_name
FROM customer
WHERE EXISTS (
SELECT first_name
FROM common_names
WHERE customer.first_name = common_names.first_name
);
For each of the records in the customer table, this will find all of the first_name values from the common_names table that match the customer’s first name.
It performs the same kind of logic as the IN keyword.
But there are some differences – which I’ll explain shortly.
The SQL NOT EXISTS Keyword
The NOT EXISTS keyword is used to see if a value is not returned by a subquery.
The NOT EXISTS will check the results from a subquery, and return TRUE if no results are found in the subquery. It’s the opposite of EXISTS.
WHERE NOT EXISTS (subquery)
For example:
SELECT id, first_name, last_name
FROM customer
WHERE NOT EXISTS (
SELECT first_name
FROM common_names
WHERE customer.first_name = common_names.first_name
);
This query will find all customer rows where the first_name is not in the common_names table.
What’s the Difference Between EXISTS, IN, and a Join?
So we’ve taken a look at both the IN and the EXISTS keywords, and their negative versions (using the NOT keyword).
What are the differences between IN and EXISTS?
- IN can be run with specific values or a subquery. EXISTS must use a subquery.
- IN checks all values for a match. EXISTS only checks if at least one value is returned.
The EXISTS keyword is intended as a way to avoid checking all of the data and avoid counting.
SELECT id, first_name, address_state
FROM customer
WHERE EXISTS (
SELECT order_id
FROM order
WHERE order.customer_id = customer.id
);
This will find customers that have at least one order. It is likely to be more efficient than this query, as this query needs to check all of the records:
SELECT id, first_name, address_state
FROM customer
WHERE id IN (
SELECT customer_id
FROM order
);
IN is also better if you have a specific list to use, as you can just enter those values and not use an EXISTS with a subquery.
SELECT id, first_name, address_state
FROM customer
WHERE first_name IN ('John', 'Mary', 'Susan');
What about with a join? The example we saw earlier checks if first name values match between two tables, which sounds like a join.
SELECT id, first_name, last_name
FROM customer
INNER JOIN common_names ON customer.first_name = common_names.first_name;
This should return the same results as the IN query. Depending on your database, they should also evaluate to the same process and plan internally.
Why Do Queries SELECT Literal Values with EXISTS?
Have you ever seen a query like this?
SELECT id, first_name, address_state
FROM customer
WHERE EXISTS (
SELECT 1
FROM order
WHERE order.customer_id = customer.id
AND order_value > 100
);
Notice the SELECT clause in the subquery: it shows SELECT 1.
Why would this be there?
You might have seen this in online examples or in your project’s code.
The reason this is done is to improve the performance and to make the query easier to understand.
Because the subquery in the EXISTS clause only checks to see if at least one record is returned, it doesn’t actually care what value is returned.
Unlike the IN keyword, the value isn’t used. So, you can actually select any column from the table in the subquery.
Or, you can select a literal value like this example. This will select the literal value of 1 instead of a column from the database.
It’s one less thing the database needs to do.
More Information
Here are some more articles on EXISTS:
Copyright de Ben Brumm - DatabaseStar
|
