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.

cidcname
1Albert Spencer
2Julia Wells
3Sandra Bull
4Robert Fox
5Jane Frost
6Simon Ray
Table: candidates
eidename
1Kirsten Rose
2Julia Wells
4Robert Fox
5Steven Pitt
Table: employees

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.

cidcnameeidename
2Julia Wells2Julia Wells
4Robert Fox4Robert 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.

cidcnameeidename
1Albert SpencerNULLNULL
2Julia Wells2Julia Wells
3Sandra BullNULLNULL
4Robert Fox4Robert Fox
5Jane FrostNULLNULL
6Simon RayNULLNULL

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 4th row of the right table is reached the query is completed.

cidcnameeidename
NULLNULL1Kirsten Rose
2Julia Wells2Julia Wells
4Robert Fox4Robert Fox
NULLNULL5Steven 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.

cidcnameeidename
1Albert SpencerNULLNULL
2Julia Wells2Julia Wells
3Sandra BullNULLNULL
4Robert Fox4Robert Fox
5Jane FrostNULLNULL
6Simon RayNULLNULL
NULLNULL1Kirsten Rose
NULLNULL5Steven 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.

cidcnameeidename
1Albert Spencer1Kirsten Rose
1Albert Spencer2Julia Wells
1Albert Spencer4Robert Fox
1Albert Spencer5Steven Pitt
2Julia Wells5Steven Pitt
2Julia Wells4Robert Fox
2Julia Wells2Julia Wells
2Julia Wells1Kirsten Rose
3Sandra Bull1Kirsten Rose
3Sandra Bull2Julia Wells
3Sandra Bull4Robert Fox
3Sandra Bull5Steven Pitt
4Robert Fox5Steven Pitt
4Robert Fox4Robert Fox
4Robert Fox2Julia Wells
4Robert Fox1Kirsten Rose
5Jane Frost1Kirsten Rose
5Jane Frost2Julia Wells
5Jane Frost4Robert Fox
5Jane Frost5Steven Pitt
6Simon Ray5Steven Pitt
6Simon Ray4Robert Fox
6Simon Ray2Julia Wells
6Simon Ray1Kirsten Rose

Advertisement