SQL Group By with Limit in MySQL: Finding the top n values per group

Say you have the following database table called "tbl_test" which contains rows which can be grouped by a column.

id	group_name	value
1	a	0
2	a	8
3	a	9
4	a	3
5	b	5
6	b	6
7	b	4
8	c	2
9	c	9

If you want to find the maximum value in each group, that's easy using a Group By statement and a MAX function:

SELECT
	`tbl_test`.`group_name` AS `group_name`,
    MAX(`tbl_test`.`value`) AS `value`
FROM
	`tbl_test`
GROUP BY
	`tbl_test`.`group_name`
ORDER BY
	`tbl_test`.`group_name`
;

group_name	value
a	9
b	6
c	9

But what if you wanted to find the top 2 values in each group? In MySQL, this can be achieved using the aggregate function GROUP_CONCAT which takes all the values in a column in a group, sorts them, and concatenates them into a single string. The problem is that in MySQL v5 there is no way to limit the number of values used, so we'll simulate this using the SUBSTRING_INDEX function, which takes the first n values in a string containing delimited values. For example SUBSTRING_INDEX("ab,c,de", ",", 2) returns "ab,c".

SELECT
	`tbl_test`.`group_name` AS `group_name`,
    SUBSTRING_INDEX(
        GROUP_CONCAT(`tbl_test`.`value` ORDER BY `tbl_test`.`value` DESC SEPARATOR ","),
        ",", 2
    ) AS `values`
FROM
	`tbl_test`
GROUP BY
	`tbl_test`.`group_name`
;

group_name	value
a	9,8
b	6,5
c	9,2

This puts the values together in one column. But sometimes you'll want to have each value being in its own row. In that case you can use the same trick we saw in an earlier post about finding the median value in MySQL, which involves using session variables. I got this trick from this other blog.

SET @row_num := 0;
SET @prev_group := NULL;
SELECT
	`t`.`group_name` AS `group_name`,
    `t`.`value` AS `value`
FROM (
    SELECT
        @row_num := IF(@prev_group = `tbl_test`.`group_name`, @row_num + 1, 1) AS `row_num`,
        @prev_group := `tbl_test`.`group_name`,
        `tbl_test`.`group_name` AS `group_name`,
        `tbl_test`.`value` AS `value`
    FROM
        `tbl_test`
    HAVING
        `row_num` <= 2
    ORDER BY
        `tbl_test`.`group_name` ASC,
        `tbl_test`.`value` DESC
) AS `t`
;

The idea here is to sort by the grouping column and the value and then associate a row number with each row, where the row number restarts on the first row of each group. We then use a Having clause to filter out only the rows that have a row number that is two or less.

group_name	value
a	9
a	8
b	6
b	5
c	9
c	2

MySQL extra inner join taking too long: optimizer_search_depth

MySQL has a query optimiser that takes in SQL queries and tries to find the best way to execute them. Then the query gets executed using the best execution plan found. When you use joins in your query, the time the optimiser takes to finish optimising is exponential to the number of joins. This means that for a few joins it only takes a negligible amount of time to finish but after a point, which is somewhere between 7 and 10 tables, the optimisation time will shoot up. In my case I went from 0.1 seconds to 17 seconds but just adding another table in my joins.

To check if this is happening to you, you can profile your query by executing the following:

SET profiling = 1;

*your query*

SHOW PROFILE FOR QUERY 1;

(If you using phpMyAdmin you might need to add "SHOW PROFILE;" as well at the end)

This will show you a table of execution times for each phase in the execution of the query. The time taken by the query optimiser is in the "statistics" row. If this is too high, then you need to stop your optimiser from spending so much time.

Controlling how much time the optimiser should spend doing its job can be accomplished using the optimizer_search_depth variable. This is the maximum depth to search (presumably in some tree search algorithm) when optimising the query and is unfortunately set to the highest value by default. Being set to the highest value makes it the most likely to find the best execution plan but may also be unnecessarily time consuming. Thankfully there is a better default value to use: 0. When this variable is set to zero, the optimiser automatically picks a value based on the query and that seems to work well for me. To do this just execute the following query:

SET optimizer_search_depth = 0;

Finding the nth row in every group in SQL

