今夜IT网

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 977|回复: 0

MySQL 三种关联查询的方式: ON vs USING vs 传统风格

[复制链接]
发表于 2012-7-12 12:40:37 | 显示全部楼层 |阅读模式
  看看下面三个关联查询的 SQL 语句有何区别?
  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
  最大的不同更多是语法糖,但有一些有意思的东西值得关注。
  为了方便区别,我们将前两种写法称作是 ANSI 风格,第三种称为 Theta 风格。
  Theta 风格
  在 FROM 短语中列出了关联的表名,而 WHERE 短语则指定如何关联。
  这种写法被认为是古老的方式,有些时候比较难以理解,请看下面查询:
  SELECT * FROM film, film_actor WHERE film.film_id = film_actor.film_id ANDactor_id = 17 AND film.length > 120
  上述查询列出片长超过 120 分钟的电影,其中包括演员编号是 17 的条件。别在意查询结果,查询本身如何呢?WHERE 表达式中包含三个条件,要看出哪个条件是关联,哪个条件是过滤还是稍费点事的。不过还是相对简单的,但如果是 5 个表,20 多个条件呢?
  ANSI 风格: ON
  使用 JOIN ... ON 可以将表关联的条件和记录过滤条件分开,将上面的语句重写后的结果如下:
  SELECT * FROM film JOIN film_actor ON (film.film_id = film_actor.film_id) WHEREactor_id = 17 AND film.length > 120
  看起来清晰许多。
  注意: ON 语句中的括号不是必须的,我个人喜欢这样写而已。

  ANSI 风格: USING
  有一种特殊情况,当两个要关联表的字段名是一样的,我们可以使用 USING ,可减少 SQL 语句的长度:
  SELECT * FROM film JOIN film_actor USING (film_id) WHERE actor_id = 17 ANDfilm.length > 120
  这个时候括号就是必须的了。这种写法很好,输入更少的单词,查询的性能也非常棒,但还需要注意一些差异。

  USING 和 ON
  下面语句是可行的:
  SELECT film.title, film_id FROM film JOIN film_actor USING (film_id) WHEREactor_id = 17 AND film.length > 120;
  但下面这个就不行:
  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
  因为 USING "知道" film_id 字段在两个表中都有,所以没有指定确切的表都没关系,两个值必须一致就是。
  ON 就没那么智能,你必须指明要关联的表和字段名。

  上面两个实际的结果是比较有趣的,当使用 USING 时,字段只在结果中出现一次:

  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

  而使用 ON 时,字段就会出现两次:

  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

  幕后
  MySQL 对两者的处理方式是相同的,使用 EXPLAIN EXTENDED 我们可以看到:

  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)
  )

  最终所有的查询都被转成了 Theta 风格。
  译者:就是说这三种方式除了写法不同外,没什么区别。
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

快速回复 返回列表 官方QQ群

QQ|小黑屋|手机版|今夜IT网 (京ICP备11034012号) 

Powered by Discuz! X3.2

快速回复 返回顶部 返回列表