Subqueries versus Joins

Use both Joins and subqueries to query data from different tables. Though they may even share the same query plan, are many differences between them.  Knowing the differences and when to use either a join or subquery to search data from one or more tables is key to mastering SQL.

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database.  Get started using these free tools with my Guide Getting Started Using SQL Server.

Joins versus Subqueries

Joins and subqueries both combine data into a single result using either .  They share many similarities and differences.

Once difference to notice is Subqueries return either scalar (single) values or a row set; whereas, joins return rows.

Example Subquery

A common use for a subquery may be to calculate a summary value for use in a query.  For instance we can use a subquery to help us obtain all products have a greater than average product price.

SELECT ProductID,
       Name,
       ListPrice,
       (SELECT AVG(ListPrice)
          FROM Production.Product) AS AvgListPrice
  FROM Production.Product
 WHERE ListPrice > (SELECT AVG(ListPrice)
  FROM Production.Product)

There are two subqueries in this SELECT statement.  The first’s purpose is to display the average list price of all products, the second’s purpose is for filtering out products less than or equal to the average list price.

Below you’ll see a subquery filtering out products with the single value it returns.

Notice how the subqueries are queries unto themselves.  In this example you could paste the subquery, without the parenthesis, into a query window and run it.

Example JOIN

Contrast this with a join whose main purpose of a join is to combine rows from one or more tables based on a match condition.  For example we can use a join display product names and models.

Select Product.Name,
       ProductModel.Name as ModelName
FROM   Production.product
       INNER JOIN Production.ProductModel
       ON Product.ProductModelID = ProductModel.ProductModelID

In this statement we’re using an INNER JOIN to match rows from both the Product and ProductModel tables.  Notice that the column ProducModel.Name is available for use throughout the query.

The combined row set is then available by the select statement for use to display, filter, or group by the columns.

Read More: SQL Joins – The Ultimate Guide

This is different than the subquery.  The subquery returns a single result, which then filters the records.

Note that the join is an integral part of the select statement.  It can not stand on its own as a subquery can.

You’ll notice that some subqueries act as separate queries within the main outer query. You can actually copy and run the in their own query window. But there are other times where an outer query is “interwoven” into the subquery’s conditions.

These are correlated subqueries. The subquery is evaluated once for each outer query row.

Read More: Correlated Subqueries >>

Where are Joins and Subqueries Found?

Joins are used in the FROM clause of the WHERE statement; however, you’ll find subqueries used in most clauses such as the:

• SELECT List – These subqueries typically return single values.
• WHERE clause– depending on the conditional operator you’ll see single value or row based subqueries.
• FROM clause– It is typical to see row based result subqueries used here.
• HAVING clause – I mostly see subqueries returning single values in this situation.

Comparing Join and Subquery Execution Plans

Despite their differences, joins and subqueries are used to solve similar problems. In fact just because you write a SQL statement as a subquery doesn’t mean the DBMS executes as such.

Let’s look at an example.

Suppose the Sales Manager for Adventure Works wants a detailed listing of all sales orders and the number of order details lines for each order.

Surprisingly there are two ways to go about solving this.  We can use a join or a subquery.

Here are the two statements side by side:

Obviously they look different, but did you know they have very similar query plans?

Here is the query plan for a subquery

If you look closely you’ll see there is a Merge Join operation.  The subquery uses the same set of operations to return a result as you see with the join!   In fact, if you look at the corresponding joins query plan, you’ll see it is very similar.  You can get more detail about his in my article what is a query plan.

Subqueries and joins can be confusing, but they don’t have to be that way.  I have put together a really great series of videos explaining subqueries and their mysteries.  Click the button below to see more!