Popular Posts

Sunday, February 05, 2006

How to write efficient database JOINs

There are 3 kinds of join: INNER, LEFT OUTER, and RIGHT OUTER. Each requires an ON clause to let the RDBMS know what fields to use 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 | RIGHT} 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 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 JOIN works exactly the same as a left 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 joins, as a query can always be rewritten to use left joins which tend to be more portable and easier to read.

With all of the joins, if there are multiple rows in one table that match one row in the other table, that row will get returned 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,


Enjoy!!

No comments: