T-SQL——关于Join on的的连接条件和where的筛选条件的区分

目录

• 1.测试数据
• 2. left join 之后ON中筛选条件
• 3. left join之后使用where连接筛选条件

1.测试数据

WITH
temp1 AS
    (
          SELECT '张三' AS Name, 1 AS ClassNo
          UNION ALL
          SELECT '李四' AS Name, 2 AS ClassNo
          UNION ALL
          SELECT '王五' AS Name, 3 AS ClassNo
          UNION ALL
          SELECT '赵六' AS Name, NULL AS ClassNo
          UNION ALL
          SELECT '钱七' AS Name, 4 AS ClassNo
    ),
temp2 AS
    (
          SELECT '1' AS ClassNo, '一年级' AS ClassName
          UNION ALL
          SELECT '2' AS ClassNo, '二年级' AS ClassName
          UNION ALL
          SELECT '3' AS ClassNo, '三年级' AS ClassName
    )

2. left join 之后ON中筛选条件

SELECT * FROM temp1 AS a LEFT JOIN temp2 AS b ON a.ClassNo=b.ClassNo AND a.ClassNo IS NOT NULL;

结果：
Name ClassNo     ClassNo ClassName
---- ----------- ------- ---------
张三   1           1       一年级
李四   2           2       二年级
王五   3           3       三年级
赵六   NULL        NULL    NULL
钱七   4           NULL    NULL

• on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录
• 说明：ON是作为join语句的连接条件，这里是left join 在说明不论ON中的条件是否符合，左表中的所有记录都会显示，所以这里的a.ClassNo IS NOT NULL对最终显示的结果是没有作用的

3. left join之后使用where连接筛选条件

SELECT * FROM temp1 AS a LEFT JOIN temp2 AS b ON a.ClassNo=b.ClassNo WHERE a.ClassNo IS NOT NULL;

结果：
Name ClassNo     ClassNo ClassName
---- ----------- ------- ---------
张三   1           1       一年级
李四   2           2       二年级
王五   3           3       三年级

• where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。