JOINS in SQL Server: Types with Examples

What are Joins in SQL Server?

Data can be retrieved from more than one table using the JOIN statement in SQL Server. A JOIN combines rows from two or more tables based on a related column between them, most often a primary key matched to a foreign key. There are mainly four types of JOINs in SQL Server:

• INNER JOIN / simple join
• LEFT OUTER JOIN / LEFT JOIN
• RIGHT OUTER JOIN / RIGHT JOIN
• FULL OUTER JOIN

The table below summarizes which rows each JOIN type returns:

The examples below use two tables, Students and Fee, joined on their common admission column. Each section explains one JOIN type with its syntax, a worked query, and the result.

INNER JOIN

This type of SQL Server JOIN returns rows from all tables in which the join condition is true. It takes the following syntax:

SELECT columns
FROM table_1 
INNER JOIN table_2
ON table_1.column = table_2.column;

We will use the following two tables to demonstrate this. The Students table below lists each student and admission number:

The Fee table below records the amount each admission number has paid:

The following command demonstrates an INNER JOIN in SQL Server with an example:

SELECT Students.admission, Students.firstName, Students.lastName, Fee.amount_paid
FROM Students
INNER JOIN Fee
ON Students.admission = Fee.admission

The command returns the following result, showing only admission numbers present in both tables:

We can tell which students have paid their fee. We used the column with common values in both tables, which is the admission column.

LEFT OUTER JOIN

This type of join returns all rows from the left-hand table plus the records in the right-hand table with matching values. For example:

SELECT Students.admission, Students.firstName, Students.lastName, Fee.amount_paid
FROM Students
LEFT OUTER JOIN Fee
ON Students.admission = Fee.admission

The code returns the following result, keeping every student even when no fee record matches:

The records without matching values are replaced with NULLs in the respective columns.

RIGHT OUTER JOIN

This type of join returns all rows from the right-hand table and only those with matching values in the left-hand table. For example:

SELECT Students.admission, Students.firstName, Students.lastName, Fee.amount_paid
FROM Students
RIGHT OUTER JOIN Fee
ON Students.admission = Fee.admission

The statement for OUTER JOINS in SQL Server returns the following result:

The reason for the above output is that all rows in the Fee table are available in the Students table when matched on the admission column.

FULL OUTER JOIN

This type of join returns all rows from both tables, with NULL values wherever the JOIN condition is not true. For example:

SELECT Students.admission, Students.firstName, Students.lastName, Fee.amount_paid
FROM Students
FULL OUTER JOIN Fee
ON Students.admission = Fee.admission

The code returns the following result for FULL OUTER JOIN queries in SQL, combining every row from both tables:

FAQs