=====Mit einem "Left Join" Daten filtern - Einen Anti Join in Oracle SQL erzeugen=====

Ziel ist es, alle Datensätze aus der Tabelle zu filtern, die <fc #800000>NICHT</fc> in der anderen Tabelle vorkommen!


Beispiel: Hole alle Mitarbeiter deren Department NICHT in der Department Table vorkommt:
<code sql>
select EMPLOYEE_ID,FIRST_NAME 
  from EMPLOYEES e
       LEFT JOIN DEPARTMENTS d ON ( nvl(e.DEPARTMENT_ID,'-1') = d.DEPARTMENT_ID) 
where d.DEPARTMENT_ID is null;
</code>
(nvl wird nur benötigt falls die Join Bedingung Null Values enthält!)

Durch den LEFT Join ist die Department ID ja NULL in der Ergebnismenge, falls das Gegenstück nicht in der Department Tabelle gefunden werden kann. 

Genau auf diese Treffer filtern wir mit der "d.DEPARTMENT_ID is null" Bedingung.

----

===Der Ausführungsplan===

Klassisch wäre die Lösung mit " where nvl(e.DEPARTMENT_ID,'-1')  not in (select d.DEPARTMENT_ID from DEPARTMENTS d)"


Auf einer 11g ergibt sich tatsächlich der gleiche Plan, bin verwundert:

<code sql>
explain plan for select EMPLOYEE_ID,FIRST_NAME  from EMPLOYEES e   LEFT JOIN DEPARTMENTS d ON ( nvl(e.DEPARTMENT_ID,-1)= d.DEPARTMENT_ID) where d.DEPARTMENT_ID is null;

....


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 1538847337

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |     1 |    18 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI |            |     1 |    18 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMPLOYEES  |   107 |  1498 |     3   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| DEPT_ID_PK |    27 |   108 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------

explain plan for select EMPLOYEE_ID,FIRST_NAME from EMPLOYEES e  where nvl(e.DEPARTMENT_ID,'-1')  not in ( select d.DEPARTMENT_ID from DEPARTMENTS d );

....

Plan hash value: 1538847337

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |     1 |    18 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI |            |     1 |    18 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMPLOYEES  |   107 |  1498 |     3   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| DEPT_ID_PK |    27 |   108 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------

</code>

Muss ich noch auf einer 12c testen.... 



----


==== Quellen ====

siehe auch Join Syntax : [[prog:sql_join_syntax|Oracle ANSI-89 Standard versus dem ANSI-92 - The SQL Join Battle]]