Play Store Application link – SQL in 18 steps – App on Google Play

Introduction

Imagine you have two lists—one with customer details and another with their orders. How do you combine them? That’s where SQL JOINS come in! Joins help fetch data from multiple tables based on a common column.

1. INNER JOIN – Common Data Only

Fetches rows that have matching values in both tables.

Example Scenario:

Find customers who have placed orders.

SELECT Customers.name, Orders.order_id, Orders.amount
FROM Customers
INNER JOIN Orders ON Customers.customer_id = Orders.customer_id;

Result:

If a customer hasn’t placed an order, they won’t appear in the result.

2. LEFT JOIN – All from Left, Matching from Right

Fetches all rows from the left table and matching rows from the right. If no match is found, NULL is returned.

Example Scenario:

Find all customers and their orders, even if they haven’t placed any.

SELECT Customers.name, Orders.order_id, Orders.amount
FROM Customers
LEFT JOIN Orders ON Customers.customer_id = Orders.customer_id;

Result:

Bob has no orders, so NULL appears for order details.

3. RIGHT JOIN – All from Right, Matching from Left

Similar to LEFT JOIN but fetches all records from the right table.

Example Scenario:

Find all orders and their customers, even if some orders are not linked to customers.

SELECT Customers.name, Orders.order_id, Orders.amount
FROM Customers
RIGHT JOIN Orders ON Customers.customer_id = Orders.customer_id;

If an order exists without a linked customer, NULL appears in the customer column.

4. FULL OUTER JOIN – All Data, Matching or Not

Fetches all rows from both tables, with NULL for missing matches.

⚠ MySQL does not support FULL OUTER JOIN. You can simulate it using UNION.

SELECT Customers.name, Orders.order_id, Orders.amount
FROM Customers
LEFT JOIN Orders ON Customers.customer_id = Orders.customer_id
UNION
SELECT Customers.name, Orders.order_id, Orders.amount
FROM Customers
RIGHT JOIN Orders ON Customers.customer_id = Orders.customer_id;

For databases like PostgreSQL, SQL Server, Oracle:

SELECT Customers.name, Orders.order_id, Orders.amount
FROM Customers
FULL OUTER JOIN Orders ON Customers.customer_id = Orders.customer_id;

5. SELF JOIN – Joining a Table with Itself

Used to find relationships within the same table.

Example Scenario:

Find employees and their managers from the same Employees table.

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

6. CROSS JOIN – Cartesian Product

Each row from the first table is paired with all rows from the second table.

Example Scenario:

Find all possible product and discount combinations.

SELECT Products.name, Discounts.discount_percentage
FROM Products
CROSS JOIN Discounts;

This creates a combination of every product with every discount.

7. Join Syntax Differences – MySQL vs Oracle vs SQL Server

Conclusion

SQL Joins are essential for working with relational databases. Depending on the scenario, choosing the right join helps retrieve meaningful data.