Zrádný LEFT JOIN

V SQL je JOIN klauzule používána pro spojení tabulek. Klauzule LEFT JOIN spojí všechny záznamy z tabulky uvedené nalevo od klauzule se záznamy v tabulce uvedené napravo.

SELECT * FROM tabulka_jedna LEFT JOIN tabulka_dve

Zrádností LEFT JOINu (to samé platí i pro RIGHT JOIN) je to, že vezme všechny záznamy z levé tabulky a přiřadí je k (spojí je s) záznamům v pravé tabulce. Pokud ale v pravé tabulce není záznam, bude sloupec obsahovat null. Takhle to vypadá zřejmě, pojďme se ale podívat na následující příklad (v PostgreSQL).

Vytvoříme si schéma ZLJ (Zrádný Left Join) a v něm si vytvoříme tři tabulky. Tabulka USER bude obsahovat údaje o uživateli jako je jeho id, číslo, jméno. Tabulku AUTH_TYPE bude číselník s typy autorizací (sms, pin, heslo). Jako poslední vytvoříme tabulku AUTHENTCATION, která bude uchovávat údaje týkající se authentikace (typ, kdy byla vytvořena,) a bude provázána s tabulkou USER pomocí cizího klíče. Tabulky si rovnou naplníme daty.

DROP SCHEMA IF EXISTS ZLJ CASCADE;
CREATE SCHEMA ZLJ;

CREATE TABLE ZLJ.USER (
	user_id	serial not null,
	user_number varchar(10) not null,
	username varchar(10) not null,

	primary key(user_id)
);

CREATE TABLE ZLJ.AUTH_TYPE (
	auth_type_id serial not null,
	type varchar(10) not null,

	primary key(auth_type_id)
);

CREATE TABLE ZLJ.AUTHENTICATION (
	authentication_id serial not null,
	user_id int not null,
	auth_type_id int not null,
	created timestamp not null,

	primary key(authentication_id),
	foreign key(user_id) references ZLJ.USER(user_id),
	foreign key(auth_type_id) references ZLJ.AUTH_TYPE(auth_type_id)
);

INSERT INTO ZLJ.USER (user_number, username) VALUES
	('9954258641', 'fnovotny'),
	('9865662384', 'franta03'),
	('9932652322', 'mariem');

INSERT INTO ZLJ.AUTH_TYPE (type) VALUES
	('pin'),
	('sms'),
	('password');

INSERT INTO ZLJ.AUTHENTICATION (user_id, auth_type_id, created) VALUES
	(1, 2, '2016-01-05 21:11:58'),
	(3, 3, '2016-01-05 22:03:17');

Všimněte si jedné věci. Všechny sloupce mají definované omezení NOT NULL. To znamená, že do každého sloupce záznamu je nutné vložit hodnotu. Neexistuje sloupec, ve kterém by mohla být NULL hodnota.

Naši databázi voláme z nějaké aplikace. Tato aplikace požaduje následující údaje: authentication_id (id záznamu v tabulce AUTHENTICATION) a auth_type_id (foreign key na záznam v číselníku). Tyto údaje potřebuje pro určitého uživatele. Jelikož chceme mít ve výsledku obsaženy všechny uživatele, použijem LEFT JOIN.

SELECT authentication_id, auth_type_id 
FROM 
ZLJ.USER LEFT JOIN ZLJ.AUTHENTICATION ON (ZLJ.USER.user_id = ZLJ.AUTHENTICATION.user_id)
WHERE user_number = ?

Za otazník se v prepared statementu dosadí user_number. Jelikož mají všechny sloupce omezení NOT NULL, vždy dostaneme nějakou hodnotu. Chyba. Aplikace začne občas padat. Přesněji řečeno začne padat v momentě, kdy se pokusíme v tabulce AUTH_TYPE dohledat záznam type dle auth_type_id, které získáme  z předchozího selectu. Pokud totiž budeme hledat záznam auth_type_id pro user_number 9865662384, dostaneme NULL.

Dotaz:

SELECT * 
FROM 
ZLJ.USER LEFT JOIN ZLJ.AUTHENTICATION ON (ZLJ.USER.user_id = ZLJ.AUTHENTICATION.user_id)

Výsledek:

Dotaz:

select * from 
	(SELECT * FROM ZLJ.USER LEFT JOIN ZLJ.AUTHENTICATION ON (ZLJ.USER.user_id = ZLJ.AUTHENTICATION.user_id)) as SEL 
where auth_type_id is null;

Vrátí záznamy, které mají ve sloupci auth_type_id NULL.