SQL Server JOINS

Learning Objective

The objective of this SQL Server tutorial is to teach you how to use the different types of JOINS available in SQL Server.

What are JOINS in SQL Server?

A JOIN is a means of combining data from multiple tables. The need for JOINS in a relational database is predicated by the fact that data is spread across multiple tables and individual table data is not sufficient to get any real insight or make any conclusions. Therefore, data has to mined and combined from different tables in the database as per requirement to generate meaningful and workable datasets. The same is accomplished through JOINS. Using JOINS, we can select relevant columns from different tables where the tables are joined on a common column. Common column means that the column data in the different tables is essentially the same. The column names might be different. The first table in a JOIN is called the LEFT TABLE and the second table in a JOIN is called the RIGHT JOIN.

SQL server supports 5 types of JOINS. They are:

1. INNER JOIN (also referred to as JOIN)
2. LEFT OUTER JOIN (also referred to as LEFT JOIN)
3. RIGHT OUTER JOIN (also referred to as RIGHT JOIN)
4. FULL OUTER JOIN (also referred to as FULL JOIN)
5. CROSS JOIN

SQL Server JOINS with Examples

We will discuss all the different types of JOINS one by one with the help of 2 sample tables. Suppose we have 2 tables – one called candidates which contains the names of interview candidates and another called employees which contains the names of the company employees some of which are the candidates who have been selected and converted into employees. We will use these sample tables to study and understand the different JOINS with the help of examples. The sample tables are represented below.

cid	cname
1	Albert Spencer
2	Julia Wells
3	Sandra Bull
4	Robert Fox
5	Jane Frost
6	Simon Ray

eid	ename
1	Kirsten Rose
2	Julia Wells
4	Robert Fox
5	Steven Pitt

1)  INNER JOIN

An INNER JOIN is a JOIN between two tables where the JOIN resultset consists of rows from the left table which match rows from the right table (simply put it returns the common rows from both tables).

INNER JOIN Syntax

The basic syntax of INNER JOIN is given below.

SELECT column_list
FROM table1
INNER JOIN
table2
ON table1.columnX=table2.columnX;

It can also be written with JOIN instead of INNER JOIN. Both mean the same thing and are valid.

SELECT column_list
FROM table1
JOIN
table2
ON table1.columnX=table2.columnX;

In this syntax,

• column_list – the list of columns to be used from the participating tables by the SELECT statement.
• table1 – the first or left table.
• table2 – the second or right table.
• INNER JOIN/JOIN – SQL keyword combination to implement an INNER JOIN (where the resultset is the intersecting rows of the participating tables).
• columnX – column common to both tables on which the JOIIN is made.

INNER JOIN Example

Using the sample tables cited above we can do an INNER JOIN with the following query.

SELECT *
FROM candidates
INNER JOIN employees
ON candidates.cname=employees.ename;

It will produce the following output. From the resultset we can see that it consists of rows where the left and right tables have matching values. The first two columns in the resultset are from the left table and the second two columns from the right table.

cid	cname	eid	ename
2	Julia Wells	2	Julia Wells
4	Robert Fox	4	Robert Fox

2) LEFT OUTER JOIN (or LEFT JOIN)

A LEFT OUTER JOIN between 2 tables is a JOIN where the resultset consists of all rows from the left table including unique rows (which do not match any row in the right table) and matching rows (common rows of both tables) but include only matching rows from the right table. The non-matching rows of the right table are represented by NULL values.

LEFT OUTER JOIN Syntax

The basic syntax of LEFT OUTER JOIN/LEFT JOIN is given below.

SELECT column_list
FROM table1
LEFT OUTER JOIN
table2
ON table1.columnX=table2.columnX;

It can also be written as below. Both above and below mean the same thing and are valid.

SELECT column_list
FROM table1
LEFT JOIN
table2
ON table1.columnX=table2.columnX;

In this syntax,

