Subquery or Join - Which One Is Better to Use
There are ongoing arguments among experts about the superiority of sub-query vs join. Both sides have their reasons to support their position. From my point of view, there are several axis of being "better"--
→better performance
→better readability
→better maintainability
→better flexibility and so on
In JOINs RDBMS can create an execution plan that is better for our query and can predict what data should be loaded to be processed and save time, unlike the sub-query where it will run all the queries and load all their data to do the processing.
The good thing in sub-queries is that they are more readable than JOINs: that's why most new SQL people prefer them; it is the easy way; but when it comes to performance, JOINS are better in most cases even though they are not hard to read too. In most cases JOINs are faster than sub-queries and it is very rare for a sub-query to be faster.
Now-a-days, most databases includes it as an optimization step to convert sub-queries into joins when it is analyzing our query; this indeed gives a better performance. Let’s use the following example to predict which gives the better performance:
Let’s say, we have 3 tables related to each other, and we need to select data from one table that has some fields related to the other 2 tables. To perform the task, we designed 2 SQL statements; one with one with JOIN and another with standard sub-query. We now have to decide which will perform better to do this specific task:
Query-1 (with JOIN):
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
Query-2 (with standard sub-query):
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
For this task, the first approach, with
→better performance
→better readability
→better maintainability
→better flexibility and so on
In JOINs RDBMS can create an execution plan that is better for our query and can predict what data should be loaded to be processed and save time, unlike the sub-query where it will run all the queries and load all their data to do the processing.
The good thing in sub-queries is that they are more readable than JOINs: that's why most new SQL people prefer them; it is the easy way; but when it comes to performance, JOINS are better in most cases even though they are not hard to read too. In most cases JOINs are faster than sub-queries and it is very rare for a sub-query to be faster.
Now-a-days, most databases includes it as an optimization step to convert sub-queries into joins when it is analyzing our query; this indeed gives a better performance. Let’s use the following example to predict which gives the better performance:
Let’s say, we have 3 tables related to each other, and we need to select data from one table that has some fields related to the other 2 tables. To perform the task, we designed 2 SQL statements; one with one with JOIN and another with standard sub-query. We now have to decide which will perform better to do this specific task:
Query-1 (with JOIN):
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
Query-2 (with standard sub-query):
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
For this task, the first approach, with