Hubert Lubaczewski makes a comparison:
Every now and then I see something like this:
SELECT u.* FROM users u WHERE 0 = (SELECT COUNT(*) FROM addresses a WHERE a.user_id = u.id);and it kinda pains me. So figured, I’ll write about what is the problem with it, and how to avoid such constructs.
Read on to understand what the problem is and how you can fix it.
I’m surprised you’d link to someone falling for the “sub selects are bad” meme.
He didn’t fall for that trap. There was a commenter who did but I think Hubert’s response was totally fair: don’t avoid sub-selects out of some misguided notion that they’re always worse, and check the performance of multiple options (with and without sub-selects) given your specific data distribution.
That also does not matter. It’s all a semi join regardless, e.g. https://www.brentozar.com/pastetheplan/?id=HJPM8IjX1l
Very interesting. I didn’t know SQL Server had that optimization, so you win this round, Gadget. But next time, next time!
Now it makes me want to test this out in Postgres to see if its optimizer doesn’t have that particular optimization rule.
I dug into this a little further and it turns out that Postgres 17.2 does not have the same optimization that exists in SQL Server. A simple repro:
CREATE TABLE Product (
ProductID SERIAL PRIMARY KEY,
SomeNum INT
);
INSERT INTO Product(SomeNum) SELECT generate_series(0, 100);
CREATE TABLE Sales (
SalesID SERIAL PRIMARY KEY,
ProductID INT
);
INSERT INTO Sales(ProductID) SELECT generate_series(0, 10000) % 100;
EXPLAIN
SELECT COUNT(*) FROM Product p
WHERE NOT EXISTS (SELECT * FROM Sales s WHERE s.ProductID = p.ProductID);
EXPLAIN
SELECT COUNT(*) FROM Product p
WHERE 0 = (SELECT COUNT(*) FROM Sales s WHERE s.ProductID = p.ProductID);
—
In the first case, you get the Hash Right Anti-Join that you’d expect, and that we see in SQL Server for both cases. But in the latter, it performs a sequence scan that is considerably more expensive. Which means that the advice is good for Postgres but not SQL Server. My mistake was in not being aware of the optimization for SQL Server, and so I didn’t call out that this was a Postgres-specific note. I do appreciate you showing a bit of SQL Server superiority.