Where do you know after the join?

1. First Look at the sample table: Oracle, Scott's two sample tables SQL codeCREATETABLEEMP (EMPNO number (4), ename VARCHAR2 (byte), JOB VARCHAR2 (9 byte), MGR Numbe R (4), HireDateDATE, SAL number (7,2), COMM number (7,2), DEPTNO number (2))CREATE TABLEDEPT (DEPTNO number (2), Dname VARCHAR2 (byte), LOC VARCHAR2 (byte))

CREATE TABLE EMP
(
  EMPNO number     (4),
  ename     VARCHAR2 (byte),
  JOB       VARCHAR2 (9 byte),
  MGR       Number (4),
  hiredate  DATE,
  SAL number       (7,2),
  COMM number      (7,2),
  DEPTNO    Number (2)
)

CREATE TABLE DEPT
(
  DEPTNO number  (2),
  dname   VARCHAR2 (BYTE),
  LOC     VARCHAR2 (in BYTE)
)

2. A few SQL questions: (filter conditions in the left table emp, SAL!= 3000) SQL code--SQL1: all the conditions for joinSelectE.*, D.deptno, D.dname fromEMP E LEFT JOIN Dept D onE.deptno=d.deptno and Sal!= 3000 Order byEmpno --SQL2: First filter and then joinSelectE.*,d.deptno, D.dname from(Select* fromEmpwhereSal!= 3000) e left JOIN Dept D onE.deptno=d.deptno Order byEmpno --SQL3: First filter and then joinSelectE.*, D.deptno, D.dname fromEMP E LEFT JOIN Dept D onE.deptno=d.deptnowhereSal!= 3000 Order byEmpno

--SQL1: All as join conditions
Select e.*, D.deptno, d.dname from EMP e-LEFT JOIN dept D on 
E.deptno=d.deptno and Sal!= 3000 Order by  Empno;

--SQL2: First filter and then join
Select E.*,d.deptno, D.dname from (SELECT * from emp  where Sal!= 3000) e-left JOIN dept D 
E.deptno=d.deptno order by Empno;
--SQL3: First filter and then join
Select E.*, D.deptno, d.dname from EMP e-LEFT JOIN dept D on 
E.deptno=d.deptno where Sal!= 3000 Order BY Empno;

Results generated by SQL1:

The results generated by SQL2 and SQL3 are consistent:

The execution plan for SQL2 or 3 is consistent: The SQL code Execution plan----------------------------------------------------------plans hash value:1901738359 ----------------------------------------------------------------------------    | Id | Operation |Name|Rows| Bytes | Cost (%CPU) | Time|   ----------------------------------------------------------------------------    | 0 |SELECTSTATEMENT |    |   12 |     600 | 8 (25) |    00:00:01 |   |  1 | SORT Order by|    |   12 |     600 | 8 (25) |    00:00:01 |   |* 2 |      HASH JOIN OUTER |    |   12 |     600 | 7 (15) |    00:00:01 | |* 3 |TABLEACCESS Full|    EMP |   12 |     444 | 3 (0) |    00:00:01 |   | 4 |TABLEACCESS Full|     DEPT |    4 |     52 | 3 (0) |    00:00:01 | ----------------------------------------------------------------------------predicate information (identified byOperation ID):---------------------------------------------------2-access ("EMP".) DEPTNO "=" D "." DEPTNO "(+)) 3-filter (" SAL "<>3000)

Visible is the first filter sal!=3000, then join

And SQL1 will be the entire e.deptno=d.deptno and Sal!= 3000 as a join condition, once satisfied sal=3000 will not find the corresponding dept.

So whoever meets the join adds the filtered SQL code from the left table, select e.*, D.deptno, d.dname from emp E left JOIN dept D on E.D Eptno=d.deptno where sal!= 3000 ORDER by Empno;

Select E.*, D.deptno, d.dname from EMP e-LEFT JOIN dept D in 
E.deptno=d.deptno where Sal!= 3000 order by empno;

As the execution plan can be understood: SQL code-first filter and then join select e.*,d.deptno, D.dname from (select * from emp where sal!= 3000) e left JOIN Dept D in E.deptno=d.deptno ORDER by Empno;

--First filter and join
Select E.*,d.deptno, D.dname from (SELECT * from emp where SAL!= 3000) e-left JOIN dept D on 
E.DEPTN O=d.deptno ORDER by Empno;

So, for the filter is the left table emp condition sal!=3000, if the filter condition is placed in the join condition, such as SQL1, it will be regarded as the condition of the whole join, if the filter condition as where placed in the join, such as SQL3, is first filtered and then join, The result is naturally different.

