Select statements that use join operator and join statements can be written in Subqueries and similarly Subqueries can be written in Joins. Writing the Select statements with Join operator can help database engine to find more efficient strategy for retrieving the appropriate data. However each having their own advantage over other. Some problems can be easily solve with Subqueries and some can be solve with Joins. More about SQL Joins can read here.
Subqueries are advantageous over joins when we have to calculate an aggregate value on the fly and use it in the outer query for comparison.
Example: To Get the employee IDs and HireDates of all employees with hiredate equal to the earliest date. We could write the query
The same query can’t be easily written using Joins but we can write two separate queries in relation to Employee table.
Joins are advantageous over sub-queries if the SELECT query contains columns from more than one table.
Example: To get the employee ids, names for all employees who terminated on particular date then we could write the following query
The following URL contains good discussion on this
|Share this post :|