Introduction
SQL Joins and Window Functions are essential tools for any data professional. Joins help combine data from multiple tables, while Window Functions allow calculations across rows without collapsing them. This guide breaks down both concepts with simple examples using African names and cities.
Part 1: SQL Joins
Sample Tables
First, let's create our sample data:
sql
-- Departments table
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50),
location VARCHAR(50)
);
INSERT INTO departments VALUES
(1, 'Engineering', 'Nairobi, Kenya'),
(2, 'Marketing', 'Cape Town, South Africa'),
(3, 'Sales', 'Lagos, Nigeria'),
(4, 'Legal', 'Nairobi, Kenya'),
(5, 'HR', 'Kampala, Uganda');
-- Employees table
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
dept_id INT,
salary DECIMAL(10,2)
);
INSERT INTO employees VALUES
(1, 'Nginda Nganga', 1, 85000),
(2, 'Kwame Mensah', 1, 75000),
(3, 'Zanele Khumalo', 2, 65000),
(4, 'Akinyi Odera', NULL, 55000),
(5, 'Kacungira Shanice', NULL, 6600);
1. INNER JOIN
Returns only matching records from both tables.
sql
SELECT e.emp_name, d.dept_name, d.location
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;
Result:
emp_name dept_name location
Nginda Nganga Engineering Nairobi, Kenya
Kwame Mensah Engineering Nairobi, Kenya
Zanele Khumalo Marketing Cape Town, South Africa
Only employees with departments appear (Akinyi and Kacungira are excluded because they have no department).
2. LEFT JOIN
Returns all records from the left table (employees).
sql
SELECT e.emp_name, d.dept_name, d.location
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
Result:
emp_name dept_name location
Nginda Nganga Engineering Nairobi, Kenya
Kwame Mensah Engineering Nairobi, Kenya
Zanele Khumalo Marketing Cape Town, South Africa
Akinyi Odera NULL NULL
Kacungira Shanice NULL NULL
All employees appear. Akinyi and Kacungira show NULL for department since they're unassigned.
- RIGHT JOIN
Returns all records from the right table (departments).
sql
SELECT e.emp_name, d.dept_name, d.location
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;
Result:
emp_name dept_name location
Nginda Nganga Engineering Nairobi, Kenya
Kwame Mensah Engineering Nairobi, Kenya
Zanele Khumalo Marketing Cape Town, South Africa
NULL Sales Lagos, Nigeria
NULL Legal Nairobi, Kenya
NULL HR Kampala, Uganda
All departments appear. Sales, Legal, and HR have no employees assigned.
- SELF JOIN
Joining a table with itself.
sql
-- Find employees who share the same department
SELECT
e1.emp_name AS employee1,
e2.emp_name AS employee2,
d.dept_name
FROM employees e1
INNER JOIN employees e2 ON e1.dept_id = e2.dept_id AND e1.emp_id < e2.emp_id
INNER JOIN departments d ON e1.dept_id = d.dept_id;
Result:
employee1 employee2 dept_name
Nginda Nganga Kwame Mensah Engineering
Shows colleagues working in the same department.
Part 2: Window Functions
Window functions perform calculations across rows while keeping individual row details.
Sample Sales Data
sql
-- Sales table
CREATE TABLE sales (
sale_id INT PRIMARY KEY,
product VARCHAR(50),
category VARCHAR(50),
amount DECIMAL(10,2),
sale_date DATE
);
INSERT INTO sales VALUES
(1, 'Maize Flour', 'Food', 12500, '2024-01-15'),
(2, 'Coffee Beans', 'Beverages', 45000, '2024-01-15'),
(3, 'Shea Butter', 'Beauty', 8900, '2024-01-18'),
(4, 'Maize Flour', 'Food', 15200, '2024-01-20'),
(5, 'Green Tea', 'Beverages', 85000, '2024-01-10');
- ROW_NUMBER() - Ranking Items sql
SELECT
product,
category,
amount,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY amount DESC) AS rank
FROM sales;
Result:
product category amount rank
Green Tea Beverages 85000 1
Coffee Beans Beverages 45000 2
Shea Butter Beauty 8900 1
Maize Flour Food 15200 1
Maize Flour Food 12500 2
Ranks products within each category by amount.
- Running Total sql
SELECT
sale_date,
product,
amount,
SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales
ORDER BY sale_date;
Result:
sale_date product amount running_total
2024-01-10 Green Tea 85000 85000
2024-01-15 Maize Flour 12500 97500
2024-01-15 Coffee Beans 45000 142500
2024-01-18 Shea Butter 8900 151400
2024-01-20 Maize Flour 15200 166600
Shows cumulative sales over time.
- LAG() - Compare with Previous Row sql
SELECT
sale_date,
product,
amount,
LAG(amount, 1) OVER (ORDER BY sale_date) AS previous_amount,
amount - LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS difference
FROM sales;
Result:
sale_date product amount previous_amount difference
2024-01-10 Green Tea 85000 NULL 85000
2024-01-15 Maize Flour 12500 85000 -72500
2024-01-15 Coffee Beans 45000 12500 32500
2024-01-18 Shea Butter 8900 45000 -36100
2024-01-20 Maize Flour 15200 8900 6300
Compares each sale to the previous one.
- Average by Department sql
SELECT
emp_name,
dept_id,
salary,
AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg_salary
FROM employees
WHERE dept_id IS NOT NULL;
Result:
emp_name dept_id salary dept_avg_salary
Nginda Nganga 1 85000 80000
Kwame Mensah 1 75000 80000
Zanele Khumalo 2 65000 65000
Shows each employee compared to their department average.
Quick Reference
JOIN Types
Join Type - What It Returns
INNER JOIN - Only matching records from both tables
LEFT JOIN - All records from left table + matches from right
RIGHT JOIN - All records from right table + matches from left
FULL JOIN - All records from both tables
Common Window Functions
Function - What It Does
ROW_NUMBER() - Assigns unique rank (1,2,3,4)
RANK() - Same rank for ties, skips numbers (1,2,2,4)
DENSE_RANK()- Same rank for ties, no skipping (1,2,2,3)
LAG() - Access previous row's data
LEAD() - Access next row's data
SUM() - OVER() Running totals
Summary
Joins combine data from multiple tables based on relationships
Window functions perform calculations across rows without grouping
Use INNER JOIN for matched records, LEFT/RIGHT JOIN to preserve all records
Use ROW_NUMBER() for ranking, LAG() for comparisons, SUM() OVER() for running totals
Both tools are essential for writing efficient, powerful SQL queries in real-world applications.
Nginda Nganga- a data science student, balancing life and hustle
Top comments (0)