• column_list – the list of columns to be used from the participating tables by the SELECT statement.
• table1 – the first or left table.
• table2 – the second or right table.
• LEFT OUTER JOIN/LEFT JOIN – SQL keyword combination to implement a LEFT JOIN (where the resultset contains complete data of the left table but partial data of the right table which matches the left table.
• columnX – column common to both tables on which the JOIIN is made.

LEFT OUTER JOIN Example

Using the sample tables cited above we can do a LEFT JOIN with the following query.

SELECT *
FROM candidates
LEFT JOIN employees
ON candidates.cname=employees.ename;

It will produce the following output. From the resultset we can see that it contains the entire data from the left table in the first two columns but only two rows of data from the right table which match the left table. All the other non-matching unique right table rows are represented as NULL.

cid	cname	eid	ename
1	Albert Spencer	NULL	NULL
2	Julia Wells	2	Julia Wells
3	Sandra Bull	NULL	NULL
4	Robert Fox	4	Robert Fox
5	Jane Frost	NULL	NULL
6	Simon Ray	NULL	NULL

3) RIGHT OUTER JOIN (or RIGHT JOIN)

A RIGHT OUTER JOIN between 2 tables is a JOIN where the resultset consists of all rows from the right table including unique rows (which do not match any row in the left table) and matching rows (common rows of both tables) but includes only matching rows from the left table. The non-matching rows of the left table are represented by NULL values. So, it is the exact reverse of a LEFT JOIN.

RIGHT OUTER JOIN Syntax

The basic syntax of RIGHT OUTER JOIN/RIGHT JOIN is given below.

SELECT column_list
FROM table1
RIGHT OUTER JOIN
table2
ON table1.columnX=table2.columnX;

It can also be written as below. Both above and below mean the same thing and are valid.

SELECT column_list
FROM table1
RIGHT JOIN
table2
ON table1.columnX=table2.columnX;

In this syntax,

• column_list – the list of columns to be used from the participating tables by the SELECT statement.
• table1 – the first or left table.
• table2 – the second or right table.
• RIGHT OUTER JOIN/RIGHT JOIN – SQL keyword combination to implement a RIGHT JOIN (where the resultset contains complete data of the right table but partial data of the left table which matches the right table.
• columnX – column common to both tables on which the JOIIN is made.

RIGHT JOIN Example

Using the sample tables cited above we can do a RIGHT JOIN with the following query.

SELECT *
FROM candidates
RIGHT JOIN employees
ON candidates.cname=employees.ename;

It will produce the following output. From the resultset we can see that it contains the entire data from the right table in the last two columns but only two rows of data from the left table which match the right table. The other non-matching unique left table rows are represented as NULL. Also, we can see that the resultset consists of 4 rows only since a right join is implemented from the right table and the right table has only 4 rows in it. So once the 4^th row of the right table is reached the query is completed.

cid	cname	eid	ename
NULL	NULL	1	Kirsten Rose
2	Julia Wells	2	Julia Wells
4	Robert Fox	4	Robert Fox
NULL	NULL	5	Steven Pitt

4)  FULL OUTER JOIN (or FULL JOIN)

A FULL OUTER JOIN between 2 tables is a JOIN where the resultset consists of the entire set of rows from both the left and right table. Rows of the left table which do not have matching counterpart in the right are represented by NULL and rows of right table which do have matching counterpart in the left are represented by NULL.

FULL OUTER JOIN Syntax

The basic syntax of FULL OUTER JOIN/FULL JOIN is given below.

SELECT column_list
FROM table1
FULL OUTER JOIN
table2
ON table1.columnX=table2.columnX;

It can also be written as below. Both above and below mean the same thing and are valid.

SELECT column_list
FROM table1
FULL JOIN
table2
ON table1.columnX=table2.columnX;

In this syntax,

