In SQL Server, JOIN is used to combine rows from two or more tables based on a related column between them. There are different types of JOIN operations, including INNER JOIN and OUTER JOIN. The main difference between INNER JOIN and OUTER JOIN is how they handle unmatched rows.

INNER JOIN

INNER JOIN returns only the rows that have matching values in both tables based on the specified condition in the ON clause. It excludes all the rows that do not have a match in either table. For example, if you have two tables - "orders" and "customers" - you can use INNER JOIN to retrieve only the orders that belong to a customer by joining the "customer_id" column in both tables.

Syntax

SELECT * FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_id;

In the example above, only the rows that have matching values in both tables based on the "customer_id" column will be returned.

OUTER JOIN

OUTER JOIN is used to retrieve all the rows from one table, even if there is no matching row in the other table. There are three types of OUTER JOIN - LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.

LEFT OUTER JOIN returns all the rows from the left table and matching rows from the right table based on the specified condition in the ON clause. If there is no matching row in the right table, the result set will contain NULL values for the columns of the right table.

Syntax

SELECT * FROM orders
LEFT OUTER JOIN customers
ON orders.customer_id = customers.customer_id;

In the example above, all the rows from the "orders" table will be returned, even if there is no matching row in the "customers" table. The columns of the "customers" table that do not have a matching row in the "orders" table will contain NULL values.

RIGHT OUTER JOIN is similar to LEFT OUTER JOIN, but it returns all the rows from the right table and matching rows from the left table based on the specified condition in the ON clause. If there is no matching row in the left table, the result set will contain NULL values for the columns of the left table.

Syntax

SELECT * FROM orders
RIGHT OUTER JOIN customers
ON orders.customer_id = customers.customer_id;

In the example above, all the rows from the "customers" table will be returned, even if there is no matching row in the "orders" table. The columns of the "orders" table that do not have a matching row in the "customers" table will contain NULL values.

FULL OUTER JOIN returns all the rows from both tables, including unmatched rows. If there is no matching row in one of the tables, the result set will contain NULL values for the columns of the table that does not have a match.

Syntax

SELECT * FROM orders
FULL OUTER JOIN customers
ON orders.customer_id = customers.customer_id;

In the example above, all the rows from both tables will be returned, including unmatched rows. The columns that do not have a matching row in either table will contain NULL values.

Inner Join vs Outer Join

The Inner join returns only the rows for which there is an match in both tables.
The Inner join eliminate the rows that do not match with a row from the other table.
The Outer join returns unmatched rows.
The Outer join return all rows from at least one of the tables.

Students table:

Library table:

Inner Join

select s.ID, s.Name, l.Title
from Students s
INNER JOIN Library l
on s.ID=l.Student_id;

Results

Full Outer Join

select s.ID, s.Name, l.Title
from Students s
FULL OUTER JOIN Library l
on s.ID=l.Student_id;

Results