TechBubbles Microsoft Technology BLOG

SQL server Joins or Subqueries?

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 :

About the author

Kalyan Bandarupalli

My name is kalyan, I am a software architect and builds the applications using Microsoft .NET technologies. Here I am trying to share what I feel and what I think with whoever comes along wandering to Internet home of mine.I hope that this page and its contents will speak for me and that is the reason I am not going to say anything specially about my self here.

Add Comment

TechBubbles Microsoft Technology BLOG

Follow me

Archives

Tag Cloud