Vorteile von Inner Join gegenüber "where" ?

[EiskalterEngel]

Hallo liebe Community,

in der Berufsschule haben wir eine Aufgabe, ein Referat über Inner Join zu machen. Des Weiteren sollen wir darauf eingehen, was der Vorteil von Inner Join ist, gegenüber das ganze mit "where" zu lösen.

Beispiel:

InnerJoin:

SELECT T_Kunden.kunden_nr, T_Bestellungen.bestell_nr, T_Kunden.name, T_Positionen.gesamtpreis
FROM ((T_Bestellungen INNER JOIN T_Kunden ON T_Bestellungen.kunden_nr = T_Kunden.kunden_nr) 
INNER JOIN T_Positionen ON T_Bestellungen.bestell_nr = T_Positionen.bestell_nr)

Where:

SELECT T_Kunden.kunden_nr, T_Bestellungen.bestell_nr, T_Kunden.name
FROM T_Kunden, T_Bestellungen, T_Positionen
where T_Kunden.kunden_nr = T_Bestellungen.kunden_nr AND T_Bestellungen.bestell_nr = T_Positionen.bestell_nr

Unser Problem: Unser Lehrer meinte, wenn man es mit dem "where"-Fall lösen würde, es zu mehr Datensätze kommen würde (Anzahl der Datensätze aus Tabelle A mal Anzahl der Datensätze aus Tabelle B). Als wir dies jedoch implementiert haben, konnten wir keinen Unterschied feststellen.

Hat jemand von euch vielleicht eine Idee, woran das liegt, bzw. was die Vor- und Nachteile sind? Oder ggf. ein Beispiel?

Sind über jede Hilfe dankbar!

Liebe Grüße

[Bernhard Geyer]

Mit passenden Where-Klauseln ist das Ergebnis gleich, jedoch die Serverbelastung u.U. höher. Einen Inner Join werden die meisten DBMS schneller ausführen können.

[Falk Prüfer]

Hallo,

ein INNER JOIN läßt sich prinzipiell immer (und mit gleichem Ergebnis) auch über eine passende WHERE-Klausel formulieren. Jedes moderne DBMS sollte dabei auch den gleichen Ausführungspfad wählen so das es keine Unterschiede in der Performance geben sollte.
Der Vorteil liegt in der besseren Lesbarkeit des SQL-Codes mit explizit formuliertem Join. Es findet eine saubere Trennung zwischen der eigentlichen JOIN-Bedingung und evtl. "echten" Where-Bedingungen statt, die das Ergebnis einschräncken. Spätestens wenn ich statt dem INNER JOIN einen OUTER JOIN benötige muß ich die Abfrage dann doch umschreiben.
Deshalb meine Empfehlung: Einen JOIN immer als JOIN formulieren, auch wenn man ihn durch eine passende WHERE-Bedingung implizieren könnte.

Gruß Falk

[EiskalterEngel]

Vielen Dank an alle.

Ihr habt mir sehr geholfen!

Liebe Grüße

[Bernhard Geyer]

ein INNER JOIN läßt sich prinzipiell immer (und mit gleichem Ergebnis) auch über eine passende WHERE-Klausel formulieren. Jedes moderne DBMS sollte dabei auch den gleichen Ausführungspfad wählen so das es keine Unterschiede in der Performance geben sollte.

Theoretisch ja, aber eigene Erfahrungen sprechen dafür das Query Parser auch nur von fehlerbehafteten Menschen entwickelt werden. Und da habe ich schon komischste Query-Optimierungen gesehen.

[Falk Prüfer]

...Und da habe ich schon komischste Query-Optimierungen gesehen.

Deshalb habe ich auch die Möglichkeitsform gewählt ...

[TomT]

Einen Inner Join werden die meisten DBMS schneller ausführen können.

Das ist mal eine Interessante Behauptung. Gibts da auch eine Beweisführung dazu?

Ich machs schon mal für Oracle:

explain plan for
select av.* 
 from tbl0100person pers,
      tbl2000pcbperson pb,
      tbl2000pcbpolice pp,
      tbl0200police pol,
      tbl0200allgvertrag av
where pers.name1='Hugo'
  and pers.persno=pb.persno
  and pb.pcbid=pp.pcbid
  and pp.polid=pol.polid
  and pol.polid=av.polid
  and av.sparte='123'
  and pb.crm=0;

