Alle Werte aus Tabelle 1, außer in Tabelle 2 vorhanden

M

Michi_R

Fleissiger Benutzer
Beiträge
81
Hallo,

irgendwie steh ich gerade mal wieder total auf dem Schlauch. Ich habe zwei Tabellen mit je 2 Spalten (die selben) und ich möchte alle Wertepaare aus Tabelle 1 die NICHT in Tabelle 2 vorkommen.
Also sozusagen das Gegenteil eines inner join (hier würde ich ja alle aus 1 nehmen die AUCH in 2 vorkommen).
Ich bräuchte also ein except, oder? Aber irgendwie weiß ich nicht wie :(
Hier die Tabellen noch als Beispiel:


Danke schon mal;)
 
Werbung:
Das hatte ich versucht, dauert aber Ewigkeiten
Tabelle 1 hat 9000 Einträge, Tabelle 2 hat 500. Das erschien mir auch nicht ganz richtig :O
Abfrage der Werte der beiden einzelnen Tabelle je 1-2 Sekunden. Ist das Join so aufwändig? Irgendwie passt das doch nicht.
 
Zuletzt bearbeitet:
Michi_R schrieb:
Das hatte ich versucht, dauert aber Ewigkeiten
Tabelle 1 hat 9000 Einträge, Tabelle 2 hat 500. Das erschien mir auch nicht ganz richtig :O
Abfrage der Werte der beiden einzelnen Tabelle je 1-2 Sekunden. Ist das Join so aufwändig? Irgendwie passt das doch nicht.
Zum Vergrößern anklicken....


Mit 50.000 und 5.000 Werten:

Code: 
test=# create table t1 (i int);
CREATE TABLE
test=*# create table t2 (i int);
CREATE TABLE
test=*# insert into t1 select random() * 10000 from generate_series(0,50000)s;
INSERT 0 50001
test=*# insert into t2 select random() * 10000 from generate_series(0,5000)s;
INSERT 0 5001
test=*# create index idx_t1 on t1(i);
CREATE INDEX
test=*# create index idx_t2 on t2(i);
CREATE INDEX
test=*# explain analyse select t1.* from t1 left join t2 on (t1.i=t2.i) where t2.i is null;
  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Hash Anti Join  (cost=135.52..1873.17 rows=25000 width=4) (actual time=2.157..31.406 rows=30181 loops=1)
  Hash Cond: (t1.i = t2.i)
  ->  Seq Scan on t1  (cost=0.00..722.01 rows=50001 width=4) (actual time=0.009..8.967 rows=50001 loops=1)
  ->  Hash  (cost=73.01..73.01 rows=5001 width=4) (actual time=2.141..2.141 rows=5001 loops=1)
  Buckets: 1024  Batches: 1  Memory Usage: 176kB
  ->  Seq Scan on t2  (cost=0.00..73.01 rows=5001 width=4) (actual time=0.010..0.705 rows=5001 loops=1)
 Total runtime: 34.629 ms
(7 rows)

34millisekunden sind nun doch ganz passabel, oder?
 
So sieht der Query ungefähr aus

select * from
(select nummer, status from table1) as t1
left outer join
(select nummer, status from table2) as t2
on (t1.nummer=t2.nummer and t1.status=t2.status)
where t2.nummer is null

Also Tabelle 1 und Tabelle 2 existieren nicht direkt in der DB, sondern sind auch schon "zusammengejoint" über 3 bzw 2 Tabellen, aber wie gesagte diese Abfragen sind sofort ausgeführt.
 
Versuchs mal damit:
Code: 
SELECT T1.*
FROM table1 AS T1
LEFT OUTER JOIN
table2 AS T2
ON T1.nummer = T2.nummer
WHERE T2.nummer IS NULL
 
Michi_R schrieb:
Also Tabelle 1 und Tabelle 2 existieren nicht direkt in der DB, sondern sind auch schon "zusammengejoint" über 3 bzw 2 Tabellen, aber wie gesagte diese Abfragen sind sofort ausgeführt.
Zum Vergrößern anklicken....

Den Status brauchst du nicht zu vergleichen weil er für die Selektion mit WHERE keine Rolle spielt. Vermutlich hast du da auch keinen Index was die Anfragedauer so steigen lässt.
 
Ich meine schon, dass ich den status mit vergleich muss. Denn wenn ich den nicht vergleiche, also wenn der join nur anhand der Nummer joint, woher soll er dann wissen was er im Fall von Wertepaar (1001 - 1) und (1001 - 2) machen soll. Hier wäre die Nummer gleich, aber der status nicht.

hab es aber ohne status versucht, klappt nicht.
Aus ca. 8000 (hatte mich vertan vorhin) macht er mit ca. 5300. Und wnen Tabelle 2 nur 500 hat, dann könnte es ja wenigstens 7500 werden und nicht 5300.
 
