[SQL] Enkele vragen

[EfBe]

Voor een niet nader te noemen tool ben ik op zoek naar antwoorden op de volgende vragen. Voor de urineerders: ik heb gegoogled, gesearched, Books Online omgespit, goed nagedacht, maar kom er 1 2 3 niet uit.

De vragen hebben betrekking op de volgorde van join expressies in SQL. Het onderliggende vraagstuk is nl.: is er een volgorde van join expressies nodig of niet? (dus moet je haakjes plaatsen bij join expressies om een join volgorde te omschrijven of niet?) Alle voorbeelden die ik zelf tot nu toe heb uitgeschreven resulteren in dezelfde rowset, wat eventueel erop duidt dat er geen volgorde gespecificeerd hoeft te worden. Er is een uitzondering: cross joins, vandaar vraag 1

1) Wat is het semantische nut van een CROSS JOIN?
Cross joins joinen alle rows van operand 1 met operand 2, dus row 1 van operand 1 wordt gejoined met alle rows van operand 2 etc. Dit levert op dat bij de crossjoin van 3 of meer objects (views/tables) je een volgorde moet opgeven, omdat dit het eindresultaat beinvloedt. Nu is mijn vraag echter: wat is het nut van CROSS JOINs? Ik gebruik ze zelf NOOIT, en heb toch echt al wat lappen SQL geschreven in mn leven.

2) Is er een join voorbeeld waarbij volgorde van belang is?
Eigenlijk de kern van de zaak: is er een join expressie met 3 of meerdere objects (tables/views) waarin volgorde van essentieel belang is, zodat je haakjes moet plaatsen? Het gaat er om dat het eindresultaat verschilt, als je de haakjes anders plaatst (wat dus duidt op een volgorde) plus dat je de join niet op een andere manier kunt schrijven zodat de volgorde-eis wegvalt. Ik heb zelf het vermoeden van niet, dus dat volgorde niet uitmaakt.

voorbeeld:
A INNER JOIN B ON ... INNER JOIN C ON ... INNER JOIN D ON...
is gelijk aan
(A INNER JOIN B ON ...) INNER JOIN (C INNER JOIN D ON) ON ...
(dit is zo omdat foreign key constraints ervoor zorgen dat er geen 'orphaned FK velden' zijn en dus dat een table het eindresultaat filtert)

maar hoe zit dit met LEFT / RIGHT joins? Iemand een voorbeeld dat volgorde essentieel maakt en dat niet omgeschreven kan worden naar een volgorde-vrij voorbeeld? Alvast bedankt

[whoami]

Cross joins heb ik zelf ook nog nooit gebruikt.

Over de volgorde van joins: ik heb nog nooit meegemaakt dat de volgorde van een join van belang is.
Zeker als je geen OUTER join gebruikt kan een volgorde imho nooit van belang zijn.

Ik dacht trouwens dat de optimizer van een DBMS zelf ging bepalen in welke volgorde de joins en de where clause moeten uitgevoerd worden, om zo de query op de meest performante manier te kunnen uitvoeren.

[momania]

Op je eerste vraag weet ik zo 123 ook geen antwoord.
De tweede vraag:
Een voorbeeld waarbij een andere volgorde van de JOINS ook een ander resultaat geeft zie ik ook ff niet.
Wel weet ik uit eigen ervaring dat de volgorde soms veel uitmaakt voor de snelheid waarmee een query uitgevoerd wordt. Dit omdat met een verkeerde volgorde de query fout geoptimaliseerd kan worden en dus soms minuten kan duren. De volgorde van de JOINS beter aangepast op het database model kan dan een hoop tijdswinst geven.

[Verwijderd]

