Three methods of MySQL association query: ON vs USING vs traditional style

What are the differences between the following three SQL statements for join queries?

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 biggest difference is syntactic sugar, but there are some interesting things worth noting.

To facilitate the difference, we call the first two writing methods ANSI style and the third one Theta style.

Theta Style

The associated table names are listed in the FROM phrase, while the WHERE phrase specifies how to associate.

This method is considered to be an old method and is difficult to understand in some cases. Please refer to 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 query lists movies with a length of more than 120 minutes, including the condition that the actor number is 17. Do not care about the query results. What is the query itself? The WHERE expression contains three conditions. You need to see which condition is associated and which condition is filter or slightly charged. However, it is relatively simple, but what if there are five tables and more than 20 conditions?

ANSI style: ON
JOIN... ON can be used to separate the table JOIN conditions from the record filter conditions. The result of overwrite the preceding statement is as follows:

SELECT * FROM film JOIN film_actor ON (film. film_id = film_actor.film_id) WHERE actor_id = 17 AND film. length> 120

It looks much clearer.

Note: parentheses in the ON statement are not mandatory. I personally prefer to write them like this.

ANSI style: USING
In a special case, when the field names of two tables to be joined are the same, we can use USING to reduce the length of the SQL statement:

SELECT * FROM film JOIN film_actor USING (film_id) WHERE actor_id = 17 AND film. length> 120

Brackets are required at this time. This is a good way to write, enter less words, and query performance is also good, but pay attention to some differences.

USING and ON
The following statement is feasible:

SELECT film. title, film_id FROM film JOIN film_actor USING (film_id) WHERE actor_id = 17 AND film. length> 120;

But the following won't work:

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

Because the USING "knows" film_id field exists in both tables, it does not matter if no exact table is specified. The two values must be consistent.

ON is not so intelligent. You must specify the table and field name to be associated.

The above two actual results are interesting. When USING, the field appears only once in the result:

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
Required age_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

When ON is used, the field appears 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
Required age_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 scenes

MySQL processes the two in the same way. Using explain extended, we can 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)
)

Eventually all the queries are converted into Theta.

Translator: that is to say, there is no difference between the three methods except for different writing methods.