Meer doen met SQL

SQL is een programeer taal op zich, alleen specifiek voor het manipuleren van databases en het ophalen van gegevens daaruit.

In een goed genormaliseerde database zijn query's zonder joins bijna ondenkbaar.
Het grootste gedeelte van deze tutorial zal dan ook joins behandelen.

Soms zijn er ook weleens subquery's nodig, group by met aggegrate funcies is handig om rapportages te generen, en dan in combinatie met condtional statements zijn de mogelijkheden nagenoeg onbeperkt.

Enjoy(n) ;-)

Join is het samenvoegen van 2 of meer tabellen tot één grote verzameling van kolommen.

Stel we hebben 3 tabellen:
tabel_a met de kolommen a_id, a_naam, a_iets
tabel_b met de kolommen b_id, a_id, b_naam, b_iets, b_nogwat
tabel_c met de kolommen c_id, a_id, c_naam, c_iets, c_nogwat

In de eerste stap worden tabel_a en tabel_b samengevoegd, (tussen)resultaat:
tabel_a.a_id
tabel_a.a_naam
tabel_a.a_iets
- - - - - - - - - - - - - - - -
tabel_b.b_id
tabel_b.a_id
tabel_b.b_naam
tabel_b.b_iets
tabel_b.b_nogwat

Dan wordt in de tweede stap tabel_c erbij gevoegd, resultaat:
tabel_a.a_id
tabel_a.a_naam
tabel_a.a_iets
tabel_b.b_id
tabel_b.a_id
tabel_b.b_naam
tabel_b.b_iets
tabel_b.b_nogwat
- - - - - - - - - - - - - - - -
tabel_c.c_id
tabel_c.a_id
tabel_c.c_naam
tabel_c.c_iets
tabel_c.c_nogwat

Zoals je ziet worden bij elke join de kolommen van de tabel die gejoind wordt aan de verzameling toegevoegd.
Ik heb door middel van de stippellijn aangeven wat door SQL als links (boven de stippelijn) en rechts (eronder) beschouwd wordt. Die stippellijn zal bij elke join dus opschuiven.