De volgorde van uitvoeren is van erg groot belang. Dit bepaald de performance van je statement. Het gaat erom om je hoeveelheid data zo snel mogelijk te begrenzen zodat de data die erbij opgehaald moet worden via unieke sleutels opgehaald kan worden.
Het executie plan toont via welke sleutels de data opgehaald gaat worden.
Outer joins dienen altijd zo laat mogelijk uigevoerd te worden omdat die data er mischien niet is en dus niet begrenzend werkt. Het is alleen om additionele data bij de output op te halen.

[ Voor 1% gewijzigd door Verwijderd op 09-04-2003 11:40 . Reden: typo ]

[Verwijderd]

SELECT COUNT(*)
FROM bom_bill_of_materials bom
, bom_inventory_components bic
, bom_inventory_components_dfv icd
, mtl_system_items msi
WHERE NVL(bom.alternate_bom_designator,'@@@') <> 'INV_STND'
AND NVL(msi.item_type,'@') <> 'I'
AND bic.bill_sequence_id = bom.bill_sequence_id
AND bom.organization_id = msi.organization_id
AND bom.assembly_item_id = msi.inventory_item_id
AND bic.ROWID = icd.row_id
AND bic.component_item_id = 52821
AND bom.organization_id = 115

In dit voorbeeld staat de volgorde van de tabellen verkeerd maar omdat de enige input parameters naar BIC en BOM wijzen zal de DB altijd via een van die tabellen gaan.
De volgorde geeft echter aan dat MSI de driving table zou moeten zijn (bij Oracle wordt de volgorde van onder naar boven aangegeven). De parser vindt MSI echter geen geschikte kandidaat (geen input parameter) en gaat dus voor BIC.
Het executie plan wordt dus :
SELECT STATEMENT Optimizer=RULE
SORT (AGGREGATE)
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
INDEX (RANGE SCAN) OF BOM_INVENTORY_COMPONENTS_N1 (NON-UNIQUE)
TABLE ACCESS (BY ROWID) OF BOM_INVENTORY_COMPONENTS
TABLE ACCESS (BY ROWID) OF BOM_BILL_OF_MATERIALS
INDEX (UNIQUE SCAN) OF BOM_BILL_OF_MATERIALS_U2 (UNIQUE)
TABLE ACCESS (BY ROWID) OF MTL_SYSTEM_ITEMS
INDEX (UNIQUE SCAN) OF MTL_SYSTEM_ITEMS_U1 (UNIQUE)

ICD is een view op BIC die gekoppeld wordt via row-id (unieke interne key).

[ Voor 5% gewijzigd door Verwijderd op 09-04-2003 11:48 ]

[whoami]

Maar heeft dit dan ook een ander resultaat ?
Want daar is het waar het EfBe om gaat, niet om de performance...

[Verwijderd]

Nee.

[momania]

Een query waar de JOIN volgorde uitmaakt moet al een onderliggende datbase hebben waarin tabellen zitten die meerde FK's hebben naar andere tabellen op de zelfde sleutel.

