How to write efficient database JOINs

How to write efficient database JOINs

Last updated March 27, 2009. Created by moshe weitzman on June 21, 2003.
Edited by bekasu, gkallenberg, puregin, Steven. Log in to edit this page.

This page is based on an e-mail posted by Craig Courtney on 6/21/2003 to the drupal-devel mailing list.

There are 3 kinds of joins: INNER, LEFT OUTER, and RIGHT OUTER. Each requires an ON clause to let the RDBMS know what fields to use when joining the tables. For each join there are two tables: the LEFT table and the RIGHT table. The syntax is as follows:

{left table} (INNER | LEFT OUTER | RIGHT OUTER) JOIN {right table} ON (join criteria)

An INNER JOIN returns only those rows from the LEFT table having a matching row in the RIGHT table based on the join criteria.

A LEFT OUTER JOIN returns all rows from the LEFT table even if no matching rows where found in the RIGHT table. Any values selected out of the RIGHT table will be NULL for those rows where no matching row is found in the RIGHT table.

A RIGHT OUTER JOIN works exactly the same as a LEFT OUTER JOIN but reversing the direction. So it would return all rows in the RIGHT table regardless of matching rows in the LEFT table.

It is recommended that you not use RIGHT OUTER JOIN since a query can always be rewritten to use LEFT OUTER JOIN which tends to be more portable and easier to read.

If there are multiple rows in one table that match one row in the other table, the join will return that same row many times.

For example:

Table A

tid, name

1, 'Linux'

2, 'Debian'

Table B

fid, tid, message

1, 1, 'Very Cool'

2, 1, 'What an example'

Query 1: SELECT a.name, b.message FROM a INNER JOIN b ON a.tid = b.tid

Result 1:

Linux, Very Cool

Linux, What an example

Query 2: SELECT a.name, b.message FROM a LEFT JOIN b ON a.tid = b.tid

Result 2:

Linux, Very Cool

Linux, What an example

Debian, NULL

Visual Examples
codinghorror.com example

Hope that helps in reading some of the queries.