SQL Refresher: Joins & Aggregates

My memory on some SQL features often gets hazy until they’re needed by a project that demands those skills. This blog serves as a recall aid - hope you find it useful also.

Inner Join

Returns only rows that have a key match in both tables. Typically a join that does not use inner/outer notation is implicitly an inner join.
An inner join is also known as an Equi-Join.

Example: List all employees of an organisation who have been assigned to a division

select e.name, d.name
from employees e
inner join divisions d on e.division_id = d.id

Outer Join

There are 3 types; Left, Right and Full. They ensure all rows from one or both tables are present in the result set.

Left Outer Join

All rows in the left table are included in the result regardless of a key match with the right table.

Example: List all employees of an organisation regardless of whether they are assigned to a division

select e.name, d.name
from employees e
left outer join divisions d on e.division_id = d.id

Right Outer Join

The reverse of a left join, all rows in the right table are included in the result regardless of a key match with the left table.

Example: List all divisions of an organisation regardless of whether they have an employee assigned

select e.name, d.name
from employees e
right outer join divisions d on e.division_id = d.id

Full Outer Join

Combines a Left and Right Join, all rows in both tables are included in the result regardless of a key match.

Example: List all employees and divisions of an organisation

select d.name, e.name
from divisions d
full outer join employees e on d.id = e.division_id

More on Joins: Wikipedia. Note Natural Joins are similar to Inner Joins and Cross Joins perform a cartesian product.

Group By

Aids removing redundant results when aggregate functions are performed.

Example: List the total cost of orders

select sum(cost) as total
from purchase_items
group by order_id

Having

Facilitates adding selection criteria to aggregate columns in a result set.

Example: List the total cost of orders with a minimum spend

select sum(cost) as total
from purchase_items
having total > 100