MySQL Nối: Bên trong, Bên ngoài, Bên trái, Bên phải, Chéo

THAM GIA là gì?

Tham gia giúp lấy 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 sai nhiều nhất trong số những người học SQL. Để đơn giản và dễ hiểu hơn, chúng ta sẽ sử dụng một cơ sở dữ liệu mới làm ví dụ thực hành. Như hình bên dưới

Mỗi ví dụ dưới đây đều sử dụng hai bảng này. id phim cột trong thành viên chỉ vào id cột trong phim — Mối quan hệ mà mỗi lệnh JOIN khớp với nhau.

thành viên

phim

Tại sao chúng ta nên sử dụng JOIN?

Trước khi xem xét từng loại JOIN, điều đáng hiểu là tại sao JOIN lại được ưu tiên hơn so với việc chạy nhiều truy vấn riêng lẻ.

Bây giờ bạn có thể nghĩ, tại sao chúng ta sử dụng THAM GIA khi chúng ta có thể thực hiện cùng một tác vụ khi chạy 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 kết quả của từng truy vấn trong các truy vấn liên tiếp. Tất nhiên, điều đó là có thể. Nhưng khi sử dụng THAM GIA, 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 THAM GIA vì nó có thể sử dụng Lập chỉ mục. Chỉ cần sử dụng một truy vấn THAM GIA thay vì chạy nhiều truy vấn sẽ giảm chi phí hoạt động của máy chủ. Thay vào đó, việc sử dụng nhiều truy vấn sẽ dẫn đến nhiều lần truyền dữ liệu hơn giữa MySQL và ứ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 ở cuối ứng dụng.

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

Các loại phép nối

MySQL Thư viện này hỗ trợ nhiều loại JOIN, mỗi loại trả lời một câu hỏi khác nhau về cùng hai bảng. Bảng dưới đây so sánh chúng; mỗi loại sau đó được minh họa bằng một truy vấn và kết quả đầu ra của nó.

THAM GIA

Cross THAM GIA là một dạng THAM GIA đơ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 một bảng khác.

Nói cách khác, nó cho chúng ta sự kết hợp của từng 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 tập lệnh hiển thị bên dưới để nhận được kết quả mong muốn.

SELECT * FROM `movies` CROSS JOIN `members`

Thực thi đoạn script trên trong MySQL bàn làm việc 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

INNER JOIN

Phép nối chéo (CROSS JOIN) trả về mọi cặp có thể, điều này hiếm khi là điều bạn muốn. Phép nối trong (INNER JOIN) thu hẹp kết quả xuống chỉ còn những cặp thực sự có liên quan.

THAM GIA 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 tiêu đề các bộ phim họ đã thuê. Bạn có thể sử dụng INNER JOIN để thực hiện điều đó, thao tác này sẽ trả về các hàng từ cả hai bảng thỏa mãn các điều kiện đã cho.

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

Thực thi đoạn script trên cho

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 ý tập lệnh 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 A
INNER JOIN `movies` AS B
ON B.`id` = A.`movie_id`

THAM GIA bên ngoài

Phép INNER JOIN âm thầm loại bỏ các hàng không có hàng đối tác. Khi các hàng không có hàng đối tác đó quan trọng, phép OUTER JOIN là lựa chọn phù hợp.

MySQL Phép nối ngoài (Outer JOIN) 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 đã nối. Nó trở lại NULL giá trị cho các bản ghi của bảng đã nối nếu không tìm thấy kết quả khớp.

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

LEFT JOIN

Giả sử bây giờ bạn muốn lấy tiêu đề của tất cả các bộ 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 chưa được ai thuê. Chúng ta có thể đơn giản 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. Khi không tìm thấy kết quả trùng khớp nào trong bảng bên phải, NULL sẽ được trả về.

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

Thực thi đoạn script trên trong MySQL Workbench cung cấp kết quả như sau: Bạn có thể thấy trong kết quả trả về được liệt kê bên dưới rằng đối với các phim chưa được thuê, trường tên thành viên có giá trị NULL. Điều đó có nghĩa là không tìm thấy thành viên nào phù hợp trong 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. Khi không tìm thấy kết quả trùng khớp nào trong bảng bên trái, NULL sẽ đượ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 ta có thành viên mới chưa thuê phim nào

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

Thực thi đoạn script trên trong MySQL workbench đưa ra 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 đề “BẬT” và “SỬ DỤNG”

Cho đến nay, mọi truy vấn đều khớp với các hàng có mệnh đề ON. MySQL Phương án này cung cấp cách viết ngắn gọn hơn khi các cột khớp nhau có cùng tên.

Trong các ví dụ về truy vấn THAM GIA ở trên, chúng tôi đã sử dụng mệnh đề ON để khớp các bản ghi giữa các 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 SỬ DỤNG Là nó cần phải có tên giống nhau cho các cột trùng khớ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 bảng “thành viên” với tên “movie_id”.

Hãy đổi tên trường “phim” bảng “id” để có tên “movie_id”. Chúng tôi làm điều này để có tên trường trùng khớ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 A
LEFT JOIN `members` AS B
USING ( `movie_id` )

Ngoài việc sử dụng ON và SỬ DỤNG với THAM GIA bạn có thể sử dụng nhiều thứ khác MySQL mệnh đề như NHÓM THEO, Ở ĐÂU và thậm chí các chức năng như TÓM TẮT, AVG, Vv

Câu Hỏi Thường Gặp