MySQL Hướng dẫn THAM GIA: INNER, OUTER, LEFT, RIGHT, CROSS

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 ý: THAM GIA 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 dưới đây

id	họ	last_name	id phim
1	Adam	Smith	1
2	Ravi	Kumar	2
3	Susan	Davidson	5
4	cú chọc	Adrianna	8
5	Lee	Pong	10

id	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 GIA	18 +
8	Hạn cuối 2009	18 +
9	Bức tranh bẩn thỉu	18 +
10	Marley và tôi	Lãng mạn

Các loại kết nối

THAM GIA chéo

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

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 tên phim họ thuê. Bạn có thể chỉ cần sử dụng INNER JOIN để 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 ,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

MySQL THAM GIA bên ngoài 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 bàn làm việc mang lại. 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 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. 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”

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

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

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.

Tổng kết

• THAM GIA 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.
• THAM GIA có hiệu suất tốt hơn so với truy vấn phụ
• INNER THAM GIA chỉ trả về các hàng đáp ứng 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. Các hàng không khớp được trả về bằng từ khóa NULL.
• Các loại JOIN chính bao gồm Inner, Left Outer, Right Outer, Cross JOINS, v.v.
• Mệnh đề được sử dụng thường xuyên trong các hoạt động THAM GIA là “BẬT”. Mệnh đề “USING” yêu cầu các cột trùng khớp phải có cùng tên.
• THAM GIA cũng có thể được sử dụng trong các mệnh đề khác như NHÓM THEO, Ở ĐÂU, TRUY VẤN SUB, CHỨC NĂNG TỔNG HỢP, v.v.