What is the difference between the following three syntaxes?
SELECT * FROM film JOIN film_actor ON (film.film_id = film_actor.film_id) SELECT * FROM film JOIN film_actor USING (film_id) SELECT * FROM film, film_actor WHERE film.film_id = film_actor.film_id
The difference is mostly syntactic sugar, but with a couple interesting notes.
To put names, the first two are called “ANSI-style” while the third is called “Theta-style”.
Theta style
On the FROM clause, tables are listed as if with Cartesian products, and the WHERE clause specifies how the join should take place.
This is considered to be the “old” style. It is somewhat confusing to read. Consider the following query:
SELECT * FROM film, film_actor WHERE film.film_id = film_actor.film_id AND actor_id = 17 AND film.length > 120
The above lists films over 120 minutes in length, in which actor #17 plays. Never mind the results; what about the query? Being just one part of the WHERE clause, a one out of three elements in the AND expression, the join equation gets lost. It is difficult to find and isolate the terms which make for table joins as opposed to terms which filter out rows. In the above example it is still relatively easy to point out. How about a query with 5 tables and a 20 terms WHERE clause?
ANSI style: ON
With JOIN … ON, one separates the join terms from the filtering terms. Rewriting the previous example:
SELECT * FROM film JOIN film_actor ON (film.film_id = film_actor.film_id) WHERE actor_id = 17 AND film.length > 120
It is quite clear now what belongs to what.
Note: the parenthesis are not strictly required in the ON clause. I personally like to use them: it makes for an even greater distinction between query parts. SQL syntax is such a mess!
ANSI style: USING
Is the special case where we join tables on columns of the same name, we can make a shortcut and use USING:
SELECT * FROM film JOIN film_actor USING (film_id) WHERE actor_id = 17 AND film.length > 120
This time the parenthesis are required (I’m not sure why the difference on that part).
This is mainly a nicety, less words to type, and a resulting prettified query. But also note a couple differences:
USING vs. ON
The following is valid:
SELECT film.title, film_id FROM film JOIN film_actor USING (film_id) WHERE actor_id = 17 AND film.length > 120;
But the following is not:
SELECT film.title, film_id FROM film JOIN film_actor ON (film.film_id = film_actor.film_id) WHERE actor_id = 17 AND film.length > 120; ERROR 1052 (23000): Column 'film_id' in field list is ambiguous
Since USING “knows” the film_id column is shared between both tables, it doesn’t mind if we ask it without specifying an exact table. It would be the same value anyway!
ON is not as smart and requires further clarifications: which table exactly do you want?
And the above is actually the result of this interesting phenomena: when using USING, the column only appears once in the result set:
SELECT * FROM film JOIN film_actor USING (film_id) WHERE actor_id = 17 AND film.length > 120 LIMIT 1\G *************************** 1. row *************************** film_id: 96 title: BREAKING HOME description: A Beautiful Display of a Secret Agent And a Monkey who must Battle a Sumo Wrestler in An Abandoned Mine Shaft release_year: 2006 language_id: 1 original_language_id: NULL rental_duration: 4 rental_rate: 2.99 length: 169 replacement_cost: 21.99 rating: PG-13 special_features: Trailers,Commentaries last_update: 2006-02-15 05:03:42 actor_id: 17 last_update: 2006-02-15 05:05:03
But joining on ON, we get this column twice:
SELECT * FROM film JOIN film_actor ON film.film_id = film_actor.film_id WHERE actor_id = 17 AND film.length > 120 LIMIT 1\G *************************** 1. row *************************** film_id: 96 title: BREAKING HOME description: A Beautiful Display of a Secret Agent And a Monkey who must Battle a Sumo Wrestler in An Abandoned Mine Shaft release_year: 2006 language_id: 1 original_language_id: NULL rental_duration: 4 rental_rate: 2.99 length: 169 replacement_cost: 21.99 rating: PG-13 special_features: Trailers,Commentaries last_update: 2006-02-15 05:03:42 actor_id: 17 film_id: 96 last_update: 2006-02-15 05:05:03
Behind the scenes
The news is that MySQL treats all in the exact same way. With the kind help of EXPLAIN EXTENDED, we see that:
EXPLAIN EXTENDED SELECT film.title, film_id FROM film JOIN film_actor USING (film_id) WHERE actor_id = 17 AND film.length > 120\G
*************************** 1. row ***************************
...
2 rows in set, 1 warning (0.00 sec)
root@mysql-5.1.51> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select `sakila`.`film`.`title` AS `title`,`sakila`.`film`.`film_id` AS `film_id`
from `sakila`.`film` join `sakila`.`film_actor`
where (
(`sakila`.`film`.`film_id` = `sakila`.`film_actor`.`film_id`)
and (`sakila`.`film_actor`.`actor_id` = 17)
and (`sakila`.`film`.`length` > 120)
)
All queries are translated internally to theta-style.
This post only discusses inner joins. With outer joins the situation is somewhat different. Read this post for more insight.
Perhaps you forgot the special NATURAL JOIN which is kind of like USING , but with different semantics about which columns are returned from the query.
@Justin,
I did not forget – I just ignore its existence 🙂
Great post! It begs the question, which syntax do you prefer? Personally, I strongly prefer ANSI-style over Theta-style because I find it much less error prone and more readable. As for USING vs. ON, I prefer ON. I find USING to be overly prescriptive in terms of column naming. Unless you never use self-referencing foreign keys or multiple foreign keys from a child table to the same parent table, then there will be cases where you need to use ON instead of USING. I’m not sure if you are advertising the “SELECT *” differences and the ability to omit the… Read more »
@Ike, When I started programming with SQL, I wan’t even aware of the “JOIN … ON” syntax. I was just doing Theta style. When I came to learn of the “JOIN … ON” I realized it was a much cleaner syntax. I now use it regularly. I don’t use USING much. Not because I don’t like to, but because you need to use a very specific naming convention to be able to use it. When possible, I typically use it. There can be, at rare times, an optimization issue, where you want to explicitly use the film.film_id in some equation.… Read more »
Hi! Nice write-up. My guess is, JOIN syntax will never cease to be an object of discussion. It’s clear to me there will always be a need to specify very specific join conditions, and we can’t get by with only “smart” join syntax like NATURAL and USING. (By “smart” I mean a declarative syntax, that does not require explicit comparisons and logical operators) That said, I think that at least 80% of all join syntax conforms to foreign keys, probably more in typical normalized OLTP data base schemas. So I think there certainly is room for a “smart” join syntax.… Read more »
Roland,
Great examples on multiple choices (multiple FKs, multiple columns names address_id).
With those examples it becomes very clear how being declarative SQL becomes ambiguous or undefined.
I agree to the notion of RELATE using a FK.
To be honest, I think SQL is a mess. It also uses ancient syntax rules. This doesn’t mean I don’t enjoy it, though 🙂
But it is my belief that ANSI SQL could have been much better if redesigned, in many aspects.
Hi Shlomi – yeah I feel the same, it’s a mess. But I love to tinker away and write queries.
I think that the ANSIstyle UISNG syntax requires parenthesis to distinguish the anbiguity:
FROM film JOIN film_actor USING (film_id, x)
(where x is a column)
vs
FROM (film JOIN film_actor USING film_id) , x
(where x is a table)
@ypercube,
In MySQL USING requires parenthesis regardless, so no ambiguities here.
Sorry, I wasn’t clear. I meant that without parenthesis, this would be ambiguous:
FROM film JOIN film_actor USING film_id, x
as to which of the two parenthesized versions above it should be parsed.
@ypercube: you were very clear 🙂
I’m saying this syntax is not allowed in MySQL anyway: you HAVE to put parenthesis with USING.
Hi,
I had a doubt with respect to the ANSI syntax for join. How would the execution of the query change if i use the two queries given below –
query 1:
table_1 join table_2 using(col 1) where table_1.date_1 >= table_2.date_2 and table_1.date_1 >= table_2.date_3 and table_1.date_1 = table_2.date_2 and table_1.date_1 >= table_2.date_3 and table_1.date_1 < table_2.date_4);
When i used query 1, my total records were reduced from 3 million to 600k. But it was not the case when i used the query 2.
I am unable to understand what went wrong in query 1.
My apologies. I might have posted incomplete question.
query 1:
table_1 join table_2 using(col 1) where table_1.date_1 >= table_2.date_2 and table_1.date_1 >= table_2.date_3 and table_1.date_1 = table_2.date_2 and table_1.date_1 >= table_2.date_3 and table_1.date_1 < table_2.date_4);
What is the difference between the execution and expected results from the 2 queries?
Query 1 returned on 10% of records from query_1 while query 2 returned the desired result.
What’s query number two?
[…] https://shlomi-noach.github.io/blog/mysql/mysql-joins-on-vs-using-vs-theta-style […]