Joins (MySQL)

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

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

Casus

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 -- Als je alleen "*" doet zonder tabelnaam, krijg je een Cartesiaans product
left join brush_tool
on brush_tool_tmp.tool_id = brush_tool.tool_id
where brush_tool.tool_id is null

Niet de records die juist wél overeenkomen

Misschien is dit een variant van het probleem hierboven, of misschien verwar ik mezelf:

Casus

Ik wil 100.000 producten uploaden. Maar van een paar duizend producten, is er geen afbeelding. Ik heb een tabel met een lijst van producten waarvan er geen afbeelding is. Dus zoiets als:

Welke producten zitten in tabel A, maar niet in tabel B?

Yep: Dit is hetzelfde als het probleem hierboven, maar met A & B en left & right omgedraaid ofzo. Boeit niet, gewoon doorgaan:

Oplossing in pseudo-code

select <...>
from table_a
left join table_b on a.key = b.key
where b.key is null

Implementatie - Compleet 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"),("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.