Let's say you have the following log table which stores the dates of each access:

TimeStamp	IP	UserName
2005-10-30 10:45:03	172.16.254.10	jlor
2005-10-30 10:46:31	172.16.254.12	kpar
2005-10-31 09:14:13	172.16.254.14	jlor
2005-10-31 09:25:42	172.16.254.16	kpar
2005-10-31 12:41:14	172.16.254.19	jlor
2005-11-01 07:15:15	172.16.254.20	kpar

You are asked to make a report of the last time each user has accessed the system using SQL.

At first you try using GROUP BY but then realize that it's not so simple to include the IP field along with the TimeStamp and UserName. GROUP BY works when you're interested in aggregating every field that is not used to group the records. In other words, you can easily do this:

SELECT UserName, MAX(TimeStamp)
FROM log
GROUP BY UserName

USERNAME	MAX(TIMESTAMP)
jlor	October, 31 2005 12:41:14+0000
kpar	November, 01 2005 07:15:15+0000

But if you also want to show the corresponding IP address of the access with the latest time stamp, you'd have a problem using simple SQL. If you add the IP field in the SELECT statement, you'd end up with the first IP in the table that belongs to the corresponding user, rather than the IP of the latest time stamp.

SELECT UserName, IP, MAX(TimeStamp)
FROM log
GROUP BY UserName

USERNAME	IP	MAX(TIMESTAMP)
jlor	172.16.254.10	October, 31 2005 12:41:14+0000
kpar	172.16.254.12	November, 01 2005 07:15:15+0000

The way to do this is to simulate the GROUP BY statement using more expressiveness methods.

MS SQL Server

In MS SQL Server, this is achieved using the ROW_NUMBER function. This function gives a number for each row (1, 2, 3, ...) which can be used inside a SELECT statement. The cool thing about this function is that the numbering can be made to restart for every different value in a field. So if we used it on the UserName field we'd have the following:

SELECT UserName, IP, TimeStamp, ROW_NUMBER() OVER(PARTITION BY UserName ORDER BY TimeStamp DESC)
FROM log

USERNAME	IP	TIMESTAMP	COLUMN_3
jlor	172.16.254.10	October, 30 2005 10:45:03+0000	1
jlor	172.16.254.14	October, 31 2005 09:14:13+0000	2
jlor	172.16.254.19	October, 31 2005 12:41:14+0000	3
kpar	172.16.254.12	October, 30 2005 10:46:31+0000	1
kpar	172.16.254.16	October, 31 2005 09:25:42+0000	2
kpar	172.16.254.20	November, 01 2005 07:15:15+0000	3

It even orders the rows by user name and it lets you say how you want the rows of each user to be ordered so that you can say how you want the numbering. Using the SQL above, the row with the latest time stamp of each user has a 1 in the last column. This allows us to select it. It will have to be inside a nested query however in order to be used in a WHERE statement.

SELECT UserName, IP, TimeStamp
FROM (
  SELECT UserName, IP, TimeStamp, ROW_NUMBER() OVER(PARTITION BY UserName ORDER BY TimeStamp DESC) AS rank
  FROM log
) AS t
WHERE rank = 1

USERNAME	IP	TIMESTAMP	COLUMN_3
jlor	172.16.254.19	October, 31 2005 12:41:14+0000
kpar	172.16.254.20	November, 01 2005 07:15:15+0000

Notice that you can even find when the second to last time an access was made by changing the 1 in the WHERE statement to a 2.

You can experiment with this in this SQL Fiddle.

MySQL

Unfortunately MySQL doesn't have a function as nifty as ROW_NUMBER so instead we'll have to simulate that using variables. In MySQL you can create variables using the SET statement and then update them within a SELECT statement so that they change for each row, like this:

SET @row_number := 0;
SELECT UserName, IP, TimeStamp, @row_number := @row_number + 1
FROM log

USERNAME	IP	TIMESTAMP	@ROW_NUMBER := @ROW_NUMBER + 1
jlor	172.16.254.10	October, 30 2005 10:45:03+0000	1
kpar	172.16.254.12	October, 30 2005 10:46:31+0000	2
jlor	172.16.254.14	October, 31 2005 09:14:13+0000	3
kpar	172.16.254.16	October, 31 2005 09:25:42+0000	4
jlor	172.16.254.19	October, 31 2005 12:41:14+0000	5
kpar	172.16.254.20	November, 01 2005 07:15:15+0000	6

