Gunther Oracle APEX und Datenbank Wiki

Benutzer-Werkzeuge

Webseiten-Werkzeuge

Zuletzt angesehen:
prog:sql_anti_join

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen RevisionVorhergehende Überarbeitung
Nächste Überarbeitung		Vorhergehende Überarbeitung
prog:sql_anti_join [2016/08/17 15:17] – [Der Ausführungsplan] gpipperrprog:sql_anti_join [2016/08/18 20:57] (aktuell) – gpipperr
Zeile 1: Zeile 1:
 +=====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]]
 +
  
prog/sql_anti_join.txt · Zuletzt geändert: 2016/08/18 20:57 von gpipperr