On gists

Non Equi Join

MySql - advanced

non-equi.sql Raw #

-- Zaměstnanci

+------+-------+--------------+
| ID   | Jméno | Úroveň       |
+------+-------+--------------+
|    1 | John  | Expert       |
|    2 | Mary  | Intermediate |
|    3 | Peter | Beginner     |
+------+-------+--------------+

-- Zakázky
+------+-------------------+
| ID   | Požadovaná_úroveň |
+------+-------------------+
|    1 | Intermediate      |
|    2 | Beginner          |
|    3 | Expert            |
+------+-------------------+


SELECT Zaměstnanci.ID, Zaměstnanci.Jméno, Zaměstnanci.Úroveň, Zakázky.Požadovaná_úroveň
FROM Zaměstnanci
JOIN Zakázky ON Zaměstnanci.Úroveň > Zakázky.Požadovaná_úroveň;

-- Výsledek, 
+------+-------+--------------+-------------------+
| ID   | Jméno | Úroveň       | Požadovaná_úroveň |
+------+-------+--------------+-------------------+
|    1 | John  | Expert       | Beginner          |
|    2 | Mary  | Intermediate | Beginner          |
|    2 | Mary  | Intermediate | Expert            |
+------+-------+--------------+-------------------+

non-equi2.sql Raw #

-- Filmy
+------+--------------------------+------+
| ID   | Nazev                    | Rok  |
+------+--------------------------+------+
|    1 | Pulp Fiction             | 1994 |
|    3 | Inception                | 2010 |
|    4 | XXX (no Heath, no Bobby) | 1977 |
+------+--------------------------+------+

-- Herci
+------+--------------------+--------------+
| ID   | Jmeno              | Rok_narozeni |
+------+--------------------+--------------+
|    1 | John Travolta      |         1954 |
|    2 | Heath Ledger       |         1979 |
|    3 | Leonardo DiCaprio  |         1974 |
|    4 | Bobbie Milie Brown |         2012 |
+------+--------------------+--------------+


SELECT Filmy.ID, Filmy.Nazev, Filmy.Rok, Herci.Jmeno, Herci.Rok_narozeni
    FROM Filmy
    JOIN Herci ON Filmy.Rok < Herci.Rok_narozeni
    ORDER BY filmy.nazev, herci.Jmeno;
+------+--------------------------+------+--------------------+--------------+
| ID   | Nazev                    | Rok  | Jmeno              | Rok_narozeni |
+------+--------------------------+------+--------------------+--------------+
|    3 | Inception                | 2010 | Bobbie Milie Brown |         2012 |
|    1 | Pulp Fiction             | 1994 | Bobbie Milie Brown |         2012 |
|    2 | The Dark Knight          | 2008 | Bobbie Milie Brown |         2012 |
|    4 | XXX (no Heath, no Bobby) | 1977 | Bobbie Milie Brown |         2012 |
|    4 | XXX (no Heath, no Bobby) | 1977 | Heath Ledger       |         1979 |
+------+--------------------------+------+--------------------+--------------+

SELECT Filmy.ID, Filmy.Nazev, Filmy.Rok, Herci.Jmeno, Herci.Rok_narozeni
    FROM Filmy
    JOIN Herci ON Filmy.Rok > Herci.Rok_narozeni
    ORDER BY filmy.nazev, herci.Jmeno;
+------+--------------------------+------+-------------------+--------------+
| ID   | Nazev                    | Rok  | Jmeno             | Rok_narozeni |
+------+--------------------------+------+-------------------+--------------+
|    3 | Inception                | 2010 | Heath Ledger      |         1979 |
|    3 | Inception                | 2010 | John Travolta     |         1954 |
|    3 | Inception                | 2010 | Leonardo DiCaprio |         1974 |
|    1 | Pulp Fiction             | 1994 | Heath Ledger      |         1979 |
|    1 | Pulp Fiction             | 1994 | John Travolta     |         1954 |
|    1 | Pulp Fiction             | 1994 | Leonardo DiCaprio |         1974 |
|    2 | The Dark Knight          | 2008 | Heath Ledger      |         1979 |
|    2 | The Dark Knight          | 2008 | John Travolta     |         1954 |
|    2 | The Dark Knight          | 2008 | Leonardo DiCaprio |         1974 |
|    4 | XXX (no Heath, no Bobby) | 1977 | John Travolta     |         1954 |
|    4 | XXX (no Heath, no Bobby) | 1977 | Leonardo DiCaprio |         1974 |
+------+--------------------------+------+-------------------+--------------+


-- SELF JOIN
-- kolik filmu je dle roku pred nim 
 SELECT b.nazev, COUNT(A.ID)
    -> FROM filmy a right JOIN filmy b
    -> on a.ID <> b.ID
    -> AND a.Rok > b.Rok
    -> GROUP BY b.ID;
+--------------------------+-------------+
| nazev                    | COUNT(A.ID) |
+--------------------------+-------------+
| Pulp Fiction             |           2 |
| The Dark Knight          |           1 |
| Inception                |           0 |
| XXX (no Heath, no Bobby) |           3 |
+--------------------------+-------------+

-- nebo korelovany, vede ke stejnemu vysledku
SELECT *, (SELECT COUNT(*) FROM filmy WHERE rok > x.rok ) FROM filmy x