Subquery VS join with respect to performance

Question

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

score 2 · Accepted Answer · edited May 23 '17 at 12:26

2

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

edited May 23 '17 at 12:26

Community

1
1

answered Mar 10 '11 at 09:31

Ilya Saunkin

18,934
9
36
50

but what about if records in Table2 are little roughly up to 40 records at maximum and records in Table1 is increased by the time is the join will be better ? – Ahmy Mar 10 '11 at 09:33
inner join for small tables is ultra fast in most DBMS – Ilya Saunkin Mar 10 '11 at 09:37
@Elijah Saounkine but Table1 and Table2 are not small they are increased by the time tremendously so which SQL statement is better? – Ahmy Mar 10 '11 at 09:40
1

You just said that Table2 is 40 records at most. Anyway, join is faster. Especially when you join on indexes. It's highly unlikely that a nested query outperforms a join at all! – Ilya Saunkin Mar 10 '11 at 09:42
It's possible that some DBMS will translate your second query by your first query, so... – Cyril Gandon Mar 10 '11 at 09:44
@Elijah Saounkine sorry i mean Table1 and Table3 @Scorpi0 i am using SQL2008 and how can this translation be done? – Ahmy Mar 10 '11 at 09:46
He probably means the optimization that database does on your query, but I wouldn't count on that and just go with the joins. – Ilya Saunkin Mar 10 '11 at 09:53

score 0 · Answer 2 · answered Jan 26 '18 at 19:54

0

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.

answered Jan 26 '18 at 19:54

RubbelDeCatc

747
1
8
10

Subquery VS join with respect to performance

2 Answers2