Hướng dẫn tham gia MySQL: INNER, OUTER, LEFT, RIGHT, CROSS

Anonim

THAM GIA là gì?

Tham gia giúp truy xuất dữ liệu từ hai hoặc nhiều bảng cơ sở dữ liệu. Các bảng có liên quan lẫn nhau bằng cách sử dụng khóa chính và khóa ngoài.

Lưu ý: JOIN là chủ đề bị hiểu lầm nhiều nhất trong số những người học SQL. Để đơn giản và dễ hiểu, chúng tôi sẽ sử dụng Cơ sở dữ liệu mới để thực hành mẫu. Như hình bên dưới

Tôi tên đầu tiên last_name movie_id
1 Adam thợ rèn 1
2 Ravi Kumar 2
3 Susan Davidson 5
4 cú chọc Adrianna số 8
6 Lee Pong 10
Tôi tiêu đề thể loại
1 ASSASSIN'S CREED: EMBERS Ảnh động
2 Thép thật (2012) Ảnh động
3 Alvin and the Chipmunks Ảnh động
4 Những cuộc phiêu lưu của Tin Tin Ảnh động
5 An toàn (2012) Hoạt động
6 Ngôi nhà an toàn (2012) Hoạt động
7 GIA 18+
số 8 Hạn cuối năm 2009 18+
9 Bức tranh bẩn thỉu 18+
10 Marley và tôi Tình cảm

Các loại liên kết

THAM GIA chéo

Cross JOIN là một dạng JOIN đơn giản nhất để khớp từng hàng từ một bảng cơ sở dữ liệu với tất cả các hàng của bảng khác.

Nói cách khác, nó cung cấp cho chúng tôi các kết hợp của mỗi hàng của bảng đầu tiên với tất cả các bản ghi trong bảng thứ hai.

Giả sử chúng ta muốn lấy tất cả các bản ghi thành viên so với tất cả các bản ghi phim, chúng ta có thể sử dụng đoạn mã hiển thị bên dưới để có được kết quả mong muốn.

SELECT * FROM `movies` CROSS JOIN `members`

Việc thực thi tập lệnh trên trong MySQL workbench cho chúng ta kết quả sau.

 

 

 

 id title   id first_name last_name movie_id 


 

 1 ASSASSIN'S CREED: EMBERS Animations 1 Adam Smith 1 


 

 1 ASSASSIN'S CREED: EMBERS Animations 2 Ravi Kumar 2 


 

 1 ASSASSIN'S CREED: EMBERS Animations 3 Susan Davidson 5 


 

 1 ASSASSIN'S CREED: EMBERS Animations 4 Jenny Adrianna 8 


 

 1 ASSASSIN'S CREED: EMBERS Animations 6 Lee Pong 10 


 

 2 Real Steel(2012) Animations 1 Adam Smith 1 


 

 2 Real Steel(2012) Animations 2 Ravi Kumar 2 


 

 2 Real Steel(2012) Animations 3 Susan Davidson 5 


 

 2 Real Steel(2012) Animations 4 Jenny Adrianna 8 


 

 2 Real Steel(2012) Animations 6 Lee Pong 10 


 

 3 Alvin and the Chipmunks Animations 1 Adam Smith 1 


 

 3 Alvin and the Chipmunks Animations 2 Ravi Kumar 2 


 

 3 Alvin and the Chipmunks Animations 3 Susan Davidson 5 


 

 3 Alvin and the Chipmunks Animations 4 Jenny Adrianna 8 


 

 3 Alvin and the Chipmunks Animations 6 Lee Pong 10 


 

 4 The Adventures of Tin Tin Animations 1 Adam Smith 1 


 

 4 The Adventures of Tin Tin Animations 2 Ravi Kumar 2 


 

 4 The Adventures of Tin Tin Animations 3 Susan Davidson 5 


 

 4 The Adventures of Tin Tin Animations 4 Jenny Adrianna 8 


 

 4 The Adventures of Tin Tin Animations 6 Lee Pong 10 


 

 5 Safe (2012) Action 1 Adam Smith 1 


 

 5 Safe (2012) Action 2 Ravi Kumar 2 


 

 5 Safe (2012) Action 3 Susan Davidson 5 


 

 5 Safe (2012) Action 4 Jenny Adrianna 8 


 

 5 Safe (2012) Action 6 Lee Pong 10 


 

 6 Safe House(2012) Action 1 Adam Smith 1 


 

 6 Safe House(2012) Action 2 Ravi Kumar 2 


 

 6 Safe House(2012) Action 3 Susan Davidson 5 


 

 6 Safe House(2012) Action 4 Jenny Adrianna 8 


 

 6 Safe House(2012) Action 6 Lee Pong 10 


 

 7 GIA 18+ 1 Adam Smith 1 


 

 7 GIA 18+ 2 Ravi Kumar 2 


 

 7 GIA 18+ 3 Susan Davidson 5 


 

 7 GIA 18+ 4 Jenny Adrianna 8 


 

 7 GIA 18+ 6 Lee Pong 10 


 

 8 Deadline(2009) 18+ 1 Adam Smith 1 


 

 8 Deadline(2009) 18+ 2 Ravi Kumar 2 


 

 8 Deadline(2009) 18+ 3 Susan Davidson 5 


 

 8 Deadline(2009) 18+ 4 Jenny Adrianna 8 


 

 8 Deadline(2009) 18+ 6 Lee Pong 10 


 

 9 The Dirty Picture 18+ 1 Adam Smith 1 


 

 9 The Dirty Picture 18+ 2 Ravi Kumar 2 


 

 9 The Dirty Picture 18+ 3 Susan Davidson 5 


 

 9 The Dirty Picture 18+ 4 Jenny Adrianna 8 


 

 9 The Dirty Picture 18+ 6 Lee Pong 10 


 

 10 Marley and me Romance 1 Adam Smith 1 


 

 10 Marley and me Romance 2 Ravi Kumar 2 


 

 10 Marley and me Romance 3 Susan Davidson 5 


 

 10 Marley and me Romance 4 Jenny Adrianna 8 


 

 10 Marley and me Romance 6 Lee Pong 10