• column_list – the list of columns to be used from the participating tables by the SELECT statement.
• table1 – the first or left table.
• table2 – the second or right table.
• FULL OUTER JOIN/FULL JOIN – SQL keyword combination to implement a RIGHT JOIN (where the resultset contains complete data of the right table but partial data of the left table which matches the right table.
• columnX – column common to both tables on which the JOIIN is made.

FULL JOIN Example

Using the sample tables cited above we can do a FULL JOIN with the following query.

SELECT *
FROM candidates
FULL JOIN employees
ON candidates.cname=employees.ename;

It will produce the following output. From the resultset we can see that it contains the entire data from both tables. Non matching unique row data of either table is followed by NULL value in the other table since it does not have a matching counterpart. Also, the number of rows is more i.e. (6+4)=10 rows – 2 common rows = 8 rows, the common datasets being Julia Wells and Robert Fox.

cid	cname	eid	ename
1	Albert Spencer	NULL	NULL
2	Julia Wells	2	Julia Wells
3	Sandra Bull	NULL	NULL
4	Robert Fox	4	Robert Fox
5	Jane Frost	NULL	NULL
6	Simon Ray	NULL	NULL
NULL	NULL	1	Kirsten Rose
NULL	NULL	5	Steven Pitt

5) CROSS JOIN

A CROSS JOIN is a different kind of join which does not require any relation between the participating tables and operates without having to join the tables on a common column. The purpose of a CROSS JOIN is not to combine intersecting data or unique data or entire set of data from both tables but rather to combine the datasets of the participating tables exhaustively generating the complete list of possible combinations.

CROSS JOIN Syntax

The basic syntax of FULL OUTER JOIN/FULL JOIN is given below.

SELECT column_list
FROM table1
CROSS JOIN
table2;

In this syntax,

• column_list – the list of columns to be used from the participating tables by the SELECT statement.
• table1 – the first or left table.
• table2 – the second or right table.
• CROSS JOIN – SQL keyword combination to implement a CROSS JOIN (where the resultset is a CARTESIAN PRODUCT of the participating tables).
• columnX – column common to both tables on which the JOIIN is made.

CROSS JOIN Example

Using the sample tables cited above we can do a CROSS JOIN with the following query. The output has been sorted on the first column of the resultset using an ORDER BY clause to make it more comprehensible so that we can understand how cross join combines every record of the first table with every other record of the second table.

SELECT *
FROM candidates
CROSS JOIN employees
ORDER BY 1;

It will produce the following output. From the resultset we can see that every row or record of the first left table has been combined with every row or record of the second right table. Albert Spencer has been combined with Kirsten Rose, Julia Wells, Robert Fox and Steven Pitt. Likewise, for all records generating a resultset of 6*4=24 rows which is a CARTESIAN PRODUCT of the number of rows in both tables.

cid	cname	eid	ename
1	Albert Spencer	1	Kirsten Rose
1	Albert Spencer	2	Julia Wells
1	Albert Spencer	4	Robert Fox
1	Albert Spencer	5	Steven Pitt
2	Julia Wells	5	Steven Pitt
2	Julia Wells	4	Robert Fox
2	Julia Wells	2	Julia Wells
2	Julia Wells	1	Kirsten Rose
3	Sandra Bull	1	Kirsten Rose
3	Sandra Bull	2	Julia Wells
3	Sandra Bull	4	Robert Fox
3	Sandra Bull	5	Steven Pitt
4	Robert Fox	5	Steven Pitt
4	Robert Fox	4	Robert Fox
4	Robert Fox	2	Julia Wells
4	Robert Fox	1	Kirsten Rose
5	Jane Frost	1	Kirsten Rose
5	Jane Frost	2	Julia Wells
5	Jane Frost	4	Robert Fox
5	Jane Frost	5	Steven Pitt
6	Simon Ray	5	Steven Pitt
6	Simon Ray	4	Robert Fox
6	Simon Ray	2	Julia Wells
6	Simon Ray	1	Kirsten Rose