This is only half the story of course. We want the numbering to restart for every user and we also want this to happen after sorting the rows by user name. We also want the rows belonging to each user to be sorted by time stamp. A simple ORDER BY statement can handle the sorting part:

SET @row_number := 0;
SELECT UserName, IP, TimeStamp, @row_number := @row_number + 1
FROM log
ORDER BY UserName, TimeStamp DESC

USERNAME	IP	TIMESTAMP	@ROW_NUMBER := @ROW_NUMBER + 1
jlor	172.16.254.19	October, 31 2005 12:41:14+0000	1
jlor	172.16.254.14	October, 31 2005 09:14:13+0000	2
jlor	172.16.254.10	October, 30 2005 10:45:03+0000	3
kpar	172.16.254.20	November, 01 2005 07:15:15+0000	4
kpar	172.16.254.16	October, 31 2005 09:25:42+0000	5
kpar	172.16.254.12	October, 30 2005 10:46:31+0000	6

The restarting of numbering is a little less simple. We have to keep track of what the previous value was using another variable and we have to also choose between setting row_number to 1 or to increment it by 1. Here is the code:

SET @row_number := 0;
SET @prev_username := NULL;
SELECT UserName, IP, TimeStamp, @row_number := CASE WHEN UserName = @prev_username THEN @row_number + 1 ELSE 1 END, @prev_username := UserName
FROM log
ORDER BY UserName, TimeStamp DESC

USERNAME	IP	TIMESTAMP	@ROW_NUMBER := CASE WHEN USERNAME = @PREV_USERNAME THEN @ROW_NUMBER + 1 ELSE 1 END	@PREV_USERNAME := USERNAME
jlor	172.16.254.19	October, 31 2005 12:41:14+0000	1	jlor
jlor	172.16.254.14	October, 31 2005 09:14:13+0000	2	jlor
jlor	172.16.254.10	October, 30 2005 10:45:03+0000	3	jlor
kpar	172.16.254.20	November, 01 2005 07:15:15+0000	1	kpar
kpar	172.16.254.16	October, 31 2005 09:25:42+0000	2	kpar
kpar	172.16.254.12	October, 30 2005 10:46:31+0000	3	kpar

The CASE statement selects a value to set row_number. If the current row's user name is the same as the previous one's then the value will be one more than row_number it currently is. Otherwise it is set to 1. After that variable is set, the prev_username variable is set to the current row's user name.

Finally we can now use this to select the latest access for each user.

SET @row_number := 0;
SET @prev_username := NULL;
SELECT UserName, IP, TimeStamp
FROM (
  SELECT UserName, IP, TimeStamp, @row_number := CASE WHEN UserName = @prev_username THEN @row_number + 1 ELSE 1 END AS rank, @prev_username := UserName
  FROM log
  ORDER BY UserName, TimeStamp DESC
) AS t
WHERE rank = 1

USERNAME	IP	TIMESTAMP
jlor	172.16.254.19	October, 31 2005 12:41:14+0000
kpar	172.16.254.20	November, 01 2005 07:15:15+0000

Notice that you can even find when the second to last time an access was made by changing the 1 in the WHERE statement to a 2.

You can experiment with this in this SQL Fiddle.

Doing an UPSERT (update or insert if new) in MS SQL Server and MySQL

The UPSERT is an incredibly useful SQL instruction for databases, especially for data synchronization and key-value data structures. Unfortunately it is ignored by some databases.

An UPSERT is an SQL instruction which attempts to update a record (with a particular primary key) and will automatically create a new record with the updated data if it does not exist. This would otherwise be accomplished by first doing a SELECT and then doing an INSERT or UPDATE depending on whether the SELECT found anything. This would require two separate queries plus some programmed logic. The UPSERT allows you to do these two queries in one.

Here's how to do it for MySQL and MS SQL Server. The examples below are based on a table called "tbl" which contains 3 fields: "pk" (primary key), "f1" (field 1), and "f2" (field 2). In both examples the value being inserted is "key" for pk, "data1" for f1, and "data2" for f2.

