DEV Community

What's the difference between the SUB SELECT and LEFT JION SQL query?

In the following example, every query clause has the same result, so what is the difference between the two clauses of the query in terms of speed in returning data from database?

Alt Text

Top comments (5)

Collapse
 
adron profile image
Adron Hall

Title mispelled + content seems to be missing. Was this supposed to be posted?

Collapse
 
alqaqaaben profile image
𝓐𝓵𝓠𝓪𝓺𝓪𝓪 𝓑𝓮𝓷𝓰𝓱𝓾𝔃𝓲 ☪

The post has been modified

Collapse
 
adron profile image
Adron Hall

Ah, yup. BTW join is mispelled in the title.

The first thing is, the first query is a single query, the second is actually two. Having a nested query requires the inside query being executed and then the query being executed on that data. Depending on how those perform it's pretty standard to see slower performance running two queries instead of one, and in a situation like this where things are being executed in memory this could become substantial.

There are other differences, but that's the first and most notable one.

Thread Thread
 
alqaqaaben profile image
𝓐𝓵𝓠𝓪𝓺𝓪𝓪 𝓑𝓮𝓷𝓰𝓱𝓾𝔃𝓲 ☪

This means that the first query will be faster with big databases!

Thread Thread
 
adron profile image
Adron Hall

Yup.