SQL JOIN e diversi tipi di JOIN



Answers

Che cos'è SQL JOIN ?

SQL JOIN è un metodo per recuperare i dati da due o più tabelle di database.

Quali sono i diversi SQL JOIN s?

Ci sono un totale di cinque JOIN s. Loro sono :

  1. JOIN or INNER JOIN
  2. OUTER JOIN

     2.1 LEFT OUTER JOIN or LEFT JOIN
     2.2 RIGHT OUTER JOIN or RIGHT JOIN
     2.3 FULL OUTER JOIN or FULL JOIN

  3. NATURAL JOIN
  4. CROSS JOIN
  5. SELF JOIN

1. JOIN o INNER JOIN:

In questo tipo di JOIN , otteniamo tutti i record che corrispondono alla condizione in entrambe le tabelle e i record in entrambe le tabelle che non corrispondono non vengono segnalati.

In altre parole, INNER JOIN si basa sul singolo fatto che: SOLO le voci corrispondenti in ENTRAMBI le tabelle DOVREBBERO essere elencate.

Nota che un JOIN senza altre parole chiave JOIN (come INNER , OUTER , LEFT , ecc.) È un INNER JOIN . In altre parole, JOIN è uno zucchero sintattico per INNER JOIN (vedi: Differenza tra JOIN e INNER JOIN ).

2. ISCRIZIONE ESTERNA:

OUTER JOIN recupera

O le righe corrispondenti di una tabella e tutte le righe nell'altra tabella O tutte le righe di tutte le tabelle (non importa se c'è una corrispondenza).

Esistono tre tipi di Join esterno:

2.1 SINISTRA OUTER JOIN o LEFT JOIN

Questo join restituisce tutte le righe dalla tabella di sinistra in combinazione con le righe corrispondenti dalla tabella di destra. Se non ci sono colonne corrispondenti nella tabella di destra, restituisce valori NULL .

2.2 GIÙ ESTERNO JOIN o RIGHT JOIN

Questo JOIN restituisce tutte le righe dalla tabella di destra insieme alle righe corrispondenti dalla tabella sinistra. Se non ci sono colonne corrispondenti nella tabella sinistra, restituisce valori NULL .

2.3 FULL OUTER JOIN o FULL JOIN

Questo JOIN unisce LEFT OUTER JOIN e RIGHT OUTER JOIN . Restituisce una riga da entrambe le tabelle quando le condizioni sono soddisfatte e restituisce il valore NULL quando non c'è corrispondenza.

In altre parole, OUTER JOIN si basa sul fatto che: SOLO le voci corrispondenti in UNA delle tabelle (DESTRA o SINISTRA) o ENTRAMBI delle tabelle (PIENA) DOVREBBERO essere elencate.

Note that `OUTER JOIN` is a loosened form of `INNER JOIN`.

3. NATURAL JOIN:

Si basa sulle due condizioni:

  1. il JOIN è fatto su tutte le colonne con lo stesso nome per l'uguaglianza.
  2. Rimuove le colonne duplicate dal risultato.

Questo sembra essere di natura più teorica e di conseguenza (probabilmente) la maggior parte dei DBMS non si preoccupa nemmeno di supportarla.

4. CROSS JOIN:

È il prodotto cartesiano delle due tabelle coinvolte. Il risultato di un CROSS JOIN non ha senso nella maggior parte delle situazioni. Inoltre, non ne abbiamo affatto bisogno (o ha bisogno di meno, per essere precisi).

5. SELF JOIN:

Non è una forma diversa di JOIN , piuttosto è un JOIN ( INNER , OUTER , ecc.) Di un tavolo a se stesso.

PARTECIPAZIONI basate su Operatori

A seconda dell'operatore utilizzato per una clausola JOIN , possono esserci due tipi di JOIN s. Loro sono

  1. Equi JOIN
  2. Theta UNISCITI

1. Equi JOIN:

Per qualsiasi tipo di JOIN ( INNER , OUTER , ecc.), Se utilizziamo SOLO l'operatore di uguaglianza (=), allora diciamo che JOIN è un EQUI JOIN .

2. Theta JOIN:

È uguale a EQUI JOIN ma consente a tutti gli altri operatori come>, <,> = ecc.

