[sql] alle records in dataset 1 maar niet in dataset 2

[Nick_S]

Ik kom er even niet uit. Kan iemand helpen?

Ik heb twee tabellen met elk twee kolommen. Nu wil ik elk record uit dataset 1 welke niet in dataset 2 zit. Ik ben al aan het rommelen geweest met count having maar ik kom er ff niet uit.

[Bergen]

select * from table1 where id not in (select id from table2);

Zoiets bedoel je?

[ Voor 11% gewijzigd door Bergen op 03-07-2009 01:19 ]

[Nick_S]

Wel zoiets, maar helaas heb ik geen id's. pk is gecombineerd.

Dus:
Tabel 1

kolom a	kolom b
x	1
x	2
y	1

Tabel 2

kolom c	kolom d
x	1

En dan wil ik als uitkomst:

kolom a	kolom b
x	2
y	1

[Bergen]

Misschien met concat...

select * from table1 where concat(column1,column2) not in (select concat(column1, column2) from table2);

Maar stel dat in tabel 1 dan staat: "kat", "hond" en in tabel 2 staat "kath", "ond", dan wordt die ook niet weergegeven. Je zou met concat_ws een separator ertussen kunnen plaatsen waarvan het zeker is dat die niet in de tabellen voor komt. Een of andere gekke string dus.

select * from table1 where concat_ws('--++--++--', column1,column2) not in (select concat_ws('--++--++--', column1, column2) from table2);

Dit kan iig in MySQL, ik weet niet hoe standaard deze functie is onder verschillende SQL-smaken. Maar misschien is er wel een veel mooiere oplossing...

[ Voor 0% gewijzigd door Bergen op 03-07-2009 02:11 . Reden: separator, niet seperator ]

[Nick_S]

Ik denk dat ik hem heb:

SQL:

select Table1.veld_a, Table1.veld_b, (select count(*) from Table2 where Table1.veld_a = Table2.veld_c and Table1.veld_b = Table2.veld_d) as aantal from Table1 having aantal = 0;

[pedorus]

Concat lijkt me beroerd qua performance. De meeste database-engines behalve MySQL kennen hiervoor EXCEPT. Dus (vervang * en table? door iets beters):

SQL:

select * from table1 except select * from table2;

In MySQL kun je iets doen als wat je net zelf zegt, ervan uitgegaan dat je geen null-waardes hebt. Alternatief is dit net iets handiger denk ik:

SQL:

select * from table1 where not exists (select * from table2 where a=c and b=d);

of, wellicht sneller:

SQL:

select a,b from table1 left join table2 on a=c and b=d where c is null;

even snel getest met

SQL:

drop table if exists table1,table2;
create table table1(a varchar(10),b int);
insert into table1 values ('x',1),('x',2),('y',1);
create table table2(c varchar(10),d int);
insert into table2 values ('x',1);
explain select a,b from table1 where (select count(*) from table2 where a=c and b=d)=0;
explain select * from table1 where not exists (select * from table2 where a=c and b=d);
explain select a,b from table1 left join table2 on a=c and b=d where c is null;

en het lijkt erop dat de laatste variant het beste gaat werken, omdat ook exists een dependent subquery geeft. Maar dat zal ook van indexes afhangen, dus dat is een kwestie van testen.

offtopic:
voor de rest is de topicstart een beetje beroerd natuurlijk; de 2e post is veel beter

[Nick_S]

pedorus schreef op vrijdag 03 juli 2009 @ 02:38:

offtopic:
voor de rest is de topicstart een beetje beroerd natuurlijk; de 2e post is veel beter

Ja, dat klopt. Dat krijg je als een maat van je een probleem op twee bierviltjes op het terras van de kroeg uitlegt en je er niet uitkomt. Dan ga je met je G1 posten op tweakers en dat geeft niet de beste resultaten.

[pedorus]

Nick_S schreef op vrijdag 03 juli 2009 @ 02:47:
[...]

Ja, dat klopt. Dat krijg je als een maat van je een probleem op twee bierviltjes op het terras van de kroeg uitlegt en je er niet uitkomt. Dan ga je met je G1 posten op tweakers en dat geeft niet de beste resultaten.

offtopic:
Ah, dus daarom is er nog geen goede mobiele versie van het forum

---

Ik bedacht me nog dat dit ook gewoon kan in MySQL (dus gewoon zonder concat):

SQL:

explain select * from table1 where (a,b) not in (select c,d from table2);

En intern wordt dat dan omgezet naar die exists als ik het goed heb, en het geeft dan ook exact dezelfde explain.

[Remus]

pedorus schreef op vrijdag 03 juli 2009 @ 03:08:
[...]
Ik bedacht me nog dat dit ook gewoon kan in MySQL (dus gewoon zonder concat):

SQL:

explain select * from table1 where (a,b) not in (select c,d from table2);

En intern wordt dat dan omgezet naar die exists als ik het goed heb, en het geeft dan ook exact dezelfde explain.

Als je een grote dataset hebt is een left join met een is null check denk ik over het algemeen efficienter dan een not in of not exists query.

[glmona]

als het een oracle db is kan je ook

SQL:

select a, b from table1 minus select c, d from table2

[zAo]

Nick_S schreef op vrijdag 03 juli 2009 @ 02:47:
[...]
Ja, dat klopt. Dat krijg je als een maat van je een probleem op twee bierviltjes op het terras van de kroeg uitlegt en je er niet uitkomt. Dan ga je met je G1 posten op tweakers en dat geeft niet de beste resultaten.

Geef die G1 maar de schuld

glmona schreef op vrijdag 03 juli 2009 @ 08:49:
als het een oracle db is kan je ook

SQL:

select a, b from table1 minus select c, d from table2

Het stond niet in de TS, maar het ging inderdaad om Oracle 10g and thus we have a winner

/me zAo vraagt zich af waarom je na 3 uur moeilijk doen, tot de meest eenvoudige oplossing komt.

Thanks all

[winkbrace]

En de belangrijkste opmerking wordt zoals gewoonlijk genegeerd.

Remus schreef op vrijdag 03 juli 2009 @ 08:33:
[...]

Als je een grote dataset hebt is een left join met een is null check denk ik over het algemeen efficienter dan een not in of not exists query.

SQL:

select a.* 
from   table a 
left join table b on  a.id = b.id
                  and a.id2 = b.id2
where  b.id is null

Maar voortaan wel erg handig om de database erbij te vermelden. Doorgaans komen hier MySQL vragen en dan moet je altijd moeilijker doen dan bij Oracle.

[The Eagle]

Ik spreek uit ervaring als ik zeg dat een MINUS statement onder Oracle lang niet altijd de juiste gegevens weergeeft. Draai je statement maar eens om en kijk wat er dan uit komt.

[winkbrace]

The Eagle schreef op vrijdag 03 juli 2009 @ 15:29:
Ik spreek uit ervaring als ik zeg dat een MINUS statement onder Oracle lang niet altijd de juiste gegevens weergeeft. Draai je statement maar eens om en kijk wat er dan uit komt.

MINUS werkt prima in Oracle. Je moet je query wel goed schrijven, he.
8 records - 4 records is nu eenmaal wat anders dan 4 records - 8 records