SQL Joins (Inner Join, Left and Right Join,Full Outer Join,Cross Join,Self Join)

SQL Joins are critical features of relational database systems, enabling you to merge and analyze data from multiple tables based on shared columns. They allow efficient data retrieval and play a significant role in solving real-world data challenges.

In this guide, we’ll explore various types of SQL Joins, such as INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN,CROSS JOIN, and SELF JOIN, along with their syntax, examples, and use cases.

What is SQL Join?

SQL JOIN is a powerful operation used in relational database management systems (RDBMS) to combine data from two or more tables based on a related column between them. It allows you to retrieve meaningful data from multiple tables in a single query, making it easier to analyze and manage relational data.

When working with multiple tables, SQL JOINs enable you to link the tables together through common columns. These relationships can be defined using primary keys and foreign keys or any other matching attributes between the tables.

Types of SQL Joins:

There are different types of SQL joins, and each is used for different purposes based on how you want to handle rows that don’t have matching values in the related tables. The most common types are:

• INNER JOIN
• LEFT JOIN (LEFT  OUTER JOIN)
• RIGHT JOIN (RIGHT OUTER JOIN)
• FULL JOIN (FULL  OUTER JOIN)
• CROSS JOIN
• SELF JOIN

SQL INNER JOIN

Definition:
An INNER JOIN is a type of JOIN operation in SQL used to combine rows from two or more tables based on a related column between them. It returns only the rows where there is a match in both tables.

Syntax:

SELECT column1, column2, ...
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Example Scenario:

You have two tables:

1. customers
2. orders

customers Table:

orders Table:

You want to retrieve the customer names and their respective orders using an INNER JOIN.

SQL Query:

SELECT customers.Name, orders.order_id, orders.order_date
FROM customers
INNER JOIN orders
ON customers.id = orders.customer_id;

Output:

Advantages:

1. Retrieves only matching data, providing precise results.
2. Reduces unnecessary data in the result set, improving performance.
3. Supports combining data from multiple tables for more comprehensive queries.
4. Ensures data consistency by only returning rows that have related entries in both tables.
5. Useful for relational database design, where data is normalized across multiple tables.

Uses:

1. Combining customer and order data from a customers table and an orders table.
2. Fetching data from multiple tables in reporting applications.
3. Querying sales transactions along with product details.
4. Joining user data with login history for analyzing user activity.

LEFT JOIN

Definition:

A LEFT JOIN (or LEFT OUTER JOIN) returns all the rows from the left table (in this case, the customers table), and the matched rows from the right table (in this case, the orders table). If there is no match, the result is NULL on the right side.

SELECT customers.Name, orders.order_id, orders.order_date
FROM customers
LEFT JOIN orders
ON customers.id = orders.customer_id;

Output:

Explanation:

• In the case of LEFT JOIN, the result includes all customers from the customers table.
• For customers who have matching orders, their order details (like order_id and order_date) will appear.
• If there were any customers without orders, their order_id and order_date would show as NULL.

RIGHT JOIN

Definition:

A RIGHT JOIN (or RIGHT OUTER JOIN) returns all the rows from the right table (in this case, the orders table), and the matched rows from the left table (in this case, the customers table). If there is no match, the result will contain NULL on the left side.

SQL Query with RIGHT JOIN:

SELECT customers.Name, orders.order_id, orders.order_date
FROM customers
RIGHT JOIN orders
ON customers.id = orders.customer_id;

Output:

Explanation:

• In the case of RIGHT JOIN, the result includes all orders from the orders table.
• For each order, the corresponding customer name will appear if there is a matching customer_id.
• If an order exists but there is no matching customer (e.g., a customer with no ID or an invalid customer), the Name will be shown as NULL.

FULL OUTER JOIN

Definition:

A FULL OUTER JOIN returns all the rows when there is a match in either the left table (customers) or the right table (orders). If there is no match, the result will contain NULL on the side without a match. It combines the results of both LEFT JOIN and RIGHT JOIN.

SQL Query with FULL OUTER JOIN:

SELECT customers.Name, orders.order_id, orders.order_date
FROM customers
FULL OUTER JOIN orders
ON customers.id = orders.customer_id;

Explanation:

• A FULL OUTER JOIN returns all rows from both tables, whether there is a match or not.
• If there is no matching row in the other table, NULL values will be filled for the missing columns.

This result combines all rows from both the customers and orders tables, filling in NULLs where there is no match.

CROSS JOIN

Definition:

A CROSS JOIN returns the Cartesian product of the two tables involved. It combines each row from the first table with every row from the second table. This results in the total number of rows being the product of the number of rows in both tables.

SQL Query with CROSS JOIN:

SELECT customers.Name, orders.order_id, orders.order_date
FROM customers
CROSS JOIN orders;

Output:

Explanation:

• A CROSS JOIN returns the Cartesian product of the customers and orders tables.
• Each customer is paired with every order. If there are 3 customers and 4 orders, the result will have 3 × 4 = 12 rows (as shown in the output).
• No condition is applied here, unlike other joins. It simply combines every row from the first table with every row from the second table.

SELF JOIN

Definition:

A SELF JOIN is a join in which a table is joined with itself. This can be useful when you need to compare rows within the same table. A SELF JOIN uses aliases for the table to distinguish between the multiple instances of the same table being joined.

SQL Query with SELF JOIN:

Let’s assume we have a employees table where we want to find managers and their subordinates. The table looks like this:

employees Table:

Here, manager_id represents the employee_id of the manager.

SQL Query with SELF JOIN:

SELECT e1.name AS Employee, e2.name AS Manager
FROM employees e1
LEFT JOIN employees e2
ON e1.manager_id = e2.employee_id;

Output: