SQL 中 关于Left Join 转为 Inner Join 的问题「建议收藏」

结论

从下面的测试结果很容易看出，左关联的查询语句中，只要有 WHERE 的过滤条件，那么该语句将被转为内关联。

一、创建表

演示使用，随便创建两张表

--班级表
CREATE TABLE T_CLASS(
  class_id NUMBER not null,
  class_name VARCHAR2(100)
)
--学生表
CREATE TABLE T_STUDENT(
  student_id NUMBER not null,
  class_id NUMBER not null,
  student_name VARCHAR2(100),
  age NUMBER,
  sex CHAR(1) 
)

二、创建数据

--班级数据
insert into T_CLASS (CLASS_ID, CLASS_NAME) values (1, '一班');

insert into T_CLASS (CLASS_ID, CLASS_NAME) values (2, '二班');

insert into T_CLASS (CLASS_ID, CLASS_NAME) values (3, '三班');

insert into T_CLASS (CLASS_ID, CLASS_NAME) values (4, '四班');

insert into T_CLASS (CLASS_ID, CLASS_NAME) values (5, '五班');

--学生数据
insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX) values (1, 1, '李1', 3, '1');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX) values (2, 1, '李2', 2, '1');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX) values (3, 1, '李3', 3, '1');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX) values (4, 2, '李4', 4, '1');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX) values (5, 2, '李5', 3, '2');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX) values (6, 2, '李6', 3, '1');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX) values (7, 3, '李7', 6, '2');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX) values (8, 3, '李8', 4, '2');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX) values (9, 2, '李9', 2, '2');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX) values (10, 2, '李10', 3, '1');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX) values (11, 3, '李11', 3, '2');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX) values (12, 2, '李12', 8, '2');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX) values (13, 1, '李13', 6, '2');

三、左关联出现的几种情形

1.无过滤条件

查询语句：

SELECT *
  FROM T_CLASS A
  LEFT JOIN T_STUDENT B
    ON A.CLASS_ID = B.CLASS_ID
 ORDER BY A.CLASS_ID;

查询结果：

2.A表过滤条件在AND中

查询语句：

SELECT *
  FROM T_CLASS A
  LEFT JOIN T_STUDENT B
    ON A.CLASS_ID = B.CLASS_ID
   AND A.CLASS_ID = 1
 ORDER BY A.CLASS_ID;

查询结果：

3.A表过滤条件在WHERE中

查询语句：

SELECT *
  FROM T_CLASS A
  LEFT JOIN T_STUDENT B
    ON A.CLASS_ID = B.CLASS_ID
 WHERE A.CLASS_ID = 1
 ORDER BY A.CLASS_ID;

查询结果：

4.B表过滤条件在AND中

查询语句：

SELECT *
  FROM T_CLASS A
  LEFT JOIN T_STUDENT B
    ON A.CLASS_ID = B.CLASS_ID
   AND B.AGE = 3
 ORDER BY A.CLASS_ID;

查询结果：

5.B表过滤条件在WHERE中

查询语句：

SELECT *
  FROM T_CLASS A
  LEFT JOIN T_STUDENT B
    ON A.CLASS_ID = B.CLASS_ID
 WHERE B.AGE = 3
 ORDER BY A.CLASS_ID;

查询结果：

6.A表过滤条件在AND中，B表过滤条件在WHERE中

查询语句：

SELECT *
  FROM T_CLASS A
  LEFT JOIN T_STUDENT B
    ON A.CLASS_ID = B.CLASS_ID
   AND A.CLASS_ID = 1
 WHERE B.AGE = 3
 ORDER BY A.CLASS_ID;

查询结果：

7.A表过滤条件在WHERE中，B表过滤条件在AND中

查询语句：

SELECT *
  FROM T_CLASS A
  LEFT JOIN T_STUDENT B
    ON A.CLASS_ID = B.CLASS_ID
   AND B.AGE = 3
 WHERE A.CLASS_ID = 1
 ORDER BY A.CLASS_ID;

查询结果：

8.A表和B表过滤条件都在AND中

查询语句：

SELECT *
  FROM T_CLASS A
  LEFT JOIN T_STUDENT B
    ON A.CLASS_ID = B.CLASS_ID
   AND B.AGE = 3
   AND A.CLASS_ID = 1
 ORDER BY A.CLASS_ID;

查询结果：

9.A表和B表过滤条件都在WHERE中

查询语句：

SELECT *
  FROM T_CLASS A
  LEFT JOIN T_STUDENT B
    ON A.CLASS_ID = B.CLASS_ID
 WHERE B.AGE = 3
   AND A.CLASS_ID = 1
 ORDER BY A.CLASS_ID;

查询结果：

以下附上所有查询语句：

--1.无过滤条件
SELECT *
  FROM T_CLASS A
  LEFT JOIN T_STUDENT B
    ON A.CLASS_ID = B.CLASS_ID
 ORDER BY A.CLASS_ID;
--2.A表过滤条件在AND中
SELECT *
  FROM T_CLASS A
  LEFT JOIN T_STUDENT B
    ON A.CLASS_ID = B.CLASS_ID
   AND A.CLASS_ID = 1
 ORDER BY A.CLASS_ID;
--3.A表过滤条件在WHERE中
SELECT *
  FROM T_CLASS A
  LEFT JOIN T_STUDENT B
    ON A.CLASS_ID = B.CLASS_ID
 WHERE A.CLASS_ID = 1
 ORDER BY A.CLASS_ID;
--4.B表过滤条件在AND中
SELECT *
  FROM T_CLASS A
  LEFT JOIN T_STUDENT B
    ON A.CLASS_ID = B.CLASS_ID
   AND B.AGE = 3
 ORDER BY A.CLASS_ID;
--5.B表过滤条件在WHERE中
SELECT *
  FROM T_CLASS A
  LEFT JOIN T_STUDENT B
    ON A.CLASS_ID = B.CLASS_ID
 WHERE B.AGE = 3
 ORDER BY A.CLASS_ID;
--6.A表过滤条件在AND中，B表过滤条件在WHERE中
SELECT *
  FROM T_CLASS A
  LEFT JOIN T_STUDENT B
    ON A.CLASS_ID = B.CLASS_ID
   AND A.CLASS_ID = 1
 WHERE B.AGE = 3
 ORDER BY A.CLASS_ID;
--7.A表过滤条件在WHERE中，B表过滤条件在AND中
SELECT *
  FROM T_CLASS A
  LEFT JOIN T_STUDENT B
    ON A.CLASS_ID = B.CLASS_ID
   AND B.AGE = 3
 WHERE A.CLASS_ID = 1
 ORDER BY A.CLASS_ID;
--8.A表和B表过滤条件都在AND中
SELECT *
  FROM T_CLASS A
  LEFT JOIN T_STUDENT B
    ON A.CLASS_ID = B.CLASS_ID
   AND B.AGE = 3
   AND A.CLASS_ID = 1
 ORDER BY A.CLASS_ID;
--9.A表和B表过滤条件都在WHERE中
SELECT *
  FROM T_CLASS A
  LEFT JOIN T_STUDENT B
    ON A.CLASS_ID = B.CLASS_ID
 WHERE B.AGE = 3
   AND A.CLASS_ID = 1
 ORDER BY A.CLASS_ID;