代码之家  ›  专栏  ›  技术社区  ›  rxmnnxfpvg

使用join而不是intersect查询

  •  2
  • rxmnnxfpvg  · 技术社区  · 14 年前

    我有这样一个模式:

    // table badge_types
    id | name
    +++|++++++++++
    1  | mentor
    2  | proctor
    3  | doctor
    
    // table badges
    id | badge_type_id | user_id     
    +++|+++++++++++++++|++++++++
    1  | 1             | 5
    2  | 1             | 6
    3  | 2             | 6
    4  | 3             | 6
    5  | 2             | 19
    6  | 3             | 20
    

    我要做的是全选 badge_types 一个特定的用户还没有得到。在上面的示例中,调用以下查询:

    user_id = 5 收益率 badge_type_id 2 3

    user_id = 6 收益率 empty 设置(用户已获得所有设置)

    user_id = 19 收益率 巴德格型 1

    我可以用一个 INTERSECT 条款。但我想知道是否可以用一个简单的 JOIN ?任何帮助都将不胜感激。

    1 回复  |  直到 14 年前
        1
  •  13
  •   OMG Ponies    14 年前

    使用左联接/为空:

       SELECT bt.name
         FROM BADGE_TYPES bt
    LEFT JOIN BAGDES b ON b.badge_type_id = bt.id
                      AND b.user_id = ?
        WHERE b.id IS NULL
    

    使用不存在

       SELECT bt.name
         FROM BADGE_TYPES bt
        WHERE NOT EXISTS(SELECT NULL
                           FROM BADGES b 
                          WHERE b.badge_type_id = bt.id
                            AND b.user_id = ?)
    

    不使用

       SELECT bt.name
         FROM BADGE_TYPES bt
        WHERE bt.id NOT IN (SELECT b.badge_type_id
                           FROM BADGES b 
                          WHERE b.user_id = ?)
    

    MySQL

    如果列是 not nullable, LEFT JOIN/IS NULL is the best choice . 如果他们 are nullable, NOT EXISTS and NOT IN are the best choice .

    SQL Server

    介意 on SQL Server, NOT IN and NOT EXISTS perform better than LEFT JOIN /IS NULL if the columns in question are not nullable .

    甲骨文公司

    为了 Oracle, all three are equivalent but LEFT JOIN/IS NULL and NOT EXISTS are preferable if columns aren't nullable .