Een voorbeeld:
(Is dan even een bankvorbeeld omdat ik dar werk en daar hebben we zo'n situatie)
Op een verzamelpunt komt een zooitje geld binnen wat van een bepaald kantoor afkomt.
Hiervoor heb je dus een tabel met alle kantoren erin en die zit dan met een FK vast aan de tabel met de gegevens van het geld wat binnenkomt.
Dat geld moet ook weer ergens naartoe, dus zijn er ook rekeninggegevens en persoonsgegevens van clienten. Die clienten hebben hun rekening lopen bij een bepaald kantoor: dat is dus weer die kantoren tabel aleen nu ook met een FK aan de clienten vast.

Wil je dus van een bepaalde transactie een kantoor weten, dan maakt het uit aan welke andere tabel je kantoren tabel JOINt, want in het ene geval krijg je dus het rekeinghoudend kantoor en het andere geval het afstortend kantoor.

[EfBe]

Performance is geen issue, want bv SqlServer optimaliseert dat zelf goed, die joint zelf eerst wat nodig is (en filtert eventueel al). vteuniss ik zie dat je Oracle gebruikt, die heeft een niet zo goede live optimizer nog, maar dat verandert wel.

Het gaat er mij om of een volgorde gespecificeerd moet worden, want dan moet ik daarvoor een veel complexere editor maken, anders kan ik volstaan met het adden van join expressies op hetzelfde level. (in de visual Sql editor van visual studio.net zit overigens geen methode om een volgorde te specificeren)

momania: nee, je moet dan toch t.a.t. table aliasses gebruiken en dan heb je daar dus geen last van.

Wat ik bedoel is dit:

(A LEFT JOIN B ) INNER JOIN C
A LEFT JOIN B INNER JOIN C
C INNER JOIN (B RIGHT JOIN A)
C INNER JOIN B RIGHT JOIN A

Mocht dit uberhaupt al een semantisch legitieme join zijn (nl. is er een situatie denkbaar waar zo'n soort constructie mogelijk moet zijn en dat de relaties daar ook voor liggen), levert dit dan dezelfde resultsets op (column order is niet van belang voor resultsets van selects)? Ik kan dit niet testen hier want ik heb geen database hier die dit soort relaties heeft. Het is ook moeilijk zoeken op dit fenomeen, want zodra je 'Order' specificeert in je zoekquery bij google en 'join sql' krijg je veelal vrolijk queries te zien met ORDER BY ..

[edit]
Het lijkt er wel op dat de 'ON <search expression> ' plaats van invloed is op de eindresultaten. (bij outer joins).

[ Voor 13% gewijzigd door EfBe op 09-04-2003 12:51 ]

[EfBe]

Ok ik heb een voorbeeld bedacht dat aantoont dat er een volgorde is.
Forum, thread en user. Forum - THread hebben PK/FK relatie over ForumID, Thread en User hebben FK/PK relatie over User ID (fk in Thread, startedbyuserid).

Wanneer een forum van een lijst forums geen thread bevat krijg je bij deze query:

SQL:

SELECT  *
FROM    TF_Forum LEFT JOIN (TF_Thread
    INNER JOIN TF_User
    ON TF_Thread.StartedByUserID = TF_User.UserID)
    ON
    TF_Forum.ForumID = TF_Thread.ForumID

meer rows dan bij

SQL:

SELECT  *
FROM    TF_Forum LEFT JOIN TF_Thread
    ON
    TF_Forum.ForumID = TF_Thread.ForumID
    INNER JOIN TF_User
    ON TF_Thread.StartedByUserID = TF_User.UserID

en bij

SQL:

SELECT  *
FROM    (TF_Thread
    INNER JOIN TF_User
    ON TF_Thread.StartedByUserID = TF_User.UserID)
    RIGHT JOIN TF_Forum
    ON
    TF_Forum.ForumID = TF_Thread.ForumID

m.a.w. er is wel een volgorde van belang, wanneer 1:n relaties worden genomen PLUS wanneer LEFT/RIGHT joins in het spel zijn EN wanneer de left operand van de LEFT join of de right operand van de RIGHT join de PK bevat in de ON expressie.

*sigh*

Dat wordt dus de complexe editor variant... Iedereen bedankt voor het meedenken.

[Verwijderd]

Je gebruikt cross-joinen nooit? Klinkt als een Cartesisch product en daarmee zou je het in elk Select-from statement gebruiken...

[ Voor 3% gewijzigd door Verwijderd op 09-04-2003 13:43 ]

[whoami]

Een cartesisch product gebruik je niet als je goed joined.

(In 1 geval heb ik eens een cartesisch product moeten gebruiken. Ik wou 10x hetzelfde record hebben; om dat te verwezenlijken heb ik een dummy tabel die enkel uit 2 kolommen bestaat en 10 rijen bevatte).

[Verwijderd]

ik geef alleen maar aan dat een cartesisch product zo'n beetje altijd getrokken wordt in een Select statement. Je kunt hem dus ook expliciet gebruiken als de overige joins niet toereikend zijn. Maar je gebruikt het daarmee haast nooit expliciet inderdaad...

[EfBe]

Onder water gebruikt de optimizer idd wel eens de carthesian product, maar in een statement zelf lijkt het me een overbodige join. Je raakt nl. opgezadeld met inmens veel overbodige rows.

[Verwijderd]

wel eens??? Select * from a, b where b.id = 9;
Daar heb je een cartesisch product

[EfBe]

Verwijderd schreef op 09 april 2003 @ 14:00:
wel eens??? Select * from a, b where b.id = 9;
Daar heb je een cartesisch product

Nee, in SqlServer neemt ie dan een INNER JOIN (Dat is niet correct, INNER JOIN is wel default maar alleen als je simpel 'JOIN' specificeert, anders gebruikt ie inderdaad een carthesian product). Verder is dat pre SQL-92 oldstyle syntaxis die niet aanbevolen wordt.

Verder levert jouw voorbeeld dus een inmense hoeveelheid rows op, terwijl je dat helemaal niet wilt. Je client krijgt dan veel rows die hij weer moet uitfilteren. Niet echt handig.

Ik vermoed dat je mySQL gebruikt. Ik kan me herinneren dat deze 'database' sinds kort FK's ondersteunt, maar verder is het nauwelijks een database te noemen, ik doel dus ook op de RDBMS'en met een solide SQL ondersteuning en ditto referential integrity.

[ Voor 11% gewijzigd door EfBe op 09-04-2003 14:26 ]

[Verwijderd]

Ten eerste doe ik niet aan kleuter speelgoed (mysql, php, vb, etc...) Een inner join is overigens fout op dat moment, want ik wil ook rijen zijn waar de PK niet overeen komt...

[whoami]

Verwijderd schreef op 09 April 2003 @ 14:00:
wel eens??? Select * from a, b where b.id = 9;
Daar heb je een cartesisch product

Waarom zou je dat willen doen?

[EfBe]

Verwijderd schreef op 09 April 2003 @ 14:26:
Ten eerste doe ik niet aan kleuter speelgoed (mysql, php, vb, etc...)

Ik ben intens blij voor je, dat jij met grote mensenspullen mag spelen. Wat zal jij een goede developer zijn.

Een inner join is overigens fout op dat moment, want ik wil ook rijen zijn waar de PK niet overeen komt...

Een carthesian product joint rijen zonder relaties daarin te betrekken. Dus select * from a, b plakt alle rijen van b achter alle rijen van a, je krijgt dan rowcount(a) * rowcount(b) aantal rijen, met data die niet aan elkaar gerelateerd is. M.a.w.: onzindata.

Wat jij wilt doet de carthesian product niet, jij krijgt nl ook veel onzin rijen terug. Als a 100.000 rijen heeft en b 100 krijg jij een rowset met 100.000 rijen en achter elke row van a zit de row uit b met id=9. How nice.

[Verwijderd]

whoami schreef op 09 april 2003 @ 14:29:
[...]


Waarom zou je dat willen doen?

Zeg, nogmaals, ik geef alleen maar aan dat het voorkomt, ik wil dit soort dingen niet.

[EfBe]

Verwijderd schreef op 09 April 2003 @ 14:32:
[...]
Zeg, nogmaals, ik geef alleen maar aan dat het voorkomt, ik wil dit soort dingen niet.

Ik kan ook uit de syntaxis van FROM clauses afleiden dat het wellicht mogelijk is. Ik vroeg me alleen af wanneer je dat gebruikt, ik kon nl. geen situatie bedenken DAT je het zou gebruiken. Jouw illustere voorbeeld is dus geen voorbeeld maar een "goh het kan"-illustratie.

Jouw "Wel eens??? (voorbeeld)" reactie doet vermoeden dat jij denkt dat een carthesian product vaker dan 0 keer voor komt en er een toepassing voor is. De enige reden dat die joins er uberhaupt zijn is bij gebrek aan inner/left/right joins, waar mySql last van had.

Enige conclusie die mogelijk is: CROSS JOIN is niet nuttig en overbodig.

[Verwijderd]

Een carthesian product joint rijen zonder relaties daarin te betrekken. Dus select * from a, b plakt alle rijen van b achter alle rijen van a, je krijgt dan rowcount(a) * rowcount(b) aantal rijen, met data die niet aan elkaar gerelateerd is. M.a.w.: onzindata.

Yup klopt, dat is een cartesisch product...

Wat jij wilt doet de carthesian product niet, jij krijgt nl ook veel onzin rijen terug. Als a 100.000 rijen heeft en b 100 krijg jij een rowset met 100.000 rijen en achter elke row van a zit de row uit b met id=9. How nice.

Ja ik weet dat je dan een hoop "onzin" data krijgt, en onzin tussen aanhalings tekens, want misschien is het wel gewenst... Het gaat domweg om het voorbeeld van een cartesisch product, ofwel een cross-join. Dus die onzin data wil ik ook...

[whoami]

Verwijderd schreef op 09 April 2003 @ 14:32:
[...]

Zeg, nogmaals, ik geef alleen maar aan dat het voorkomt, ik wil dit soort dingen niet.

Waar komt dat dan voor? Ik heb het nog nooit gezien.
(Behalve in mijn geval dat ik eerder aanhaalde, maar dat was iets bewust:

code:

select naam, adres
from   klant, dummy
where klant.id = 1
AND dummy.id >= 1 and dummy.id <= 10

dan krijg ik 10x dezelfde klantgegevens.)

[Verwijderd]

Nou is even wat fantasie gebruiken,
ik heb een tabel met vormen en een tabel met kleuren, en ik wil alle mogelijke paren laten zien. Of bijvoorbeeld alleen alle rode paren, dan kun je crossjoinen. Er moet dus expliciet geen directe relatie bestaan tussen de tabellen...

[EfBe]

Verwijderd schreef op 09 april 2003 @ 14:44:
Nou is even wat fantasie gebruiken,
ik heb een tabel met vormen en een tabel met kleuren, en ik wil alle mogelijke paren laten zien. Of bijvoorbeeld alleen alle rode paren, dan kun je crossjoinen. Er moet dus expliciet geen directe relatie bestaan tussen de tabellen...

Volgens mij is joinen zonder relatie een contradictie in terminus.

Als jij gui-related logica nodig hebt om een join op database niveau te gebruiken zijn we snel klaar . Jouw voorbeeld kan ook met 2 queries op de tables en die in een loop in de client afbeelden. Is waarschijnlijk nog sneller ook.

[EfBe]

Verwijderd schreef op 09 april 2003 @ 14:38:
[...]
Yup klopt, dat is een cartesisch product...

Gee..

[...]
Ja ik weet dat je dan een hoop "onzin" data krijgt, en onzin tussen aanhalings tekens, want misschien is het wel gewenst... Het gaat domweg om het voorbeeld van een cartesisch product, ofwel een cross-join. Dus die onzin data wil ik ook...

Wat is er mis met 2 queries op die 2 aparte tabellen? Immers: je kunt wel joinen maar dat is semantisch gezien onzinnig: er bestaat geen relatie tussen de rows, je gaat wel rows aan elkaar plakken. De interpretatie van de rows valt dan uiteen in 2 delen: interpretatie van columnsubset A en columnsubset B.

[Verwijderd]

ik denk dat je fantasie genoeg hebt om zelf een situatie te kunnen bedenken waar het wel handig is. Ik ga alleen in de baas zijn tijd daar niet naar opzoek...

[EfBe]

Verwijderd schreef op 09 April 2003 @ 14:57:
ik denk dat je fantasie genoeg hebt om zelf een situatie te kunnen bedenken waar het wel handig is.

Heh Nou dat is het probleem, mijn fantasie was / is niet toereikend genoeg vandaar de vraag.