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.
Subquery Advantages
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
1: USE AdventureWorks
2: SELECT EmployeeID,HireDate
3: FROM HumanResources.Employee
4: WHERE HireDate = (SELECT min(HireDate)
5: FROM HumanResources.Employee)
The same query can’t be easily written using Joins but we can write two separate queries in relation to Employee table.
Join Advantages
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
1: SELECT HumanResources.Employee.EmployeeID 2: FROM HumanResources.Employee, 3: HumanResources.EmployeeDepartmentHistory 4: WHERE HumanResources.Employee.EmployeeID 5: = HumanResources.EmployeeDepartmentHistory.EmployeeID 6: AND HumanResources.EmployeeDepartmentHistory.EndDate 7: = '2000-06-30 00:00:00.000';
The following URL contains good discussion on this
http://stackoverflow.com/questions/141278/subqueries-vs-joins
| Share this post : |  |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
