Self Join

Když potřebuju porovnat řádky uvnitř jedné tabulky, spojím tabulku samu se sebou. Jedná se o standardní join, ale na obou stranách spojení mám tu samou tabulku. Abych byl schopný rozlišit, která je která, přiřadím jí na obou stranách alias:

SELECT t1.data, t2.data
FROM tabulka t1
JOIN tabulka t2
ON t1.sloupec = t2.sloupec;

t1 a t2 jsou aliasy naší tabulky

Kdy použít Self Join

1. Hierarchické struktury

vypiš všechny zaměstnance a jejich šéfy

SELECT podrizeny.jmeno, nadrizeny.jmeno
FROM zamestnanci podrizeny
LEFT JOIN zamestnanci nadrizeny
ON podrizeny.sef_id = nadrizeny.id;

(Pozn. použil jsem LEFT JOIN, aby ve výpisu byli i zaměstnanci, kteří nemají šéfa)

rodokmeny (dítě-rodič)

SELECT dite.jmeno, rodic.jmeno
FROM clenove_rodiny AS dite
LEFT JOIN clenove_rodiny AS rodic
ON dite.rodic_id = rodic.id;

2. Hledání podobných dat nebo duplicit

zákazníci se stejným emailem

SELECT a.jmeno, a.email
FROM zakaznici a
JOIN zakaznici b
ON a.email = b.email
WHERE a.id <> b.id;      -- vyradim radky, kde se mi spojil zakaznik zam se sebou

duplicitní objednávky

Pro tenhle složitější příklad uvedu i strukturu tabulky.

id	cislo_objednavky	zakaznik_id	datum_objednavky	celkova_castka
1	OBJ001	101	2024-02-01	1500.00
2	OBJ002	102	2024-02-02	2000.00
3	OBJ003	101	2024-02-01	1500.00
4	OBJ004	103	2024-02-05	2500.00
5	OBJ005	101	2024-02-01	1500.00
6	OBJ006	102	2024-02-02	2000.00

Sql pro vytvoření tabulky

CREATE TABLE objednavky (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cislo_objednavky VARCHAR(20) NOT NULL,
    zakaznik_id INT NOT NULL,
    datum_objednavky DATE NOT NULL,
    celkova_castka DECIMAL(10,2) NOT NULL
);

Sql pro naplnění daty

INSERT INTO objednavky (cislo_objednavky, zakaznik_id, datum_objednavky, celkova_castka) VALUES
('OBJ001', 101, '2024-02-01', 1500.00),
('OBJ002', 102, '2024-02-02', 2000.00),
('OBJ003', 101, '2024-02-01', 1500.00), -- Duplicitní OBJ001
('OBJ004', 103, '2024-02-05', 2500.00),
('OBJ005', 101, '2024-02-01', 1500.00), -- Duplicitní OBJ001 & OBJ003
('OBJ006', 102, '2024-02-02', 2000.00); -- Duplicitní OBJ002

Dotaz pro nalezení duplicitních objednávek

SELECT
o1.id AS objednavka_id_1,
o1.cislo_objednavky AS cislo_objednavky_1,
o2.id AS objednavka_id_2,
o2.cislo_objednavky AS cislo_objednavky_2,
o1.zakaznik_id,
o1.datum_objednavky,
o1.celkova_castka
FROM objednavky o1
JOIN objednavky o2
ON o1.zakaznik_id = o2.zakaznik_id  -- Stejný zákazník
AND o1.datum_objednavky = o2.datum_objednavky  -- Stejný datum objednávky
AND o1.celkova_castka = o2.celkova_castka  -- Stejná částka
AND o1.id > o2.id;  -- Zabrání zobrazení stejných párů dvakrát

JOIN objednavky o2 ON ... - Propojíme tabulku sama se sebou (o1 a o2 reprezentují různé řádky).
o1.zakaznik_id = o2.zakaznik_id - Objednávky musí patřit stejnému zákazníkovi.
o1.datum_objednavky = o2.datum_objednavky - Objednávky musí být vytvořeny ve stejný den.
o1.celkova_castka = o2.celkova_castka - Objednávky musí mít stejnou částku.
o1.id > o2.id - Každý duplicitní pár se zobrazí jen jednou (zabrání zobrazení OBJ001-OBJ003 i OBJ003-OBJ001).

Výstup dotazu

objednavka_id_1	cislo_objednavky_1	objednavka_id_2	cislo_objednavky_2	zakaznik_id	datum_objednavky	celkova_castka
3	OBJ003	1	OBJ001	101	2024-02-01	1500.00
5	OBJ005	1	OBJ001	101	2024-02-01	1500.00
5	OBJ005	3	OBJ003	101	2024-02-01	1500.00
6	OBJ006	2	OBJ002	102	2024-02-02	2000.00

OBJ003 je duplicitní objednávka k OBJ001 (stejný zákazník, datum a částka).
OBJ005 je duplicitní k OBJ001 a OBJ003, což tvoří skupinu tří duplicitních objednávek.
OBJ006 je duplicitní k OBJ002.
podobné názvy knih v knihovně

SELECT k1.id AS id_kniha_1, k1.nazev AS nazev_kniha_1,
       k2.id AS id_kniha_2, k2.nazev AS nazev_kniha_2
FROM knihy k1
JOIN knihy k2 ON k1.id_kniha != k2.id_kniha
WHERE k1.nazev LIKE CONCAT('%', SUBSTRING(k2.nazev, 1, 5), '%')
ORDER BY k1.id_kniha, k2.id_kniha;

Podmínka b1.id != b2.id zajišťuje, že neporovnáváme knihu samu se sebou
Podmínka LIKE ověřuje, jestli prvních 5 písmen názvu knihy se vyskytuje kdekoliv v názvu jiné knihy

3. Porovnávání dat v čase (současný vs. předchozí stav)

vývoj ceny akcií

vytvoření tabulky s cenami akcií v čase

CREATE TABLE ceny_akcii (
    id INT AUTO_INCREMENT PRIMARY KEY,
    symbol_akcie VARCHAR(10),
    cena DECIMAL(10, 2),
    datum DATE
);

naplnění tabulky daty

INSERT INTO ceny_akcii (symbol_akcie, cena, datum) VALUES
('AAPL', 145.00, '2025-02-18'),
('AAPL', 140.00, '2025-02-11'),
('TSLA', 700.00, '2025-02-18'),
('TSLA', 680.00, '2025-02-11'),
('GOOG', 2800.00, '2025-02-18'),
('GOOG', 2750.00, '2025-02-11');

dotaz na změcu cen akcií za poslední týden

SELECT
    dnes.symbol_akcie,
    dnes.cena AS aktualni_cena,
    minuly_tyden.cena AS cena_minuly_tyden,
    (dnes.cena - minuly_tyden.cena) AS zmena_ceny
FROM
    ceny_akcii dnes
JOIN
    ceny_akcii minuly_tyden
    ON dnes.symbol_akcie = minuly_tyden.symbol_akcie
    AND dnes.datum = CURDATE()
    AND minuly_tyden.datum = CURDATE() - INTERVAL 7 DAY;
    ```