SQL Join in SQL Server

The SQL Join lets us combine results from two or more tables into a single result set. The tables are joined using a join condition. This article explores the fundamentals of SQL Join in more detail with examples. 

Basics of SQL Join

In a real-world database, data is split into multiple tables. And that is why we need to join the data from multiple tables to get the desired result.

For example, the Customers and Orders table as shown below.

SQL Join Example : Customer Table
Customers Table
SQL Join Example : Order Table
Orders Table

The information about the customer is saved in the Customers table, with customerID as the Primary key.

The Orders from the customer are stored in a separate Orders table with OrderID uniquely identifying each Order. We add the CustomerID in the Orders table to link each Order to the Customer. The CustomerID becomes the Foreign Key.

To get the list of Orders, all we need to execute the following query

The result contains customerID, but not the name of the Customer. To get the customer name

  1. We need to go through each record of the order table
  2. Read the customer id
  3. Search for the customer id in the customer table
  4. Get the customer name
  5. Include the customer name in the result

In short, we need to join the results from the order table with the customer table based on CustomerID. And that is what SQL Join operation does behind the scene.

Join Syntax

The Syntax of the Join statement is as shown below

columns

The columns you want in the result. The columns can come from both the tables. Note that all column references must be unambiguous.

first_table

The first table in the query. Also referred to as the Left table

join_type

join_type specifies the type of join. The join type can be of any of the following. The result of the join operation depends on the join type used to join the tables.

  • (INNER ) JOIN
  • LEFT (LEFT OUTER) JOIN
  • RIGHT (RIGHT OUTER) JOIN
  • FULL (FULL OUTER) JOIN
  • CROSS JOIN

second_table

The table, which we would like to join the first_table and is alternatively referred to as Right table.

join_condition

Join condition defines the condition on which the tables are joined. This condition is evaluated for each row of the result. If the condition is evaluated to true, then the rows are joined else not.

A join condition must specify the column from each table to be used for the join.

It must specify the logical operator (for example, = or <>,) to be used in comparing values from the columns

Join Example

Now, let us see how we can get the list order with the customer name.

Sample Database

Use the following query create the tables along with the sample data.

SQL Join Query

First, we select the columns we want in the final result. We have prefixed the column names with table alias. Ord for Orders table and cust for Customers table

We have Orders table in the from clause aliased with ord.The orders table is the Left table.

Next, we join Customers to the Orders table. The Customers table has the alias cust.The Customers table is the Right table.

Finally, we have the Join condition. The customerID from the orders table must be equal to customerID from the Customers table

Join Result

The Result above query is as shown below.

Result of join operation between customers & orders table
SQL Join: List of Orders with Customer

Now, we learned how to join two tables, let us see some of the tips on joins.

SQL Join Tips

Join Types

There are many ways we can join the tables. While all of the Join types return the matching rows, they differ in how they treat the unmatched rows. Simplest of them is inner join, which only returns the matching rows. The Left join returns all the unmatched rows from the left table along with rows that matched. The Right Joins is opposite of Left join. It returns all the rows from the right table along with the rows that matched. Full Join includes all the rows from both the table. A cross join joins each row of the left table with every row of the right table.

  • (INNER ) JOIN
  • LEFT (LEFT OUTER) JOIN
  • RIGHT (RIGHT OUTER) JOIN
  • FULL (FULL OUTER) JOIN
  • CROSS JOIN

You can refer to the following articles to find out more about each of these join types

Qualify column name with the Table name or Table Alias

The column names must be unambiguous.

Consider the following query. We have customerID in the select column list.

If you execute the above query, the following error will be thrown

CustomerID appears in both Customers & Orders table. The SQL server will not know, whether you are referring to the CustomerID of the Orders table or Customers table.

Hence, it is necessary to qualify the column name with the table name or table alias. Use table alias for better readability and quicker typing. An Alias is a shorthand for a table name.

Join condition

The join condition must specify the column from both the tables and how they must be compared.

Checking for equality (=) is the most used condition in a Join statement.

But you can also use any other logical operators like <,> &!=. But be careful of such operation as it may degrade the performance of the query.

Usually, we use a primary key and foreign key column in a join operation. But you can use any other column as well.

Use more than one column in join condition by using an AND or OR operator as shown below.

You can also use a subquery / View as a join table

Using a subquery or a view as a join table is perfectly ok

You can join more than 2 tables

You can join more than 2 table’s in a single query. The maximum number of tables in a Join statement can be 256.

In multiple join statements like the above joins are performed in an incremental fashion.

  1. The TableA is joined with TableB.
  2. Result of step 1 is then joined with the TableC
  3. Result of step 2 is then joined with the TableD

Order of tables is important

For the Inner Join or Full Join, the order does not matter. The Order matters for the LEFT & RIGHT Join statements or any query which has a mix of join types

You can mix various join types in a single query

You can mix various types of join types in a Single Query as shown below.

Columns in the join condition do not have to be in the select list

You do not have to include the columns participating in the join condition in the select list

References

  1. Join Syntax (SQL Server)
  2. Sample database

Summary

Now, we have learned the basics of the SQL Joins statement in the next tutorial, we will learn more about the types of join statements

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top