• INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN
  • SELF JOIN
  • SUBQUERY

Úloha

Vyskusajte si prikazy uvedene na strankach:


INNER JOIN

Vrati hodnoty, ktore je mozne spojit v oboch tabulkach.
innerjoin

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.
leftjoin

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.
rightjoin

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.
fulljoin

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);
    

Zdroj: https://www.w3schools.com/sql/sql_join.asp