Orignally posted 2008-05-14 on my old Joomla site.

Just thought I might share a neat little join syntax that I came across recently.

Say you have two tables: orders and users
What I've seen a lot of people write a SELECT statement like this:

SELECT *
FROM order, users
WHERE order.user_id = users.user_id
AND user_id = 1;


Well, that's all fine and dandy, but I like to keep my where statements clean and only be related to actual select criteria. So lets use an explicit join:

SELECT *
FROM users
JOIN orders ON (orders.user_id = users.user_id)
WHERE users.user_id = 1;


It's a little cleaner and easier to read (IMO), but we still have a problem. In the result set, we end up with duplicate columns for the user_id. This means that we need to explicitly state which user_id column to operate on in the WHERE clause. On top of that, since database rows are often returned to a programming environment in the form of a hash/array, duplicate column names overwrite each other. For quite a while now, I've wished there was a "SELECT * EXCEPT column" syntax, but since we don't, we have to explicitly state all the columns we want to include. That's no fun. Well, there's a nice little join syntax that PostgreSQL and MySQL has implemented:

SELECT *
FROM users
JOIN orders USING (user_id)
WHERE user_id = 1;


Woot! Nice, clean, readable SQL. No duplicate columns, and the where clause doesn't have to explicitly state which user_id column to use, as the USING statement merges the columns together. It's kind of like an explicitly stated NATURAL JOIN.

Obviously this doesn't work for all queries, but it's a handy shortcut for small queries joining a couple of tables, and especially with lookup tables.

NOTE: This only works with MySQL, so if you want your queries to be cross-db compatible, you'll want to avoid this syntax.