SQLite Kết nối: Bên trái tự nhiên, Bên trong, Kết nối chéo với bảng
Marcus Allen
⚡ Tóm tắt thông minh
SQLite Mệnh đề JOIN kết hợp các hàng từ hai hoặc nhiều bảng bằng cách sử dụng INNER JOIN, JOIN USING, NATURAL JOIN, LEFT OUTER JOIN và CROSS JOIN, cho phép bạn ghép các bản ghi liên quan theo các cột chung và đọc dữ liệu trên toàn bộ cơ sở dữ liệu đã được chuẩn hóa.
SQLite hỗ trợ các loại khác nhau của SQL Tham gia, như INNER JOIN, LEFT OUTER JOIN và CROSS JOIN. Mỗi loại THAM GIA được sử dụng cho một tình huống khác nhau như chúng ta sẽ thấy trong hướng dẫn này.
Giới thiệu về SQLite Điều khoản THAM GIA
Khi làm việc trên cơ sở dữ liệu có nhiều bảng, bạn thường cần lấy dữ liệu từ nhiều bảng này.
Với mệnh đề THAM GIA, bạn có thể liên kết hai hoặc nhiều bảng hoặc truy vấn con bằng cách nối chúng. Ngoài ra, bạn có thể xác định cột nào bạn cần liên kết các bảng và theo điều kiện nào.
Bất kỳ mệnh đề JOIN nào cũng phải có cú pháp sau:
Mỗi mệnh đề nối có chứa:
- Một bảng hoặc truy vấn con là bảng bên trái; bảng hoặc truy vấn con trước mệnh đề nối (ở bên trái của nó).
- Toán tử THAM GIA – chỉ định loại liên kết (INNER JOIN, LEFT OUTER JOIN hoặc CROSS JOIN).
- Ràng buộc tham gia - sau khi bạn chỉ định các bảng hoặc truy vấn con để tham gia, bạn cần chỉ định một ràng buộc tham gia, đây sẽ là điều kiện mà các hàng khớp với điều kiện đó sẽ được chọn tùy thuộc vào loại tham gia.
Lưu ý rằng, đối với tất cả những điều sau đây SQLite Ví dụ về bảng JOIN, bạn phải chạy sqlite3.exe và mở kết nối tới cơ sở dữ liệu mẫu như sau:
Bước 1) Ở bước này, hãy mở My Computer và điều hướng đến thư mục “C:\sqlite”, sau đó mở tệp “sqlite3.exe“:
Bước 2) Mở cơ sở dữ liệu “TutorialsSampleDB.db” bằng lệnh sau:
Bây giờ bạn đã sẵn sàng chạy bất kỳ loại truy vấn nào trên cơ sở dữ liệu.
SQLite INNER JOIN
Phép INNER JOIN chỉ trả về các hàng thỏa mãn điều kiện kết nối và loại bỏ tất cả các hàng khác không thỏa mãn điều kiện kết nối.
Ví dụ
Trong ví dụ sau, chúng ta sẽ kết hợp hai bảng “Students” và “Departments” bằng DepartmentId để lấy tên khoa/bộ môn cho mỗi sinh viên, như sau:
SELECT Students.StudentName, Departments.DepartmentName FROM Students INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;
Giải thích mã
INNER JOIN hoạt động như sau:
- Trong mệnh đề Select, bạn có thể chọn bất kỳ cột nào bạn muốn từ hai bảng được tham chiếu.
- Mệnh đề INNER JOIN được viết sau bảng đầu tiên được tham chiếu bằng mệnh đề “From”.
- Sau đó, điều kiện nối được chỉ định bằng BẬT.
- Bí danh có thể được chỉ định cho các bảng được tham chiếu.
- Từ INNER là tùy chọn, bạn chỉ cần viết THAM GIA.
Đầu ra
Phép nối INNER JOIN sẽ lấy các bản ghi từ cả hai bảng – bảng sinh viên và bảng phòng ban – khớp với điều kiện “Students.DepartmentId = Departments.DepartmentId”. Các hàng không khớp sẽ bị bỏ qua và không được bao gồm trong kết quả.
Đó là lý do tại sao chỉ có 8 sinh viên trong số 10 sinh viên thuộc các khoa CNTT, toán học và vật lý được trả về từ truy vấn này. Trong khi đó, sinh viên “Jena” và “George” không được bao gồm, vì họ có ID khoa là null, không khớp với cột departmentId trong bảng departments. Cụ thể như sau:
SQLite THAM GIA… SỬ DỤNG
INNER JOIN có thể được viết bằng mệnh đề “USING” để tránh dư thừa, vì vậy thay vì viết “ON Students.DepartmentId = Departments.DepartmentId”, bạn chỉ cần viết “USING(DepartmentID)”.
Bạn có thể sử dụng “THAM GIA .. SỬ DỤNG” bất cứ khi nào các cột bạn sẽ so sánh trong điều kiện nối có cùng tên. Trong những trường hợp như vậy, không cần lặp lại chúng bằng điều kiện on và chỉ cần nêu tên cột và SQLite sẽ phát hiện ra điều đó.
Sự khác biệt giữa INNER JOIN và JOIN... SỬ DỤNG:
Với “JOIN … USING”, bạn không cần viết điều kiện kết nối, mà chỉ cần viết cột chung giữa hai bảng được kết nối. Thay vì viết table1 “INNER JOIN table2 ON table1.cola = table2.cola”, chúng ta viết như sau: “table1 JOIN table2 USING(cola)”.
Ví dụ
Trong ví dụ sau, chúng ta sẽ kết hợp hai bảng “Students” và “Departments” bằng DepartmentId để lấy tên khoa/bộ môn cho mỗi sinh viên, như sau:
SELECT Students.StudentName, Departments.DepartmentName FROM Students INNER JOIN Departments USING(DepartmentId);
Giải thích
- Khác với ví dụ trước, chúng ta không viết “ON Students.DepartmentId = Departments.DepartmentId“. Chúng ta chỉ viết “USING(DepartmentId)“.
- SQLite tự động suy ra điều kiện nối và so sánh DepartmentId từ cả hai bảng – Students và Departments.
- Bạn có thể sử dụng cú pháp này bất cứ khi nào hai cột bạn đang so sánh có cùng tên.
Đầu ra
Điều này sẽ cho bạn kết quả chính xác giống như ví dụ trước:
SQLite THAM GIA TỰ NHIÊN
THAM GIA TỰ NHIÊN tương tự như THAM GIA…USE, điểm khác biệt là nó tự động kiểm tra sự bằng nhau giữa các giá trị của mọi cột tồn tại trong cả hai bảng.
Sự khác biệt giữa INNER THAM GIA và THAM GIA TỰ NHIÊN:
- Trong INNER JOIN, bạn phải chỉ định điều kiện kết nối mà INNER JOIN sử dụng để kết nối hai bảng. Trong khi đó, với Natural JOIN, bạn không cần viết điều kiện kết nối. Bạn chỉ cần viết tên của hai bảng mà không cần bất kỳ điều kiện nào. Sau đó, Natural JOIN sẽ tự động kiểm tra sự bằng nhau giữa các giá trị cho mỗi cột tồn tại trong cả hai bảng. Natural JOIN tự động suy ra điều kiện kết nối.
- Trong THAM GIA TỰ NHIÊN, tất cả các cột từ cả hai bảng có cùng tên sẽ được khớp với nhau. Ví dụ: nếu chúng ta có hai bảng có hai tên cột chung (hai cột tồn tại cùng tên trong hai bảng), thì phép nối tự nhiên sẽ nối hai bảng bằng cách so sánh giá trị của cả hai cột chứ không chỉ từ một cột. cột.
Ví dụ
SELECT Students.StudentName, Departments.DepartmentName FROM Students Natural JOIN Departments;
Giải thích
- Chúng ta không cần phải viết điều kiện kết nối với tên cột (như chúng ta đã làm trong INNER JOIN). Chúng ta thậm chí không cần phải viết tên cột một lần nào (như chúng ta đã làm trong JOIN USING).
- Phép nối tự nhiên sẽ quét cả hai cột từ hai bảng. Nó sẽ phát hiện ra rằng điều kiện phải bao gồm việc so sánh DepartmentId từ cả hai bảng Sinh viên và Phòng ban.
Đầu ra
Phép NATURAL JOIN sẽ cho ra kết quả chính xác giống như kết quả chúng ta nhận được từ các ví dụ INNER JOIN và JOIN USING, bởi vì trong ví dụ của chúng ta, cả ba truy vấn đều tương đương nhau. Tuy nhiên, trong một số trường hợp, kết quả của inner join sẽ khác với natural join. Ví dụ, nếu có nhiều bảng có cùng tên, thì natural join sẽ so khớp tất cả các cột với nhau. Tuy nhiên, inner join chỉ so khớp các cột trong điều kiện join.
SQLite THAM GIA BÊN NGOÀI TRÁI
Tiêu chuẩn SQL định nghĩa ba loại phép nối ngoài (OUTER JOIN): LEFT, RIGHT và FULL, nhưng SQLite chỉ hỗ trợ LEFT OUTER JOIN tự nhiên.
Trong LEFT OUTER JOIN, tất cả các giá trị của các cột bạn chọn từ bảng bên trái sẽ được bao gồm trong kết quả truy vấn, vì vậy bất kể giá trị đó có khớp với điều kiện kết nối hay không, nó vẫn sẽ được bao gồm trong kết quả.
Vì vậy, nếu bảng bên trái có 'n' hàng, kết quả truy vấn cũng sẽ có 'n' hàng. Tuy nhiên, đối với các giá trị của các cột đến từ bảng bên phải, nếu bất kỳ giá trị nào không khớp với điều kiện kết nối thì nó sẽ chứa giá trị "null".
Vì vậy, bạn sẽ nhận được số hàng tương đương với số hàng ở phép nối bên trái. Vì vậy, bạn sẽ nhận được các hàng khớp từ cả hai bảng (như kết quả INNER JOIN), cộng với các hàng không khớp từ bảng bên trái.
Ví dụ
Trong ví dụ sau, chúng ta sẽ thử “LEFT JOIN” để nối hai bảng “Students” và “Departments”:
SELECT Students.StudentName, Departments.DepartmentName FROM Students -- this is the left table LEFT JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;
Giải thích
- SQLite Cú pháp LEFT JOIN giống như INNER JOIN; bạn viết LEFT THAM GIA giữa hai bảng và sau đó điều kiện nối sẽ xuất hiện sau mệnh đề ON.
- Bảng đầu tiên sau mệnh đề from là bảng bên trái. Trong khi bảng thứ hai được chỉ định sau LEFT JOIN tự nhiên là bảng bên phải.
- Mệnh đề OUTER là tùy chọn; LEFT tự nhiên OUTER JOIN cũng giống như LEFT JOIN.
Đầu ra
Như bạn thấy, tất cả các hàng từ bảng sinh viên đều được bao gồm, tổng cộng có 10 sinh viên. Ngay cả khi sinh viên thứ tư và sinh viên cuối cùng, Jena và George, có departmentIds không tồn tại trong bảng Departments, họ vẫn được bao gồm.
Trong những trường hợp này, giá trị departmentName của cả Jena và George sẽ là “null” vì bảng departments không có departmentName nào trùng khớp với giá trị departmentId của họ.
Chúng ta hãy cùng tìm hiểu sâu hơn về truy vấn trước đó sử dụng phép nối trái (left join) bằng sơ đồ Venn:
Phép nối LEFT JOIN sẽ trả về tất cả tên sinh viên từ bảng students, ngay cả khi sinh viên đó có mã khoa không tồn tại trong bảng departments. Vì vậy, truy vấn sẽ không chỉ trả về các hàng khớp như INNER JOIN, mà còn trả về phần bổ sung chứa các hàng không khớp từ bảng bên trái, tức là bảng students.
Lưu ý rằng bất kỳ tên sinh viên nào không có khoa trùng khớp sẽ có giá trị “null” cho tên khoa, vì không có giá trị khớp nào cho tên đó và các giá trị đó là giá trị trong các hàng không khớp.
SQLite THAM GIA
CROSS JOIN cung cấp tích Descartes cho các cột đã chọn của hai bảng đã nối, bằng cách khớp tất cả các giá trị từ bảng đầu tiên với tất cả các giá trị từ bảng thứ hai.
Vì vậy, với mọi giá trị trong bảng đầu tiên, bạn sẽ nhận được 'n' kết quả khớp từ bảng thứ hai trong đó n là số hàng của bảng thứ hai.
Khác với INNER JOIN và LEFT OUTER JOIN, với CROSS JOIN, bạn không cần phải chỉ định điều kiện kết nối, bởi vì SQLite Không cần đến nó cho phép CROSS JOIN.
SQLite Việc này sẽ tạo ra một tập hợp kết quả logic bằng cách kết hợp tất cả các giá trị từ bảng thứ nhất với tất cả các giá trị từ bảng thứ hai.
Ví dụ, nếu bạn chọn một cột từ bảng thứ nhất (colA) và một cột khác từ bảng thứ hai (colB). Cột colA chứa hai giá trị (1,2) và cột colB cũng chứa hai giá trị (3,4).
Khi đó kết quả của CROSS JOIN sẽ là bốn hàng:
- Hai hàng bằng cách kết hợp giá trị đầu tiên từ colA là 1 với hai giá trị của colB (3,4) sẽ là (1,3), (1,4).
- Tương tự như vậy, hai hàng bằng cách kết hợp giá trị thứ hai từ colA là 2 với hai giá trị của colB (3,4) là (2,3), (2,4).
Ví dụ
Trong truy vấn sau, chúng ta sẽ thử CROSS JOIN giữa các bảng Students và Departments:
SELECT Students.StudentName, Departments.DepartmentName FROM Students CROSS JOIN Departments;
Giải thích
- Trong tạp chí SQLite chọn từ nhiều bảng, chúng tôi chỉ chọn hai cột “tên sinh viên” từ bảng sinh viên và “Tên bộ phận” từ bảng bộ phận.
- Đối với phép nối chéo, chúng tôi không chỉ định bất kỳ điều kiện nối nào mà chỉ đơn giản là kết hợp hai bảng bằng phép nối chéo ở giữa chúng.
Đầu ra
Như bạn có thể thấy, kết quả là 40 hàng; 10 giá trị từ bảng students khớp với 4 department từ bảng department. Như sau:
- Bốn giá trị của bốn khoa trong bảng khoa khớp với sinh viên đầu tiên Michel.
- Bốn giá trị từ bốn khoa trong bảng khoa trùng khớp với sinh viên thứ hai tên John.
- Bốn giá trị cho bốn khoa từ bảng khoa được đối chiếu với sinh viên thứ ba tên Jack… và cứ thế tiếp tục.