SQL> select * from table(dbms_xplan.display);
Plan hash value: 10822363

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                              |  7224 |  1911K| 87434   (3)| 00:17:30 |
|*  1 |  HASH JOIN                       |                              |  7224 |  1911K| 87434   (3)| 00:17:30 |
|   2 |   TABLE ACCESS BY INDEX ROWID    | TBL0100PERSON                |   610 | 10980 |   607   (0)| 00:00:08 |
|*  3 |    INDEX RANGE SCAN              | TBL0100PERSON_IX_N1N3        |   610 |       |     4   (0)| 00:00:01 |
|*  4 |   HASH JOIN                      |                              |    85M|    20G| 86235   (2)| 00:17:15 |
|   5 |    TABLE ACCESS BY INDEX ROWID   | TBL2000PCBPOLICE             |     1 |    24 |     3   (0)| 00:00:01 |
|   6 |     NESTED LOOPS                 |                              |  4137 |   933K| 13073   (1)| 00:02:37 |
|   7 |      NESTED LOOPS                |                              |  3296 |   666K|  3548   (1)| 00:00:43 |
|   8 |       TABLE ACCESS BY INDEX ROWID| TBL0200ALLGVERTRAG           |  3296 |   624K|   250   (0)| 00:00:04 |
|*  9 |        INDEX RANGE SCAN          | TBL0200ALLGVERTRAG_SPARTE_IX |  3405 |       |    10   (0)| 00:00:01 |
|* 10 |       INDEX UNIQUE SCAN          | PK_TBL0200POLICE             |     1 |    13 |     1   (0)| 00:00:01 |
|* 11 |      INDEX RANGE SCAN            | TBL2000PCBPOLICE_IX1         |     1 |       |     2   (0)| 00:00:01 |
|* 12 |    TABLE ACCESS FULL             | TBL2000PCBPERSON             |    16M|   335M| 72570   (2)| 00:14:31 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("PERS"."PERSNO"="PB"."PERSNO")
   3 - access("PERS"."NAME1"='Hugo')
   4 - access("PB"."PCBID"="PP"."PCBID")
   9 - access("AV"."SPARTE"='123')
  10 - access("POL"."POLID"="AV"."POLID")
  11 - access("PP"."POLID"="POL"."POLID")
  12 - filter(TO_NUMBER("PB"."CRM")=0)

30 Zeilen ausgewählt.

Das war der alte Kommajoin (im Filterprädikat sieht man auch sehr schön, warum es so wichtig ist die richtigen Datentypen zu verwenden).

explain plan for 
select av.* 
 from tbl0100person pers inner join tbl2000pcbperson pb   on pers.persno=pb.persno
                         inner join tbl2000pcbpolice pp   on pb.pcbid=pp.pcbid
                         inner join tbl0200police   pol   on pp.polid=pol.polid
                         inner join tbl0200allgvertrag av on pol.polid=av.polid
  where pers.name1='Hugo'
    and av.sparte='123'
    and pb.crm=0;

SQL> select * from table(dbms_xplan.display);
Plan hash value: 10822363

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                              |  7224 |  1911K| 87434   (3)| 00:17:30 |
|*  1 |  HASH JOIN                       |                              |  7224 |  1911K| 87434   (3)| 00:17:30 |
|   2 |   TABLE ACCESS BY INDEX ROWID    | TBL0100PERSON                |   610 | 10980 |   607   (0)| 00:00:08 |
|*  3 |    INDEX RANGE SCAN              | TBL0100PERSON_IX_N1N3        |   610 |       |     4   (0)| 00:00:01 |
|*  4 |   HASH JOIN                      |                              |    85M|    20G| 86235   (2)| 00:17:15 |
|   5 |    TABLE ACCESS BY INDEX ROWID   | TBL2000PCBPOLICE             |     1 |    24 |     3   (0)| 00:00:01 |
|   6 |     NESTED LOOPS                 |                              |  4137 |   933K| 13073   (1)| 00:02:37 |
|   7 |      NESTED LOOPS                |                              |  3296 |   666K|  3548   (1)| 00:00:43 |
|   8 |       TABLE ACCESS BY INDEX ROWID| TBL0200ALLGVERTRAG           |  3296 |   624K|   250   (0)| 00:00:04 |
|*  9 |        INDEX RANGE SCAN          | TBL0200ALLGVERTRAG_SPARTE_IX |  3405 |       |    10   (0)| 00:00:01 |
|* 10 |       INDEX UNIQUE SCAN          | PK_TBL0200POLICE             |     1 |    13 |     1   (0)| 00:00:01 |
|* 11 |      INDEX RANGE SCAN            | TBL2000PCBPOLICE_IX1         |     1 |       |     2   (0)| 00:00:01 |
|* 12 |    TABLE ACCESS FULL             | TBL2000PCBPERSON             |    16M|   335M| 72570   (2)| 00:14:31 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("PERS"."PERSNO"="PB"."PERSNO")
   3 - access("PERS"."NAME1"='Hugo')
   4 - access("PB"."PCBID"="PP"."PCBID")
   9 - access("AV"."SPARTE"='123')
  10 - access("AV"."POLID"="POL"."POLID")
  11 - access("PP"."POLID"="POL"."POLID")
  12 - filter(TO_NUMBER("PB"."CRM")=0)

30 Zeilen ausgewählt.

So, der gleiche Plan, gleiche Kosten, gleiche Access und Filterkriterien.

Zumindest für Oracle stimmt die Behauptung "die meisten" nicht. Ich sage eher, die meisten modernen DBMS werden dabei absolut keinen Unterschied machen.

Aber vielleicht gibts ja im Verlauf des Threads noch ein paar MSSQL, PgSQL etc.. Pläne die zeigen was da passiert.