Simple comparison of three kinds of relational query methods in Mysql _mysql

What is the difference between the SQL statements for the following three associated queries?

SELECT * FROM film join Film_actor to (film.film_id = film_actor.film_id)
select * from film join Film_actor USING (fi LM_ID)
SELECT * from film, film_actor where film.film_id = film_actor.film_id

The biggest difference is more grammatical sugar, but there are some interesting things to pay attention to.

For convenience of distinction, we call the first two styles ANSI, and the third is called Theta style.
Theta Style

The associated table name is listed in the from phrase, and the WHERE phrase specifies how to associate it.

This type of writing is considered an ancient way, sometimes difficult to understand, please look at the following query:

SELECT * from film, film_actor where film.film_id = film_actor.film_id and actor_id = and film.length > 120

The above query lists films that are longer than 120 minutes, including the condition that the actor number is 17. Don't worry about the query results, what about the query itself? There are three conditions in the WHERE expression to see which condition is associated and which condition is filtered or a bit of a bit of a fee. But it is relatively simple, but if it is 5 tables, more than 20 conditions?
ANSI style: on

Use JOIN ... On can separate the criteria associated with the table from the record filtering criteria, and rewrite the preceding statement as follows:

SELECT * from film JOIN film_actor on (film.film_id = film_actor.film_id) WHERE actor_id = and film.length > 120

It looks a lot clearer.

Note: The parentheses in the on statement are not required, I personally like to write it.

ANSI Style: USING

There is a special case where the two field names that are associated with the table are the same, and we can use using to reduce the length of the SQL statement:

SELECT * from film JOIN film_actor USING (film_id) WHERE actor_id = and film.length > 120

The parentheses are necessary at this time. This is a good writing, the input of fewer words, query performance is also very good, but also need to pay attention to some differences.

USING and on

The following statement is possible:

SELECT Film.title, film_id from film JOIN Film_actor USING (film_id) WHERE actor_id = and film.length > 120;

But here's not the way:

SELECT Film.title, film_id from film JOIN film_actor on (film.film_id = film_actor.film_id) WHERE actor_id = and FILM.L Ength > 120; ERROR 1052 (23000): Column ' film_id ' in the field list is ambiguous

Because the USING "know" film_id field is available in two tables, it doesn't matter if the exact table is not specified, and the two values must be the same.

On is less intelligent, you must indicate the table and field names to be associated.

The above two actual results are more interesting, and when using use, the field only appears once in the result:

SELECT * from film JOIN film_actor USING (film_id) WHERE actor_id = and film.length > LIMIT 1\g ************
1. Row ***************************
       film_id:96
        title:breaking home
     description:a Beautiful Display of Secret Agent and a monkey who must Battle a Sumo wrestler in a abandoned Mine Shaft release_year:2006 language_i
     D: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 two times:

SELECT * from film JOIN film_actor on film.film_id = film_actor.film_id WHERE actor_id = and film.length > 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 a abandoned Mine
    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

MySQL is the same way to handle both, using EXPLAIN EXTENDED we can see:

EXPLAIN EXTENDED SELECT Film.title, film_id from film JOIN Film_actor USING (film_id) WHERE actor_id = 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 '
         ) and
         (' Sakila '. ' film '. ' Length ' >)
        )

Eventually all the queries were turned into Theta style.

Translator: That is to say that these three ways apart from writing different, no difference.