Joins (MySQL)
Mijn eerste join
22 september 2015:
select tbl_temp.ean, tbl_root.ean, tbl_root.sku from tbl_root join tbl_temp on tbl_temp.ean = tbl_root.ean;
In 2020 leerde ik dat dit compacter kon:
select tbl_temp.ean, tbl_root.ean, tbl_root.sku from tbl_root join tbl_temp using (ean);
Dubbele sleutel
Van ca. 1997 tot en met ca. 2015, werkte ik vel met Microsoft Access. Queries waren daar meestal verscholen achter een QBE-window. De enkele keer dat ik toch met SQL te maken had, viel het me op wat een wout aan haakjes het was. Godzijdank is dat niet het geval in fatsoenlijke SQL:
select powertools_es.powertool, tbl_powertools_brand_kind_type.Kind02_Uppercase from tbl_powertools_brand_kind_type join powertools_es on tbl_powertools_brand_kind_type.Brand = powertools_es.brand and tbl_powertools_brand_kind_type.Type = powertools_es.type limit 0,10000
Multiple joins
Het is triviaal om een join uit te breiden met additionele joins. En een ander opvallend verschil met multiple joins in Microsoft Access: Geen foutmeldingen omtrent causualiteit:
select node.nid, node.title, uc_products.model, uc_product_beterenamen.title from node inner join uc_products on node.nid = uc_products.nid inner join uc_product_beterenamen on uc_products.model = uc_product_beterenamen.sku;
En deze query is vrij eenvoudig om te fietsen naar een update-query:
update node inner join uc_products on node.nid = uc_products.nid inner join uc_product_beterenamen on uc_products.model = uc_product_beterenamen.sku set node.title = uc_product_beterenamen.title;
Joins & OR
In maart 2021 merkte ik tot m'n verbazing, dat je OR
kunt gebruiken om meerdere criteria in een join te formuleren. Ik was nog meer verbaasd dat ik al zes jaar redelijk intensief in SQL werk, zonder dit te kennen. Merk ook op, dat de match in de joins, gebruik maakt van like
en wildcards. Dat was niet nieuw voor me, maar het blijft me verbazen hoe flexibel dat is (zal wel niet goed zijn voor de performance, sois):
update tmp10 join translation on tmp10.meta_value_src like concat("%", translation.nl_org, "%") or tmp10.meta_value_src like concat("%", translation.nl_org2, "%") set tmp10.string_src = nl_org;
Using
Er is een manier om joins iets compacter te schrijven, vermits de sleutel in beide tabellen identiek is. Voorbeeld:
update feed join product_spc_202011 using (sku) set feed.sku_oem = product_spc_202011.sku_oem, feed_product_title = product_spc_202011.product_title;
Let op de (haakjes) achter using
- Vreemde syntaxis, als je het mij vraagt.
Carthesiaans product - Cross join
In dit voorbeeld worden alle advertenties gecombineerd met alle zoektermen:
create table tbl_zoekterm_tmp select "Wdigets" as "account", "Merken" as "campaign", merk02 as "adgroup", concat(zoekterm," ",merk02) as "zoekterm", " " as modifier, null as memo from tbl_zoekterm cross join tbl_merknaam;
Carthesiaans product - Onbedoeld
Meestal krijg ik carthesiaanse producten onbedoeld. Hier is een datawarehouse-voorbeeld
select --- from root left join tabela on root.sku = tablea.sku left join tableb on root.sku = tableb.sku left join tabelc on root.sku = tablec.sku
Als bv. root en tabela een 1-op-veel-relatie hebben, dan krijg je een cartetisch product, ook al staat er nog tien keer left join.
Left joins & precedence
Het maakt uit of je in het ON-gedeelte het ene veld eerst doet, of het andere veld eerst. Deze twee queries geven verschillende resultaten:
Query 1
-- ================================================================ -- Verbeterde ProductType-query -- ================================================================ -- * Meeste inner joins vervangen door left joins -- * 3.304 records. Moet 3.242 zijn -- SELECT DISTINCT -- -- n.nid AS nid, -- n.type AS type, -- n.title AS title, -- n.created AS created, -- n.changed AS changed, -- n.status AS status, -- ucp.model AS model, -- ucp.sell_price AS sell_price, -- url_alias.dst AS dst, -- node_revisions.teaser AS teaser, -- node_revisions.body AS body, -- files.filename AS filename, -- n.uid AS uid -- -- FROM node n -- INNER JOIN uc_products ucp ON n.nid = ucp.nid AND n.vid = ucp.vid -- left join url_alias url_alias ON n.nid = url_alias.nid -- left join node_revisions node_revisions ON n.vid = node_revisions.vid -- left join content_field_image_cache content_field_image_cache ON content_field_image_cache.vid = n.vid -- left join files files ON files.fid = content_field_image_cache.field_image_cache_fid -- WHERE (n.type = 'product')
Query 2
-- ================================================================ -- Left outer joins → Left joins + volgorde ON-velden aangepast -- ================================================================ -- Aantal records: 3.242 (correct) -- SELECT DISTINCT n.nid AS nid, n.type AS type, n.title AS title, n.created AS created, n.changed AS changed, n.status AS status, -- ucp.model AS model, -- ucp.sell_price AS sell_price, -- url_alias.dst AS dst, -- node_revisions.teaser AS teaser, -- node_revisions.body AS body, -- files.filename AS filename, n.uid AS uid FROM node n left join uc_products ucp ON n.nid = ucp.nid AND n.vid = ucp.vid -- Ziet er ok uit LEFT JOIN url_alias url_alias ON n.nid = url_alias.nid LEFT JOIN node_revisions node_revisions ON n.vid = node_revisions.vid LEFT JOIN content_field_image_cache content_field_image_cache ON n.vid = content_field_image_cache.vid LEFT JOIN files files ON content_field_image_cache.field_image_cache_fid = files.fid -- Deze veroorzaakt het probleem WHERE (n.type = 'product');
Records die juist niet overeenkomen
Queries om records te vinden die juist niet overeenkomen, vind ik intuïtief vaak lastig. Maar het is echt geen zwarte magie:
- Begin met de 'complete' tabel en join die met de 'incomplete' tabel
- Gebruik een
left join
zodat alle records van de 'complete' tabel worden geïncorporeerd - Gebruik een
where
-clause om te matchen met lege records uit de 'incomplete' tabel.
Zoiets als dit:
select * from complete_tabel left join incomplete_tabel on complete_tabel.pk = incomplete_tabel.pk where incomplete_tabel.pk is null
Uitgewerkt voorbeeld:
Probleem
- In tabel
tmp1
zitten records a, b, c & d - In tabel
tmp2
zitten records c, d, e & f - Welke records zitten wel in tmp1 maar niet in tmp2?
- Verwachte antwoord: a & b.
Analyse
tmp1
is de 'complete tabel'tmp2
is de 'incomplete tabel'- Begin de query met
tmp1
- Gebruik een
left join
voortmp2
Where
-clause op tmp2.
Implementatie
drop table if exists tmp1; create table tmp1 ( pk tinytext ); insert into tmp1 values ("a"), ("b"), ("c"), ("d"); drop table if exists tmp2; create table tmp2 ( pk tinytext ); insert into tmp2 values ("c"), ("d"), ("e"), ("f"); select * from tmp1; select * from tmp2; select * from tmp1 left join tmp2 using (pk) where tmp2.pk is null
Resultaat
a
b
Join met LIMIT 1
Probleem
Ik heb twee tabellen, met een 1-op-n-relatie. Ik wil alle records uit de eerste tabel, en maximaal 1 record uit de tweede tabel. Als die tweede tabel echter meerdere records bevatten, krijg ik meerdere records in the join. Ook LEFT JOIN
is daar geen oplossing voor.
Voorbeeld:
drop table if exists tmp1; create table tmp1(veld1 tinytext); insert into tmp1 values ("a"),("b"),("c"),("d"); drop table if exists tmp2; create table tmp2 (veld2 tinytext); insert into tmp2 values ("c"),("d"),("d"),("d"); select tmp1.*, tmp2.* from tmp1 left join tmp2 on tmp1.veld1 = tmp2.veld2
Output:
c c d d d d d d a null b null
terwijl ik wil hebben:
c c d d a null b null
Oplossing
De oplossing is onthutsend simpel: Enkel een group by-toevoeging:
drop table if exists tmp1; create table tmp1(veld1 tinytext); insert into tmp1 values ("a"),("b"),("c"),("d"); drop table if exists tmp2; create table tmp2(veld2 tinytext); insert into tmp2 values ("c"),("d"),("d"),("d"); # select * from tmp1; # select * from tmp2; select tmp1.*, tmp2.* from tmp1 left join tmp2 on tmp1.veld1 = tmp2.veld2 group by tmp1.veld1
Nieuw probleem: Non-determinisme
In de echte wereld kreeg ik snel deze foutmelding:
Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column '...' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Wat-ie bedoeld: MySQL moet nu zelf kiezen welk record behouden wordt, en welke niet. Dat wil-ie niet.
Ongerelateerde tabellen matchen
Soms wil ik ongerelateerde tabellen matchen. Bv. om EAN-nummers toe te kennen aan nieuwe SKU's. Dat gaat echter niet zomaar. Zie Match ongerelateerde tabellen (MySQL) voor oplossingen.
Bronnen
- http://www.sitepoint.com/understanding-sql-joins-mysql-database/
- http://stackoverflow.com/questions/8974328/mysql-multiple-joins-in-one-query
- http://stackoverflow.com/questions/6879391/mysql-join-with-limit-1-on-joined-table
- http://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql