(SQL) mening: join of subselect?

[Verwijderd]

Ik wil graag jullie mening weten over het volgende.:

Ik heb bv. twee simpele tabellen

code:

tabel1:              tabel2:
+-------+-------+    +-------+--------------+
|id     |product|    |product|omschrijving  |
+-------+-------+    +-------+--------------+
|0      |X      |    |X      |X is een nieuw|
+-------+-------+    |       |product.      |
                     +-------+--------------+

Beide tabellen moeten gekoppeld worden om de gewenste resultset te krijgen:

code:

+-------+-------+--------------+
|id     |product|omschrijving  |
+-------+-------+--------------+
|0      |X      |X is een nieuw|
|       |       |product.      |
+-------+-------+--------------+

Dit kan met een left join:

code:

select
 tabel1.id,
 tabel1.product,
 tabel2.omschrijving
from
 tabel1
left join tabel2
 on tabel2.product = tabel1.product

of een subselect:

code:

select
 id,
 product,
 (select omschrijving from tabel2 where product = tabel1.product)
from
 tabel1

Met een subselect kan je maar één veld uit tabel2 benaderen terwijl je met een join beschikking hebt over alle velden uit tabel2.
Voordeel van een subselect vind ik wel weer dat je geen lelijke join namen hebt (join1, join2 etc..) als je meerdere keren dezelfde tabel moet joinen met verschillende criteria.
Met het testen op een grote resultset blijkt overigens een subselect sneller te zijn dan een join.

Wat is jullie mening?
Zouden jullie voor het ophalen van een veld een join of een subselect gebruiken?

[dominic]

Als dit de definitieve query is, dus je wil niet nog meer kolommen erbij betrekken, meet dan simpelweg de performance van join vs. subselect met een aantal verschillende rowcounts.

Mocht je in de toekomst nog meer kolommen nodig hebben uit tabel2, ga dan voor een join. Nadat de query geparsed en geoptimaliseerd is door SQL maakt het verschil tussen een join en subselect veelal niet veel mer uit qua performance.

[ Voor 21% gewijzigd door dominic op 11-01-2009 13:28 ]

[Fish]

ik zou de left join doen. als je de omschrijving bent vergeten heb heb je teminste resultaat.
i stand corected

[ Voor 11% gewijzigd door Fish op 11-01-2009 14:04 ]

[ValHallASW]

Verwijderd schreef op zondag 11 januari 2009 @ 13:22:
Met het testen op een grote resultset blijkt overigens een subselect sneller te zijn dan een join.

Doe dan eens een explain op beide queries? Als je een join geeft dan heeft je database alle vrijheid om de queries in te delen - ik zou dan ook verwachten dat het eerder sneller gaat.

-edit-: ik heb het even uitgeprobeerd op twee grote tabellen met een 1-op-1 relatie: de join en de subquery doen volgens mij hetzelfde, aangezien ze van hetzelfde gecachte resultaat gebruik maken. De explains zijn deze:

SQL:

mysql> explain select *, (select user_id from user_ids where user_name=user.user_name) from user limit 1000;
+----+--------------------+-------+--------+---------------+-----------+---------+------+--------+--------------------------+
| id | select_type        | table | type   | possible_keys | key       | key_len | ref  | rows   | Extra                    |
+----+--------------------+-------+--------+---------------+-----------+---------+------+--------+--------------------------+
|  1 | PRIMARY            | user  | ALL    | NULL          | NULL      | NULL    | NULL | 208854 |                          |
|  2 | DEPENDENT SUBQUERY | user  | eq_ref | user_name     | user_name | 257     | func |      1 | Using where; Using index |
+----+--------------------+-------+--------+---------------+-----------+---------+------+--------+--------------------------+
2 rows in set (0.00 sec)

mysql> explain select * from user left join user_ids on user.user_name = user_ids.user_name limit 1000;
+----+-------------+-------+--------+---------------+-----------+---------+------+--------+-------------+
| id | select_type | table | type   | possible_keys | key       | key_len | ref  | rows   | Extra       |
+----+-------------+-------+--------+---------------+-----------+---------+------+--------+-------------+
|  1 | SIMPLE      | user  | ALL    | NULL          | NULL      | NULL    | NULL | 208854 |             |
|  1 | SIMPLE      | user  | eq_ref | user_name     | user_name | 257     | func |      1 | Using index |
+----+-------------+-------+--------+---------------+-----------+---------+------+--------+-------------+
2 rows in set (0.00 sec)

Wat qua uitvoering op hetzelfde neerkomt.

Overigens doen de queries ook niet hetzelfde. Met de volgende data:

SQL:

mysql> select * from tabel1;
+------+---------+
| id   | product |
+------+---------+
|    0 | X       |
|    0 | Y       |
|    0 | Z       |
+------+---------+
3 rows in set (0.00 sec)

mysql> select * from tabel2;
+---------+--------+
| product | beschr |
+---------+--------+
| X       | bla    |
| X       | bla2   |
| Y       | boe    |
+---------+--------+
3 rows in set (0.00 sec)

