SQL | vitfo.cz

drop table if exists t_tree; create table t_tree (id bigint, text text); insert into t_tree (id, text) values (1, 'javor'), (2, 'dub'), (3, 'borovice'); drop table if exists t_car; create table t_car (id bigint, text text); insert into t_car (id, text) values (1, 'škoda'), (2, 'nisan'), (3, 'ford'); drop table if exists t_sport; create table t_sport (id bigint, text text); insert into t_sport (id, text) values (1, 'hokej'), (2, 'judo'), (3, 'biatlon');

Klíčové slovo LIMIT umožňuje omezit počet vrácených záznamů SQL dotazu. Dotaz s LIMIT vrátí určený počet záznamů nebo méně (pokud samotný dotaz vrací méně záznamů než je limit)

SELECT * FROM table_name LIMIT 10

Vrátí prvních deset záznamů.

SELECT * FROM table_name ORDER BY column_name ASC LIMIT 5

Vrátí prvních pět záznamů. Dotaz nejdříve nalezne všechny záznamy. Ty seřadí sestupně (ASC) a vrátí prvních pět.

S limitem se pojí další klíčové slovo a to OFFSET. To určuje, kolik nalezených záznamů se má přeskočit.

SELECT * FROM table_name LIMIT 5 OFFSET 10

Vrátí jedenáctý až patnáctý záznam četně. Prvních deset záznamů se přeskočí.

OFFSET je možné ale použít i samostatně bez LIMIT.

SELECT * FROM table_name OFFSET 10

Vrátí všechny záznamy od jedenáctého níže.

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.

Spojení výsledků více SQL dotazů do jednoho sloupce

Omezení počtu vrácených záznamů pomocí LIMIT

Zrádný LEFT JOIN