{"id":5050,"date":"2012-07-11T06:54:20","date_gmt":"2012-07-11T04:54:20","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=5050"},"modified":"2012-07-11T06:54:20","modified_gmt":"2012-07-11T04:54:20","slug":"mysql-joins-on-vs-using-vs-theta-style","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/mysql-joins-on-vs-using-vs-theta-style","title":{"rendered":"MySQL joins: ON vs. USING vs. Theta-style"},"content":{"rendered":"

What is the difference between the following three syntaxes?<\/p>\n

\n
SELECT * FROM film JOIN film_actor ON (film.film_id = film_actor.film_id)\r\nSELECT * FROM film JOIN film_actor USING (film_id)\r\nSELECT * FROM film, film_actor WHERE film.film_id = film_actor.film_id<\/pre>\n<\/blockquote>\n
The difference is mostly syntactic sugar, but with a couple interesting notes.<\/p>\n
To put names, the first two are called “ANSI-style”<\/strong> while the third is called “Theta-style”<\/strong>.<\/p>\n
Theta style<\/h4>\n
On the FROM<\/strong> clause, tables are listed as if with Cartesian products, and the WHERE<\/strong> clause specifies how the join should take place.<\/p>\n
This is considered to be the “old” style. It is somewhat confusing to read. Consider the following query:<\/p>\n
\n
SELECT<\/strong> * FROM<\/strong> film, film_actor WHERE<\/strong> film.film_id = film_actor.film_id AND<\/strong> actor_id = 17 AND<\/strong> film.length > 120<\/pre>\n<\/blockquote>\n
The above lists films over 120<\/strong> minutes in length, in which actor #17<\/strong> plays. Never mind the results; what about the query? Being just one part of the WHERE<\/strong> clause, a one out of three elements in the AND<\/strong> 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<\/strong> tables and a 20<\/strong> terms WHERE<\/strong> clause?<\/p>\n
ANSI style: ON<\/h4>\n
With JOIN<\/strong> … ON<\/strong>, one separates the join terms from the filtering terms. Rewriting the previous example:<\/p>\n
\n
SELECT<\/strong> * FROM<\/strong> film JOIN<\/strong> film_actor ON<\/strong> (film.film_id = film_actor.film_id) WHERE<\/strong> actor_id = 17 AND<\/strong> film.length > 120<\/pre>\n<\/blockquote>\n
It is quite clear now what belongs to what.<\/p>\n
Note: the parenthesis are not strictly required in the ON<\/strong> clause. I personally like to use them: it makes for an even greater distinction between query parts. SQL syntax is such a mess!<\/p>\n
ANSI style: USING<\/h4>\n
Is the special case where we join tables on columns of the same name, we can make a shortcut and use USING<\/strong>:<\/p>\n
\n
SELECT<\/strong> * FROM<\/strong> film JOIN<\/strong> film_actor USING<\/strong> (film_id) WHERE<\/strong> actor_id = 17 AND<\/strong> film.length > 120<\/pre>\n<\/blockquote>\n
This time the parenthesis are required (I’m not sure why the difference on that part).<\/p>\n
This is mainly a nicety, less words to type, and a resulting prettified query. But also note a couple differences:<\/p>\n
USING vs. ON<\/h4>\n
The following is valid:<\/p>\n
\n
SELECT film.title, film_id FROM film JOIN film_actor USING (film_id) WHERE actor_id = 17 AND film.length > 120;<\/pre>\n<\/blockquote>\n
But the following is not:<\/p>\n
\n
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;\r\nERROR 1052 (23000): Column 'film_id' in field list is ambiguous<\/strong><\/pre>\n<\/blockquote>\n
Since USING<\/strong> “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!<\/p>\n
ON<\/strong> is not as smart and requires further clarifications: which table exactly do you want?<\/p>\n
And the above is actually the result of this interesting phenomena: when using USING<\/strong>, the column only appears once<\/em> in the result set:<\/p>\n
\n
SELECT * FROM film JOIN film_actor USING (film_id) WHERE actor_id = 17 AND film.length > 120 LIMIT 1\\G\r\n*************************** 1. row ***************************\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 film_id<\/strong>: 96\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 title: BREAKING HOME\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 description: A Beautiful Display of a Secret Agent And a Monkey who must Battle a Sumo Wrestler in An Abandoned Mine Shaft\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 release_year: 2006\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 language_id: 1\r\noriginal_language_id: NULL\r\n\u00a0\u00a0\u00a0\u00a0 rental_duration: 4\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 rental_rate: 2.99\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 length: 169\r\n\u00a0\u00a0\u00a0 replacement_cost: 21.99\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 rating: PG-13\r\n\u00a0\u00a0\u00a0 special_features: Trailers,Commentaries\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 last_update: 2006-02-15 05:03:42\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 actor_id: 17\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 last_update: 2006-02-15 05:05:03<\/pre>\n<\/blockquote>\n
But joining on ON<\/strong>, we get this column twice<\/em>:<\/p>\n
\n
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\r\n*************************** 1. row ***************************\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 film_id<\/strong>: 96\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 title: BREAKING HOME\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 description: A Beautiful Display of a Secret Agent And a Monkey who must Battle a Sumo Wrestler in An Abandoned Mine Shaft\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 release_year: 2006\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 language_id: 1\r\noriginal_language_id: NULL\r\n\u00a0\u00a0\u00a0\u00a0 rental_duration: 4\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 rental_rate: 2.99\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 length: 169\r\n\u00a0\u00a0\u00a0 replacement_cost: 21.99\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 rating: PG-13\r\n\u00a0\u00a0\u00a0 special_features: Trailers,Commentaries\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 last_update: 2006-02-15 05:03:42\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 actor_id: 17\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 film_id<\/strong>: 96\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 last_update: 2006-02-15 05:05:03<\/pre>\n<\/blockquote>\n
Behind the scenes<\/h4>\n
The news is that MySQL treats all in the exact same way. With the kind help of EXPLAIN EXTENDED<\/strong>, we see that:<\/p>\n
\n
EXPLAIN EXTENDED SELECT film.title, film_id FROM film JOIN film_actor USING<\/strong> (film_id) WHERE actor_id = 17 AND film.length > 120\\G\r\n*************************** 1. row ***************************\r\n...\r\n2 rows in set, 1 warning (0.00 sec)\r\n\r\nroot@mysql-5.1.51> SHOW WARNINGS\\G\r\n*************************** 1. row ***************************\r\n\u00a0 Level: Note\r\n\u00a0\u00a0 Code: 1003\r\nMessage: select `sakila`.`film`.`title` AS `title`,`sakila`.`film`.`film_id` AS `film_id` \r\n         from `sakila`.`film` join `sakila`.`film_actor` \r\n         where (\r\n                 (`sakila`.`film`.`film_id` = `sakila`.`film_actor`.`film_id`)<\/strong> \r\n                 and (`sakila`.`film_actor`.`actor_id` = 17) \r\n                 and (`sakila`.`film`.`length` > 120)\r\n               )<\/pre>\n<\/blockquote>\n
All queries are translated internally to theta-style<\/em>.<\/p>\n
This post only discusses inner joins. With outer joins the situation is somewhat different. Read this post<\/a> for more insight.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[21,20],"class_list":["post-5050","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-sql","tag-syntax"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1js","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5050","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=5050"}],"version-history":[{"count":18,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5050\/revisions"}],"predecessor-version":[{"id":5069,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5050\/revisions\/5069"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=5050"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=5050"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=5050"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}