krijg je deze resultaten:

SQL:

mysql> select * from tabel1 left join tabel2 on tabel1.product=tabel2.product;
+------+---------+---------+--------+
| id   | product | product | beschr |
+------+---------+---------+--------+
|    0 | X       | X       | bla    |
|    0 | X       | X       | bla2   |
|    0 | Y       | Y       | boe    |
|    0 | Z       | NULL    | NULL   |
+------+---------+---------+--------+
4 rows in set (0.02 sec)

mysql> select *, (select beschr from tabel2 where product=tabel2.product) from tabel1;
ERROR 1242 (21000): Subquery returns more than 1 row

Als je dan één van de X-beschrijvingen weglaat krijg je:

code:

mysql> select *, (select beschr from tabel2 where product=tabel1.product) from tabel1;
+------+---------+----------------------------------------------------------+
| id   | product | (select beschr from tabel2 where product=tabel1.product) |
+------+---------+----------------------------------------------------------+
|    0 | X       | bla2                                                     |
|    0 | Y       | boe                                                      |
|    0 | Z       | NULL                                                     |
+------+---------+----------------------------------------------------------+
3 rows in set (0.00 sec)

Je krijgt dus, itt wat fish beweert, wél NULL-resultaten als er geen beschrijving is, maar meerdere beschrijvingen zijn niet mogelijk. Het is uit je verhaal niet duidelijk of het een 1-to-many-relatie zou moeten zijn of een 1-op-1, maar als het een 1-op-1-relatie moet zijn dan kan je ook overwegen om de gegevens gewoon in één tabel te zetten.

[ Voor 27% gewijzigd door ValHallASW op 11-01-2009 14:08 ]

[RobIII]

Verwijderd schreef op zondag 11 januari 2009 @ 13:22:
Dit kan met een left join:

Heb je een reden voor die left join? Waarom niet gewoon een inner join? Ik haal niet uit je verhaal waarom het een left join zou moeten zijn. Al eens gekeken naar Hoe werken joins?

Verwijderd schreef op zondag 11 januari 2009 @ 13:22:
Voordeel van een subselect vind ik wel weer dat je geen lelijke join namen hebt (join1, join2 etc..) als je meerdere keren dezelfde tabel moet joinen met verschillende criteria.

Niets lelijks aan, je kunt gewoon aliassen met een zinnige naam. "join1" zegt geen drol over de join, kies een naam die de lading dekt.

[ Voor 12% gewijzigd door RobIII op 11-01-2009 13:56 ]

[Verwijderd]

Ik weet dat dit niet kan als er meerdere rows zijn. Het gaat dan ook om 1-op-1 relaties.
Ook weet ik hoe joins werken, Rob.

Bovenstaand is een voorbeeld, het gaat mij meer om de discussie voor het gebruiken van subselects of joins.

Zijn jullie voorstander van één van beide methodes en waarom?

[Cousin Boneless]

Volgens mij is het gebruik van een join onderhoudstechnisch beter. Heb je een extra veld nodig, dan selecteer je er een extra veld bij. Als je je subselect oplossing aan een junior programmeur geeft, heb je kans dat je je query over een tijdje terug ziet met een hele reeks subselects omdat jij die weg bent ingeslagen. En (zoals al gezegd) gebruik aliases voor je tabellen om het duidelijk te houden.
Die oplossing van de subselect zou ik alleen gebruiken in combinatie met aggregate functies. En dan nog zou ik nog eerder een subquery gebruiken dan een subselect. Dat misschien wel omdat de subselect als bijeffect een TOP 1 uitvoert en ik graag aan de query wil kunnen zien wat distinct geselecteerd moet worden.

Edit: als een subselect meerdere records teruggeeft krijg ik in mysql een error om m'n oren: "Subquery returns more than 1 row". Dat kan ook een overweging zijn om het wel/niet te gebruiken.

[ Voor 12% gewijzigd door Cousin Boneless op 11-01-2009 18:45 ]

[DJF5]

Daarom gebruik ik bij een subselect ook vaan TOP 1 (in T-SQL dan). Bij mysql is dit volgens mij een LIMIT 1

Als ik van een bepaalde tabel een enkel gegeven nodig heb voor een berekening gebruik ik een subquery. Mocht ik twee tabellen willen 'mergen' tot een resultset gebruik ik joins.

[ACM]

Als de data die nu via een subselect wordt opgehaald heel duur is om bij de normale select in te trekken middels een join - bijvoorbeeld omdat de dataset sorteren of pagineren dan ineens veel zwaarder is -, dan zou ik voor je subselect-constructie gaan. In alle andere gevallen zou ik voor de betere leesbaarheid en onderhoudbaarheid van een query met joins gaan.
Oftewel, imho alleen dit soort trucken uithalen als het voor je performance echt flink scheelt.

[EfBe]

In deze specifieke situatie is de join beter, want de query is efficienter.

