Joins and subqueries are both be used to query data from different tables and may even share the same query plan, but there 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.  You can get started using these free tools using my Guide Getting Started Using SQL Server.

Joins versus Subqueries

Joins and subqueries are both used to combine data from different tables into a single result.  They share many similarities and differences.

Subqueries can be used to return either a scalar (single) value or a row set; whereas, joins are used to return rows.

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 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.

Subquery Free Video Offer

Here the subquery is returning a single value which is then used filter out products.

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.

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.

This is different than the subquery.  There the subquery returns a result, which is immediately used.

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

A subquery is used to run a separate query from within the main query.  In many cases the returned value is displayed as a column or used in a filter condition such as where or having clause.  When a subquery incorporates a column from the main query it is said to be correlated.  In this way a sub query is somewhat like a join in that values from two or more tables can be compared.

My article Introduction to Subqueries in the SELECT Statement provides a good explanation of correlated subqueries.

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 – here a subqueries used to return single values are used.
  • 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 – In my experience scalar (single value) subqueries are used here.

Though joins and subqueries have many differences, they can be 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:

join and subquery compared
Side-by-Side Comparison of Join and Subquery

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

Here is the query plan for a subquery

query plan for a subquery
Subquery Query Plan

If you look closely you’ll see there is a Merge Join operation.  The subquery is being translated into the same set of operation used for 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!

Subquery Free Video Offer

About the author 

Kris Wenzel

Kris Wenzel has been working with databases over the past 30 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

  • thank you, this is very helpful for me to conceptually understand the difference between the two concepts, especially the attached image of the query plan.

  • Thanks, this is a helpful introduction. Aside from one’s preferred solution methodology, when should one be used over the other? What sort of situations might arise where the dbms uses a different query plan? It’d be helpful to know if there are instances where one has a significantly greater computational cost.

    I’ll experiment with my own benchmark comparisons, but it would be worth adding this info to a section of the article. Otherwise, thanks, this was helpful.

  • This is amazing. My entire life has been a lie.

    I blame being a regular programmer vs sql programmer for thinking that subqueries do the query for each row (just like they would in a program with nested FOR loops)

    This was very eye opening. I really need to recheck all of my DB fundamentals as i’m doing heavy SQL programming now

  • In your examples, you use subquery when it will only calculate same thing for any rows, like average of a table. What happens when you write a subquery that contains data from the rows. Will it run for each row? For example, lets say we are looking for name of the companies which has at least 1 employe over age 40. When you compare these 2 :




    would there be a major performance difference? Thanks !

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}


    Nothing is worse than, being excited to learn a new tool but not knowing where to start, wasting time learning the wrong features, and being overwhelmed .

    But it doesn't have to be this way.

    I'm Putting together a free email course to help you get started learning SQL Server.