3. Or a few SQL leads to the problem: (Filter conditions in the Right table dept, d.loc!= ' NEW YORK ')

SQL Code--sql 4SelectE.*, D.loc fromEMP E LEFT JOIN Dept D onE.deptno=d.deptno and d.loc!= ' NEW YORK ' Order byEmpno --sql 5SelectE.*, D.loc fromEMP E LEFT JOIN (Select* fromDeptwheredept.loc!= ' NEW YORK ') d onE.deptno=d.deptno Order byEmpno --sql 6SelectE.*, D.loc fromEMP E LEFT JOIN Dept D onE.deptno=d.deptnowhered.loc!= ' NEW YORK ' Order byEmpno

--sql 4
Select e.*, d.loc from EMP e-LEFT JOIN Dept D in E.deptno=d.deptno and d.loc!= ' NEW YORK ' ORDER by empno;
  
   --sql 5
Select e.*, d.loc from EMP e-LEFT JOIN (SELECT * to dept where dept.loc!= ' NEW YORK ') d on e.deptno=d.dept No order by Empno;
--sql 6
Select e.*, d.loc from EMP e-LEFT JOIN Dept D in E.deptno=d.deptno where d.loc!= ' NEW YORK ' ORDER by empno;
  

SQL4 Execution Plan: SQL code execution plans----------------------------------------------------------plan hash value:1901738359- ---------------------------------------------------------------------------    | Id | Operation |Name|Rows| Bytes | Cost (%CPU) | Time|   ----------------------------------------------------------------------------    | 0 |SELECTSTATEMENT |    |   14 |     672 | 8 (25) |    00:00:01 |   |  1 | SORT Order by|    |   14 |     672 | 8 (25) |    00:00:01 |   |* 2 |      HASH JOIN OUTER |    |   14 |     672 | 7 (15) |    00:00:01 |   | 3 |TABLEACCESS Full|    EMP |   14 |     518 | 3 (0) |    00:00:01 | |* 4 |TABLEACCESS Full|     DEPT |    3 |     33 | 3 (0) |    00:00:01 | ----------------------------------------------------------------------------predicate information (identified by operation id):   ---------------------------------------------------          2 - access ("E".) DEPTNO "=" D "." DEPTNO "(+))       4 - filter (" D ".) LOC "(+) <> ' new york ')   

Execution Plan Hash value:1901738359---------------------------------------------------------------------------------- ----------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |
Time |   ----------------------------------------------------------------------------
| 0 |      SELECT STATEMENT |    |   14 |     672 | 8 (25) |   00:00:01 | |  1 |      SORT ORDER BY |    |   14 |     672 | 8 (25) |   00:00:01 | |* 2 |      HASH JOIN OUTER |    |   14 |     672 | 7 (15) |   00:00:01 | |    3 | TABLE ACCESS full|    EMP |   14 |     518 | 3 (0) |    00:00:01 | |* 4 | TABLE ACCESS full|     DEPT |    3 |     33 | 3 (0) |
00:00:01 | ----------------------------------------------------------------------------predicate information (identified by Operation ID):---------------------------------------------------2-access ("E". ") DEPTNO "=" D "." DEPTNO "(+)) 4-filter (" D ".) LOC "(+) <> ' NEW YORK ')

Visible is the first filter ("D".) LOC "(+) <> ' NEW YORK '), join again. Equivalent to SQL5

So the results of SQL4 and SQL5 are the same.

But SQL6 's result is very dramatic, SQL6 execution results

Join and Filter First:

SELECT * FROM (select e.*, d.loc from EMP e-LEFT JOIN Dept D in E.deptno=d.deptno) where loc!= ' NEW YORK ' ORDER by empno

4. More Classic (filter two tables common columns): SQL code Select e.*, D.deptno, d.dname from emp e -left JOIN Dept D on E.deptno= D.deptno where d.deptno!=30 ORDER by Empno;

Select E.*, D.deptno, d.dname from EMP e-LEFT JOIN Dept D in E.deptno=d.deptno where d.deptno!=30 order by empno;

SQL code execution plans----------------------------------------------------------plan hash value:3357797783------------ ----------------------------------------------------------------    | Id | Operation |Name|Rows| Bytes | Cost (%CPU) | Time|   ----------------------------------------------------------------------------    | 0 |SELECTSTATEMENT |     |   9 |     450 | 8 (25) |    00:00:01 |   |  1 | SORT Order