Sub queries or joins?

Whats better in terms of SQL server efficiency; to use sub queries or joins?

I know uncorrelated are better than correlated sub-queries. But what about joins?

The SQL becomes more readable and understandable using joins

OUTER JOIN and check for NULLS

but is it worse or better for performance of the DB?

Taking your examples, these are normally different queries if c2 is a child of c1 (a.k.a many rows in c2 per row in c1). Which is the usual case unless you only have 1:1 relationships.

select c1 from t1 join t2 on c1 = c2

select c1 from t1 where c1 in (select c2 from t2)

• The first one will give a row for each match

• The second gives unique c1 values only.

To make them the same you'd need DISTINCT on the first one: which doesn't make it more readable.

Going further, the 2nd one can be expressed as

select c1 from t1
INTERSECT
select c2 from t2

select c1 from t1 where EXISTS (select * from t2 WHERE t1.c1 = t2.c2))

That is: IN, EXISTS and INTERSECT give the same results.

Now, we may as well cover the inverse case too since you asked about LEFT JOINs. Here we have NOT IN, NOT EXISTS, LEFT JOIN and EXCEPT.

Let's say we want rows from t1 with no rows in t2 where t2.c3 = 'foo'

select c1 from t1 left join t2 on t1.c1 = t2.c2 AND t2.c3 = 'foo'
WHERE t2.c2 IS NULL

select c1 from t1 where c1 NOT in (select c2 from t2 where t2.c3 = 'foo')

select c1 from t1
EXCEPT
select c2 from t2 where t2.c3 = 'foo'

select c1 from t1 where NOT EXISTS
        (select * from t2 WHERE t1.c1 = t2.c2 AND t2.c3 = 'foo')

In this case, only EXCEPT and NOT EXISTS are always correct. NOT IN will fail in t2.c2 is ever NULL. And you need DISTINCT in the LEFT JOIN.

Note also that the LEFT JOIN filter is in the ON clause. To remove this you'd need a derived table or a CTE.

I would also say that the subqueries these days don't matter too much given the sophistication of the query optimisers (unless abused of course)

Personally I almost always use EXISTS and NOT EXISTS (maybe INTERSECT or EXCEPT for clarity) so my code is consistent and works in every case. I don't have to worry about bollixing a LEFT JOIN into an INNER JOIN, or NULLs in an NOT IN.

StackExchange Database Administrators Q#1650, answer score: 6