Notice: Be sure to avoid using autoincrement primary keys for tables in which you want to upsert or it wouldn't make sense to insert a specific key. It is also advisable to lock the tables prior to upserting if the data is busy with multiple upserts being performed concurrently.

MySQL

Source: http://mechanics.flite.com/blog/2013/09/30/how-to-do-an-upsert-in-mysql/

The ON DUPLICATE KEY UPDATE statement allows you to perform an update if an insert is not possible due to an attempt to insert a duplicate value in a field with a UNIQUE constraint.

INSERT INTO tbl (pk, f1, f2) 
  VALUES ('key', 'data1', 'data2')
  ON DUPLICATE KEY UPDATE
    pk = VALUES(pk),
    f1 = VALUES(f1),
    f2 = VALUES(f2)

In order to avoid rewriting the values twice, we can refer to values already specified in an INSERT by using the VALUES function.

MS SQL Server

Source: http://www.sergeyv.com/blog/archive/2010/09/10/sql-server-upsert-equivalent.aspx

In MS SQL, an UPSERT can be achieved using a MERGE. This is originally meant to be used to insert many rows together into a table and then perform some actions (delete, update, insert, etc.) depending on whether or not a particular field already exists.

MERGE INTO tbl AS t
  USING 
    (SELECT pk='key', f1='data1', f2='data2') AS s
  ON t.pk = s.pk
  WHEN MATCHED THEN
    UPDATE SET pk=s.pk, f1=s.f1, f2=s.f2
  WHEN NOT MATCHED THEN
    INSERT (pk, f1, f2)
    VALUES (s.pk, s.f1, s.f2); 

In order to avoid rewriting the values three times, the values were placed in a named nested SELECT. "s" refers to the source of the values, that is the values to add, whilst "t" refers to the target of the values, that is the table in which to add the values.

Finding the median value using MySQL sql

The median of a finite list of numbers is defined as the number in the middle of the list when the list is sorted, or the average (arithmetic mean) of the two numbers in the middle if the size of the list is even.

Since, as I described in my previous post, averages are meaningless when it comes to something like marks, I've decided to start using medians instead since it would be more useful to know in which half of the class you stand with your mark, and medians are much more intuitive than means.

