CODEWITHSUNDEEP
sqlsql-serverperformanceselectinner-join
asmgx
asmgx

Reputation: 8034

Difference between using IN SELECT and INNER JOIN

I want to know if there is performance difference in using 

SELECT Table1.*
FROM Table1
WHERE Col1 IN (SELECT Col2 FROM Table2)

AND

SELECT Table1.*
FROM Table1 INNER JOIN Table2
ON Table1.Col1 = Table2.Col2

Upvotes: 1

Views: 168

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

The two do different things. The in version will not return duplicates. The inner join does return duplicates.

Often, the inner join version has better performance, although that depends on the data and the database.

I would generally use exists:

SELECT t1.*
FROM Table1 t1
WHERE EXISTS (SELECT FROM Table2 t2 WHERE t2.Col2 = t1.col1);

This can directly take advantage of an index on table2(col2).

Upvotes: 3

KumarHarsh
KumarHarsh

Reputation: 5094

SELECT Table1.*
FROM Table1
WHERE Col1 IN (SELECT Col2 FROM Table2)

In this query, I cannot use any column of table2 in my result set.

If I need column of both table then I will use INNER JOIN.

SELECT Table1.*
FROM Table1 INNER JOIN Table2
ON Table1.Col1 = Table2.Col2

In one to one relationship and as per requirement INNER JOIN will perform better.

Upvotes: 0

chaunv
chaunv

Reputation: 843

Usually joins will work faster than subquery. But sometimes the performance may depend on the data in the tables or other factors.

You can write your SQL by JOIN or SUBQUERY, SQL Server will always transform it on an execution plan.

You can see more: https://www.essentialsql.com/what-is-the-difference-between-a-join-and-subquery/

Upvotes: 1

Related Questions