SQLite Join: Natural Left Outer, Inner, Cross with Table Ví dụ
Marcus Allen
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) Trong bước này,
- Mở My Computer và điều hướng đến thư mục sau “C:\sqlite"Và
- Sau đó mở “sqlite3.exe":
Bước 2) Mở cơ sở dữ liệu “Hướng dẫnSampleDB.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
INNER JOIN chỉ trả về các hàng phù hợp với điều kiện nối và loại bỏ tất cả các hàng khác không khớp với điều kiện nối.
Ví dụ
Trong ví dụ sau, chúng ta sẽ nối hai bảng “Sinh viên"Và"Sở” với DepartmentId để lấy tên khoa của từng 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
- INNER JOIN tạo ra các bản ghi từ cả hai – bảng của sinh viên và của bộ môn phù hợp với điều kiện “Students.DepartmentId = Department.DepartmentId “. Các hàng không khớp sẽ bị bỏ qua và không được đưa vào kết quả.
- Đó là lý do tại sao chỉ có 8 sinh viên trong số 10 sinh viên được trả về từ truy vấn này với các khoa CNTT, toán và vật lý. Trong khi đó, các sinh viên “Jena” và “George” không được đưa vào vì họ có mã khoa rỗng, không khớp với cột departmentId từ bảng department. 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 viết điều kiện nối mà chỉ viết cột nối chung giữa hai bảng đã nối, thay vì viết bảng1 “INNER JOIN table2 ON table1.cola = table2.cola” chúng ta viết nó giống như “bảng1 THAM GIA bảng2 SỬ DỤNG(cola)”.
Ví dụ
Trong ví dụ sau, chúng ta sẽ nối hai bảng “Sinh viên"Và"Sở” với DepartmentId để lấy tên khoa của từng sinh viên như sau:
SELECT Students.StudentName, Departments.DepartmentName FROM Students INNER JOIN Departments USING(DepartmentId);
Giải thích
- Không giống như ví dụ trước, chúng tôi không viết “TRÊN Sinh viên.DepartmentId = Departments.DepartmentId“. Chúng tôi vừa viết “SỬ DỤNG(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 nối mà phép nối bên trong sử dụng để nối hai bảng. Trong khi đó, trong phép nối tự nhiên, bạn không viết điều kiện nối. Bạn chỉ cần viết tên hai bảng mà không cần điều kiện gì. Sau đó, phép nối tự nhiên 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. Tham gia tự nhiên tự động suy ra điều kiện tham gia.
- 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 viết điều kiện nối với tên cột (như chúng ta đã làm trong INNER JOIN). Chúng tôi thậm chí không cần phải viết tên cột một lần (giống như chúng tôi đã làm trong THAM GIA SỬ DỤNG).
- 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
- Natural JOIN sẽ cung cấp cho bạn cùng một đầu ra chính xác như đầu ra mà 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. Nhưng trong một số trường hợp, đầu ra sẽ khác với inner join so với natural join. Ví dụ, nếu có nhiều bảng có cùng tên, thì natural join sẽ khớp tất cả các cột với nhau. Tuy nhiên, inner join sẽ chỉ khớp các cột trong điều kiện join (chi tiết hơn ở phần tiếp theo; sự khác biệt giữa inner join và natural join).
SQLite THAM GIA BÊN NGOÀI TRÁI
Tiêu chuẩn SQL xác định ba loại THAM GIA NGOÀI: TRÁI, PHẢI và ĐẦY ĐỦ nhưng SQLite chỉ hỗ trợ LEFT OUTER JOIN tự nhiên.
Trong LEFT OUTER JOIN, tất cả giá trị của các cột bạn chọn từ bảng bên trái sẽ được đưa vào kết quả của truy vấn, do đó, bất kể giá trị có khớp với điều kiện nối hay không, nó sẽ được đưa vào kết quả.
Vì vậy, nếu bảng bên trái có 'n' hàng thì kết quả truy vấn 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 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 có thể thấy, tất cả các hàng trong 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à cuối cùng, Jena và George DepartmentIds không tồn tại trong bảng Departments thì chúng cũng được đưa vào.
- Và trong những trường hợp này, giá trị DepartmentName cho cả Jena và George sẽ là “null” vì bảng các phòng ban không có DepartmentName khớp với giá trị DepartmentId của chúng.
Hãy giải thích sâu hơn cho truy vấn trước đó bằng cách sử dụng phép nối bên trái bằng sơ đồ Van:
LEFT JOIN sẽ cung cấp tất cả tên sinh viên từ bảng sinh viên ngay cả khi sinh viên đó có id khoa không tồn tại trong bảng khoa. Vì vậy, truy vấn sẽ không chỉ cung cấp cho bạn các hàng phù hợp dưới dạng INNER JOIN mà sẽ cung cấp cho bạn phần bổ sung có các hàng không khớp từ bảng bên trái là bảng sinh viên.
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ông giống như INNER JOIN và LEFT OUTER JOIN, với CROSS JOIN, bạn không cần chỉ định điều kiện nối, bởi vì SQLite không cần nó cho CROSS JOIN.
Sản phẩm SQLite sẽ dẫn đến 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 đầu tiên 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 đầu tiên (colA) và một cột khác từ bảng thứ hai (colB). ColA chứa hai giá trị (1,2) và 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ỉ có hai bảng được kết hợp với CROSS JOIN ở 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ị cho Bốn phòng ban từ bảng phòng ban khớp với sinh viên thứ hai John.
- Bốn giá trị cho Bốn khoa từ bảng khoa khớp với sinh viên thứ ba Jack… v.v.
Tổng kết
Sử dụng SQLite THAM GIA truy vấn, bạn có thể liên kết một hoặc nhiều bảng hoặc truy vấn con với nhau để chọn các cột từ cả hai bảng hoặc truy vấn con.