Molti considerano sia EQUI JOIN che Theta JOIN simili a INNER , OUTER , ecc. JOIN s. Ma credo fermamente che sia un errore e rende vaghe le idee. Poiché INNER JOIN , OUTER JOIN ecc., Sono tutti collegati alle tabelle e ai loro dati dove EQUI JOIN e THETA JOIN sono collegati solo agli operatori che utilizziamo nel primo.

Ancora una volta, ci sono molti che considerano NATURAL JOIN come una sorta di EQUI JOIN "peculiare". In effetti, è vero, a causa della prima condizione che ho citato per NATURAL JOIN . Tuttavia, non dobbiamo limitarlo semplicemente ai soli NATURAL JOIN . INNER JOIN s, OUTER JOIN s ecc potrebbe essere un EQUI JOIN .

Question

Che cos'è SQL JOIN e quali sono i diversi tipi?




È interessante notare che la maggior parte delle altre risposte soffrono di questi due problemi:

Di recente ho scritto un articolo sull'argomento: una guida completa e incompleta sui molti modi diversi per unire le tabelle in SQL , che riassumerò qui.

Prima di tutto: i JOIN sono prodotti cartesiani

Questo è il motivo per cui i diagrammi di Venn li spiegano in modo così inaccurato, perché un JOIN crea un prodotto cartesiano tra le due tabelle unite. Wikipedia lo illustra bene:

La sintassi SQL per i prodotti cartesiani è CROSS JOIN . Per esempio:

SELECT *

-- This just generates all the days in January 2017
FROM generate_series(
  '2017-01-01'::TIMESTAMP,
  '2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day',
  INTERVAL '1 day'
) AS days(day)

-- Here, we're combining all days with all departments
CROSS JOIN departments

Che combina tutte le righe da una tabella con tutte le righe dall'altra tabella:

Fonte:

+--------+   +------------+
| day    |   | department |
+--------+   +------------+
| Jan 01 |   | Dept 1     |
| Jan 02 |   | Dept 2     |
| ...    |   | Dept 3     |
| Jan 30 |   +------------+
| Jan 31 |
+--------+

Risultato:

+--------+------------+
| day    | department |
+--------+------------+
| Jan 01 | Dept 1     |
| Jan 01 | Dept 2     |
| Jan 01 | Dept 3     |
| Jan 02 | Dept 1     |
| Jan 02 | Dept 2     |
| Jan 02 | Dept 3     |
| ...    | ...        |
| Jan 31 | Dept 1     |
| Jan 31 | Dept 2     |
| Jan 31 | Dept 3     |
+--------+------------+

Se scriviamo solo un elenco di tabelle separato da virgole, otterremo lo stesso:

-- CROSS JOINing two tables:
SELECT * FROM table1, table2

INNER JOIN (Theta-JOIN)

Un INNER JOIN è solo un CROSS JOIN filtrato in cui il predicato del filtro è chiamato Theta nell'algebra relazionale.

Per esempio:

SELECT *

-- Same as before
FROM generate_series(
  '2017-01-01'::TIMESTAMP,
  '2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day',
  INTERVAL '1 day'
) AS days(day)

-- Now, exclude all days/departments combinations for
-- days before the department was created
JOIN departments AS d ON day >= d.created_at

Si noti che la parola chiave INNER è facoltativa (tranne che in MS Access).

