Querying Multiple Tables in SQL Server (2023)


  • Introduction
  • Understanding Joins
  • Using Inner Joins
  • Using Outer Joins
  • Using Cross Joins
  • Self Cross Joins
  • Conclusion
  • Learn More
  • Top


In other guides, you have learned how to write basic SQL queries to retrieve data from a table. In real-life applications, you would need to fetch data from multiple tables to achieve your goals. To do so, you would need to use SQL joins. In this guide, you will learn how to query data from multiple tables using joins.

Understanding Joins

In order to understand joins, you should first understand the concept of a Cartesian product. In mathematics, this is the product of two sets. For example, a set of two items multiplied by a set of six items will produce a set of twelve items.

In databases, a Cartesian product is the result of joining every row of one input table to all rows of another table. Therefore, the product of a table of two rows and a table of three rows would be a set of six rows.

Example: A Cartesian product of orders and items

Table 1: Orders


Table 2: Items


Result: Each row of orders would multiply by each row of items, and the result would be as below.


In SQL, to fetch data from multiple tables, the join operator is used. The join operator adds or removes rows in the virtual table that is used by SQL server to process data before the other steps of the query consume the data. Joins can be of the following categories:

  1. A cross join, also known as a Cartesian product, adds all possible combinations of the two input tables' rows to the virtual table. All data filtering of the rows will be done in the where clause.

  2. (Video) Using SQL to Select Records from Multiple Tables
  3. The inner join operator first creates a Cartesian product, and then filters the results using the predicate supplied in the ON clause, removing any rows from the virtual table that do not satisfy the predicate. It is the most commonly used join type.

  4. An outer join operator (LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN) first creates a Cartesian product, then filters the results to find rows that match in each table. The difference is that all rows from one table are preserved and added back to the virtual table after the initial filter is applied. All null values are placed on attributes where no matching values are found.

Using Inner Joins

Inner joins are used to retrieve data that has been stored across multiple tables. As discussed earlier, an inner join begins its logical processing phase as a Cartesian product that is then filtered to remove any rows that don't match the predicate.

It is important to note that INNER JOINS returns only rows where a match is found in both input tables.

The inner join syntax is as follows:

1SELECT o.orderid, o.amount, i.description2FROM orders o3INNER JOIN items i4ON o.itemid = i.itemid

Some key elements to note from the query above:

  • Note the use of alias in the FROM, SELECT and ON statements. The orders table is aliased as o and is used in the ON statement as well as in the SELECT statement.
  • The key word INNER JOIN could be expressed as only JOIN. Both expressions would mean an inner join.
  • Because we are using an INNER JOIN, the result would only be rows that exists in BOTH tables.

Example of Inner Join

Consider the tables below.

Table Orders


Table Items


In the tables above, if we needed to retrieve orderid, order quantity, item price, and itemdesc for all orders that have an item, the query would be as follows.

(Video) Querying Multiple Tables with SQL
1SELECT o.orderid, o.qty, i.itemprice, i.itemdesc2FROM orders o3INNER JOIN items i4on o.itemid = i.itemid

The result set would be as below.


From the result above, not that orderid 4 did not come into the result set because itemid 3 in the orders table is not found in the items table. Since INNER JOIN only returns rows that exists in both tables, orderid 4 was filtered out from the result set.

Using Outer Joins

So far, you've learned how to use inner joins to match rows in separate tables. SQL Server built the results of an inner join query by filtering out rows that failed to meet the conditions expressed in the ON clause predicate. The result is that only rows that matched from both tables were displayed.With an outer join, you may choose to display all the rows from one table, along with those that match from the second table.

Here are some important notes about outer joins:

  • Outer joins return all rows from one table and matching rows from the second table.
  • In cases where the join cannot find matching records from the second table, the results from the second table are displayed as NULL.
  • Unlike inner joins, the order in which tables are listed and joined in the FROM clause does matter, as it will determine whether you choose LEFT or RIGHT for your join.

The following will return all rows from the first table (a) and only matching rows from the second table (b).

