CODEWITHSUNDEEP
sql
Ahmy
Ahmy

Reputation: 5480

Subquery VS join with respect to performance

Which is better in performance [Subquery] or [join]?

I have 3 tables related to each other, and i need to select data from one table that has some fields related to the other 2 tables, which one from the following 2 SQL statements is better from the view of performance :

select Table1.City, Table1.State, Table2.Name, Table1.Code, Table3.ClassName 
from Table1 inner join Table2 on Table1.EmpId = Table2.Id inner join Table3
on Table1.ClassId = Table3.Id where Table.Active = 1

OR 

select City, State, (select Name from Table2 where Id = Table1.EmpId) as Name, Code,
(select ClassName from Table3 where Id = Table1.ClassId) as ClassName from Table1
where Active = 1

I have tried the execution plan but its statistics is not expressive to me because the current data is a test data not real one, so i can't imagine the amount of data when tables are live of course they will be more than the test one.

Note : The Id field in Table2 and Table3 is primary key

Thanks in advance

Upvotes: 3

Views: 12540

Answers (2)

RubbelDeCatc
RubbelDeCatc

Reputation: 755

I use subqueries often if I expect large joins with big tables or many joins.

Especially with left joins it can happen that the query exceeds the size of the join cache.

Upvotes: 0

Ilya Saunkin
Ilya Saunkin

Reputation: 19810

The first approach, with joins, is by far faster. In second the query will be executed for each row. Some databases optimize nested queries into joins though.

Join vs. sub-query

Upvotes: 2

Related Questions