Join-y

Úloha

Vyskusajte si prikazy uvedene na strankach:

• MySQL INNER JOIN
• MySQL LEFT JOIN
• MySQL RIGHT JOIN
• MySQL Self Join
• MySQL Subquery

INNER JOIN

Vrati hodnoty, ktore je mozne spojit v oboch tabulkach.

SELECT 
	c.customer_id, 
    c.first_name, 
    c.last_name,
    a.actor_id,
    a.first_name,
    a.last_name
FROM customer c
JOIN actor a 
ON c.last_name = a.last_name
ORDER BY c.last_name;
    

LEFT JOIN

Vrati hodnoty, ktore je mozne spojit s pravou tabulkou a vsetky zaroven vsetky aj vsetky z lavej tabulky.

SELECT 
	c.customer_id, 
    c.first_name, 
    c.last_name,
    a.actor_id,
    a.first_name,
    a.last_name
FROM customer c
LEFT JOIN actor a 
ON c.last_name = a.last_name
ORDER BY c.last_name;
    

RIGHT JOIN

Vrati hodnoty, ktore je mozne spojit s lavou tabulkou a vsetky zaroven vsetky aj vsetky z pravej tabulky.

SELECT 
	c.customer_id, 
    c.first_name, 
    c.last_name,
    a.actor_id,
    a.first_name,
    a.last_name
FROM customer c
RIGHT JOIN actor a 
ON c.last_name = a.last_name
ORDER BY a.last_name;
    

FULL JOIN

Vrati hodnoty, ktore je mozne spojit medzi tabulkami a vsetky zaroven vsetky aj vsetky z lavej a pravej tabulky.

SELECT 
	c.customer_id, 
    c.first_name, 
    c.last_name,
    a.actor_id,
    a.first_name,
    a.last_name
FROM customer c
LEFT JOIN actor a 
ON c.last_name = a.last_name

UNION

SELECT 
	c.customer_id, 
    c.first_name, 
    c.last_name,
    a.actor_id,
    a.first_name,
    a.last_name
FROM customer c
RIGHT JOIN actor a 
ON c.last_name = a.last_name
    

SELF JOIN

Pouziva sa pri spajani rovnakej tabulky viac krat v danom selekte. Je pri nom mozne pouzit INNER, LEFT, RIGHT alebo aj FULL JOIN.

SELECT 
	a.customer_id, 
	a.first_name, 
	a.last_name, 
    	b.customer_id,
	b.first_name, 
	b.last_name 
FROM customer a
INNER JOIN customer b
ON a.last_name = b.first_name;
    

SUBQUERY

Pouziva sa ked nam existujuce tabulky nevyhovuju a chceme si predpripravit tabulku, ktoru chceme dalej pouzivat.

SELECT * FROM actor
WHERE actor_id IN 
	(SELECT actor_id FROM film_actor
	WHERE film_id = 2);