0

Some of my coworkers have been using sub-queries in places where you can simply use a join. pros and cons partially discussed in this SO post.

Most of their queries can be reduced down to the following pattern:

SELECT T1.a, (SELECT T2.b FROM T2 WHERE T2.a = T1.a )
FROM T1

which can be re-written as:

SELECT T1.a, T2.b
FROM T1 
LEFT JOIN T2
ON T1.a = T2.a

Can some awesome SOer give me an example where sub-queries can be used to implement an algorithm that can't be re-written using joins?

back story: I find JOINs much easier to read and manage. sql queries written using sub-queries drive me (my subconscious) nuts! I'm trying to find a reason not to hate them so much!

Zahra
  • 6,798
  • 9
  • 51
  • 76
  • I was asking a similar question related to subqueries: https://stackoverflow.com/questions/45327267/rewriting-sql-queries-with-group-by-and-without-having-using-a-subquery I believe that every dependent subquery behind `SELECT` can be rewritten into a query without it. – Radim Bača Nov 01 '17 at 21:49
  • 1
    @RadimBača . . . I'm not sure if what you say is true. In some cases, writing the equivalent query without a correlated subquery could be very hard and quite non-obvious. – Gordon Linoff Nov 02 '17 at 16:17

1 Answers1

1

Although you can construct an equivalent query without a subquery, it is more complex than just a join:

SELECT T1.a, (SELECT SUM(T2.b) FROM T2 WHERE T2.a <= T1.a )
FROM T1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786