Συνενώσεις (joins)
Επιλογή δεδομένων από δύο ή περισσότερους πίνακες
Έστω οι δύο παρακάτω πίνακες table_a και table_b
| table_a | table_b | ||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
|
Το ερώτημα:
SELECT * FROM table_a, table_b;
Επιλέγει όλους τους πιθανούς συνδυασμούς, δηλαδή 4*4=16 πλειάδες όπως φαίνονται στον παρακάτω πίνακα (δεν διαγράφονται πιθανές διπλοεγγραφές).
Το παραπάνω ερώτημα αντιστοιχεί στο καρτεσιανό γινόμενο (table_a X table_b)
| A_ID | A1 | A2 | W | B_ID | B1 | B2 | W |
|---|---|---|---|---|---|---|---|
| 1 | 1 | 2 | 5 | 1 | 5 | 2 | 2 |
| 2 | 4 | 2 | 6 | 1 | 5 | 2 | 2 |
| 3 | 2 | 3 | 3 | 1 | 5 | 2 | 2 |
| 4 | 1 | 4 | 1 | 1 | 5 | 2 | 2 |
| 1 | 1 | 2 | 5 | 2 | 3 | 1 | 5 |
| 2 | 4 | 2 | 6 | 2 | 3 | 1 | 5 |
| 3 | 2 | 3 | 3 | 2 | 3 | 1 | 5 |
| 4 | 1 | 4 | 1 | 2 | 3 | 1 | 5 |
| 1 | 1 | 2 | 5 | 3 | 1 | 3 | 1 |
| 2 | 4 | 2 | 6 | 3 | 1 | 3 | 1 |
| 3 | 2 | 3 | 3 | 3 | 1 | 3 | 1 |
| 4 | 1 | 4 | 1 | 3 | 1 | 3 | 1 |
| 1 | 1 | 2 | 5 | 4 | 4 | 5 | 7 |
| 2 | 4 | 2 | 6 | 4 | 4 | 5 | 7 |
| 3 | 2 | 3 | 3 | 4 | 4 | 5 | 7 |
| 4 | 1 | 4 | 1 | 4 | 4 | 5 | 7 |
Μπορείτε να έχετε περισσότερους από δύο πίνακες όπως:
SELECT * FROM table_a, table_b, table_c;
Συνένωση Θ (θήτα)
Αν προσθέσουμε το WHERE στο παραπάνω ερώτημα θα έχουμε μια συνένωση Θ (θήτα).
SELECT * FROM table_a, table_b WHERE a1>b1;
| A_ID | A1 | A2 | W | B_ID | B1 | B2 | W |
|---|---|---|---|---|---|---|---|
| 2 | 4 | 2 | 6 | 2 | 3 | 1 | 5 |
| 2 | 4 | 2 | 6 | 3 | 1 | 3 | 1 |
| 3 | 2 | 3 | 3 | 3 | 1 | 3 | 1 |
Στην περίπτωση που τα πεδία που συγκρίνουμε έχουν ίδιο όνομα τα ξεχωρίζουμε βάζοντας το όνομα του πίνακα και μια τελεία όπως στο παρακάτω παράδειγμα.
SELECT * FROM table_a, table_b WHERE table_a.w > table_b.w;
| A_ID | A1 | A2 | W | B_ID | B1 | B2 | W |
|---|---|---|---|---|---|---|---|
| 1 | 1 | 2 | 5 | 1 | 5 | 2 | 2 |
| 2 | 4 | 2 | 6 | 1 | 5 | 2 | 2 |
| 3 | 2 | 3 | 3 | 1 | 5 | 2 | 2 |
| 2 | 4 | 2 | 6 | 2 | 3 | 1 | 5 |
| 1 | 1 | 2 | 5 | 3 | 1 | 3 | 1 |
| 2 | 4 | 2 | 6 | 3 | 1 | 3 | 1 |
| 3 | 2 | 3 | 3 | 3 | 1 | 3 | 1 |
Συνένωση Ισότητας (equijoin)
Αν προσθέσουμε στο WHERE συνθήκη ισότητας έχουμε συνένωση ισότητας.
SELECT * FROM table_a, table_b WHERE a2=b2;
| A_ID | A1 | A2 | W | B_ID | B1 | B2 | W |
|---|---|---|---|---|---|---|---|
| 1 | 1 | 2 | 5 | 1 | 5 | 2 | 2 |
| 2 | 4 | 2 | 6 | 1 | 5 | 2 | 2 |
| 3 | 2 | 3 | 3 | 3 | 1 | 3 | 1 |
Επίσης όταν έχουμε ίδια ονόματα πεδίων σε δύο πίνακες, τότε έχουμε:
SELECT * FROM table_a, table_b WHERE table_a.w=table_b.w;
Συνενώσεις joins
Οι συνενώσεις (joins) χρησιμοποιούνται για ερωτήματα σε δύο οι περισσότερους πίνακες για τους οποίους υπάρχει κάποια σχέση ανάμεσα στα γνωρίσματα (συνήθως ανάμεσα στα κλειδιά).
JOIN ή INNER JOIN
Η JOIN είναι αντίστοιχη του καρτεσιανού γινομένου.
SELECT * FROM table_a JOIN table_b;
| A_ID | A1 | A2 | W | B_ID | B1 | B2 | W |
|---|---|---|---|---|---|---|---|
| 1 | 1 | 2 | 5 | 1 | 5 | 2 | 2 |
| 2 | 4 | 2 | 6 | 1 | 5 | 2 | 2 |
| 3 | 2 | 3 | 3 | 1 | 5 | 2 | 2 |
| 4 | 1 | 4 | 1 | 1 | 5 | 2 | 2 |
| 1 | 1 | 2 | 5 | 2 | 3 | 1 | 5 |
| 2 | 4 | 2 | 6 | 2 | 3 | 1 | 5 |
| 3 | 2 | 3 | 3 | 2 | 3 | 1 | 5 |
| 4 | 1 | 4 | 1 | 2 | 3 | 1 | 5 |
| 1 | 1 | 2 | 5 | 3 | 1 | 3 | 1 |
| 2 | 4 | 2 | 6 | 3 | 1 | 3 | 1 |
| 3 | 2 | 3 | 3 | 3 | 1 | 3 | 1 |
| 4 | 1 | 4 | 1 | 3 | 1 | 3 | 1 |
| 1 | 1 | 2 | 5 | 4 | 4 | 5 | 7 |
| 2 | 4 | 2 | 6 | 4 | 4 | 5 | 7 |
| 3 | 2 | 3 | 3 | 4 | 4 | 5 | 7 |
| 4 | 1 | 4 | 1 | 4 | 4 | 5 | 7 |
Μπορείτε να κάνετε JOIN περισσότερους από δύο πίνακες όπως:
SELECT * FROM table_a JOIN table_b JOIN table_c;
Με την JOIN μπορείτε επίσης να χρησιμοποιείτε και το WHERE
SELECT * FROM table_a JOIN table_b where a1=b1;
Η άλλη επιλογή είναι η αντικατάσταση του WHERE με το ON ή το USING που χρησιμοποιούνται στις συνενώσεις δύο πινάκων.
- ON χρησιμοποιείται για συνένωση στηλών με διαφορετικό όνομα
SELECT * FROM table_a JOIN table_b ON table_a.a1 = table_b.b1;
| A_ID | A1 | A2 | W | B_ID | B1 | B2 | W |
|---|---|---|---|---|---|---|---|
| 1 | 1 | 2 | 5 | 3 | 1 | 3 | 1 |
| 4 | 1 | 4 | 1 | 3 | 1 | 3 | 1 |
| 2 | 4 | 2 | 6 | 4 | 4 | 5 | 7 |
- USING χρησιμοποιείται για συνένωση στηλών με ίδιο όνομα (έστω η στήλη w). Επίσης, με τον τελεστή USING επιλέγονται μόνο οι εγγραφές για τις οποίες οι αντίστοιχες τιμές στο πεδίο ή πεδία με ίδιο όνομα (w) είναι ίσες. Τέλος, επιστρέφεται μόνο η μία στήλη ή στήλες με το κοινό όνομα.
SELECT * FROM table_a JOIN table_b USING (w);
| W | A_ID | A1 | A2 | B_ID | B1 | B2 |
|---|---|---|---|---|---|---|
| 5 | 1 | 1 | 2 | 2 | 3 | 1 |
| 1 | 4 | 1 | 4 | 3 | 1 | 3 |
NATURAL JOIN
Η NATURAL JOIN λειτουργεί όπως η JOIN σε συνδυασμό με το USING. Η διαφορά είναι ότι δεν χρειάζεται το USING. Η NATURAL JOIN ψάχνει όλα τα πεδία με κοινό όνομα και εφαρμόζει το USING χωρίς να χρειάζεται να δηλωθεί.
SELECT * FROM table_a NATURAL JOIN table_b;
| W | A_ID | A1 | A2 | B_ID | B1 | B2 |
|---|---|---|---|---|---|---|
| 5 | 1 | 1 | 2 | 2 | 3 | 1 |
| 1 | 4 | 1 | 4 | 3 | 1 | 3 |
Ή για περισσότερους πίνακες.
SELECT * FROM table_a NATURAL JOIN table_b NATURAL JOIN table_c;
Στην επιλογή με NATURAL JOIN μπορεί να ακολουθεί και η WHERE μετά την FROM.
SELECT * FROM table_a NATURAL JOIN table_b WHERE a1 = b1;
| W | A_ID | A1 | A2 | B_ID | B1 | B2 |
|---|---|---|---|---|---|---|
| 1 | 4 | 1 | 4 | 3 | 1 | 3 |
OUTER JOINS
Σε μια συνένωση τύπου OUTER JOIN δεν είναι υποχρεωτικό να επιστρέφονται μόνο οι εγγραφές που υπακούουν στη συνθήκη, αλλά και όλες οι εγγραφές από τον έναν ή και από τους δύο πίνακες της συνένωσης.
Έτσι έχουμε:
- LEFT OUTER JOIN ή LEFT JOIN
- RIGHT OUTER JOIN ή RIGHT JOIN
- FULL OUTER JOIN ή FULL JOIN
Για τις OUTER συνενώσεις δεν υπάρχει αντίστοιχη εντολή στην πρότυπη SQL
LEFT JOIN
Η LEFT JOIN επιστρέφει όλες τις εγγραφές από τον αριστερό πίνακα και κρατάει από τον δεξιό αυτές που ικανοποιούν τη συνθήκη. Στα κενά κελιά μπαίνει το NULL.
SELECT * FROM table_a LEFT JOIN table_b ON a1 = b1;
| A_ID | A1 | A2 | W | B_ID | B1 | B2 | W |
|---|---|---|---|---|---|---|---|
| 1 | 1 | 2 | 5 | 3 | 1 | 3 | 1 |
| 2 | 4 | 2 | 6 | 4 | 4 | 5 | 7 |
| 3 | 2 | 3 | 3 | NULL | NULL | NULL | NULL |
| 4 | 1 | 4 | 1 | 3 | 1 | 3 | 1 |
Επίσης με τη USING.
SELECT * FROM table_a LEFT JOIN table_b USING(w);
| W | A_ID | A1 | A2 | B_ID | B1 | B2 |
|---|---|---|---|---|---|---|
| 5 | 1 | 1 | 2 | 2 | 3 | 1 |
| 6 | 2 | 4 | 2 | NULL | NULL | NULL |
| 3 | 3 | 2 | 3 | NULL | NULL | NULL |
| 1 | 4 | 1 | 4 | 3 | 1 | 3 |
Εναλλακτικά με NATURAL LEFT JOIN
SELECT * FROM table_a NATURAL LEFT JOIN table_b;
| W | A_ID | A1 | A2 | B_ID | B1 | B2 |
|---|---|---|---|---|---|---|
| 5 | 1 | 1 | 2 | 2 | 3 | 1 |
| 6 | 2 | 4 | 2 | NULL | NULL | NULL |
| 3 | 3 | 2 | 3 | NULL | NULL | NULL |
| 1 | 4 | 1 | 4 | 3 | 1 | 3 |
RIGHT JOIN
Η RIGHT JOIN επιστρέφει όλες τις εγγραφές από τον δεξιό πίνακα και κρατάει από τον αριστερό αυτές που ικανοποιούν τη συνθήκη. Στα κενά κελιά μπαίνει το NULL.
SELECT * FROM table_a RIGHT JOIN table_b ON a1 = b1;
| A_ID | A1 | A2 | W | B_ID | B1 | B2 | W |
|---|---|---|---|---|---|---|---|
| NULL | NULL | NULL | NULL | 1 | 5 | 2 | 2 |
| NULL | NULL | NULL | NULL | 2 | 3 | 1 | 5 |
| 1 | 1 | 2 | 5 | 3 | 1 | 3 | 1 |
| 4 | 1 | 4 | 1 | 3 | 1 | 3 | 1 |
| 2 | 4 | 2 | 6 | 4 | 4 | 5 | 7 |
Επίσης με τη USING.
SELECT * FROM table_a RIGHT JOIN table_b USING(W);
| W | B_ID | B1 | B2 | A_ID | A1 | A2 |
|---|---|---|---|---|---|---|
| 2 | 1 | 5 | 2 | NULL | NULL | NULL |
| 5 | 2 | 3 | 1 | 1 | 1 | 2 |
| 1 | 3 | 1 | 3 | 4 | 1 | 4 |
| 7 | 4 | 4 | 5 | NULL | NULL | NULL |
Εναλλακτικά με NATURAL RIGHT JOIN
SELECT * FROM table_a NATURAL RIGHT JOIN table_b;
| W | B_ID | B1 | B2 | A_ID | A1 | A2 |
|---|---|---|---|---|---|---|
| 2 | 1 | 5 | 2 | NULL | NULL | NULL |
| 5 | 2 | 3 | 1 | 1 | 1 | 2 |
| 1 | 3 | 1 | 3 | 4 | 1 | 4 |
| 7 | 4 | 4 | 5 | NULL | NULL | NULL |
FULL JOIN
Η FULL JOIN είναι ένας συνδυασμός των δύο παραπάνω (LEFT JOIN και RIGHT JOIN) και επιστρέφει όλες τις εγγραφές που ικανοποιούν τη συνθήκη. Στα κενά κελιά μπαίνει το NULL.
SELECT * FROM table_a FULL JOIN table_b ON a1 = b1;
Επειδή η MySQL δεν υποστηρίζει την FULL JOIN μπορούμε να χρησιμοποιήσουμε την παρακάτω ισοδύναμη έκφραση:
SELECT * FROM table_a LEFT JOIN table_b ON a1 = b1 UNION SELECT * FROM table_a RIGHT JOIN table_b ON a1 = b1;
| A_ID | A1 | A2 | W | B_ID | B1 | B2 | W |
|---|---|---|---|---|---|---|---|
| 1 | 1 | 2 | 5 | 3 | 1 | 3 | 1 |
| 2 | 4 | 2 | 6 | 4 | 4 | 5 | 7 |
| 3 | 2 | 3 | 3 | NULL | NULL | NULL | NULL |
| 4 | 1 | 4 | 1 | 3 | 1 | 3 | 1 |
| NULL | NULL | NULL | NULL | 1 | 5 | 2 | 2 |
| NULL | NULL | NULL | NULL | 2 | 3 | 1 | 5 |
Επίσης για την παρακάτω έκφραση
SELECT * FROM table_a FULL JOIN table_b USING(W);
μπορούμε να χρησιμοποιήσουμε την ισοδύναμη
SELECT * FROM table_a LEFT JOIN table_b USING(w) UNION SELECT * FROM table_a RIGHT JOIN table_b USING(w);
| W | A_ID | A1 | A2 | B_ID | B1 | B2 |
|---|---|---|---|---|---|---|
| 5 | 1 | 1 | 2 | 2 | 3 | 1 |
| 6 | 2 | 4 | 2 | NULL | NULL | NULL |
| 3 | 3 | 2 | 3 | NULL | NULL | NULL |
| 1 | 4 | 1 | 4 | 3 | 1 | 3 |
| 2 | 1 | 5 | 2 | NULL | NULL | NULL |
| 5 | 2 | 3 | 1 | 1 | 1 | 2 |
| 1 | 3 | 1 | 3 | 4 | 1 | 4 |
| 7 | 4 | 4 | 5 | NULL | NULL | NULL |