• using与on的区别


    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

    语法糖

    ON

    使用 JOIN ... ON 可以将表关联的条件和记录过滤条件分开,将上面的语句重写后的结果如下:

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

    USING

    有一种特殊情况,当两个要关联表的字段名是一样的,我们可以使用 USING ,可减少 SQL 语句的长度:

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

    这个时候括号就是必须的了

    USING 和 ON

    下面语句是可行的:

    SELECT film.title, film_id FROM film JOIN film_actor USING (film_id) WHERE actor_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 1G
    *************************** 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 1G
    *************************** 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
    论读书
    睁开眼,书在面前
    闭上眼,书在心里
  • 相关阅读:
    WEB 应用缓存解析以及使用 Redis 实现分布式缓存
    MVC 架构模式
    用两个栈实现队列
    重建二叉树
    从尾到头打印链表
    替换空格
    二维数组中的查找
    二叉树与线索二叉树
    OpenYurt:延伸原生 Kubernetes 到边缘场景下的落地实践
    开放下载!解锁 Serverless 从入门到实战大“橙”就
  • 原文地址:https://www.cnblogs.com/YC-L/p/14461585.html
Copyright © 2020-2023  润新知