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

Select * from Orders

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

We need to go through each record of the order table
Read the customer id
Search for the customer id in the customer table
Get the customer name
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

SELECT <Columns>

FROM first_table

[join_type] JOIN second_table

[ON (join_condition)]

columns

The columns you want in the result. The columns can come from both 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

The 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 to create the tables along with the sample data.

create table Customers (

CustomerID int primary key,

Name varchar(100)

);

insert into Customers values(1, 'Kevin Costner');

insert into Customers values(2, 'Akshay Kumar');

insert into Customers values(3, 'Sean Connery');

insert into Customers values(4, 'Sanjay Dutt');

insert into Customers values(5, 'Sharukh Khan');

insert into Customers values(6, 'Jet Li');

create table Orders (

OrderID int primary key,

CustomerID int,

OrderDate DateTime,

Value Decimal(12,0),

CONSTRAINT FK_Orders_CustomerID FOREIGN KEY (CustomerID)

REFERENCES Customers (CustomerID)

);

insert into Orders Values(1,1,'2019-12-08',1500)

insert into Orders Values(2,2,'2019-09-06',3700)

insert into Orders Values(3,1,'2019-07-18',4300)

insert into Orders Values(4,5,'2019-09-21',1200)

insert into Orders Values(5,6,'2019-10-25',500)

insert into Orders Values(6,6,'2019-11-10',700)

insert into Orders Values(7,2,'2019-12-01',1800)

insert into Orders Values(8,1,'2019-09-07',2500)

SQL Join Query

select ord.OrderID, ord.CustomerID, ord.OrderDate, ord.Value , cust.Name

from Orders ord

Join Customers cust

on ( ord.CustomerID=cust.CustomerID)

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

select ord.OrderID, ord.CustomerID, ord.OrderDate, ord.Value , cust.Name

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

from Orders ord

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

Join Customers cust

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

on ( ord.CustomerID=cust.CustomerID)

Join Result

The result of the above query is 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. The 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 Join 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.

select OrderID, CustomerID, OrderDate, Value ,Name

from Orders

Join Customers

on ( Orders.CustomerID=Customers.CustomerID)

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

Ambiguous column name 'CustomerID'

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.

select ord.OrderID, ord.CustomerID, ord.OrderDate, ord.Value , cust.Name

from Orders ord

Join Customers cust

on ( ord.CustomerID=cust.CustomerID)

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 the join condition by using an AND or OR operator as shown below.

Select A.Column1, A.Column2, B.Column1, B.Column2

From TableA A

Join TableB B

on ( A.Column1=B.Column1 AND A.Column2 = B.Column2)

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

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

Select A.Column1, A.Column2, B.Column1, B.Column2

From ( ** A Subquery Here *** ) A

Join ( ** Another Subquery Here *** ) B

on ( A.Column1=B.Column1 AND A.Column2 = B.Column2)

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.

Select A.Column1, A.Column2, B.Column1, B.Column2

From TableA A

Join TableB B

on ( A.ColumnA=B.ColumnB)

Join TableC C

on ( A.ColumnA=C.ColumnC)

Join TableD D

on ( A.ColumnD=D.ColumnD)

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

TableA is joined with TableB.
Result of step 1 is then joined with the TableC
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 join types in a Single Query as shown below.

Select A.Column1, A.Column2, B.Column1, B.Column2

From TableA A

Join TableB B

on ( A.ColumnA=B.ColumnB)

Left Join TableC C

on ( A.ColumnA=C.ColumnC)

Full Join TableD D

on ( A.ColumnD=D.ColumnD)

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

Read More

Summary

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

Check Constraint

Join Types in SQL Server

Basics of SQL Join

Join Syntax

columns

first_table

join_type

second_table

join_condition

Join Example

Sample Database

SQL Join Query

Join Result

SQL Join Tips

Join Types

Qualify column name with the Table name or Table Alias

Join condition

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

You can join more than 2 tables

Order of tables is important

You can mix various join types in a single query

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

References

Summary

Related Posts

Leave a Comment Cancel Reply