DEV Community: Ian munene

SQL Joins Explained in Detail

Ian munene — Mon, 28 Sep 2026 19:04:18 +0000

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.

Getting into SQL For Beginners:

Ian munene — Fri, 11 Sep 2026 20:12:55 +0000

Starting new endeavors can feel daunting, but every journey begins with a single step. Learning SQL opens doors to new opportunities. This guide simplifies the fundamentals of SQL, teaching you its essential elements.

1.DDL(Data Definition Language)

DDL defines and modifies the database structure and schema, determining table appearance and data content. There are various commands that are used to achieve this:

CREATE SCHEMA- is a standard SQL statement used to create new namespaces within a database, grouping related objects like tables.
CREATE TABLE- creates a new table and defines its columns.
ALTER- command modifies existing tables by adding, renaming, or changing the data type of columns.
DROP-permanently removes unnecessary columns.
TRUNCATE- It removes all the rows from the table while leaving the structure intact.

This example demonstrates creating a schema for Greenwood Academy ,the table students and altering:

CREATE SCHEMA greenwood_academy;
CREATE TABLE greenwood_academy.students(
student_id INT PRIMARY KEY,
first_name  VARCHAR(50) NOT null,
last_name   VARCHAR(50) NOT null,
gender  VARCHAR(1));

Notice the punctuation marks on the schema name where the _ is used as a space.
The _VARCHAR(50)_is used to indicate that it can store a text with around 50 strings
When creating a table, it's advisable to include the schema name separated by a full stop (e.g., schema_name.table_name). This ensures the table is created within the specified schema, such as greenwood_academy.

To add a new column, use ALTER TABLE.

ALTER TABLE greenwood_academy.students
add column phone_number VARCHAR(20);

2.DML(Data Manipulation Language)

DML helps to manipulate the data records within the structure where commands like:
INSERT- adds new rows of data into a database table.
UPDATE- modifies or changes existing records within a table.
DELETE- removes existing rows
SELECT*- retrieves and read data from a database.
Example:

update greenwood_academy.students
set city= 'Nairobi'
where student_id =5;

This code means that where the student_id is 5 the students city should be changed to Nairobi.

3.SQL Joins

SQL Joins are used to combine data from two/more tables using a related column:
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) eg.;

select c.name,o.product_id, quantity --This shows the columns one want to see in the final results.
from  duka1.duka_customers c --the `c` is an alias we give to the customers table.
inner join duka1.duka_orders o -- the `o` is an alias given to the orders table.
on c.customer_id = o.customer_id;

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. eg;

select dc.name
from duka1.duka_customers dc 
left join duka1.duka_orders t on dc.customer_id = t.customer_id
where t.order_id is null;

the null shows the results of a customer with no orders.
Full Outer Join - Brings together everything from both tables even if they are not matching.

4.SUBQUERIES & CTES

A subquery is a query embedded within another SQL statement, whereas a CTE (Common Table Expression) is a named, temporary result set defined with the WITH clause at the start of a query.
They are useful in different scenarios where the subquery is used when writing simple calculations while CTES are used with complex multi-step queries/calculation.
If done correctly they all give the same results.
An example showing the difference between a CTE and a subquery:

--SUBQUERY WAY--
select score_id,member_id,score 
from study_group.quiz_scores qs
where score > ( select avg(score) from study_group.quiz_scores) order by score desc;

--THE CTE way- This creates a temporary result at first called the group_avg which then used by the main query.
with group_avg as 
( select avg(score) as avg_score
from study_group.quiz_scores)
select score_id, member_id,score
from study_group.quiz_scores
where score > ( select avg_score from group_avg)
order by score desc;

The subguery puts the calculation directly inside the WHERE while the CTE puts it separately using WITH.
In simple terms the CTE does the avg first then uses it while the subquery calculates the avg as it uses it.

5.SQL FUNCTIONS

SQL functions help to manipulate data, perform calculations and format outputs directly within database queries.
They are categorized into 2 where:

scalar Functions - they operate on a single row and return one value per row.eg;** UPPER(),LOWER(),ROUND(),LENGTH()**

select first_name, UPPER (first_name) as name_upper,
       last_name, LOWER(last_name) as name_lower
  from greenwood_academy.students;

as renames the column.

-Aggregate functions - they operate on a collection of rows to return a single summarized value.eg. Count(),AVG(),SUM(),MIN/MAX()...
Example:

select 
     COUNT(*) as total_results,
     AVG(marks) as average_mark,
     MIN(marks) as lowest_mark,
     MAX(marks) as highest_mark,
     SUM(marks) as total_marks
     from greenwood_academy.exam_results;

Conclusion

Mastering DDL, DML, joins, subqueries, CTEs, and SQL functions is crucial for SQL beginners. Consistent practice with these concepts will enable confident data analysis.