THAM GIA INNER

JOIN bên trong được sử dụng để trả về các hàng từ cả hai bảng thỏa mãn điều kiện đã cho.

Giả sử, bạn muốn lấy danh sách các thành viên đã thuê phim cùng với tên phim mà họ đã thuê. Bạn có thể chỉ cần sử dụng INNER JOIN cho điều đó, nó trả về các hàng từ cả hai bảng thỏa mãn các điều kiện nhất định.

SELECT members.`first_name` , members.`last_name` , movies.`title`FROM members ,moviesWHERE movies.`id` = members.`movie_id`

Thực thi script trên give

 

 

 

 first_name last_name title 


 

 Adam Smith ASSASSIN'S CREED: EMBERS 


 

 Ravi Kumar Real Steel(2012) 


 

 Susan Davidson Safe (2012) 


 

 Jenny Adrianna Deadline(2009) 


 

 Lee Pong Marley and me

Lưu ý rằng kịch bản kết quả ở trên cũng có thể được viết như sau để đạt được kết quả tương tự.

SELECT A.`first_name` , A.`last_name` , B.`title`FROM `members`AS AINNER JOIN `movies` AS BON B.`id` = A.`movie_id`

THAM GIA bên ngoài

MySQL Outer JOINs trả về tất cả các bản ghi khớp từ cả hai bảng.

Nó có thể phát hiện các bản ghi không khớp trong bảng đã tham gia. Nó trả về giá trị NULL cho các bản ghi của bảng đã tham gia nếu không tìm thấy kết quả phù hợp nào.

Nghe có vẻ khó hiểu? Hãy xem xét một ví dụ -

CHỖ NỐI BÊN TRÁI

Giả sử bây giờ bạn muốn lấy tiêu đề của tất cả các phim cùng với tên của các thành viên đã thuê chúng. Rõ ràng là một số bộ phim đã không được thuê bởi bất kỳ ai. Chúng ta chỉ có thể sử dụng LEFT JOIN cho mục đích.

LEFT JOIN trả về tất cả các hàng từ bảng bên trái ngay cả khi không tìm thấy hàng nào phù hợp trong bảng bên phải. Trường hợp không tìm thấy kết quả phù hợp nào trong bảng bên phải, NULL được trả về.

SELECT A.`title` , B.`first_name` , B.`last_name`FROM `movies` AS ALEFT JOIN `members` AS BON B.`movie_id` = A.`id`

Bạn có thể thấy rằng trong kết quả trả về được liệt kê bên dưới, đối với các phim không được thuê, các trường tên thành viên có giá trị NULL. Điều đó có nghĩa là không có thành viên phù hợp nào được tìm thấy bảng thành viên cho bộ phim cụ thể đó.

 

 

 

 title first_name last_name 


 

 ASSASSIN'S CREED: EMBERS Adam Smith 


 

 Real Steel(2012) Ravi Kumar 


 

 Safe (2012) Susan Davidson 


 

 Deadline(2009) Jenny Adrianna 


 

 Marley and me Lee Pong 


 

 Alvin and the Chipmunks NULL NULL 


 

 The Adventures of Tin Tin NULL NULL 


 

 Safe House(2012) NULL NULL 


 

 GIA NULL NULL 


 

 The Dirty Picture NULL NULL 


 

 


Note: Null is returned for non-matching rows on right

THAM GIA ĐÚNG

RIGHT JOIN rõ ràng là đối lập với LEFT JOIN. RIGHT JOIN trả về tất cả các cột từ bảng bên phải ngay cả khi không tìm thấy hàng nào phù hợp trong bảng bên trái. Trường hợp không tìm thấy kết quả phù hợp nào trong bảng bên trái, NULL được trả về.

