SQLite Ένωση: Φυσικό Αριστερά Εξωτερικό, Εσωτερικό, Σταυρός με Πίνακες
Μάρκους Άλεν
⚡ Έξυπνη Σύνοψη
SQLite Οι όροι JOIN συνδυάζουν γραμμές από δύο ή περισσότερους πίνακες χρησιμοποιώντας τις συναρτήσεις INNER JOIN, JOIN USING, NATURAL JOIN, LEFT OUTER JOIN και CROSS JOIN, επιτρέποντάς σας να αντιστοιχίσετε σχετικές εγγραφές ανά κοινόχρηστες στήλες και να διαβάσετε δεδομένα σε μια κανονικοποιημένη βάση δεδομένων.
SQLite υποστηρίζει διαφορετικούς τύπους SQL Ενώσεις, όπως ΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ, ΑΡΙΣΤΕΡΗ ΕΞΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ και ΣΥΝΔΕΣΗ ΣΥΝΔΕΣΗΣ. Κάθε τύπος JOIN χρησιμοποιείται για διαφορετική κατάσταση όπως θα δούμε σε αυτό το σεμινάριο.
Εισαγωγή στην SQLite Ρήτρα ΣΥΜΜΕΤΟΧΗΣ
Όταν εργάζεστε σε μια βάση δεδομένων με πολλούς πίνακες, συχνά χρειάζεται να λαμβάνετε δεδομένα από αυτούς τους πολλούς πίνακες.
Με την ρήτρα JOIN, μπορείτε να συνδέσετε δύο ή περισσότερους πίνακες ή υποερωτήματα συνδέοντάς τους. Επίσης, μπορείτε να ορίσετε με ποια στήλη πρέπει να συνδέσετε τους πίνακες και με ποιες συνθήκες.
Οποιοσδήποτε όρος JOIN πρέπει να έχει την ακόλουθη σύνταξη:
Κάθε ρήτρα συμμετοχής περιέχει:
- Ένας πίνακας ή ένα υποερώτημα που είναι ο αριστερός πίνακας. τον πίνακα ή το υποερώτημα πριν από την ρήτρα σύνδεσης (στα αριστερά του).
- Χειριστής JOIN – καθορίστε τον τύπο σύνδεσης (είτε ΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ, ΑΡΙΣΤΕΡΗ ΕΞΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ ή CROSS JOIN).
- Περιορισμός JOIN – αφού καθορίσετε τους πίνακες ή τα δευτερεύοντα ερωτήματα προς ένταξη, πρέπει να καθορίσετε έναν περιορισμό σύνδεσης, ο οποίος θα είναι μια συνθήκη στην οποία θα επιλεγούν οι αντίστοιχες σειρές που ταιριάζουν με αυτήν τη συνθήκη, ανάλογα με τον τύπο ένωσης.
Σημειώστε ότι, για όλα τα παρακάτω SQLite Παραδείγματα πινάκων JOIN, πρέπει να εκτελέσετε το sqlite3.exe και να ανοίξετε μια σύνδεση με το δείγμα βάσης δεδομένων όπως ρέει:
Βήμα 1) Σε αυτό το βήμα, ανοίξτε τον "Ο Υπολογιστής μου" και μεταβείτε στον ακόλουθο κατάλογο "C:\sqlite" και, στη συνέχεια, ανοίξτε το "sqlite3.exe":
Βήμα 2) Ανοίξτε τη βάση δεδομένων "TutorialsSampleDB.db" με την ακόλουθη εντολή:
Τώρα είστε έτοιμοι να εκτελέσετε οποιοδήποτε τύπο ερωτήματος στη βάση δεδομένων.
SQLite ΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ
Η συνάρτηση INNER JOIN επιστρέφει μόνο τις γραμμές που ταιριάζουν με τη συνθήκη σύνδεσης και εξαλείφει όλες τις άλλες γραμμές που δεν ταιριάζουν με τη συνθήκη σύνδεσης.
Παράδειγμα
Στο ακόλουθο παράδειγμα, θα ενώσουμε τους δύο πίνακες "Φοιτητές" και "Τμήματα" με το DepartmentId για να λάβουμε το όνομα του τμήματος για κάθε φοιτητή, ως εξής:
SELECT Students.StudentName, Departments.DepartmentName FROM Students INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;
Επεξήγηση κώδικα
Το INNER JOIN λειτουργεί ως εξής:
- Στον όρο Επιλογή, μπορείτε να επιλέξετε όποιες στήλες θέλετε να επιλέξετε από τους δύο πίνακες που αναφέρονται.
- Ο όρος ΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ γράφεται μετά τον πρώτο πίνακα που αναφέρεται με τον όρο «Από».
- Στη συνέχεια, η συνθήκη σύνδεσης καθορίζεται με ON.
- Μπορούν να καθοριστούν ψευδώνυμα για πίνακες αναφοράς.
- Η λέξη ΕΣΩΤΕΡΙΚΟ είναι προαιρετική, μπορείτε απλώς να γράψετε JOIN.
Παραγωγή
Η ΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ παράγει τις εγγραφές τόσο από τους πίνακες των φοιτητών όσο και από τους πίνακες του τμήματος που ταιριάζουν με τη συνθήκη "Students.DepartmentId = Departments.DepartmentId". Οι γραμμές που δεν ταιριάζουν θα αγνοηθούν και δεν θα συμπεριληφθούν στο αποτέλεσμα.
Αυτός είναι ο λόγος για τον οποίο μόνο 8 φοιτητές από τους 10 φοιτητές επιστράφηκαν από αυτό το ερώτημα με τα τμήματα Πληροφορικής, Μαθηματικών και Φυσικής. Ενώ οι φοιτητές "Jena" και "George" δεν συμπεριλήφθηκαν, επειδή έχουν ένα μηδενικό αναγνωριστικό τμήματος, το οποίο δεν ταιριάζει με τη στήλη departmentId από τον πίνακα τμημάτων. Ως εξής:
SQLite ΕΓΓΡΑΦΕΙΤΕ… ΧΡΗΣΗ
Η ΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ μπορεί να γραφτεί χρησιμοποιώντας την ρήτρα "ΧΡΗΣΗ" για να αποφευχθεί ο πλεονασμός, επομένως αντί να γράψετε "ON Students.DepartmentId = Departments.DepartmentId", μπορείτε απλώς να γράψετε "USING(DepartmentID)".
Μπορείτε να χρησιμοποιήσετε το "JOIN .. USING" όποτε οι στήλες που θα συγκρίνετε στη συνθήκη σύνδεσης έχουν το ίδιο όνομα. Σε τέτοιες περιπτώσεις, δεν χρειάζεται να τα επαναλάβετε χρησιμοποιώντας την προϋπόθεση και απλώς αναφέρετε τα ονόματα των στηλών και SQLite θα το εντοπίσει.
Η διαφορά μεταξύ του INNER JOIN και του JOIN .. ΧΡΗΣΙΜΟΠΟΙΩΝΤΑΣ:
Με την επιλογή "JOIN … USING" δεν γράφετε μια συνθήκη σύνδεσης, απλώς γράφετε τη στήλη σύνδεσης που είναι κοινή μεταξύ των δύο συνδεδεμένων πινάκων, αντί να γράψουμε τον πίνακα1 "INNER JOIN table2 ON table1.cola = table2.cola", το γράφουμε ως "table1 JOIN table2 USING(cola)".
Παράδειγμα
Στο ακόλουθο παράδειγμα, θα ενώσουμε τους δύο πίνακες "Φοιτητές" και "Τμήματα" με το DepartmentId για να λάβουμε το όνομα του τμήματος για κάθε φοιτητή, ως εξής:
SELECT Students.StudentName, Departments.DepartmentName FROM Students INNER JOIN Departments USING(DepartmentId);
εξήγηση
- Σε αντίθεση με το προηγούμενο παράδειγμα, δεν γράψαμε "ON Students.DepartmentId = Departments.DepartmentId". Απλώς γράψαμε "USING(DepartmentId)".
- SQLite συμπεραίνει αυτόματα τη συνθήκη ένωσης και συγκρίνει το αναγνωριστικό τμήματος και από τους πίνακες – Μαθητές και Τμήματα.
- Μπορείτε να χρησιμοποιήσετε αυτήν τη σύνταξη κάθε φορά που οι δύο στήλες που συγκρίνετε είναι με το ίδιο όνομα.
Παραγωγή
Αυτό θα σας δώσει το ίδιο ακριβώς αποτέλεσμα με το προηγούμενο παράδειγμα:
SQLite ΦΥΣΙΚΗ ΣΥΝΔΕΣΗ
Μια ΦΥΣΙΚΗ ΣΥΝΔΕΣΗ είναι παρόμοια με μια ΣΥΝΔΕΣΗ…ΧΡΗΣΗ, η διαφορά είναι ότι ελέγχει αυτόματα για ισότητα μεταξύ των τιμών κάθε στήλης που υπάρχει και στους δύο πίνακες.
Η διαφορά μεταξύ ΕΣΩΤΕΡΙΚΗΣ ΣΥΝΔΕΣΗΣ και ΦΥΣΙΚΗΣ ΣΥΝΔΕΣΗΣ:
- Στην ΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ, πρέπει να καθορίσετε μια συνθήκη σύνδεσης την οποία χρησιμοποιεί η εσωτερική σύνδεση για να ενώσει τους δύο πίνακες. Ενώ στη φυσική σύνδεση, δεν γράφετε συνθήκη σύνδεσης. Απλώς γράφετε τα ονόματα των δύο πινάκων χωρίς καμία συνθήκη. Στη συνέχεια, η φυσική σύνδεση θα ελέγξει αυτόματα για ισότητα μεταξύ των τιμών για κάθε στήλη που υπάρχει και στους δύο πίνακες. Η φυσική σύνδεση συνάγει αυτόματα τη συνθήκη σύνδεσης.
- Στο NATURAL JOIN, όλες οι στήλες και από τους δύο πίνακες με το ίδιο όνομα θα αντιστοιχιστούν μεταξύ τους. Για παράδειγμα, αν έχουμε δύο πίνακες με δύο κοινά ονόματα στηλών (οι δύο στήλες υπάρχουν με το ίδιο όνομα στους δύο πίνακες), τότε η φυσική ένωση θα ενώσει τους δύο πίνακες συγκρίνοντας τις τιμές και των δύο στηλών και όχι μόνο από τη μία στήλη.
Παράδειγμα
SELECT Students.StudentName, Departments.DepartmentName FROM Students Natural JOIN Departments;
εξήγηση
- Δεν χρειάζεται να γράψουμε μια συνθήκη σύνδεσης με ονόματα στηλών (όπως κάναμε στην INNER JOIN). Δεν χρειαζόταν καν να γράψουμε το όνομα της στήλης μία φορά (όπως κάναμε στην JOIN USING).
- Η φυσική ένωση θα σαρώσει και τις δύο στήλες από τους δύο πίνακες. Θα ανιχνεύσει ότι η συνθήκη θα πρέπει να αποτελείται από σύγκριση DepartmentId και από τους δύο πίνακες Students και Departments.
Παραγωγή
Η ΦΥΣΙΚΗ ΣΥΝΔΕΣΗ θα σας δώσει την ίδια ακριβώς έξοδο με την έξοδο που λάβαμε από την ΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ και την ΣΥΝΔΕΣΗ ΧΡΗΣΙΜΟΠΟΙΩΝΤΑΣ παραδείγματα, επειδή στο παράδειγμά μας και τα τρία ερωτήματα είναι ισοδύναμα. Αλλά σε ορισμένες περιπτώσεις, η έξοδος θα είναι διαφορετική από την εσωτερική σύνδεση από ό,τι σε μια φυσική σύνδεση. Για παράδειγμα, εάν υπάρχουν περισσότεροι πίνακες με τα ίδια ονόματα, τότε η φυσική σύνδεση θα αντιστοιχίσει όλες τις στήλες μεταξύ τους. Ωστόσο, η εσωτερική σύνδεση θα αντιστοιχίσει μόνο τις στήλες στη συνθήκη σύνδεσης.
SQLite ΑΡΙΣΤΕΡΑ ΕΞΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ
Το πρότυπο SQL ορίζει τρεις τύπους OUTER JOIN: LEFT, RIGHT και FULL, αλλά SQLite υποστηρίζει μόνο τη φυσική ΑΡΙΣΤΕΡΑ ΕΞΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ.
Στην LEFT OUTER JOIN, όλες οι τιμές των στηλών που επιλέγετε από τον αριστερό πίνακα θα συμπεριληφθούν στο αποτέλεσμα του ερωτήματος, επομένως ανεξάρτητα από το αν η τιμή ταιριάζει με τη συνθήκη σύνδεσης ή όχι, θα συμπεριληφθεί στο αποτέλεσμα.
Έτσι, εάν ο αριστερός πίνακας έχει 'n' γραμμές, τα αποτελέσματα του ερωτήματος θα έχουν 'n' γραμμές. Ωστόσο, για τις τιμές των στηλών που προέρχονται από τον δεξιό πίνακα, εάν κάποια τιμή δεν ταιριάζει με τη συνθήκη σύνδεσης, θα περιέχει μια τιμή "null".
Έτσι, θα λάβετε έναν αριθμό σειρών που ισοδυναμεί με τον αριθμό των σειρών στην αριστερή ένωση. Έτσι, θα λάβετε τις αντίστοιχες σειρές και από τους δύο πίνακες (όπως τα αποτελέσματα ΕΣΩΤΕΡΙΚΗΣ ΣΥΝΔΕΣΗΣ), συν τις μη αντιστοιχισμένες σειρές από τον αριστερό πίνακα.
Παράδειγμα
Στο παρακάτω παράδειγμα, θα δοκιμάσουμε το “LEFT JOIN” για να ενώσει τους δύο πίνακες “Students” και “Departments”:
SELECT Students.StudentName, Departments.DepartmentName FROM Students -- this is the left table LEFT JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;
εξήγηση
- SQLite Η σύνταξη LEFT JOIN είναι η ίδια με την ΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ. γράφετε το LEFT JOIN μεταξύ των δύο πινάκων και, στη συνέχεια, η συνθήκη ένωσης έρχεται μετά τον όρο ON.
- Ο πρώτος πίνακας μετά την ρήτρα από είναι ο αριστερός πίνακας. Ενώ ο δεύτερος πίνακας που ορίζεται μετά το φυσικό LEFT JOIN είναι ο σωστός πίνακας.
- Η ρήτρα OUTER είναι προαιρετική. Το LEFT natural OUTER JOIN είναι το ίδιο με το LEFT JOIN.
Παραγωγή
Όπως μπορείτε να δείτε, περιλαμβάνονται όλες οι γραμμές από τον πίνακα μαθητών, οι οποίες είναι συνολικά 10 μαθητές. Ακόμα κι αν ο τέταρτος και τελευταίος μαθητής, η Τζένα και ο Τζορτζ, έχουν αναγνωριστικά τμήματος που δεν υπάρχουν στον πίνακα Τμήματα, περιλαμβάνονται κι αυτοί.
Και σε αυτές τις περιπτώσεις, η τιμή departmentName τόσο για την Jena όσο και για τον George θα είναι "null" επειδή ο πίνακας departments δεν έχει departmentName που να ταιριάζει με την τιμή departmentId.
Ας δώσουμε στο προηγούμενο ερώτημα χρησιμοποιώντας την αριστερή ένωση μια βαθύτερη εξήγηση χρησιμοποιώντας διαγράμματα Venn:
Η ΑΡΙΣΤΕΡΗ ΣΥΝΔΕΣΗ θα δώσει όλα τα ονόματα των φοιτητών από τον πίνακα φοιτητών, ακόμη και αν ο φοιτητής έχει αναγνωριστικό τμήματος που δεν υπάρχει στον πίνακα τμημάτων. Έτσι, το ερώτημα δεν θα σας δώσει μόνο τις αντίστοιχες γραμμές ως ΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ, αλλά θα σας δώσει το επιπλέον μέρος που περιέχει τις μη ταιριαστές γραμμές από τον αριστερό πίνακα, που είναι ο πίνακας φοιτητών.
Σημειώστε ότι οποιοδήποτε όνομα μαθητή δεν έχει αντίστοιχο τμήμα θα έχει τιμή "μηδενική" για το όνομα τμήματος, επειδή δεν υπάρχει αντίστοιχη τιμή για αυτό και αυτές οι τιμές είναι οι τιμές στις μη αντιστοιχισμένες σειρές.
SQLite ΕΓΓΡΑΦΕΙΤΕ ΣΤΑΥΡΟΣ
Ένα CROSS JOIN δίνει το καρτεσιανό γινόμενο για τις επιλεγμένες στήλες των δύο ενωμένων πινάκων, αντιστοιχίζοντας όλες τις τιμές από τον πρώτο πίνακα με όλες τις τιμές από τον δεύτερο πίνακα.
Έτσι, για κάθε τιμή στον πρώτο πίνακα, θα λάβετε αντιστοιχίες 'n' από τον δεύτερο πίνακα όπου n είναι ο αριθμός των σειρών του δεύτερου πίνακα.
Σε αντίθεση με την INNER JOIN και την LEFT OUTER JOIN, με την CROSS JOIN, δεν χρειάζεται να καθορίσετε μια συνθήκη σύνδεσης, επειδή SQLite δεν το χρειάζεται για τη διασταυρούμενη σύνδεση.
The SQLite θα έχει ως αποτέλεσμα ένα λογικό σύνολο αποτελεσμάτων που προκύπτει από τον συνδυασμό όλων των τιμών από τον πρώτο πίνακα με όλες τις τιμές από τον δεύτερο πίνακα.
Για παράδειγμα, εάν επιλέξατε μια στήλη από τον πρώτο πίνακα (colA) και μια άλλη στήλη από τον δεύτερο πίνακα (colB). Η στήλη A περιέχει δύο τιμές (1,2) και η στήλη B περιέχει επίσης δύο τιμές (3,4).
Τότε το αποτέλεσμα του CROSS JOIN θα είναι τέσσερις σειρές:
- Δύο σειρές συνδυάζοντας την πρώτη τιμή από το colA που είναι 1 με τις δύο τιμές του colB (3,4) που θα είναι (1,3), (1,4).
- Ομοίως, δύο σειρές συνδυάζοντας τη δεύτερη τιμή από το colA που είναι 2 με τις δύο τιμές του colB (3,4) που είναι (2,3), (2,4).
Παράδειγμα
Στο παρακάτω ερώτημα θα δοκιμάσουμε CROSS JOIN μεταξύ των πινάκων Φοιτητών και Τμημάτων:
SELECT Students.StudentName, Departments.DepartmentName FROM Students CROSS JOIN Departments;
εξήγηση
- Στο SQLite επιλέξτε από πολλούς πίνακες, απλώς επιλέξαμε δύο στήλες «όνομα μαθητή» από τον πίνακα μαθητών και το «Όνομα τμήματος» από τον πίνακα τμημάτων.
- Για τη διασταυρούμενη σύνδεση, δεν καθορίσαμε καμία συνθήκη σύνδεσης, απλώς τους δύο πίνακες σε συνδυασμό με την CROSS JOIN στη μέση τους.
Παραγωγή
Όπως μπορείτε να δείτε, το αποτέλεσμα είναι 40 σειρές. 10 τιμές από τον πίνακα μαθητών ταιριάζουν με τα 4 τμήματα από τον πίνακα τμημάτων. Ως εξής:
- Τέσσερις τιμές για τα τέσσερα τμήματα από τον πίνακα τμημάτων ταιριάζουν με τον πρώτο μαθητή Michel.
- Τέσσερις τιμές για τα τέσσερα τμήματα από τον πίνακα τμημάτων ταίριαζαν με τον δεύτερο μαθητή, τον Γιάννη.
- Τέσσερις τιμές για τα τέσσερα τμήματα από τον πίνακα τμημάτων ταίριαζαν με τον τρίτο μαθητή, τον Τζακ... και ούτω καθεξής.
