sql server - Using 'IN' with a sub-query in SQL Statements

Question

Are there any performance issues of using "IN" keyword in SQL statements in places where we can use JOIN?

SELECT xxx
FROM xxx
WHERE ID IN (SELECT Id FROM xxx)

Answer 1

No, it's OK to use.

You can write the query above using IN, EXISTS in all RDBMS, some also support INTERSECT.

Semantically this is a semi-join which "give me rows from table A where I have a at least one match in tableB". An INNER JOIN is "give me all matching rows"

So if TableA has 3 rows and TableB has 5 rows that match:

• an INNER JOIN is 15 rows
• a semi-join is 3 rows

This is why IN and EXISTS are pushed by me and the other SQL types here: a JOIN is wrong, requires DISTINCT and will be slower.

EXISTS support multiple column JOINs, IN doesn't in SQL Server (it does in others).