SQL-Join Types

In SQL Server, joins are used to retrieve data from multiple tables based on related columns. Below, we'll explore the various types of joins with clear examples and images.

Types of SQL Joins

There are four types of joins in SQL Server.

1. SELF JOIN - Used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
2. INNER JOIN - Returns rows when there is a match in both tables.
3. OUTER JOIN
   • Left Outer Join - Returns all rows from the left table, even if there are no matches in the right table.
   • Right Outer Join - Returns all rows from the right table, even if there are no matches in the left table.
   • Full Outer Join - Returns rows when there is a match in one of the tables.
4. Cross Join - Returns the Cartesian product of the sets of records from the two or more joined tables.

Example

Consider two tables.

Customers

Orders

1. Self Join

A join of a table with itself, often used for hierarchical data. A table joined with itself based on a relationship.

Syntax

SELECT A.columns, B.columns FROM TableName A, TableName B WHERE A.common_column = B.common_column; 

Example

Employees

Query

SELECT E1.Name AS Employee, E2.Name AS Manager FROM Employees E1 LEFT JOIN Employees E2 ON E1.ManagerID = E2.EmployeeID;

Result

2. Inner Join

Retrieves records that have matching values in both tables. Only overlapping sections of the two tables.

Image1

Syntax

SELECT columns
FROM Table1
INNER JOIN Table2
ON Table1.common_column = Table2.common_column;

Query

SELECT Customers.Name, Orders.OrderID, Orders.OrderAmount
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

Result

3. Left Join (or Left Outer Join)

Retrieves all records from the left table and the matching records from the right table. Unmatched rows in the right table will show as NULL. Entire left table + matched rows from the right table.

Syntax

SELECT columns FROM Table1 LEFT JOIN Table2 ON Table1.common_column = Table2.common_column; 

Example

Query

SELECT Customers.Name, Orders.OrderID, Orders.OrderAmount FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID; 

Result

4. Right Join (or Right Outer Join)

Retrieves all records from the right table and the matching records from the left table. Unmatched rows in the left table will show as NULL. Entire right table + matched rows from the left table.

Syntax

SELECT columns FROM Table1 RIGHT JOIN Table2 ON Table1.common_column = Table2.common_column; 

Example

Query

SELECT Customers.Name, Orders.OrderID, Orders.OrderAmount FROM Customers RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;  

Result

5. Full Join (or Full Outer Join)

Retrieves all records when there is a match in either table. Unmatched rows in either table will show as NULL. All rows from both tables, with unmatched rows as NULL.

Syntax

SELECT columns FROM Table1 FULL JOIN Table2 ON Table1.common_column = Table2.common_column; 

Example

Query

SELECT Customers.Name, Orders.OrderID, Orders.OrderAmount FROM Customers FULL JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Result

6. Cross Join

Returns the Cartesian product of the two tables (all possible combinations of rows). All combinations of rows from both tables.

Syntax

SELECT columns FROM Table1 CROSS JOIN Table2; 

Example

Query

SELECT Customers.Name, Orders.OrderID FROM Customers CROSS JOIN Orders;

Result