Ce este un SQL "PARTICIPĂ" și care sunt de tipuri diferite?
SQL se ALĂTURE
este o metodă de a prelua date din două sau mai multe tabele de baze de date.
Există un total de cinci `se ALĂTURE. Acestea sunt :
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
În acest fel de o "se ALĂTURE", am obține toate înregistrările care se potrivesc cu starea în ambele tabele și înregistrări în ambele tabele care nu se potrivesc nu sunt raportate.
Cu alte cuvinte, INNER JOIN
este bazat pe un singur faptul că: DOAR intrările care se potrivesc în AMBELE tabele ar TREBUI să fie listate.
Rețineți că "se ALĂTURE", fără nici o altă "se ALĂTURE" cuvinte cheie (cum ar fi "INTERIOR", "OUTER", "STÂNGA", etc) este un INNER JOIN. Cu alte cuvinte, "se ALĂTURE" este o Sintactică de zahăr pentru
INNER JOIN (a se vedea: https://stackoverflow.com/questions/565620/difference-between-join-and-inner-join).
EXTERIOR se ALĂTURE` preia
Fie, potrivit rânduri dintr-un tabel și toate rândurile în alt tabel Sau, toate rândurile din toate tabelele (nu't contează dacă există sau nu un meci).
Există trei tipuri de Exterior se Alăture :
2.1 EXTERIOR STÂNGA se ALĂTURE sau la STÂNGA se ALĂTURE
Acest lucru se alăture returnează toate rândurile de la stânga masă în legătură cu rânduri de potrivire de
tabelul din dreapta. Dacă nu există coloane de potrivire în tabelul din dreapta, se întoarce NULL
valori.
2.2 EXTERIOR DREAPTA se ALĂTURE sau CHIAR se ALĂTURE
Acest "se ALĂTURE" returnează toate rândurile din tabelul din dreapta, în colaborare cu rânduri de potrivire de
masa din stânga. Dacă nu există coloane de potrivire în stânga masă, se întoarce NULL
valori.
2.3 FULL OUTER JOIN sau COMPLET se ALĂTURE
Acest "se ALĂTURE" combină LEFT OUTER JOIN " și " DREAPTA EXTERIOR ALĂTURA
. Returnează rânduri, fie de masă atunci când condițiile sunt îndeplinite și se întoarce NULL
valoare atunci când nu există nici un meci.
Cu alte cuvinte, `OUTER JOIN este bazat pe faptul că: DOAR intrările care se potrivesc într-UNA DIN tabele (DREAPTA sau STÂNGA) sau AMBELE tabele(FULL) ar TREBUI să fie listate.
Note that `OUTER JOIN` is a loosened form of `INNER JOIN`.
Ea se bazează pe două condiții :
Acest lucru pare să fie mai mult teoretică în natură și ca urmare (probabil) cele mai multe baze de date don't deranja chiar sprijinirea asta.
Acesta este produsul Cartezian a două tabele implicate. Rezultatul o CROSS JOIN
nu va face sens
în cele mai multe situații. Mai mult decât atât, ne-am castigat't nevoie de acest lucru la toate (sau are nevoie de cel mai puțin, pentru a fi precis).
Aceasta nu este o formă diferită de "se ALĂTURE", mai degrabă este un "ADERE" ("INTERIOR", "OUTER", etc) de o masă în sine.
În funcție de operatorul folosit pentru o "ADERE" clauza, pot exista două tipuri de JOIN e. Acestea sunt
Con ALĂTURA
Theta JOIN
Pentru orice "se ALĂTURE" tip ("INTERIOR", "OUTER", etc), dacă vom folosi NUMAI operatorul de egalitate (=), atunci putem spune că
a "se ALĂTURE" este un CON ALĂTURA
.
Acest lucru este la fel ca CON ALĂTURA
dar permite toate celelalte operatorii, cum ar fi >, <, >= etc.
Mulți consideră atât
CON ALĂTURA și Theta "se ALĂTURE" similar cu "INTERIOR", "OUTER" etc
se ALĂTURE. Dar cred cu tărie că este o greșeală și de a face idei vagi. Pentru INTERIOR se ALĂTURE", " OUTER JOINetc sunt toate conectate cu mese si datele lor întrucât
CON ALĂTURAȚI-vă " și " THETA JOIN` sunt doar conectat cu operatorii vom folosi în fosta.din Nou, sunt multi care considera ca
JOIN NATURAL
, ca un fel de "ciudat"CON ALĂTURA
. În fapt, este adevărat, pentru că din prima condiție-am menționat pentruJOIN NATURAL
. Cu toate acestea, nu ne't trebuie să restricționa care pur și simplu săJOIN NATURAL e singur.
INNER JOIN, pentru EXTERIOR ALĂTURA lui etc ar putea fi un `CON ALĂTURA prea.
Definiție:
Se ALĂTURĂ sunt o modalitate de a interoga datele combinate din mai multe tabele simultan.
Preocuparea pentru RDBMS sunt de 5 tipuri de îmbinări:
Equi-Join: Combină comun înregistrările din două tabele bazate pe egalitatea de condiție. Din punct de vedere tehnic, Alături de făcut prin utilizarea egalitate-operator (=) pentru a compara valori de Cheie Primară dintr-un tabel și Cheia Externă valorile din alt tabel, prin urmare, rezultatul setul include comun(potrivit) înregistrările din ambele tabele. Pentru punerea în aplicare a vedea INTERIOARĂ-se ALĂTURE.
Natural-Join: este o versiune îmbunătățită de Equi-Join, în care SELECTAȚI operațiunea omite coloana duplicat. Pentru punerea în aplicare a vedea INTERIOARĂ-ALĂTURAȚI-vă
Non-Equi-Join: este inversă de Equi-join în cazul în care conditia este alte utilizări decât operatorul egal(=) e.g, !=, <=, >=, >, < sau ÎNTRE etc. Pentru punerea în aplicare a vedea INTERIOARĂ-se ALĂTURE.
Auto-Alăturați-vă:: O personalizate comportamentul alăturați-vă în cazul în care un tabel combinat cu sine; Aceasta este de obicei necesar pentru interogarea autoreferire tabele (sau Unar relație entitate). Pentru punerea în aplicare a vedea INTERIOARĂ-se Alătură.
Produsul cartezian: cruce combină toate înregistrările din ambele tabele, fără nici o condiție. Din punct de vedere tehnic, returnează setul de rezultate de interogare, fără a UNDE-Clauză.
Ca pe SQL îngrijorare și avansare, există 3 tipuri de se alătură și toate RDBMS se alătură poate fi realizat folosind aceste tipuri de îmbinări.
INTERIOR-ALĂTURAȚI-vă: Se îmbină(sau combina) potrivit rânduri din cele două tabele. Potrivirea este făcut pe baza unor coloane de tabele si compararea lor funcționare. Dacă egalitatea bazată pe stare, atunci: EQUI-JOIN efectuate, în caz contrar Non-EQUI-Join.
OUTER-JOIN: Se îmbină(sau combina) potrivit rânduri din cele două tabele și de neegalat rânduri cu valori NULL. Cu toate acestea, pot selecție personalizată de onu, potrivit rânduri e.g, selectarea de neegalat rand din primul tabel sau a doua masa de sub-tipuri: asociere EXTERIOARE STÂNGA și DREAPTA EXTERIOR se ALĂTURE.
2.1. Exterior STÂNGA se ALĂTURE (- o.k.o, a LĂSAT-ALĂTURAȚI-vă): Returnează potrivit rânduri din cele două tabele și de neegalat de la masa din STÂNGA(i.e, în primul tabel) numai.
2.2. Se ALĂTURE DREAPTA Exterior (- o.k.o, NU-ALĂTURAȚI-vă): Returnează potrivit rânduri din cele două tabele și de neegalat de masă din DREAPTA numai.
2.3. FULL OUTER JOIN (- o.k.un OUTER JOIN): Returnează corelate și necorelate din ambele tabele.
Notă: Auto-JOIN poate fi realizat fie prin INTERIOR-ALĂTURAȚI-vă, EXTERIOR-ALĂTURAȚI-vă și CROSS-ALĂTURAȚI-vă în funcție de cerințe, dar masa trebuie să se unească cu sine.
1.1: INTERIOR-ALĂTURAȚI-vă: Equi-join implementare
SELECT *
FROM Table1 A
INNER JOIN Table2 B ON A.<Primary-Key> =B.<Foreign-Key>;
1.2: INTERIOR-ALĂTURAȚI-vă: Natural-ALĂTURAȚI-vă de implementare
Select A.*, B.Col1, B.Col2 --But no B.ForeignKeyColumn in Select
FROM Table1 A
INNER JOIN Table2 B On A.Pk = B.Fk;
1.3: INTERIOR-ALĂTURAȚI-vă cu NON-Equi-join implementare
Select *
FROM Table1 A INNER JOIN Table2 B On A.Pk <= B.Fk;
1.4: INTERIOR-ALĂTURAȚI-vă cu AUTO-ALĂTURAȚI-vă
Select *
FROM Table1 A1 INNER JOIN Table1 A2 On A1.Pk = A2.Fk;
2.1: OUTER JOIN (full outer join)
Select *
FROM Table1 A FULL OUTER JOIN Table2 B On A.Pk = B.Fk;
2.2: STÂNGA SE ALĂTURE
Select *
FROM Table1 A LEFT OUTER JOIN Table2 B On A.Pk = B.Fk;
2.3: CHIAR SE ALĂTURE
Select *
FROM Table1 A RIGHT OUTER JOIN Table2 B On A.Pk = B.Fk;
3.1: CROSS JOIN
Select *
FROM TableA CROSS JOIN TableB;
3.2: CROSS JOIN-Auto se ALĂTURE
Select *
FROM Table1 A1 CROSS JOIN Table1 A2;
//SAU//
Select *
FROM Table1 A1,Table1 A2;
Interesant este ca cele mai multe alte răspunsuri suferă de aceste două probleme:
Acesta este motivul pentru diagrame Venn a le explica atât de inexact, pentru că un ALĂTURAȚI-vă creează o produs cartezian între cele două mese unite. Wikipedia ilustrează frumos: Sintaxa SQL pentru cartezian produse este CRUCEA ALĂTURI. De exemplu:
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
Care combină toate rândurile dintr-un tabel cu toate rândurile din alte tabel: Sursa:
+--------+ +------------+
| day | | department |
+--------+ +------------+
| Jan 01 | | Dept 1 |
| Jan 02 | | Dept 2 |
| ... | | Dept 3 |
| Jan 30 | +------------+
| Jan 31 |
+--------+
Rezultatul:
+--------+------------+
| 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 |
+--------+------------+
Dacă am scrie o listă separată prin virgule de mese, ne-am'll a obține aceleași:
-- CROSS JOINing two tables:
SELECT * FROM table1, table2
O INNER JOIN
este doar un filtrat CROSS JOIN
în cazul în care filtrul predicat se numește Theta` în algebra relațională.
De exemplu:
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
Rețineți că cuvântul cheie "INTERIOR" este opțională (cu excepția cazului în MS Access). (uita-te la articol pentru rezultatul exemple)
Un tip special de Theta-JOIN equi se ALĂTURE, pe care le folosim cel mai mult. Predicatul se alătură cheie primară de un tabel cu cheie străină de la o altă masă. Dacă vom folosi Sakila baza de date pentru ilustrare, putem scrie:
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
Aceasta combină toți actorii cu filmele lor. Sau, de asemenea, pe unele baze de date:
SELECT *
FROM actor
JOIN film_actor USING (actor_id)
JOIN film USING (film_id)
FOLOSIND()
sintaxa permite specificarea unei coloane care trebuie să fie prezente pe fiecare parte a JOIN operation's tabelele și creează o egalitate predicat pe cele două coloane.
Alte răspunsuri au listat acest "JOIN tip" separat, dar asta nu't sens. L's doar o sintaxă zahăr formă de con ALĂTURA, care este un caz special de Theta-JOIN sau INNER JOIN. NATURALE se ALĂTURE pur și simplu colectează toate ** coloane care sunt comune pentru ambele tabele fiind s-au alăturat și se alătură FOLOSIND()
aceste coloane. Care este rareori util, pentru că accidentale meciuri (ca `LAST_UPDATE coloane în Sakila baza de date).
Aici's sintaxa:
SELECT *
FROM actor
NATURAL JOIN film_actor
NATURAL JOIN film
Acum, pentru EXTERIOR ALĂTURAeste un pic diferit de
INNER JOIN` cum se creează o "UNIUNE" de mai multe produse carteziene. Putem scrie:
-- 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>
)
Nimeni nu vrea să scrie din urmă, așa că am scrie EXTERIOR se ALĂTURE(care de obicei este mai bine optimizat prin baze de date). Ca "INTERIOR", cuvântul cheie
EXTERIOReste opțională, aici. EXTERIOR se ALĂTURE
vine în trei arome:
LEFT [ OUTER ] JOIN
: stânga masă a "se ALĂTURE" expresia este adăugat la uniunea așa cum se arată mai sus. : Ambele tabele a "se ALĂTURE" expresie sunt adăugate a uniunii, așa cum se arată mai sus. Toate acestea pot fi combinate cu cuvinte cheie
FOLOSIND()sau cu
NATURALE` (I'am avut de fapt o lume reală caz de utilizare pentru un NATURAL PLIN ALĂTURI recent) Există unele istorice, învechită sintaxe în Oracle și SQL Server, care a sprijinit EXTERIOR se ALĂTURE deja înainte de SQL standard avut o sintaxa pentru acest lucru:
-- 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
După ce a spus așa, don't utilizați această sintaxă. Am lista doar aici astfel încât să puteți recunoaște de la vechiul blog / cod moștenire.
Puțini oameni știu acest lucru, dar SQL standard specifică partiționat EXTERIOR se ALĂTURE` (și Oracle implementează-l). Puteți scrie lucruri de genul asta:
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
Piese de rezultat:
+--------+------------+------------+
| 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
Punctul de aici este că toate rândurile din partiționat parte a adera va ajunge în cele din urma, indiferent dacă "se ALĂTURE" potrivesc cu ceva pe "cealaltă parte a ADERA". Povestea pe scurt: Acest lucru este de a umple rare a datelor în rapoarte. Foarte util!
Serios? Nici un alt răspuns primit asta? Bineînțeles că nu, pentru că nu't au un nativ sintaxa SQL, din păcate (ca ANTI ALĂTURI de mai jos). Dar putem folosi ÎN () " și " EXISTĂ()
, de exemplu, pentru a găsi toți actorii care au jucat în filmele:
SELECT *
FROM actor a
WHERE EXISTS (
SELECT * FROM film_actor fa
WHERE a.actor_id = fa.actor_id
)
De UNDE o.actor_id = fa.actor_idpredicat acționează ca o semi alăturați-vă predicat. Dacă tu nu't crezi, verifica planurile de execuție, de exemplu în Oracle. Te'll vedea că baza de date execută o SEMI ALĂTURA operațiunii, nu
EXISTĂ()` predicat.
Acest lucru este exact opusul de SEMI-JOIN (fie atenți să nu utilizeze NU
deși, deoarece are un avertisment de important)
Aici sunt toți actorii, fără filme:
SELECT *
FROM actor a
WHERE NOT EXISTS (
SELECT * FROM film_actor fa
WHERE a.actor_id = fa.actor_id
)
Unii oameni (mai ales MySQL persoane) scrie, de asemenea, ANTI ALĂTURA astfel:
SELECT *
FROM actor a
LEFT JOIN film_actor fa
USING (actor_id)
WHERE film_id IS NULL
Cred ca istoric, motiv pentru care este performanța.
OMG, asta e prea rece. Am'm doar să-l menționez? Aici's-o rece interogare:
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
Se va găsi TOP 5 venituri producătoare de filme pe actor. De fiecare dată când ai nevoie de un TOP-N-pe-ceva de interogare, LATERALE se ALĂTURE` va fi prietenul tău. Daca're un SQL Server persoană, atunci știi acest lucru "se ALĂTURE" tip sub numele de "APLICĂ"
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, poate că's înșelăciune, deoarece un LATERALĂ se ALĂTURE " sau " se APLICĂ` expresie este într-adevăr un "subinterogare corelată" care produce mai multe rânduri. Dar dacă ne-am permite "subinterogari corelate", de asemenea, putem vorbi despre...
Aceasta este doar într-adevăr puse în aplicare de către Oracle și Informix (din cunostintele mele), dar poate fi emulat în PostgreSQL folosind tablouri și/sau XML și SQL Server folosind XML.
MULTISET
produce o subinterogare corelată și cuiburi rezultat set de rânduri în interogare exterior. Interogare de mai jos selectează toți actorii și pentru fiecare actor colectează filmele lor într-o colecție imbricată:
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
După cum ați văzut, există mai multe tipuri de JOIN decât doar "plictisitor" "INTERIOR", EXTERIOR", și " CRUCEA de ALĂTURI, care sunt de obicei menționate. Mai multe detalii în articolul meu. Și te rog, nu mai folosi diagramele Venn pentru a le ilustra.
Am'm de gând să împingă mă enervează: FOLOSIND cuvinte cheie.
Dacă ambele tabele pe ambele părți ale ALĂTURAȚI-vă lor chei externe numit în mod corespunzător (de exemplu, același nume, nu doar "id), atunci acest lucru poate fi folosit:
SELECT ...
FROM customers JOIN orders USING (customer_id)
Mi se pare foarte practic, ușor de citit, și nu sunt utilizate destul de des.