Ach ich seh meinen Verständnisfehler. Ja, ein EXCEPT wäre ideal. Gibt es in MySQL aber nicht.

Da deine Anfragen bereits Joins sind können wir auch keinen zusätzlichen Index setzen. Was sagt den EXPLAIN zu deinem Query?
 
Nein, nicht ganz.
Status ist relevant.
Ich brauche alle aus Tabelle 1 die NICHT auch zugleich in Tabelle 2 vorkommen.
union wären

Die, die grün sind will ich haben:


Except hab ich versucht (bin in MSSQL) hat mich aber nicht zum Ziel gebracht.
Was meinst du genau mit EXPLAN?
 
Also Except hat geklappt, ebenso aber der obige Vorschlag (left join und schaun wo null ist).
Except 6min 47sec
Left join: 6min 46sec
Ergebnisse sind gleich und auch plausibel.
:O
 
Werbung:
Hony% schrieb:
Falsches Forum. ;)

Was macht EXCEPT den falsch?
Zum Vergrößern anklicken....

EXCEPT sollte gehen, es gibt das zurück was mein Join auch anzeigte, allerdings nur UNIQUE-Zeilen:

Code: 
test=*# explain analyse select t1.* from t1 left join t2 on (t1.i=t2.i) where t2.i is null;
  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Hash Anti Join  (cost=135.52..1873.17 rows=25000 width=4) (actual time=2.157..31.406 rows=30181 loops=1)
  Hash Cond: (t1.i = t2.i)
  ->  Seq Scan on t1  (cost=0.00..722.01 rows=50001 width=4) (actual time=0.009..8.967 rows=50001 loops=1)
  ->  Hash  (cost=73.01..73.01 rows=5001 width=4) (actual time=2.141..2.141 rows=5001 loops=1)
  Buckets: 1024  Batches: 1  Memory Usage: 176kB
  ->  Seq Scan on t2  (cost=0.00..73.01 rows=5001 width=4) (actual time=0.010..0.705 rows=5001 loops=1)
 Total runtime: 34.629 ms
(7 rows)

test=*# explain analyse select * from t1 except select * from t2;
  QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 HashSetOp Except  (cost=0.00..1482.55 rows=200 width=4) (actual time=66.648..67.723 rows=6015 loops=1)
  ->  Append  (cost=0.00..1345.04 rows=55002 width=4) (actual time=0.017..41.915 rows=55002 loops=1)
  ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..1222.02 rows=50001 width=4) (actual time=0.016..26.931 rows=50001 loops=1)
  ->  Seq Scan on t1  (cost=0.00..722.01 rows=50001 width=4) (actual time=0.014..11.317 rows=50001 loops=1)
  ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..123.02 rows=5001 width=4) (actual time=0.010..1.869 rows=5001 loops=1)
  ->  Seq Scan on t2  (cost=0.00..73.01 rows=5001 width=4) (actual time=0.009..0.814 rows=5001 loops=1)
 Total runtime: 68.464 ms
(7 rows)

test=*# explain analyse select distinct t1.* from t1 left join t2 on (t1.i=t2.i) where t2.i is null;
  QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=1935.67..1937.67 rows=200 width=4) (actual time=54.142..55.430 rows=6015 loops=1)
  ->  Hash Anti Join  (cost=135.52..1873.17 rows=25000 width=4) (actual time=3.145..40.533 rows=30181 loops=1)
  Hash Cond: (t1.i = t2.i)
  ->  Seq Scan on t1  (cost=0.00..722.01 rows=50001 width=4) (actual time=0.014..11.901 rows=50001 loops=1)
  ->  Hash  (cost=73.01..73.01 rows=5001 width=4) (actual time=3.121..3.121 rows=5001 loops=1)
  Buckets: 1024  Batches: 1  Memory Usage: 176kB
  ->  Seq Scan on t2  (cost=0.00..73.01 rows=5001 width=4) (actual time=0.014..1.379 rows=5001 loops=1)
 Total runtime: 56.170 ms

JOIN scheint aber schneller zu sein.
 

Ähnliche Themen

M
SQL-Abfrage: Wert einer Spalte mit bestimmtem Wert in einer Zeile einer anderen Spalte
Antworten
2
Aufrufe
231
ukulele
ukulele
M
2 Tabellen mit mit schwierigen Kriterien zusammenführen
Antworten
6
Aufrufe
506
ukulele
ukulele
Kampfgummibaerlie
Distinct als Lösung
Antworten
5
Aufrufe
171
Kampfgummibaerlie
Kampfgummibaerlie
H
Auswahlliste in Spalte 3 eingrenzen Basis des Wertes aus Spalte 2
Antworten
0
Aufrufe
178
Holger S.
H
D
2 MS SQL Datenbanken (Instanzen) mit 1 MS SQL 2008 R2 und 64-bit Problem (Scheinlösung?)
Antworten
11
Aufrufe
340
ukulele
ukulele
Zurück
Oben