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: