SQL Joins Made Easy: A Practical Guide With Everyday Examples

SQL Joins are a fundamental concept for data analysts, Power BI developers, and backend engineers. In this article, I will explain different types of joins using simple Customer and Orders tables, supported with clear examples and screenshots from MySQL Workbench.

✔ customer_data :

select * from custumer_data;

Customer table & values

✔ orders_data :

select * from orders_data;

1. INNER JOIN

Definition: Returns only the rows that have matching values in both tables unmatching records are excluded.

Simple Example : Only customers who placed orders.

select a.id, a.name, a.address, b.id, b.date, b.amount from custumer_data a inner join orders_data b on a.Id = b.ID;

Inner Join Query and Output

2. LEFT JOIN (LEFT OUTER JOIN)

Definition: A LEFT JOIN returns all the rows from the left table, and the matching rows from the right table. Unmatched rows from the right table return as NULL.

Use case : Finding customers who did or did not place orders.

Query: select a.id, a.name, a.address, b.id, b.date, b.amount from custumer_data a left join orders_data b on a.Id = b.ID;

Left join with Query & Output

This gives you: ✔ All customers ✔ Their orders (if any) ✔ NULL where no orders exist

When used in real time?

✔ Find customers who never ordered ✔ Customer engagement analysis

3. RIGHT JOIN (RIGHT OUTER JOIN)

Definition: A RIGHT JOIN returns all the rows from the right table, and the matching rows from the left table. Unmatched rows from the left table return as NULL.

Use case : Identify orders that do not have matching customer records.

Query: select a.id, a.name, a.address, b.id, b.date, b.amount from custumer_data a right join orders_data b on a.Id = b.ID;

Right Join with Query and Output

When used?

✔ Identify orphan orders ✔ Data quality checks

4. FULL JOIN

(Not available in MySQL directly) But it can be simulated.

Returns all rows from both tables. Matching rows are combined. Non-matching rows show NULL on whichever table lacks the match.

Use case : Complete comparison of two datasets → matched + unmatched records.

Query:
select * from custumer_data a LEFT JOIN orders_data b ON a.ID = b.ID UNION select * from custumer_data a RIGHT JOIN orders_data b ON a.ID = b.ID;

Full Join with Query and Output

5. CROSS JOIN

Definition: Returns the Cartesian product — all combinations of rows from both tables.

Use case: Generating combinations, test datasets, scheduling grids.

Example: Every row from Table A combines with Every row from Table B

If: customer_data has 7 rows, orders_data has 5 rows

Then: Result of CROSS JOIN = 7 × 5 = 35 rows

This join does not use any JOIN condition, because it matches everything with everything.

Cross Join with Query and Output

6. SELF JOIN

Definition: A table is joined with itself. Used when a table contains hierarchical or comparative data (like manager–employee, product vs. product).

Use case :

✔ Comparing customers with other customers ✔ Finding duplicates ✔ Parent–child hierarchy

Query: select c1.id as Customer1_ID, c1.name as Customer1, c2.id as Customer2_ID, c2.name as Customer2, c1.address FROM custumer_data c1 JOIN custumer_data c2 ON c1.address = c2.address AND c1.id <> c2.id;

Self Join With Query and Output

Summary :

SQL Joins simplify the process of analyzing and combining data from multiple tables. Using simple Customer and Order datasets, we explored INNER, LEFT, RIGHT, FULL, CROSS, and SELF JOIN operations. These join techniques are essential for anyone working with MySQL, Power BI, or real-world data analytics.