Subqueries (MySQL)
Je kunt queries nesten op een manier die soms heel intuïtief is, en op andere momenten juist helemaal niet.
Kenmerken:
- De subquery of inner query moet binnen haakjes staan
- Velden in de inner & outer query moeten unieke namen hebben
- De recordset die de inner query retourneert, moet vaak een eigen naam hebben: Anders wordt de expressie waarmee die subquery is gemaakt de naam, en dat gaat vrij snel mis
- Een suquery kan zowel een scalar (dus een enkele waarde), een vector (een kolom of misschien een rij) als een matrix (een recordset) retourneren.
Mijn eerste subquery
De subquery retourneert een kolom (eventueel een kolom van maar één rij):
update
tbl_root
set
NaamBrands =
(
select Brands
from tbl_SKUandBrands
where tbl_SKUandBrands.SKU = tbl_root.SKU
)
Overigens: Bij het schrijven van subqueries doe ik minder aan inspringen van code dan gebruikelijk. Anders wordt het snel lastig te lezen.
Voorbeeld: Select-query die een recordset retourneert
Deze query is weinig realistisch (verifiëer zelf maar!), maar laat wél zien, dat subqueries complete recordsets kunnen retourneren. Rond begin 2021 verkeerde ik in de veronderstelling, dat dat niet kon. Dit voorbeeld - van een paar jaar daarvoor alweer - laat zien dat geen probleem is.
select
ven,
pes
from
(
select
tbl_vertalingen.nl as vnl,
tbl_vertalingen.en as ven,
tbl_vertalingen.es as ves,
tbl_powertools_spaans.es as pes
from
tbl_vertalingen
join
tbl_powertools_spaans
on
tbl_vertalingen.en = tbl_powertools_spaans.en
)
as blub
Casus: Queries integreren
De achtergrond van deze casus weet ik niet meer, maar het stamt vermoedelijk uit ca. 2016.
We beginnen met drie losse queries, die we willen integreren in één query:
# Query 1 ######################################## # select 1, 2, 3; # Query 2 ######################################## # select max(5); # Query 3 ######################################## # select max(tid) from widget.term_data;
Met als uitvoer:
>> 1, 2, 3 >> 5 >> 98414
De eerste twee queries - of iets dat er op lijkt - zijn moeiteloos te integreren in één query
select 1, max(5)
Maar hoe integreren we de derde query hierin? Komt-ie:
# Gewoon invoegen? - Werkt niet
########################################
#
# select
# 1,
# max(5),
# select max(device_sku_oem_counter) from sales_spc;
# Invoegen binnen haakjes? - Dit werkt
########################################
#
select
1,
max(5),
(select max(device_sku_oem_counter) from sales_spc);
# En nu met een fatsoenlijke naam
########################################
#
select
1,
max(5),
(select max(device_sku_oem_counter) from sales_spc) as blub;
Je kunt zelfs met de uitkomst van de subquery rekenen:
select (select max(tid) from term_data)+1, 2, 3;
Dit lijkt echter niet te kunnen icm. INSERT INTO:
select (select max(tid) from term_data)+1, 2, 3; insert into term_data( (select max(tid) from term_data)+1, # Foutmelding 2, 3 )
Voorbeeld: Omdat het kan
Ik wil in een prepared statement alle null-waardes in alle kolommen in een tabel omzetten naar lege strings. Een prepared statement kan echter maar één statement bevatten (dus één ';'). Kan ik dat? En kan ik dat en passant compact en impliciet doen? Nou, wat betreft nesting:
In den beginne...
select concat
(
(
"Update amazon_sjabloon_export set "
)
,
(
select group_concat(column_name,"=ifnull(",column_name,",'')")
from information_schema.columns
where table_name="amazon_sjabloon_export"
order by ordinal_position asc
)
,
(
";"
)
) as uitdrukking;
CASE-statement binnen een select-query
CREATE DEFINER=`root`@`localhost` PROCEDURE `test_cond`(in markt tinytext)
BEGIN
select
"Hello, world!",
-- Subquery tussen haakjes, zoals altijd
(
select case
when markt="mx" then "9828313011"
when markt="ca" then "13553126011"
else "12345"
end
) as recommended_browse_nodes, -- De naam vd. subquery buiten de haakjes, zoals altijd. Optioneel
"Dag"; -- Niet vergeten: sprocs moeten altijd eindigen met ;
END
Afhankelijke geneste queries?
Kun je geneste queries hebben, waarbij de inner query afhankelijk is van de outer query? Een typisch voorbeeld: Weergeven van alle orders, met bijbehorende orderregels. Complicatie hierbij: Het aantal kolommen zou per inner query variëren, en dat lijkt me problematisch.
Voorbeeld om uit te werken: De outer query zijn de cijfers 1 tot en met 10. De inner query berekent het kwadraat van deze cijfers.
Casus: Geaggregeerde functie in een subquery?
Deze casus speelt in januari 2021. Dit is wat ik wil:
select distinct device_id, sku_oem, count(*) as counter from sku_b3_spc where counter>1 # Foutmelding group by device_id, sku_oem
Ik kan echter in de where-clause geen gebruik maken van een geaggregeerde functie. (where count(*)>1 werkt ook niet). Vermoedelijk omdat het resultaat van de geaggregeerde functie nog niet beschikbaar is.
Zou ik de where-clause in een 'outer query' kunnen plaatsen, en de rest binnen een subquery?
Bouw er een 'outer query' omheen
Bovenstaande query (behalve de where-clause) is gemakkelijk om te bouwen naar een subquery met een 'lege outer query':
select
*
from
(
select distinct
device_id,
sku_oem,
count(*) as counter
from
sku_b3_spc
group by
device_id,
sku_oem
) as blub;
Merk op:
- De subquery krijgt niet z'n eigen ";"
- De subquery moet een naam hebben ('blub'). Anders kreeg ik een foutmelding.
Dit kan ook een stap verder, waarbij je de afzonderlijke kolommen van de subquery benoemt, alsof je met een view werkt:
select
device_id as dinges_01,
sku_oem as dinges_02,
counter as dinges_03
from
(
select distinct
device_id,
sku_oem,
count(*) as counter
from
sku_b3_spc
group by
device_id,
sku_oem
) as blub;
Stop de where-clause in de 'outer query'
Dit lukt. Ik kan alleen niet de naam dinger_03 gebruiken. Ik moet de naam counter gebruiken:
select
device_id as dinges_01,
sku_oem as dinges_02,
counter as dinges_03
from
(
select distinct
device_id,
sku_oem,
count(*) as counter
from
sku_b3_spc
group by
device_id,
sku_oem
)
as
blub
where
counter>1;