SQL SERVER JOINS Tutorial: INNER, LEFT, RIGHT , OUTER

We can retrieve data from more than one tables using the JOIN statement. SQL Server has 4 types of joins:

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

INNER JOIN

This type of 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:

Students Table:

Fee table:

The following command demonstrates an INNER JOIN:

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:

We can tell the students who 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 will return all rows from the left-hand table plus 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:

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 returns the following:

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 where 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: