Joins (MySQL)

Uit De Vliegende Brigade
Naar navigatie springen Naar zoeken springen
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
A join is a join is a join - LET OP: Er zijn nog meer soorten joins, zoals cross join, aka Cartesiaans product

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:

  1. Begin met de 'complete' tabel en join die met de 'incomplete' tabel
  2. Gebruik een left join zodat alle records van de 'complete' tabel worden geïncorporeerd
  3. 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 voor tmp2
  • 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

Overgenomen van "https://wiki.devliegendebrigade.nl/index.php?title=Joins_(MySQL)&oldid=25523"