CODEWITHSUNDEEP
sql
Roger Clerkwell
Roger Clerkwell

Reputation: 426

Is a Join equivalent to a query with a subquery for a column?

Sorry for the newbie sql questions but isn't this the same thing:

select a.productid, sum(b.qty)
from table1 a
inner join table2 b on b.productid = a.productid
group by a.productid
;

select a.productid
,(select sum(b.qty) from table2 b where b.productid = a.productid)
from table1 a
group by a.productid
;

Why would anyone ever use a query like above in the select, is this some old school thing to forget about using or should I still consider using it for some possible future problems?

Upvotes: 0

Views: 130

Answers (3)

Christoph Bimminger
Christoph Bimminger

Reputation: 1018

Keep performance in mind... inner join is much faster than subselect. A subselect loops through all matching results, so complexity is N x M... causing poor performance. Joins have a better performance in most cases.

See https://www.essentialsql.com/subquery-versus-inner-join/

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175994

First query:

select a.productid, sum(b.qty)
from table1 a
inner join table2 b on b.productid = a.productid
group by a.productid

It won't return row if there is no corresponding value in table2.

Second query is like LEFT JOIN:

select a.productid
,(select sum(b.qty) from table2 b where b.productid = a.productid)
from table1 a
group by a.productid
<=>
select a.productid, sum(b.qty)
from table1 a
left join table2 b on b.productid = a.productid
group by a.productid

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270623

No, they are not in fact the same thing. There are multiple differences, but the most obvious is that the join will filter out any unmatching rows. The correlated subquery will return all rows in the first table.

There are other differences as well. The sum()s will not be the same if there are duplicate productids in the first table. The execution plans are going to be different (because the result sets are different). Under some circumstances, the correlated subquery will be faster.

More generally, there are situations where the correlated subquery is the simplest way to express logic. And, as mentioned above, it can also produce the fastest execution plan under some circumstances.

Upvotes: 2

Related Questions