In andere gevallen kan een subquery weer veel efficienter zijn, bv een WHERE f IN (subquery) ipv een join met een where (die bv duplicates oplevert).

M.a.w.: de vraag: het een of het ander is dus aan de context gebonden waarin je ze gebruiken wilt en dus eigenlijk geen algemene discussie. Een 'mening' hebben over iets wat empirisch kan worden aangetoond als zijnde meer/minder efficient lijkt me verder ook overbodig: ze zijn niet equivalent aan elkaar.

[Spiral]

DJF5 schreef op zondag 11 januari 2009 @ 23:32:
Daarom gebruik ik bij een subselect ook vaan TOP 1 (in T-SQL dan). Bij mysql is dit volgens mij een LIMIT 1

Als ik van een bepaalde tabel een enkel gegeven nodig heb voor een berekening gebruik ik een subquery. Mocht ik twee tabellen willen 'mergen' tot een resultset gebruik ik joins.

Dit vind ik echt niet kunnen als je constante resultaten terug wilt hebben. Als je iets willekeurigs terug wilt krijgen dan zou dit een noodgreep kunnen zijn, maar op deze manier kan je data terugkrijgen waarop je helemaal geen grip op hebt. De ene keer zou het 'blaat' kunnen zijn en de andere keer id van 'foo'

[Janoz]

Wat is jullie mening?

Eigenlijk heb ik over je gestelde case twee meningen. In de eerste plaats vind ik dat je een 1 op 1 relatie eigenlijk nooit in je database zou moeten hebben. Wat is het nut van het verspreiden van deze gegevens over twee tabellen? Waarom zou je niet gewoon een tabel maken met id,product,omschrijving?

Let daarbij wel op dat er een verschil is tussen een 1 op 1 relatie en een 1 op meer relatie waarbij de meer beperkt is tot de range [0..1], maar dan nog zou ik dat eerder modeleren met een of meer kolommen die null mogen zijn.

Ten tweede vraag ik me af waarom je joint op product. Daarvoor zou ik gewoon product ID gebruiken.

[flat]

ValHallASW schreef op zondag 11 januari 2009 @ 13:55:

SQL:

mysql> explain select *, (select user_id from user_ids where user_name=user.user_name) from user limit 1000;
+----+--------------------+-------+--------+---------------+-----------+---------+------+--------+--------------------------+
| id | select_type        | table | type   | possible_keys | key       | key_len | ref  | rows   | Extra                    |
+----+--------------------+-------+--------+---------------+-----------+---------+------+--------+--------------------------+
|  1 | PRIMARY            | user  | ALL    | NULL          | NULL      | NULL    | NULL | 208854 |                          |
|  2 | DEPENDENT SUBQUERY | user  | eq_ref | user_name     | user_name | 257     | func |      1 | Using where; Using index |
+----+--------------------+-------+--------+---------------+-----------+---------+------+--------+--------------------------+
2 rows in set (0.00 sec)

mysql> explain select * from user left join user_ids on user.user_name = user_ids.user_name limit 1000;
+----+-------------+-------+--------+---------------+-----------+---------+------+--------+-------------+
| id | select_type | table | type   | possible_keys | key       | key_len | ref  | rows   | Extra       |
+----+-------------+-------+--------+---------------+-----------+---------+------+--------+-------------+
|  1 | SIMPLE      | user  | ALL    | NULL          | NULL      | NULL    | NULL | 208854 |             |
|  1 | SIMPLE      | user  | eq_ref | user_name     | user_name | 257     | func |      1 | Using index |
+----+-------------+-------+--------+---------------+-----------+---------+------+--------+-------------+
2 rows in set (0.00 sec)

Wat qua uitvoering op hetzelfde neerkomt.

Volgens mij betekent "dependent subquery" dat die subquery voor iedere waarde van "user.user_name" opnieuw uitgevoerd wordt. Zeker niet hetzelfde dus.

EfBe schreef op maandag 12 januari 2009 @ 09:01:
In deze specifieke situatie is de join beter, want de query is efficienter.

Waarom is de meest efficiente query de beste? Leesbaarheid en onderhoudbaarheid is mij in de meeste gevallen meer waard dan 0.2 milliseconde korting op de executietijd.

(Overigens is de query met join in dit specifieke geval wat mij betreft op alle drie de punten beter.)

[EfBe]

flat schreef op maandag 12 januari 2009 @ 11:35:
[...]
Waarom is de meest efficiente query de beste? Leesbaarheid en onderhoudbaarheid is mij in de meeste gevallen meer waard dan 0.2 milliseconde korting op de executietijd.

0.2 milliseconde op een query die 1 keer per dag draait, nee dat geeft idd niks. Echter is een subquery gebruiken in de projectie echt vele malen trager dan een join bij grotere sets en merk je het wel degelijk. Verder is in SQL weinig echt complex op te schrijven, het zijn tenslotte gewoon set operaties, dus of het ene of het andere minder leesbaar is hangt af van de mate van kennis van SQL en set operaties van de lezer, en is dus irrelevant.