Now that you understand the basic concepts and examples of SQL joins, you can explore the different types in detail.
To understand the various joins in detail, let's first review examples of joins and their uses.
Inner Joins - Returns only records that match in both tables.(Its like a command that only requests for matching records only and leaves out the others).
Left Joins-A left join returns all rows from the left table, and the matching rows from the right table. If no match is found in the right table, NULL values are returned for right table columns. A right join performs the inverse.
Full Outer Join - Brings together everything from both tables even if they are not matching.
Let's explore practical applications of joins with real-world examples;
We are going to have data from 3 different tables that are related about a shop that is called Duka which will be used as the shema name. The 3 tables will have different columns representing the data that is going to be collected by the user that is the customer table, orders table & products table.
Customer_table:
| customer_id | name | phone | location |
|---|---|---|---|
| 1 | Peter Mwangi | 0721111111 | Kileleshwa |
| 2 | Grace Njoroge | 0722222222 | Kawangware |
| 3 | John Otieno | 0723333333 | Kileleshwa |
| 4 | Faith Wambui | 0724444444 | Buruburu |
| 5 | Samuel Kiptoo | 0725555555 | Umoja |
| 6 | Lucy Achieng | 0726666666 | Kawangware |
| 7 | David Mutua | 0727777777 | Buruburu |
| 8 | Ann Wanjiku | 0728888888 | Kileleshwa |
orders table
| order_id | customer_id | product_id | quantity | order_date |
|---|---|---|---|---|
| 1 | 1 | 3 | 2 | 2026-05-01 |
| 2 | 1 | 4 | 1 | 2026-05-01 |
| 3 | 2 | 7 | 3 | 2026-05-02 |
| 4 | 3 | 1 | 1 | 2026-05-02 |
| 5 | 3 | 10 | 2 | 2026-05-03 |
| 6 | 4 | 8 | 1 | 2026-05-03 |
| 7 | 5 | 3 | 5 | 2026-05-04 |
| 8 | 6 | 7 | 2 | 2026-05-05 |
| 9 | 7 | 4 | 3 | 2026-05-05 |
| 10 | 1 | 10 | 1 | 2026-05-06 |
| 11 | 2 | 3 | 1 | 2026-05-06 |
products table
| product_id | product_name | product_category | price | stock_level | supplier |
|---|---|---|---|---|---|
| 1 | Unga wa ngano | Grains & Cereals | 180.00 | 50 | Kenya Grain Millers |
| 2 | Mchele Pishori | Grains & Cereals | 235.00 | 60 | Kenya Grain Millers |
| 3 | Sukari | Grains & Cereals | 165.00 | 90 | Kenya Grain Millers |
| 4 | Maziwa Fresh | Dairy | 60.00 | 30 | Brookside Dairy |
| 5 | Mtindi | Dairy | 90.00 | 20 | Brookside Dairy |
| 6 | Chai ya Majani | Beverages | 250.00 | 25 | Kenya Beverages Ltd |
| 7 | Soda | Beverages | 70.00 | 45 | Kenya Beverages Ltd |
| 8 | Sabuni ya kufulia | Household | 55.00 | 35 | Metro Wholesalers |
| 9 | Mkate | Snacks & Bakery | 65.00 | 20 | Britania Ltd |
| 10 | Maharagwe | Grains & Cereals | 200.00 | 35 | Kenya Grain Millers |
| 11 | Unga wa Dola | Grains & Cereals | 195.00 | 45 | Kenya Grain Millers |
--example 1:which products have never been ordered
--want to sell all products WHERE orders which are NULL
--LEFT TABLE -PRODUCTS TABLE
--RIGHT TABLE -ORDERS TABLE
LEFT JOIN
We are going to try out the first example with a Left Join.
select dp.product_name
from duka.duka_products dp --->dp acts as an alias name for the products table.
left join duka.duka_orders o --->o is the alias name for the orders table.
on dp.product_id = o.product_id
where o.order_id is NULL;
A _LEFT JOIN _returns all rows from the left table (products), and the matching rows from the right table(orders). If no match exists, the order's columns are null.
The output will be:
| product_name |
|---|
| Unga wa Dola |
| Mchele Pishori |
| Mtindi |
| Chai ya Majani |
| Mkate |
This means that the above items above were never ordered from the shop.
RIGHT JOIN
select dp.product_name
from duka.duka_orders o
right join duka.duka_products dp
on dp.product_id = o.product_id
where o.order_id is NULL;
A RIGHT JOIN retrieves all rows from the right table (products) and matching rows from the left table, with NULL values for the left table's columns if no match is found.
| product_name |
|---|
| Unga wa Dola |
| Mchele Pishori |
| Mtindi |
| Chai ya Majani |
| Mkate |
RIGHT JOIN: keep everything from the second (right) table, matching or not.
INNER JOIN
It deals with only matching records on both tables;
Example: Find a pair of customers who live in a specific location eg.Kawangware. It's a self-join: the duka_customers table is joined to itself.
select dc.name as customer_a, dc2.name as customer_b,dc.location
from duka1.duka_customers dc
inner join duka1.duka_customers dc2
on dc.location = dc2.location and dc.customer_id < dc2.customer_id
where dc.location ='Kawangware';
To compare rows within the duka_customers table, two aliases, dc and dc2, are used. This allows SQL to differentiate between the two instances of the table. The condition on dc.location = dc2.location pairs customers by location, and dc.customer_id < dc2.customer_id is crucial for the comparison.
The results for the query will be for the customer;
| customer_a | customer_b | location |
|---|---|---|
| Grace Njoroge | Lucy Achieng | Kawangware |
where dc.location = 'Kawangaware' limits the results to customers that share the same location that is kawangaware.
Summary
SQL joins combine data from multiple tables using common columns. This article demonstrated _INNER JOIN, LEFT JOIN, RIGHT JOIN _and FULL OUTER JOIN with examples from a shop database. _LEFT _and RIGHT JOINs can find un-ordered products, while INNER JOIN returns only matching records. We also covered self-joins, which compare records within the same table, like finding customers in the same location. Mastering these joins is crucial for retrieving related information, analyzing data, and answering business questions effectively in real-world data analysis.
Top comments (0)