SQL Joins

SQL joins can be complex. This page refreshes your knowledge by giving a few examples.

Let's assume the following two tables:

Employee:

Sales:

If you are interested in testing the queries below, here is the SQL to create these tables:

CREATE TABLE employee (  id int PRIMARY KEY,   firstname char(63),   lastname char(63) ); INSERT INTO employee VALUES ('1', 'John','Smith'); INSERT INTO employee VALUES ('2', 'James','Williams'); INSERT INTO employee VALUES ('3', 'David','Taylor'); CREATE TABLE sales (  employee int,   product char(63),   price float,   KEY (employee) ); INSERT INTO sales VALUES ('2', 'Printer',50.00); INSERT INTO sales VALUES ('2', 'Mouse',  10.00); INSERT INTO sales VALUES ('3', 'Laptop', 1500.00); INSERT INTO sales VALUES (NULL, 'Desk',  200.00);

Join
The following three statements are equivalent:

SELECT firstname, lastname, product, price FROM employee, sales WHERE employee.id=sales.employee

SELECT firstname, lastname, product, price FROM employee JOIN sales ON employee.id=sales.employee

SELECT firstname, lastname, product, price FROM employee INNER JOIN sales ON employee.id=sales.employee

All result in:

Note that neither John Smith, nor the Desk is listed, since they do not have an equivalent in the other table. Also, note that James Williams occurs twice.

Left Join and Right Join
To get at least one listing of each employee, use a left join:

SELECT firstname, lastname, product, price FROM employee LEFT JOIN sales ON employee.id=sales.employee

This results in:

To get at least one listing of each sale, use a right join:

SELECT firstname, lastname, product, price FROM employee RIGHT JOIN sales ON employee.id=sales.employee

This results in:

Grouping
The left join above did list all employees, but some employees had duplicate records. That may not be what you want. You can solve this by a GROUP BY:

(warning: bad example!) SELECT firstname, lastname, product, price FROM employee LEFT JOIN sales ON employee.id=sales.employee GROUP BY employee.id

Which results in:

However, a big warning is in place: There is no way to know which of the two sales of James Williams will appear in the result!

If you are only interested in the highest sale made, this is simply:

SELECT firstname, lastname, MAX(price) AS "highest sale" FROM employee LEFT JOIN sales ON employee.id=sales.employee GROUP BY employee.id

Do not make the mistake to also select the product:

(warning: bad example!) SELECT firstname, lastname, product, MAX(price) AS "highest sale" FROM employee LEFT JOIN sales ON employee.id=sales.employee GROUP BY employee.id

As the product and price entry may not match (note that the mouse cost €10 and the printer €50, not the other way around):

In fact, some databases like PostgreSQL won't even let you execute the above SQL statement, because the result is undefined.

To find the correct way to query the highest sale of each employee, we need to dive into queries that select the top n rows in a table.

Selecting Top Rows
The above problems boil down to the problem that you want to select a set of specific rows in the sales table. In this case, you want, for each employee, the record with the highest price. This problem is generally know as "selecting top n rows".

We can either use a query with HAVING, or a slightly less efficient query without a HAVING clause:

SELECT firstname, lastname, s1.product AS "best sale", s1.price AS "highest price" FROM employee LEFT JOIN sales s1 ON employee.id=s1.employee LEFT JOIN sales s2 ON s1.employee=s2.employee GROUP BY employee.id  HAVING max(s2.price)=s1.price

with result:

and:

SELECT firstname, lastname, s1.product AS "best sale", s1.price AS "highest price" FROM employee LEFT JOIN sales s1 ON employee.id=s1.employee LEFT JOIN sales s2 ON s1.employee=s2.employee AND s1.price < s2.price WHERE s2.price IS NULL

with result:

Note that these are relatively complex queries, because you LEFT JOIN the sales table twice: once to select an entry, and once to "loop" through all rows in the sales table to make sure the current row has the highest value. The details are explained on the page SQL Top n Rows.

Subselections
Smart readers will have noticed that the top-n queries are very inefficient. For each employee with n sales, there are n*n records int the LEFT JOIN queries, and only 1 is filtered out. If you have a lot of records, this may become slow.

A better solution is to use subqueries, as they support the "ANY", "ALL" and "SOME" comparison.

In this case, what we really like is to LEFT JOIN with a WHERE clause determined by a subquery:

SELECT firstname, lastname, product AS "best sale", price AS "highest price" FROM employee LEFT JOIN sales ON   price=(SELECT MAX(price) FROM sales WHERE employee.id=sales.employee)

which results in:

This is without doubt the most elegant and probably fastest way to make this query. However, not all database servers may support subqueries.

Top-2 sales
Using the technique explained in SQL Top n Rows, we can select the top-n (here top 2) sales for each employee. We first select the two highest prices for that employee with the subquery SELECT price FROM sales WHERE employee.id=sales.employee ORDER BY price DESC LIMIT 2, and then use that result to select only rows whose price is higher than at least one of those values:

SELECT firstname, lastname, product AS "best sale", price AS "highest price" FROM employee LEFT JOIN sales ON employee.id=sales.employee AND price>= ANY (SELECT price FROM sales WHERE employee.id=sales.employee ORDER BY price DESC LIMIT 2)

results in: