Joins (MySQL)
Mijn eerste join
select tbl_temp.ean, tbl_root.ean, tbl_root.sku from tbl_root join tbl_temp on tbl_temp.ean = tbl_root.EAN
Dubbele sleutel
En zonder woud van haakjes!
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
Uitbreiden van een join met een tweede join, lijkt triviaal te zijn. De laatste join lijkt zelfs niet gerelateerd te hoeven zijn aan de eerste tabel:
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;
Carthesiaans product - Cross join
In dit voorbeeld worden alle advertenties gecombineerd met alle zoektermen:
create table tbl_zoekterm_tmp select "Kohlebuersten" 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
Voorbeeld: Records die niet lukten
Ik heb mbv. 'ignore' een tabel gevuld. 64 Records werden overgeslagen ivm. een constraint violation. Nu wil ik weten welke records dat zijn. Dus zoiets als:
Welke 64 records zitten wél in tabel B, maar niet in tabel A?
Oplossing in pseudo-code
In de afbeelding aan het begin van dit artikel, is dit de situatie rechts-in-het-midden:
select <...> from table_a right join table_b on a.key = b.key where a.key is null
Implementatie
# * brush_tool - 32.931 records # * brush_tool_tmp - 35.901 records # * Verschil: 2.700 records → Die wil ik selecteren! select brush_tool_tmp.* from brush_tool_tmp left join brush_tool on brush_tool_tmp.tool_id = brush_tool.tool_id where brush_tool.tool_id is null
Compleet voorbeeld
- In tabel tmp1 zitten records a, b, c & d
- In tabel tmp2 zitten records c, d, e & f
- Ik wil weten welke records in tmp1 zitten, die ontbreken in tmp2
- Verwachte antwoord: a & b.
Code:
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"),("e"),("f"); select * from tmp1; select * from tmp2; select tmp1.*, tmp2.* from tmp1 left join tmp2 on tmp1.veld1 = tmp2.veld2 where tmp2.veld2 is null
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.
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