In theorie zijn er beperkingen op het aantal joins dat in een query toegepast kan worden (zo'n 60 in MySQL), maar in de praktijk zal je dit nooit halen (met een goed data model).

Al die kolommen kunnen in een query gebruikt worden; in de select lijst, in de join voorwaarden, in de where clause, kortom net zoals bij een query over één tabel.



Join syntax
Inner join (impliciet):

SELECT
	kolomlijst
FROM
	tabelnaam [[AS] alias1], tabelnaam[[AS] alias2]
WHERE
	alias1.idcol = alias2.idcol

Inner join (expliciet):

SELECT
	kolomlijst
FROM
	tabelnaam [[AS] alias1]
[INNER] JOIN tabelnaam [[AS] alias2]
	{ON alias1.idcol = alias2.idcol | USING (idcol)}

Outer join:

SELECT
	kolomlijst
FROM
	tabelnaam [[AS] alias1]
{LEFT|RIGHT|FULL} [OUTER] JOIN tabelnaam[[AS] alias2]
	{ON alias1.idcol = alias2.idcol | USING (idcol)}

Het keyword outer is optioneel, maar als je een outer join wilt toepassen je deze altijd vooraf moeten laten gaan door LEFT, RIGHT of FULL.
OUTER JOIN alleen zal een SQL foutmelding opleveren, en alleen JOIN zonder één van eerder genoemde keywords is automatisch een inner join.

Let op! MySQL kent geen FULL JOIN.

Cross join:

SELECT
	kolomlijst
FROM
	tabelnaam [[AS] alias1]
CROSS JOIN tabelnaam[[AS] alias2]

Opmerking

Ik heb voor de volledigheid ook een syntax voor een impliciete join aangegeven, en alleen bij een inner join omdat veel db systemen geen implicitie outer joins ondersteunen (en als dat wel zo is verschilt de notatie).
Ik gebruik ze ook nooit, en zal ze in deze tutorial ook niet verder behandelen.

De in de voorbeelden gebruikte tabellen

CREATE TABLE bedrijven (
    bedrijf_id INT(11) NOT NULL AUTO_INCREMENT,
    bedrijf_naam VARCHAR(50),
    PRIMARY KEY (bedrijf_id));
    
INSERT INTO bedrijven (bedr_naam)
VALUES ('List en Bedrog'), ('Models'), ('PHP Hulp'), ('CompuInc');

CREATE TABLE contacten (
	contact_id INT(11) NOT NULL AUTO_INCREMENT,
    	bedrijf_id INT(11),
    	voornaam VARCHAR(50),
	achternaam VARCHAR(50),
    	PRIMARY KEY (contact_id),
    	INDEX (bedrijf_id));

INSERT INTO contacten (bedrijf_id, voornaam, achternaam)
VALUES (1, 'John', 'Doe'), (2, 'Naomi', 'Campbell'), (NULL, 'Roger', 'Rabbit'), (2, 'Claudia', 'Shiffer'), (NULL, 'Albert', 'Einstein';

+ --------------- + -------------- +
| bedrijf_id      | bedr_naam      | 
+ --------------- + -------------- + 
| 1               | List en Bedrog |
| 2               | Models         |
| 3               | PHP Hulp       |
| 4               | CompuInc       |
+ --------------- + -------------- +

+ --------------- + --------------- + ------------- + --------------- +
| contact_id      | bedrijf_id      | voornaam      | achternaam      |
+ --------------- + --------------- + ------------- + --------------- + 
| 1               | 3               | John          | Doe             |
| 2               | 2               | Naomi         | Campbell        |
| 3               |                 | Roger         | Rabbit          |
| 4               | 2               | Claudia       | Shiffer         |
| 5               |                 | Albert        | Einstein        |
+ --------------- + --------------- + ------------- + --------------- +

Inner join
Met een inner join komen alleen die records in het resultaat die aan de join voorwaarden voldoen.

SELECT 
    b.bedrijf_id, b.bedr_naam, c.contact_id, c.voornaam, c.achternaam
FROM
    bedrijven AS b
INNER JOIN
    contacten AS c ON c.bedrijf_id = b.bedrijf_id;

+ --------------- + -------------- + --------------- + ------------- + --------------- +
| bedrijf_id      | bedr_naam      | contact_id      | voornaam      | achternaam      |
+ --------------- + -------------- + --------------- + ------------- + --------------- +
| 2               | Models         | 2               | Naomi         | Campbell        |
| 2               | Models         | 4               | Claudia       | Shiffer         |
| 3               | PHP Hulp       | 1               | John          | Doe             |
+ --------------- + -------------- + --------------- + ------------- + --------------- +

Je ziet dat in bovenstaand resultaat bedrijf 1 en 4 ontbreken
In dit voorbeeld worden alleen de bedrijven geselecteerd waarvan het id ook in contactpersonen voorkomt.

Left join

SELECT 
    	b.bedrijf_id, b.bedr_naam, c.contact_id, c.voornaam, c.achternaam
FROM
	bedrijven AS b
LEFT JOIN	
	contacten AS c USING (bedrijf_id);
->
+ --------------- + -------------- + --------------- + ------------- + --------------- +
| bedrijf_id      | bedr_naam      | contact_id      | voornaam      | achternaam      |
+ --------------- + -------------- + --------------- + ------------- + --------------- +
| 1               | List en Bedrog |                 |               |                 |
| 2               | Models         | 2               | Naomi         | Campbell        |
| 2               | Models         | 4               | Claudia       | Shiffer         |
| 3               | PHP Hulp       | 1               | John          | Doe             |
| 4               | CompuInc       |                 |               |                 |
+ --------------- + -------------- + --------------- + ------------- + --------------- + 

We hebben nu in het resultaat ook de bedrijven waarvan het id niet in contactpersonen voorkomt, alleen hebben de kolommen uit contactpersonen dan geen waarde (NULL)

Right join
De right join werkt precies omgekeerd tov van een left join:

SELECT 
    	b.bedrijf_id, b.bedr_naam, c.contact_id, c.voornaam, c.achternaam
FROM
	bedrijven AS b
RIGHT JOIN	
	contacten AS c USING (bedrijf_id)
->


+ --------------- + -------------- + --------------- + ------------- + --------------- +
| bedrijf_id      | bedr_naam      | contact_id      | voornaam      | achternaam      |
+ --------------- + -------------- + --------------- + ------------- + --------------- +
| 3               | PHP Hulp       | 1               | John          | Doe             |
| 2               | Models         | 2               | Naomi         | Campbell        |
|                 |                | 3               | Roger         | Rabbit          |
| 2               | Models         | 4               | Claudia       | Shiffer         |
|                 |                | 5               | Albert        | Einstein        |
+ --------------- + -------------- + --------------- + ------------- + --------------- +

We hebben nu alle contactpersonen personen in het resultaat. maar niet alle bedrijven.

FULL JOIN
Een FULL JOIN is combinatie van een LEFT JOIN en een RIGHT JOIN, je gebruikt dit als je alle records uit de linkertabel(len) ne rechtertabel bij elkaar wilt hebben.

SQL Server, PostgreSQL, DB2, Oracle etc:

SELECT 
    	b.bedrijf_id, b.bedr_naam, c.contact_id, c.voornaam, c.achternaam
FROM
	bedrijven AS b
FULL JOIN	
	contacten AS c USING (bedrijf_id)

Zoals eerder gezegd kent MySQL geen FULL JOIN, maar we kunnen dat simuleren door zelf die combinatie te maken:

SELECT 
    	b.bedrijf_id, b.bedr_naam, c.contact_id, c.voornaam, c.achternaam
FROM
	bedrijven AS b
LEFT JOIN	
	contacten AS c USING (bedrijf_id)
UNION
SELECT 
    	b.bedrijf_id, b.bedr_naam, c.contact_id, c.voornaam, c.achternaam
FROM
	bedrijven AS b
RIGHT JOIN	
	contacten AS c USING (bedrijf_id)


ORDER BY
    	bedr_naam, achternaam
->
+ --------------- + -------------- + --------------- + ------------- + --------------- +
| bedrijf_id      | bedr_naam      | contact_id      | voornaam      | achternaam      |
+ --------------- + -------------- + --------------- + ------------- + --------------- +
|                 |                | 5               | Albert        | Einstein        |
|                 |                | 3               | Roger         | Rabbit          |
| 4               | CompuInc       |                 |               |                 |
| 1               | List en Bedrog |                 |               |                 |
| 2               | Models         | 2               | Naomi         | Campbell        |
| 2               | Models         | 4               | Claudia       | Shiffer         |
| 3               | PHP Hulp       | 1               | John          | Doe             |
+ --------------- + -------------- + --------------- + ------------- + --------------- +

CROSS JOIN
Een cross join selecteert alle records van de linkertabel(len) en voegt per record alle records van de rechtertabel aan toe.

SELECT 
    	*
FROM
	bedrijven AS b
CROSS JOIN	
	contacten as C
->

+ --------------- + -------------- + --------------- + --------------- + ------------- + --------------- +
| bedrijf_id      | bedr_naam      | contact_id      | bedrijf_id      | voornaam      | achternaam      |
+ --------------- + -------------- + --------------- + --------------- + ------------- + --------------- +
| 1               | List en Bedrog | 1               | 3               | John          | Doe             |
| 2               | Models         | 1               | 3               | John          | Doe             |
| 3               | PHP Hulp       | 1               | 3               | John          | Doe             |
| 4               | CompuInc       | 1               | 3               | John          | Doe             |
| 1               | List en Bedrog | 2               | 2               | Naomi         | Campbell        |
| 2               | Models         | 2               | 2               | Naomi         | Campbell        |
| 3               | PHP Hulp       | 2               | 2               | Naomi         | Campbell        |
| 4               | CompuInc       | 2               | 2               | Naomi         | Campbell        |
| 1               | List en Bedrog | 3               |                 | Roger         | Rabbit          |
| 2               | Models         | 3               |                 | Roger         | Rabbit          |
| 3               | PHP Hulp       | 3               |                 | Roger         | Rabbit          |
| 4               | CompuInc       | 3               |                 | Roger         | Rabbit          |
| 1               | List en Bedrog | 4               | 2               | Claudia       | Shiffer         |
| 2               | Models         | 4               | 2               | Claudia       | Shiffer         |
| 3               | PHP Hulp       | 4               | 2               | Claudia       | Shiffer         |
| 4               | CompuInc       | 4               | 2               | Claudia       | Shiffer         |
| 1               | List en Bedrog | 5               |                 | Albert        | Einstein        |
| 2               | Models         | 5               |                 | Albert        | Einstein        |
| 3               | PHP Hulp       | 5               |                 | Albert        | Einstein        |
| 4               | CompuInc       | 5               |                 | Albert        | Einstein        |
+ --------------- + -------------- + --------------- + --------------- + ------------- + --------------- +

Het aantal records in het resultaat is dus het product van het aantal records van beide tabellen (in dit geval 20).
In deze context weinig zinvol, verder op een voorbeeld waarvoor het wel handig kan zijn.


NATURAL JOIN
Een natural join kijkt naar de kolomnamen van de tabellen, zijn die gelijk dan worden die kolommen opgenomen in de join voorwaarden.
In plaats van JOIN ... USING(koloma, kolomb) kan je dus NATURAL JOIN ... gebruiken.
Niet mijn persoonlijke voorkeur, met USING zie je tenminste waar je mee bezig bent.

Combinatie LEFT en INNER JOIN
Als aan één tabel meerdere tabellen gejoined worden dan mogen INNER en LEFT JOIN door elkaar gebruikt worden:

SELECT
	a.a_naam,
	b.b_naam,
	c.c_naam,
	d.d_naam
FROM
	tabelA a
INNER JOIN
	tabelB b
	ON a.a_id = b.a_id
LEFT JOIN
	tabelC c
	ON a.a_id = c.a_id
INNER JOIN
	tabelD d
	ON a.a_id = d.a_id

Waar de LEFT JOIN ook staat, het eindresultaat blijft hetzelfde, namelijk de records waarvan de a_id zowel in a,b en d voorkomen en de records waarvan de a_id eventueel in tabel c voorkomt.

Er zijn echter situaties waarin je wel alle records van tabel A wilt hebben, maar alleen de records van tabel B die in tabel C ook een gegeven hebben.
Bijvoorbeeld, bij de bedrijven en contactpersonen tabellen uit het vorige hoofdstuk hebben een derde tabel met de (privé)adresgegevens van de contactpersonen (indien bekend). We moeten dan met een nested join gaan werken, dat zie er zo uit:

SELECT
	b.bedr_naam,
	c.voornaam,
	a.adres
FROM
	bedrijven AS b
LEFT JOIN
	(
	contactpersonen AS c
	INNER JOIN
		contactadressen AS a
		ON c.contact_id = a.contact_id
	)
	ON b.bedr_id = c.bedr_id

Dit hadden we in de eerdere query ook zo kunnen doen.

Een subquery (het woord zegt het al) is een query binnen een query.
Een subquery is altijd een SELECT statement, staat altijd tussen haakjes en kan vrijwel overal in een query worden toegepast:

- Tussen de SELECT en de FROM

SELECT
	COUNT(some_id) AS total,
	(SELECT COUNT(some_id) FROM t1 WHERE active=1) AS active,
	(SELECT COUNT(some_id) FROM t1 WHERE active=0) AS inactive
FROM
	t1

- In de FROM clause
Met een net woord heet dit een derived table maar iedereen noemt dit een inline view:

SELECT c.comp_id,
	c.comp_name,
	co.firstname,
	co.lastname
FROM
	(SELECT comp_id,
		comp_name
	FROM
		companies
	ORDER BY
		comp_name
	LIMIT 0, 5) AS c
LEFT JOIN
	contacts AS co USING (comp_id)

Het alias is hier vereist.

- Als vergelijkings waarde

SELECT matchdate,
	matchtime,
	hometeam,
	awayteam
FROM
	matches2012
WHERE
	matchdate = (SELECT MIN(matchdate)
			FROM
				matches2012
			WHERE
				matchdate >= DATE(NOW()))

- Als schaalbare waarde

INSERT INTO
	t1
	(k1,k2)
VALUES
	((SELECT MAX(k1) FROM t1) + 1, 'something')

Dit zal in MySQL een SQL fout opleveren (maar niet in SQLite).
In MySQL kan dit alleen worden toegepast binnen een UPDATE, DELETE of INSERT statement zolang de subquery geen betrekking heeft op dezelfde tabel, dus:

INSERT INTO
	t1
	(k1,k2)
VALUES
	((SELECT MAX(k3) FROM t2) + 1, 'something')

Dit mag wel

Queries worden van links naar rechts uitgevoerd en in het geval van subqueries van binnen naar buiten.
Dit betekent dus dat ORDER BY en LIMIT op het totaal van de gejoinde tabellen uitgevoerd worden.

Met dit in het achterhoofd terug naar de webshop:
We willen een overzicht van producten (incl. specificaties) uit een bepaalde categorie, gesorteerd op prijs (oplopend), gefilterd op fabrikant en 15 producten per pagina. Stel dat we een LIMIT aan het einde van de query zouden zetten, dan zou het resultaat 15 rijen zijn en niet 15 producten.
Dit kan worden opgelost door eerst met een subquery de producten te selecteren en dan daarna de specs aan het product te koppelen:

SELECT
	p.prd_id, p.prd_name, p.prd_descr, p.prd_price,
	sp.spec_label, sp.spec_value
FROM
	(SELECT
		prd_id, prd_name, prd_descr, prd_price
	FROM
		products
	WHERE
		cat_id = 24 AND manu_id = 5
	ORDER BY
		prd_price
	LIMIT
		0,15
	) AS p
LEFT JOIN
	jn_product_specs AS psp ON p.prd_id = psp.prd_id
LEFT JOIN
	product_specs AS sp ON psp.spec_id = sp.spec_id
ORDER BY p.prd_price, p.prd_id

Let op de LEFT JOIN's. Het is mogelijk dat een product geen specs heeft maar die moeten wel meegenomen worden in het resultaat. Normaal zou je denken de join van product_specs op de koppeltabel een INNER JOIN zou moeten zijn, maar dat zou tot gevolg hebben dat de eerdere LEFT JOIN geen effect meer heeft, maar zich gedraagt als een INNER JOIN.

Joins in plaats van WHERE (NOT) IN (of EXISTS) subquery's:

SELECT
	product_id,
	product_name
FROM
	products
WHERE product_id IN (SELECT DISTINCT product_id FROM order_products)

Het is beter om deze query om te schrijven naar een join:

SELECT DISTINCT
	product_id,
	p.product_name
FROM
	products p
INNER JOIN
	order_products o
	USING (product_id)

Hetzelfde met NOT IN:

SELECT
	product_id,
	product_name
FROM
	products
WHERE product_id NOT IN (SELECT DISTINCT product_id FROM order_products)

Het is beter om deze query om te schrijven naar een left join:

SELECT
	product_id,
	p.product_name
FROM
	products p
LEFT JOIN
	order_products o
	USING (product_id)
WHERE o.order_id IS NOT NULL

Een GROUP BY is in principe altijd in combinatie met een aggregate functie.
Stel we hebben de volgende tabel scores:

+ -------------- + ----------------- +
| player_id      | player_score      |
+ -------------- + ----------------- +
| 2              | 41                |
| 1              | 50                |
| 1              | 60                |
| 3              | 55                |
| 2              | 59                |
| 3              | 54                |
+ -------------- + ----------------- +

We willen nu uit de tabel per speler de hoogste score opvragen, je ziet dan weleens queries zoals dit:

SELECT player_id, player_score FROM scores GROUP BY player_id ORDER BY player_score

Deze query wordt door MySQL gewoon uitgevoerd (in andere databases krijg je een foutmelding), maar met dit resultaat:

+ -------------- + ----------------- +
| player_id      | player_score      |
+ -------------- + ----------------- +
| 3              | 55                |
| 1              | 50                |
| 2              | 41                |
+ -------------- + ----------------- +

Je ziet dat het resultaat van deze query als een tang op een varken slaat.
Er wordt namelijk eerst gegroepeerd dan pas gesorteerd.
Laten we dit dus maar gewoon even in een fatsoenlijke query zetten:

Query Output:
> SELECT s.player_id,
    p.player_name,
    MAX(s.player_score) AS score
FROM scores s
JOIN players p USING (player_id)
GROUP BY s.player_id, p.player_name
ORDER BY score DESC


+ -------------- + ---------------- + ---------- +
| player_id      | player_name      | score      |
+ -------------- + ---------------- + ---------- +
| 1              | Kees             | 60         |
| 2              | Piet             | 59         |
| 3              | Karel            | 55         |
+ -------------- + ---------------- + ---------- +

Dit is het resultaat wat we willen.
In standaard SQL is het de regel dat kolommen in de SELECT list in een aggregate functie staan of gebruikt worden in de GROUP BY clause.

Conditioneel groeperen
GROUP BY ... HAVING (....) dus.
Bijvoorbeeld, we willen uit de scores tabel de spelers hebben wiens minimale score boven het algemeen gemiddelde ligt:

Query Output:
> SELECT s.player_id,
    p.player_name,
    MIN(s.player_score) AS score
FROM scores AS s
JOIN players AS p USING (player_id)
GROUP BY s.player_id, p.player_name
    HAVING (MIN(s.player_score) > (SELECT AVG(player_score) FROM scores))
ORDER BY score DESC


+ -------------- + ---------------- + ---------- +
| player_id      | player_name      | score      |
+ -------------- + ---------------- + ---------- +
| 3              | Karel            | 54         |
+ -------------- + ---------------- + ---------- +

Let op de subquery in de HAVINg clause; als ik dat niet gedaan had was het gemiddelde per speler berekend, wat in deze context natuurlijk geen resultaat opleverd.
Als een aggregate functie wordt toegepast zonder GROUP BY dan berekend ie over de gehele tabel.

COUNT(column)
Aggegrate functies berekenen alleen over niet lege velden. Met COUNT() is het dus belangrijk welke kolom je opgeeft.

SELECT
	c.cat_name,
	COUNT(c.cat_id) AS aantal
FROM
	categories AS c
LEFT JOIN
	products AS p
	ON c.cat_id = p.cat_id
GROUP BY c.cat_name;

-- of

SELECT
	c.cat_name,
	COUNT(p.cat_id) AS aantal
FROM
	categories AS c
LEFT JOIN
	products AS p
	ON c.cat_id = p.cat_id
GROUP BY c.cat_name;

Als een categorie geen producten bevat, zal in de eerste query aantal toch 1 zijn, en in de tweede 0 (zoals het hoort).

Group by met aggegrate functies wordt vaak gebruikt voor het maken van rapportages.
Er zijn diverse mogelijkheden en variaties, een paar voorbeelden:

De omzet van producten van het afgelopen jaar:

SELECT
	p.product_name,
	p.product_id,
	SUM(op.amount) AS total_amount,
	SUM(op.amount * op.price_pp) AS sales_volume
FROM
	products AS p   
LEFT JOIN
	(
	order_products op
	INNER JOIN
		orders o
		ON o.order_id = op.order_id
		AND EXTRACT(YEAR FROM o.order_date) = EXTRACT(YEAR FROM CURRENT_DATE()) – 1 
	)
	ON p.product_id = op.product_id
GROUP BY
	p.product_name, p.product_id

Kijk of er producten zijn die niet of nauwelijks verkocht worden:

SELECT
	p.product_name AS trash_candidate,
	p.product_id,
	SUM(op.amount) AS total_amount,
FROM
	products AS p   
LEFT JOIN
	(
	order_products op
	INNER JOIN
		orders o
		ON o.order_id = op.order_id
		AND EXTRACT(YEAR FROM o.order_date) = EXTRACT(YEAR FROM CURRENT_DATE())
	)
	ON p.product_id = op.product_id
GROUP BY
	trash_candidate, p.product_id 
HAVING SUM(op.amount) < 10

De omzet van producten per maand:

SELECT
	EXTRACT(MONTH FROM o.order_date) AS amonth
	p.product_name,
	p.product_id,
	SUM(op.amount) AS total_amount,
	SUM(op.amount * op.price_pp) AS sales_volume
FROM
	products AS p   
LEFT JOIN
	(
	order_products op
	INNER JOIN
		orders o
		ON o.order_id = op.order_id
		AND EXTRACT(YEAR FROM o.order_date) = EXTRACT(YEAR FROM CURRENT_DATE()) 
	)
	ON p.product_id = op.product_id
GROUP BY
	amonth, p.product_name, p.product_id

Er kan overal op gegroepeerd worden, gedeeltes van kolommen, resultaten van functies etc. als het maar in de selectlist voorkomt. In het volgende hoofdstuk volgt nog een voorbeeld met een group by op een range.

Conditionele statements (ook wel control flow statements genoemd) kunnen overal worden toegepast waar anders een waarde zou staan. In de praktijk is dit bijna altijd in de SELECT.

Coalesce
Syntax:

COALESCE(waarde1, waarde2 [, waarde3, …]

Coalesce retourneert het eerste niet NULL argument, of NULL als alle argumenten NULL zijn:

SELECT COALESCE(NULL, NULL, NULL)
->NULL
SELECT COALESCE(1, NULL, NULL)
->1
SELECT COALESCE(NULL, 2, NULL)
->2
SELECT COALESCE(NULL, NULL, 3)
->3

Handig om bijvoorbeeld een default waarde mee te geven:

SELECT
	COALESCE(roepnaam, voorletters) AS naam
FROM
	contactpersonen

If … else
Niet alle database systemen ondersteunen dit binnen een query, en er is verschil in syntax. In MySQL is dit:

IF(expr, alswaar, anders)

Let op, dit is de syntax binnen een query!
If … else constructies kunnen ook genest worden:

SELECT
	IF(home_score = away_score, 1, IF(home_score > away_score, 3, 0)) AS points
FROM
	match_schedule

En zo kan je aardig in de nesten geraken, er bestaat een veel handigere methode in standaard SQL:

Case
Syntax:

-- simpel
CASE expr
	WHEN waarde THEN resultaat
	[WHEN waarde THEN resultaat]
	[ELSE resultaat]
END

-- geavanceerd
CASE
	WHEN conditie THEN resultaat
	[WHEN conditie THEN resultaat]
	[ELSE resultaat]
END

De eerste notatie is vergelijkbaar met PHP's switch.

SELECT
	CASE status
		WHEN 1 THEN 'aan'
		WHEN 0 THEN 'stand-by'
		ELSE 'uit'
	END AS status
FROM
	tabel

In de tweede notatie zou dit zijn:

SELECT
	CASE 
		WHEN status=1 THEN 'aan'
		WHEN status=0 THEN 'stand-by'
		ELSE 'uit'
	END AS status
FROM
	tabel

De IF statement uit de vorige paragraaf met CASE:

SELECT 
	CASE
		WHEN home_score = away_score THEN 1
		WHEN home_score > away_score THEN 3
		ELSE 0
	END AS points
FROM
	match_schedule

Zodra een conditie waar oplevert wordt de rest overgeslagen, voldoet geen enkele conditie dan wordt de ELSE (default) waarde als resultaat geretourneerd als die gedefinieerd is, anders NULL.

Een voorbeeld waarbij een cross join handig is.
We hebben drie tabellen:
cursisten: crst_id, crst_name
boeken: boek_id, boek_titel
cursisten_lezen_boeken: crst_id, boek_id

Nu willen we weten per cursist weten of hij/zij een boek wel of niet gelezen heeft;
De eerste stap is om met een cross join alle boeken aan elke cursist te koppelen:

SELECT DISTINCT
	crst_id, boek_id
FROM curstisten
CROSS JOIN boeken

Daarna met een left join kijken of de combinatie cursist boek in de koppeltabel staat, bovenstaande query wordt dan een subquery (met wat kolommen extra):

SELECT
	cb.crst_name,
	cb.boek_titel,
	IF(c.crst_id IS NULL, 'Nee', 'Ja') AS boek_is_gelezen
FROM
	(
	 SELECT DISTINCT
		crst_id, boek_id, crst_name, boek_titel
	 FROM curstisten
	 CROSS JOIN boeken
	) AS cb
LEFT JOIN
	cursisten_lezen_boeken c
	USING (crst_id, boek_id)

Ik heb daar waar mogelijk de standaard (ANSI/ISO) SQL aangehouden.
Een uitzondering hierop zijn de CREATE TABLE statements, die zijn puur MySQL.

De meeste in deze tutorial gebruikte query's zullen in ieder geval in PostGreSQL en MySQL werken.
Please don't DIE me als dat in andere database systemen niet zo is.