Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:sql_anti_join

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 NICHT in der anderen Tabelle vorkommen!

Beispiel: Hole alle Mitarbeiter deren Department NICHT in der Department Table vorkommt:

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;

(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:

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 |
---------------------------------------------------------------------------------

Muss ich noch auf einer 12c testen….


Quellen

Diese Website verwendet Cookies. Durch die Nutzung der Website stimmen Sie dem Speichern von Cookies auf Ihrem Computer zu. Außerdem bestätigen Sie, dass Sie unsere Datenschutzbestimmungen gelesen und verstanden haben. Wenn Sie nicht einverstanden sind, verlassen Sie die Website.Weitere Information
prog/sql_anti_join.txt · Zuletzt geändert: 2016/08/18 20:57 von gpipperr