Which Should You Use–SQL Server Joins or Subqueries

Almost all SELECT statements that join tables and use the join operator can be rewritten as subqueries, and vice versa. Writing the SELECT statement using the join operator is often easier to read and understand and can also help the SQL Server Database Engine to find a more efficient strategy for retrieving the appropriate data. However, there are a few problems that can be easier solved using subqueries, and there are others that can be easier solved using joins.

Subquery Advantages

Subqueries are advantageous over joins when you have to calculate an aggregate value on-the-fly and use it in the outer query for comparison. Example 1 shows this.

EXAMPLE 1

Get the employee numbers and enter dates of all employees with enter dates equal to the earliest date:

 

This problem cannot be solved easily with a join, because you would have to write the aggregate function in the WHERE clause, which is not allowed. (You can solve the problem using two separate queries in relation to the works_on table.)

Join Advantages

Joins are advantageous over subqueries if the SELECT list in a query contains columns from more than one table. Example 2 shows this.

EXAMPLE 2

Get the employee numbers, last names, and jobs for all employees who entered their projects on October 15, 2007:

 

The SELECT list of the query in Example 2 contains columns emp_no and emp_lname from the employee table and the job column from the works_on table. For this reason, the equivalent solution with the subquery would display an error, because subqueries can display information only from the outer table.