Here is the SQL to find the median in MySQL (adapted from http://stackoverflow.com/a/7263925)

SET @rownum := -1;

SELECT
   AVG(t.mark)
FROM
(
   SELECT
      @rownum := @rownum + 1 AS rownum,
      results.mark AS mark
   FROM
      results
   ORDER BY results.mark
) AS t
WHERE
   t.rownum IN (
      CEIL(@rownum/2),
      FLOOR(@rownum/2)
   )
;

Here's a brief explanation:

In MySQL we cannot use expressions in LIMIT, so instead we have to simulate a LIMIT by using a counter with user defined variables. Each row in the table will have a row number associated with it starting from 0 in the variable @rownum.

We then choose from these numbered rows the ceiling and floor of "(number of rows - 1)/2" which will be the same middle value if the number of rows is odd or the middle two values if the number of rows is even. The variable will still retain its value after all the rows are numbered so we can use it to know the number of rows.

Since the average of one number is the number itself, we can simply calculate the average of whatever is returned without checking. If the number of rows is odd then we shall find the average of the middle value which will remain the same and if the number of rows is even then we shall find the average of the middle two values will will be the correct median.

Don't forget that if you're using this in PHP you have to use two "mysql_query" calls, one for the first SET and another for the SELECT as you cannot use ";" in queries.

Reading Related Tables As Nested Rows

Problem

A problem I used to face with relational databases is how to handle 1-to-many or many-to-many rows in SQL select statements. Let's say you have 2 tables with a many-to-many relationship between them, such as a Movies table and an Actors table. How do I present a list of movies together with their associated actors?

The naive way to do this is by using nested loops with a query for movies in the top loop and a query for actors in the second loop. In PHP it would look something like...

$moviesResult = mysql_query("SELECT id, title FROM movies"); 
while($moviesRow = mysql_fetch_assoc($result)) 
{ 
    echo("<h1>" . $moviesRow['title'] . "</h1>"); 

    echo("<ul>"); 
    $actorsResult = mysql_query("SELECT actors.name FROM actors INNER JOIN movies_actors ON actors.id = movies_actors.actorid WHERE movies_actors.movieid = " . $moviesRow['id'] . ";"); 
    while($actorsRow = mysql_fetch_assoc($actorsResult)) 
    { 
        echo("<li>" . $actorsRow['name'] . "</li>"); 
    } 
    echo("</ul>"); 
}

This approach however will kill your database. Ideally you should minimize the number of queries sent.

Another approach would be to join the movies table with the actors table and then read it with nested loops.

$result = mysql_query("SELECT movies.id AS id movies.title AS title, actors.name AS actor FROM movies INNER JOIN movies_actors ON movies.id = movies_actors.movie INNER JOIN actors ON actors.id = movies_actors.actor"); 
$row = mysql_fetch_assoc($result); 
while($row) 
{ 
    $currMovieId = $row['id']; 
    echo("<h1>" . $row['title'] . "</h1>"); 
    echo("<ul>"); 
    do 
    { 
        echo("<li>" . $row['name'] . "</li>"); 
    } while ($row = mysql_fetch_assoc($result) && $row['id'] == $currMovieId);
    echo("</ul>"); 
}

This works but as soon as you add another table to the join, determining which rows contain new information can be a nightmare, not to mention all the rows which just contain repeated information due to the cartesian product. For example:

Id	Title	Actor	Genre
1	The Matrix	Keanu Reeves	Action
1	The Matrix	Keanu Reeves	Adventure
1	The Matrix	Keanu Reeves	Sci-Fi
1	The Matrix	Laurence Fishburne	Action
1	The Matrix	Laurence Fishburne	Adventure
1	The Matrix	Laurence Fishburne	Sci-Fi
1	The Matrix	Carrie-Anne Moss	Action
1	The Matrix	Carrie-Anne Moss	Adventure
1	The Matrix	Carrie-Anne Moss	Sci-Fi
2	The Matrix Reloaded	Keanu Reeves	Action
2	The Matrix Reloaded	Keanu Reeves	Adventure
2	The Matrix Reloaded	Keanu Reeves	Sci-Fi
2	The Matrix Reloaded	Laurence Fishburne	Action
2	The Matrix Reloaded	Laurence Fishburne	Adventure
2	The Matrix Reloaded	Laurence Fishburne	Sci-Fi
2	The Matrix Reloaded	Carrie-Anne Moss	Action
2	The Matrix Reloaded	Carrie-Anne Moss	Adventure
2	The Matrix Reloaded	Carrie-Anne Moss	Sci-Fi
3	The Matrix Revolutions	Keanu Reeves	Action
3	The Matrix Revolutions	Keanu Reeves	Adventure
3	The Matrix Revolutions	Keanu Reeves	Sci-Fi
3	The Matrix Revolutions	Laurence Fishburne	Action
3	The Matrix Revolutions	Laurence Fishburne	Adventure
3	The Matrix Revolutions	Laurence Fishburne	Sci-Fi
3	The Matrix Revolutions	Carrie-Anne Moss	Action
3	The Matrix Revolutions	Carrie-Anne Moss	Adventure
3	The Matrix Revolutions	Carrie-Anne Moss	Sci-Fi

Are we supposed to receive all those rows just to display the below?

1	The Matrix	Keanu Reeves, Laurence Fishburne, Carrie-Anne Moss	Action, Adventure, Sci-Fi
2	The Matrix Reloaded	Keanu Reeves, Laurence Fishburne, Carrie-Anne Moss	Action, Adventure, Sci-Fi
3	The Matrix Revolutions	Keanu Reeves, Laurence Fishburne, Carrie-Anne Moss	Action, Adventure, Sci-Fi

Solution

The best solution I found is a hybrid of the above 2 solutions. You make a different query for each table and then read all the rows returned by each query, placing the information in the list it belongs to.

$moviesResult = mysql_query("SELECT id, title FROM movies;"); 
$actorsResult = mysql_query("SELECT movies_actors.movieid AS movieid, actors.name AS name FROM actors INNER JOIN movies_actors ON actors.id = movies_actors.actorid;");
$genresResult = mysql_query("SELECT movies_genres.movieid AS movieid, genres.name AS name FROM genres INNER JOIN movies_genres ON genres.id = movies_genres.genreid;"); 

$actorRow = mysql_fetch_assoc($actorsResult); 
$genreRow = mysql_fetch_assoc($genresResult); 
while($movieRow = mysql_fetch_assoc($moviesResult)) 
{ 
    $currMovieId = $movieRow['id']; 
    echo("<h1>" . $movieRow['title'] . "</h1>"); 

    echo("<ul>"); 
    while ($actorRow && $actorRow['movieid'] == $currMovieId) 
    { 
        echo("<li>" . $row['name'] . "</li>"); 
        $actorRow = mysql_fetch_assoc($actorsResult); 
    } 
    echo("</ul>"); 

    echo("<ul>"); 
    while ($genreRow && $genreRow['movieid'] == $currMovieId) 
    { 
        echo("<li>" . $row['name'] . "</li>"); 
        $genreRow = mysql_fetch_assoc($genresResult); 
    } 
    echo("</ul>"); 
}

In this way we only make 3 queries, equal to the number of tables, and we only read as many rows as needed.

SQL Joins Tutorial

This is a simple tutorial aimed towards those who, like me, completely misunderstood how to, and why to, use joins in SQL. It is not meant to be an advanced reference but a tutorial for beginners who know some SQL but can't understand joins.

Huh?

Let's say that you have a database with tables which are related by foreign keys. For example, a Customers table, a Products table and an Orders table. Each row in the Orders table is linked to a row in the Customers table (the customer who made the order) and to a row in the Products table (the product which was ordered).

Now let's say that you want to query the database to view all the rows in the Orders table but with the customer's name and product's name instead of their primary key. If you learned SQL the same way I did, you'd probably do something like this:

SELECT orders.date, customers.name, products.name
FROM orders, customers, products
WHERE orders.customer = customers.id AND orders.product = products.id;

This is called an implicit join. In fact you are doing an unconditioned join between all 3 tables and then just filtering out the joined rows which are not made of related table rows. What does this mean?

If our 3 tables contain the following data:

Orders:

Id	Date	Customer	Product
1	01/01/01	1	2
2	2/02/02	3	2
3	03/03/03	3	3

Customers:

Id	Name
1	John
2	Michael
3	Terry

Products:

Id	Name
1	Cheese
2	Parrot
3	Spam

Then the query will cause this to happen:

Orders.Date	Customers.Name	Products.Name
01/01/01	John	Cheese
01/01/01	John	Parrot
01/01/01	John	Spam
01/01/01	Michael	Cheese
01/01/01	Michael	Parrot
01/01/01	Michael	Spam
01/01/01	Terry	Cheese
01/01/01	Terry	Parrot
01/01/01	Terry	Spam
02/02/02	John	Cheese
02/02/02	John	Parrot
02/02/02	John	Spam
02/02/02	Michael	Cheese
02/02/02	Michael	Parrot
02/02/02	Michael	Spam
02/02/02	Terry	Cheese
02/02/02	Terry	Parrot
02/02/02	Terry	Spam
03/03/03	John	Cheese
03/03/03	John	Parrot
03/03/03	John	Spam
03/03/03	Michael	Cheese
03/03/03	Michael	Parrot
03/03/03	Michael	Spam
03/03/03	Terry	Cheese
03/03/03	Terry	Parrot
03/03/03	Terry	Spam

And after generating all that it will then select the rows which make sense relation wise, according to the WHERE statement.

Orders.Date	Customers.Name	Products.Name
01/01/01	John	Parrot
02/02/02	Terry	Parrot
03/03/03	Terry	Spam

The big table is called a Cartesian product, which means that all possible combinations of rows between the tables are created, yielding a number of rows equal to the multiplication of the number of rows in each table (3 * 3 * 3 = 27 in this case). As you can tell, this will get really huge really quickly as the table get bigger.

Enter explicit joins.

Joins

In SQL, the FROM statement doesn't mean "a list of tables used in the SELECT statement". In the FROM statement you mention just one table. This table could either be one of the tables you created in the database or a "custom" table, such as one which is a joined version of several tables. The JOIN statement in SQL is not a keyword on par with the FROM statement, as I used to think due to the way it is indented in most tutorials I've seen. It is in fact a binary operator between 2 tables, just like "+" and "=" as binary operators.

The JOIN operator takes 2 tables and returns a new table, which is a joined version of the 2 tables. You can then join another table to that new table and keep on adding more tables to the "composite" table. There are several joins available in standard SQL which will be described later, but if we should use the INNER JOIN type in an example, this is how joins are used in general:

SELECT orders.date, customers.name, products.name
FROM (orders INNER JOIN customers ON orders.customer = customers.id) INNER JOIN products ON orders.product = products.id;

As you can see, the join is used between tables and can even be bracketed in order to state which tables should be joined up first. The ON statement is used to immediately join up the rows which are related, avoiding the cartesian product table in the first place. This will therefore be processed much more efficiently as the third table will only be joined up after the first two tables have been joined correctly, rather than joining everything together without a condition.

In the implicit join example, the tables where joined up with an inner join without the ON condition, hence losing all the advantage of joins. Now that you know how joins work, let's see what are the differences between the 4 join types described in w3schools.com.

Join types

The main difference between the join types is how they handle rows which don't match the ON condition.

INNER JOIN:
Strictly follows the ON condition between tables such that any rows in one table which cannot be matched up with another row in the other table will be left out. This is the kind of join we are used to.

SELECT orders.date, customers.name, products.name
FROM (orders INNER JOIN customers ON orders.customer = customers.id) INNER JOIN products ON orders.product = products.id;

results in

Orders.Date	Customers.Name	Products.Name
01/01/01	John	Parrot
02/02/02	Terry	Parrot
03/03/03	Terry	Spam

As you can see, not all customers or all products where mentioned. Only the ones which were mentioned in the ORDERS table and hence were related in some way were returned.

OUTER JOIN:
This is where things get a bit hairy and hence require that you make an effort to understand. The outer join makes sure that all rows in both tables are returned, even if there is no matching row in the other table. What happens to those tables which have no matching rows? They are joined to NULL values. As long as they are returned, it doesn't matter what they're joined to right?

You can also use LEFT JOIN and RIGHT JOIN to say whether you want to return all the rows of the table on the left of the join operator only or all the rows of the table on the right of the join operator only.

Now in order to use these joins to create a complex joined table, you have to be sure that a join will not return columns with NULL values which will be used in an ON condition of another join.

SELECT orders.date, customers.name
FROM orders RIGHT JOIN customers ON orders.customer = customers.id;

results in

Orders.Date	Customers.Name
01/01/01	John
NULL	Michael
02/02/02	Terry
03/03/03	Terry

SELECT orders.date, products.name
FROM orders RIGHT JOIN products ON orders.product = products.id;

results in

Orders.Date	Customers.Name
NULL	cheese
02/02/02	parrot
01/01/01	parrot
03/03/03	spam

SELECT orders.date, products.name, customers.name
FROM (orders LEFT JOIN customers ON orders.customer = customers.id) RIGHT JOIN products ON orders.product = products.id;

results in

Orders.Date	Customers.Name	Products.Name
NULL	NULL	cheese
02/02/02	terry	parrot
01/01/01	john	parrot
03/03/03	terry	spam

As you can see, depending on how the outer joins are used, we can make a particular table return all its rows, even if it isn't related to any other table.

Final note

I'd like to leave a few notes and links before concluding.

First of all, what I said about the implicit join not being efficient next to an explicit one is not necessarily true since the SQL optimizer may fix it before executing it.

Secondly, if you can avoid using brackets in the the joins it would be better as that will allow the SQL optimizer to make adjustments to the query without it having to make sure to preserve the precedence which you unneccessarily imposed.

In a future post, I will describe a trick which I used to efficiently view multiple one-to-many related tables which I also had trouble with in SQL until recently. But for now I will leave you with these links:

http://www.w3schools.com/sql/sql_join.asp
http://onlamp.com/pub/a/onlamp/2004/09/30/from_clauses.html
http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html