purushottam2 (9/16/2012)

---

I want to know which query is more optimized?

SELECT 1 FROM ABC WHERE Id IN (SELECT Id FROM ABC1)

===================================================

SELECT 1 FROM ABC a JOIN ABC1 b ON a.Id = b.Id

Thanks
Puru

Robin Sasson (9/17/2012)

---

Its also depends on whether the columns you are joining on are indexed.
Indexing and performance is a large topic in it's own right.
Too many indexes on a large table may slow down INSERT, UPDATE and DELETE whilst giving improvement to SELECT.

Where possible, I prefer JOINS from a readability point of view.

Grant Fritchey (9/17/2012)

---

Thing is, those are two different queries. The IN statement can act to filter the results of ABC, but none of the columns from ABC1 are available in the SELECT or WHERE clause of the statement using IN. The JOIN statement is combining two tables, so you'll get more data returned. So we're actually not comparing the same things here. These are not equivalent statements and result sets that can be realistically compared, one to the other.

yuvipoy (9/17/2012)

---

An inner join between two tables does a complete join, it checks for matches and returns rows. This means, if there are multiple matching rows in the second table, multiple rows will be returned. Also, when two tables are joined, columns can be returned from either.

With an IN, what is done is a semi-join, a join that checks for matches but does not return rows. This means if there are multiple matching tables in the resultset used for the IN, it doesn’t matter. Only one row from the first table will be returned. Also, because the rows are not returned, columns from the table referenced in the IN cannot be returned.


Thanks!