Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the three related query methods of MySQL". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what are the three related query methods of MySQL".

Take a look at the difference between the SQL statements of the following three associated queries?

1SELECT * FROM film JOIN film_actor ON (film.film_id = film_actor.film_id) 2SELECT * FROM film JOIN film_actor USING (film_id) 3SELECT * 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.

To make it easier to distinguish, we call the first two styles ANSI style, and the third style Theta style.

Theta style

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

This method of writing is considered to be an ancient way, and sometimes it is difficult to understand. Please take a look at the following query:

1SELECT * FROM film, film_actor WHERE film.film_id = film_actor.film_id AND actor_id = 17 AND film.length > 120

The above query lists films that are more than 120 minutes long, including the condition that the actor number is 17. Never mind the query results, what about the query itself? The WHERE expression contains three conditions to see which conditions are relevant and which conditions are filtered or slightly cumbersome. However, it is still relatively simple, but what if it is 5 tables and more than 20 conditions?

ANSI style: ON

Use JOIN... ON can separate the conditions associated with the table from the record filtering conditions, and the result of rewriting the above statement is as follows:

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

It looks a lot clearer.

Note: parentheses in ON statements are not required, I personally like to write it this way.

ANSI style: USING

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

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

At this time, parentheses are necessary. This is a good way to write, enter fewer words, and the performance of the query is great, but you still need to pay attention to some differences.

USING and ON

The following statement is feasible:

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

But not this one:

1SELECT 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 bot error 1052 (23000): Column 'film_id' in field list is ambiguous

Because USING "knows" that the film_id field exists in both tables, it doesn't matter if you don't specify the exact table, the two values must be the same.

ON is not that smart, and you have to specify the table and field names to associate with.

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

01SELECT * FROM film JOIN film_actor USING (film_id) WHERE actor_id = 17 AND film.length > 120 LIMIT 1\ G02 03film_id * 1. Row * * 03film_id: 9604title: BREAKING HOME05description: A Beautiful Display of a Secret Agent And a Monkey who must Battle a Sumo Wrestler in An Abandoned Mine Shaft06release_year: 200607language_id: 108original_language_id: NULL09rental_duration: 410rental_rate: 2.9911length: 16912replacement_cost: 21.9913rating: PG-1314special_features: Trailers Commentaries15last_update: 2006-02-15 05:03:4216actor_id: 1717last_update: 2006-02-15 05:05:03

When using ON, the field appears twice:

01SELECT * FROM film JOIN film_actor ON film.film_id = film_actor.film_id WHERE actor_id = 17 AND film.length > 120 LIMIT 1\ G02 regions * 1. Row * * 03film_id: 9604title: BREAKING HOME05description: A Beautiful Display of a Secret Agent And a Monkey who must Battle a Sumo Wrestler In An Abandoned Mine Shaft06release_year: 200607language_id: 108original_language_id: NULL09rental_duration: 410rental_rate: 2.9911length: 16912replacement_cost: 21.9913rating: PG-1314special_features: Trailers Commentaries15last_update: 2006-02-15 05:03:4216actor_id: 1717film_id: 9618last_update: 2006-02-15 05:05:03 behind the scenes

MySQL treats the two in the same way, and with EXPLAIN EXTENDED we can see:

01EXPLAIN EXTENDED SELECT film.title, film_id FROM film JOIN film_actor USING (film_id) WHERE actor_id = 17 AND film.length > 120\ G02 rows in set * 1. Row * * 03.. 042 rows in set 1 warning (0.00 sec) 0506root@mysql-5.1.51 > SHOW WARNINGS\ G07percent * 1. Row * * 08Level: Note09Code: 100310Message: select `sakila`.`room`.`title`AS `title` `sakila`.`movie `.`film _ id`AS `sakila`11from `sakila`.`room`join `sakila`.`film _ actor`12where (13 (`sakila`.`room`.`film _ id` = `sakila`.`film _ actor`.`film _ id`) 14and (`sakila`.`film _ actor`.actor _ id` = 17) 15and (`sakila`.`oohth` > 120) 16)

In the end, all queries are converted to Theta style.

Translator: that is to say, except for the different ways of writing, there is no difference among the three ways.

Thank you for your reading, the above is the content of "what are the three related query methods of MySQL". After the study of this article, I believe you have a deeper understanding of what the three related query methods of MySQL are, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.