1FROM a LEFT OUTER JOIN b2on a.id = b.id

The following will return all rows from the second table (b) and only matching rows from the first table (a).

1FROM a RIGHT OUTER JOIN b2on a.id = b.id

Example of Outer Join

Consider the tables of orders and items used in the inner join example above.

Let's say we need to write a query to retrieve the orderid, order quantity, item price and itemdesc of all orders. If the order does not have an itemid in the items table, the order details should be returned, but the item details should be displayed as NULL.

In such a scenario, a LEFT (outer) JOIN would be preferred because it will return all rows from the first table and only matching rows from the second table.

The query would be as follows.

(Video) Querying multiple tables in MySQL

1SELECT o.orderid, o.qty, i.itemprice, i.itemdesc2FROM orders o3LEFT JOIN items i4on o.itemid = i.itemid

The result set would be as below.


Using Cross Joins

Cross join queries create a Cartesian product, which you have learned about earlier in this guide.

To explicitly create a Cartesian product, you would use the CROSS JOIN operator. This will create a result set with all possible combinations of input rows.

When writing queries with CROSS JOIN, remember that there is no matching of rows performed, and therefore no ON clause is required.

Consider the example below.

Table A


Table B


The result would be


Self Cross Joins

So far, you have learned about joins that involve separate multiple tables. There may be scenarios in which you need to compare and retrieve data stored in the same table.

For example, in a human resources application, an Employees table might include data about the supervisor of each employee in the employee's own row. Each supervisor is also listed as an employee. The table below could demonstrate such a scenario.

(Video) SQL Query 3 on Multiple Tables || Lesson 75 || DBMS || Learning Monkey ||

1emp a2
2emp b3
3emp c4
4emp d1

To retrieve the employee information and match it to the related supervisor, you can use the table twice in your query, joining it to itself for the purposes of the query.

To accomplish tasks like this, you should consider the following guidelines:

  • Create two instances of the same table in the FROM clause and join them as needed, using inner or outer joins.
  • Use table aliases to create two separate aliases for the same table. At least one of these must have an alias.
  • Use the ON clause to provide a filter using separate columns from the same table.
1SELECT e.empid ,e.empname AS empname, s.empname AS supervisorname2FROM HR.Employees AS e3JOIN HR.Employees AS s4ON e.supervisorid=s.empid

This would return each employee with their respective supervisor.

1emp aemp b
2emp bemp c
3emp cemp d
4emp demp a


In this article, you learned how to query query data using joins in SQL Server. In the next guide, we'll discuss how to handle errors in SQL Server.

Happy coding!

Learn More

Explore these SQL Server courses from Pluralsight to continue learning:

  • Developing SQL Server Databases
  • SQL Server Query Tuning
  • Advanced SQL Queries in Oracle and SQL Server
  • Transact-SQL Common Table Expressions in SQL Server


1. Introduction to INNER and OUTER JOINS - Querying Multiple Tables with MySQL Workbench
(Matt Macarty)
2. How to Query Multiple Tables with JOINs
(CBT Nuggets)
3. How to Join two or more than two Tables using multiple columns | How to Join Multiple Tables #Joins
(The Data Millennials)
4. SQL - Part 66 - Using Sub Queries for retrieving the records from multiple tables
5. Sql query to delete from multiple tables
6. How to Query Multiple SQL Servers
(Identity Managed)


Top Articles
Latest Posts
Article information

Author: Barbera Armstrong

Last Updated: 24/07/2023

Views: 5617

Rating: 4.9 / 5 (59 voted)

Reviews: 82% of readers found this page helpful

Author information

Name: Barbera Armstrong

Birthday: 1992-09-12

Address: Suite 993 99852 Daugherty Causeway, Ritchiehaven, VT 49630

Phone: +5026838435397

Job: National Engineer

Hobby: Listening to music, Board games, Photography, Ice skating, LARPing, Kite flying, Rugby

Introduction: My name is Barbera Armstrong, I am a lovely, delightful, cooperative, funny, enchanting, vivacious, tender person who loves writing and wants to share my knowledge and understanding with you.