( guarda l'articolo per esempi di risultati )

EQUI JOIN

Un tipo speciale di Theta-JOIN è equi JOIN, che usiamo di più. Il predicato unisce la chiave primaria di una tabella con la chiave esterna di un'altra tabella. Se usiamo il database di Sakila per l'illustrazione, possiamo scrivere:

SELECT *
FROM actor AS a
JOIN film_actor AS fa ON a.actor_id = fa.actor_id
JOIN film AS f ON f.film_id = fa.film_id

Questo combina tutti gli attori con i loro film.

O anche, su alcuni database:

SELECT *
FROM actor
JOIN film_actor USING (actor_id)
JOIN film USING (film_id)

La sintassi USING() consente di specificare una colonna che deve essere presente su entrambi i lati delle tabelle di un'operazione JOIN e crea un predicato di uguaglianza su queste due colonne.

JOIN NATURALE

Altre risposte hanno elencato questo "tipo JOIN" separatamente, ma questo non ha senso. È solo una forma di sintassi dello zucchero per equi JOIN, che è un caso speciale di Theta-JOIN o INNER JOIN. NATURAL JOIN raccoglie semplicemente tutte le colonne comuni a entrambe le tabelle che vengono unite e unisce USING() quelle colonne. Che è quasi mai utile, a causa di corrispondenze accidentali (come le colonne LAST_UPDATE nel database di Sakila ).

Ecco la sintassi:

SELECT *
FROM actor
NATURAL JOIN film_actor
NATURAL JOIN film

ESTERNO ORA

Ora, OUTER JOIN è un po 'diverso da INNER JOIN in quanto crea UNION di diversi prodotti cartesiani. Possiamo scrivere:

-- Convenient syntax:
SELECT *
FROM a LEFT JOIN b ON <predicate>

-- Cumbersome, equivalent syntax:
SELECT a.*, b.*
FROM a JOIN b ON <predicate>
UNION ALL
SELECT a.*, NULL, NULL, ..., NULL
FROM a
WHERE NOT EXISTS (
  SELECT * FROM b WHERE <predicate>
)

Nessuno vuole scrivere quest'ultimo, quindi scriviamo OUTER JOIN (che di solito è meglio ottimizzato dai database).

Come INNER , la parola chiave OUTER è facoltativa, qui.

OUTER JOIN disponibile in tre versioni:

  • LEFT [ OUTER ] JOIN : la tabella sinistra dell'espressione JOIN viene aggiunta all'unione come mostrato sopra.
  • RIGHT [ OUTER ] JOIN : la tabella di destra dell'espressione JOIN viene aggiunta all'unione come mostrato sopra.
  • FULL [ OUTER ] JOIN : entrambe le tabelle dell'espressione JOIN vengono aggiunte all'unione come mostrato sopra.

Tutti questi possono essere combinati con la parola chiave USING() o con NATURAL (in realtà ho avuto un caso d'uso del mondo reale per un NATURAL FULL JOIN recente )

Sintassi alternative

Esistono alcune sintassi storiche e deprecate in Oracle e SQL Server, che supportavano OUTER JOIN già prima che lo standard SQL avesse una sintassi per questo:

-- Oracle
SELECT *
FROM actor a, film_actor fa, film f
WHERE a.actor_id = fa.actor_id(+)
AND fa.film_id = f.film_id(+)

-- SQL Server
SELECT *
FROM actor a, film_actor fa, film f
WHERE a.actor_id *= fa.actor_id
AND fa.film_id *= f.film_id

Detto questo, non usare questa sintassi. Lo elenco solo qui in modo che tu possa riconoscerlo dai vecchi post del blog / codice precedente.

Partizionato OUTER JOIN

Poche persone lo sanno, ma lo standard SQL specifica partizionato OUTER JOIN (e Oracle lo implementa). Puoi scrivere cose come questa:

WITH

  -- Using CONNECT BY to generate all dates in January
  days(day) AS (
    SELECT DATE '2017-01-01' + LEVEL - 1
    FROM dual
    CONNECT BY LEVEL <= 31
  ),

  -- Our departments
  departments(department, created_at) AS (
    SELECT 'Dept 1', DATE '2017-01-10' FROM dual UNION ALL
    SELECT 'Dept 2', DATE '2017-01-11' FROM dual UNION ALL
    SELECT 'Dept 3', DATE '2017-01-12' FROM dual UNION ALL
    SELECT 'Dept 4', DATE '2017-04-01' FROM dual UNION ALL
    SELECT 'Dept 5', DATE '2017-04-02' FROM dual
  )
SELECT *
FROM days 
LEFT JOIN departments 
  PARTITION BY (department) -- This is where the magic happens
  ON day >= created_at

Parti del risultato:

+--------+------------+------------+
| day    | department | created_at |
+--------+------------+------------+
| Jan 01 | Dept 1     |            | -- Didn't match, but still get row
| Jan 02 | Dept 1     |            | -- Didn't match, but still get row
| ...    | Dept 1     |            | -- Didn't match, but still get row
| Jan 09 | Dept 1     |            | -- Didn't match, but still get row
| Jan 10 | Dept 1     | Jan 10     | -- Matches, so get join result
| Jan 11 | Dept 1     | Jan 10     | -- Matches, so get join result
| Jan 12 | Dept 1     | Jan 10     | -- Matches, so get join result
| ...    | Dept 1     | Jan 10     | -- Matches, so get join result
| Jan 31 | Dept 1     | Jan 10     | -- Matches, so get join result

Il punto qui è che tutte le righe dal lato partizionato del join finiranno nel risultato, indipendentemente dal fatto che il JOIN corrisponda a qualcosa sull'altro lato del JOIN. Per farla breve: questo è per riempire i dati sparsi nei rapporti. Molto utile!

SEMI JOIN

Sul serio? Nessuna altra risposta ha ottenuto questo? Certo che no, perché non ha una sintassi nativa in SQL, sfortunatamente (proprio come ANTI JOIN in basso). Ma possiamo usare IN() e EXISTS() , ad es. Per trovare tutti gli attori che hanno suonato nei film:

SELECT *
FROM actor a
WHERE EXISTS (
  SELECT * FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

Il WHERE a.actor_id = fa.actor_id predicate agisce come predicato semi join. Se non ci credi, controlla i piani di esecuzione, ad esempio in Oracle. Vedrai che il database esegue un'operazione SEMI JOIN, non il EXISTS() .

ANTI JOIN

Questo è esattamente l'opposto di SEMI JOIN (stai attento a non usare NOT IN , dato che ha un avvertimento importante)

Qui ci sono tutti gli attori senza film:

SELECT *
FROM actor a
WHERE NOT EXISTS (
  SELECT * FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

Alcuni (specialmente quelli di MySQL) scrivono ANTI JOIN in questo modo:

SELECT *
FROM actor a
LEFT JOIN film_actor fa
USING (actor_id)
WHERE film_id IS NULL

Penso che la ragione storica sia la performance.

LATERAL JOIN

OMG, questo è troppo bello. Sono l'unico a parlarne? Ecco una query interessante:

SELECT a.first_name, a.last_name, f.*
FROM actor AS a
LEFT OUTER JOIN LATERAL (
  SELECT f.title, SUM(amount) AS revenue
  FROM film AS f
  JOIN film_actor AS fa USING (film_id)
  JOIN inventory AS i USING (film_id)
  JOIN rental AS r USING (inventory_id)
  JOIN payment AS p USING (rental_id)
  WHERE fa.actor_id = a.actor_id -- JOIN predicate with the outer query!
  GROUP BY f.film_id
  ORDER BY revenue DESC
  LIMIT 5
) AS f
ON true

Troverà i primi 5 film che producono entrate per attore. Ogni volta che hai bisogno di una query TOP-N-per-qualcosa, LATERAL JOIN sarà tuo amico. Se sei una persona di SQL Server, allora conosci questo tipo di JOIN sotto il nome APPLY

SELECT a.first_name, a.last_name, f.*
FROM actor AS a
OUTER APPLY (
  SELECT f.title, SUM(amount) AS revenue
  FROM film AS f
  JOIN film_actor AS fa ON f.film_id = fa.film_id
  JOIN inventory AS i ON f.film_id = i.film_id
  JOIN rental AS r ON i.inventory_id = r.inventory_id
  JOIN payment AS p ON r.rental_id = p.rental_id
  WHERE fa.actor_id = a.actor_id -- JOIN predicate with the outer query!
  GROUP BY f.film_id
  ORDER BY revenue DESC
  LIMIT 5
) AS f

OK, forse è imbroglio, perché un'espressione LATERAL JOIN o APPLY è in realtà una "sottoquery correlata" che produce più righe. Ma se permettiamo "subquery correlate", possiamo anche parlare di ...

MULTISET

Questo è realmente implementato da Oracle e Informix (a mia conoscenza), ma può essere emulato in PostgreSQL usando array e / o XML e in SQL Server usando XML.

MULTISET produce una sottoquery correlata e annida il set di righe risultante nella query esterna. La query seguente seleziona tutti gli attori e per ciascun attore raccoglie i loro film in una raccolta nidificata:

SELECT a.*, MULTISET (
  SELECT f.*
  FROM film AS f
  JOIN film_actor AS fa USING (film_id)
  WHERE a.actor_id = fa.actor_id
) AS films
FROM actor

Come hai visto, ci sono più tipi di JOIN che solo i "noiosi" INNER , OUTER e CROSS JOIN che vengono solitamente menzionati. Maggiori dettagli nel mio articolo . E per favore, smetti di usare i diagrammi di Venn per illustrarli.




Ho creato un'illustrazione che spiega meglio delle parole, a mio parere:




Related