What is the difference between EXISTS and IN, and can they ever return different rows?
They ask the same question in two different ways, and for a plain IN they always give the same rows. The difference is in the speed and in how they behave with missing values.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(120) NOT NULL
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
-- These two return the same customers.
SELECT id, name FROM customers
WHERE id IN (SELECT customer_id FROM orders);
SELECT c.id, c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
So how do they differ? Four ways.
The first one is that EXISTS stops at the first match. As soon as it finds one matching order it stops looking and moves on to the next customer. The IN form has to work out the whole list of customer numbers first, and then check each customer against it. For a small list the difference is nothing, and for a large one it can be a great deal.
The second one is that EXISTS can use an index and IN with a subquery sometimes cannot, because the engine has to build the whole list before it can start.
The third one is the missing values. If the subquery on the right of an IN produces a missing value, then x IN (list) gives unknown for every x that does not match, and unknown is not true, so the row is dropped. The EXISTS form has no such problem, because it only asks whether a match was found.
The fourth one is that EXISTS does not care what the subquery selects. You write SELECT 1 because you are not using the value, and the engine can then stop as soon as it finds a row.
So can they ever return different rows? For the positive form, no. They always return the same rows, because both of them are a way of asking "is there at least one match". The difference that bites people is between NOT IN and NOT EXISTS, and those can absolutely return different rows.
The next question in this topic asks for a query that uses both of them.