Trong ví dụ của chúng tôi, giả sử rằng bạn cần lấy tên của các thành viên và phim do họ thuê. Bây giờ chúng tôi có một thành viên mới chưa thuê bất kỳ bộ phim nào

SELECT A.`first_name` , A.`last_name`, B.`title`FROM `members` AS ARIGHT JOIN `movies` AS BON B.`id` = A.`movie_id`

Thực thi tập lệnh trên trong MySQL workbench cho kết quả sau.

 

 

 

 first_name last_name title 


 

 Adam Smith ASSASSIN'S CREED: EMBERS 


 

 Ravi Kumar Real Steel(2012) 


 

 Susan Davidson Safe (2012) 


 

 Jenny Adrianna Deadline(2009) 


 

 Lee Pong Marley and me 


 

 NULL NULL Alvin and the Chipmunks 


 

 NULL NULL The Adventures of Tin Tin 


 

 NULL NULL Safe House(2012) 


 

 NULL NULL GIA 


 

 NULL NULL The Dirty Picture 


 

 


Note: Null is returned for non-matching rows on left

Mệnh đề "ON" và "USING"

Trong các ví dụ truy vấn JOIN ở trên, chúng tôi đã sử dụng mệnh đề ON để so khớp các bản ghi giữa bảng.

Mệnh đề USING cũng có thể được sử dụng cho mục đích tương tự. Sự khác biệt với USING là nó cần phải có tên giống nhau cho các cột phù hợp trong cả hai bảng.

Trong bảng "phim" cho đến nay chúng tôi đã sử dụng khóa chính của nó với tên "id". Chúng tôi đề cập đến cùng một trong bảng "thành viên" với tên "movie_id".

Hãy đổi tên bảng "phim" trường "id" để có tên "phim_id". Chúng tôi làm điều này để có các tên trường phù hợp giống hệt nhau.

ALTER TABLE `movies` CHANGE `id` `movie_id` INT( 11 ) NOT NULL AUTO_INCREMENT;

Tiếp theo, hãy sử dụng USING với ví dụ LEFT JOIN ở trên.

SELECT A.`title` , B.`first_name` , B.`last_name`FROM `movies` AS ALEFT JOIN `members` AS BUSING ( `movie_id` )

Ngoài việc sử dụng ON và USING với JOIN, bạn có thể sử dụng nhiều mệnh đề MySQL khác như GROUP BY, WHERE và thậm chí các hàm như SUM , AVG , v.v.

Tại sao chúng ta nên sử dụng các phép nối?

Bây giờ bạn có thể nghĩ, tại sao chúng tôi sử dụng JOIN khi chúng tôi có thể thực hiện cùng một tác vụ khi chạy các truy vấn. Đặc biệt nếu bạn có một số kinh nghiệm về lập trình cơ sở dữ liệu, bạn biết rằng chúng ta có thể chạy từng truy vấn một, sử dụng đầu ra của từng truy vấn liên tiếp. Tất nhiên, điều đó là có thể. Nhưng bằng cách sử dụng JOIN, bạn có thể hoàn thành công việc bằng cách chỉ sử dụng một truy vấn với bất kỳ tham số tìm kiếm nào. Mặt khác, MySQL có thể đạt được hiệu suất tốt hơn với JOIN vì nó có thể sử dụng Lập chỉ mục. Chỉ cần sử dụng một truy vấn JOIN thay vì chạy nhiều truy vấn sẽ làm giảm chi phí máy chủ. Thay vào đó, việc sử dụng nhiều truy vấn dẫn đến việc truyền nhiều dữ liệu hơn giữa MySQL và các ứng dụng (phần mềm). Hơn nữa, nó cũng đòi hỏi nhiều thao tác dữ liệu hơn trong phần cuối của ứng dụng.

Rõ ràng là chúng ta có thể đạt được hiệu suất ứng dụng và MySQL tốt hơn bằng cách sử dụng JOIN.

Tóm lược

  • JOINS cho phép chúng tôi kết hợp dữ liệu từ nhiều bảng thành một tập kết quả duy nhất.
  • JOINS có hiệu suất tốt hơn so với các truy vấn phụ
  • INNER JOINS chỉ trả về các hàng đáp ứng các tiêu chí đã cho.
  • OUTER JOINS cũng có thể trả về các hàng không tìm thấy kết quả phù hợp nào. Các hàng chưa khớp được trả về với từ khóa NULL.
  • Các loại JOIN chính bao gồm Bên trong, Bên ngoài Trái, Bên phải Bên ngoài, Cross JOINS, v.v.
  • Mệnh đề thường được sử dụng trong các hoạt động JOIN là "ON". Mệnh đề "USING" yêu cầu các cột khớp phải có cùng tên.
  • JOINS cũng có thể được sử dụng trong các mệnh đề khác như GROUP BY, WHERE, SUB QUERIES, AGGREGATE FUNCTIONS, v.v.