Sqlsever so sánh left inner outer join

Mệnh đề Join trong SQL Server dùng để kết nối các bảng, view, sub query lại với nhau thông qua các foreign key hoặc các cột có giá trị tương ứng ở cả hai bảng, view hoặc các sub query.

Một ví dụ cơ bản cho việc dùng Join trong SQL Server: Bạn có hai table: bảng 1 là Item (mặt hàng), bảng 2 là table Order (đơn đặt hàng).

Trong bảng Order chỉ lưu thông tin Mã hàng hóa, nhưng bạn muốn có kết quả truy vấn phải có cả tên hàng hóa. Lúc này, Join trong SQL sẽ được sử dụng để bạn nối table Order vào table Item thông qua Mã hàng để lấy tên hàng ra.

Trên đây là một ví dụ cơ bản nhất để chúng ta hiểu bản chất của JOIN là gì, trong thực tế việc sử dụng JOIN trong SQL rất đa dạng và phong phú.

Chuẩn bị Trước hết bạn hãy tạo ra hai table rất đơn giản và insert vào đó một ít dữ liệu như đoạn script sau:

create table Item

(

MaHang nvarchar(20) primary key,

TenHang nvarchar(200)

)

insert Item(MaHang, TenHang)

values(‘NO1700’, N’Điện thoại Nokia 1700′),

(‘NO1200’, N’Điện thoại Nokia 1200′),

(‘IPHONE3’, N’Điện thoại Iphone 3′),

(‘IPHONE4’, N’Điện thoại Iphone 4′),

(‘IPHONE5’, N’Điện thoại Iphone 5′),

(‘IPHONE6’, N’Điện thoại Iphone 6′)

go

create table OrderDetail

(

ID int identity primary key,

NgayDatHang datetime,

MaHang nvarchar(20),

SoLuong decimal(19,6) default 0 notnull

)

insert OrderDetail(NgayDatHang, MaHang,SoLuong)

values(‘20140401’, ‘NO1700’, 5),

(‘20140402’, ‘NO1700’, 1),

(‘20140402’, ‘IPHONE3’, 2),

(‘20140401’, ‘IPHONE4’,1), (‘20140401’,‘NO0001’, 1000)

Các loại JOIN trong SQL Server

1. INNER JOIN (viết tắt: JOIN)

Kết quả từ Inner join trong SQL là tập hợp dữ liệu trong đó giá trị dùng để join hai table với nhau đều có ở cả hai bảng, nghĩa là kết quả là GIAO của hai tập hợp dữ liệu. Kết quả được minh họa như sau:

Chạy script sau để thấy kết quả của inner join trong SQL Server:

select t2.MaHang, t1.TenHang, t2.SoLuong

from Item t1 inner joinOrderDetail t2 on t1.MaHang \= t2.MaHang

Bạn có thể không cần dùng INNER JOIN như trên mà chỉ cần viết tắt JOIN là được:

select t2.MaHang, t1.TenHang, t2.SoLuong

from Item t1 join OrderDetail t2 on t1.MaHang \= t2.MaHang

Kết quả:

Bốn mã hàng hóa ở cột MaHang đều có trong table Item và table OrderDetail.

2. OUTER JOIN trong SQL Server

1. LEFT OUTER JOIN (viết tắt: LEFT JOIN)

   Có thể diễn giải LEFT JOIN trong SQL như sau: “Lấy toàn bộ các dòng dữ liệu ở table bên trái và những dòng dữ liệu ở bảng bên phải CÓ giá trị MaHang tồn tại ở bảng bên trái.”.

Chạy đoạn script sau:

select t1.MaHang, t1.TenHang,t2.SoLuongfrom Item t1 left joinOrderDetail t2 on t1.MaHang \=t2.MaHang

Kết quả trả về:

Một số dòng có SoLuong == null là do các MaHang này không tồn tại ở bảng bên phải (OrderDetail), nhưng có tồn tại ở bảng bên trái (Item).

1. RIGHT OUTER JOIN (viết tắt: RIGHT JOIN)

Trái ngược với LEFT JOIN, RIGHT JOIN trong SQL Server lấy toàn bộ dữ liệu của bảng bên phải và dữ liệu của bảng bên trái nhưng giá trị cột JOIN cũng tồn tại trong bảng bên phải.

Minh họa như sau, hình Oval màu vàng chính là kết quả sẽ trả về:

Chạy script sau:

select t2.MaHang, t1.TenHang, t2.SoLuong

from Item t1 right join OrderDetail t2 on t1.MaHang \= t2.MaHang

Kết quả như sau:

1. FULL OUTER JOIN (viết tắt: FULL JOIN)

FULL OUTER JOIN trong SQL Server chính là kết quả gộp lại của cả hai table bên trái và bên phải.

Hình minh họa:

Chạy đoạn script sau:

select t1.MaHang, t1.TenHang, t2.SoLuong

from Item t1 full outer joinOrderDetail t2 on t1.MaHang \= t2.MaHang

Kết quả:

Quan sát kết quả trên có thể thấy:

Các dòng có MaHang = null là do bảng bên phải (OrderDetail) có giá trị nhưng table bên trái (Item) không có giá trị MaHang tương ứng.

Các dòng có SoLuong = null là do các mặt hàng IPHONE5, 6, NO1200 có trong bảng Item nhưng không có dòng nào tương ứng trong table OrderDetail.

1. CROSS JOIN

CROSS JOIN trong SQL ít được sử dụng hơn so với các loại JOIN ở trên do tính ít ý nghĩa của nó, kết quả trả về của CROSS JOIN là lấy số dòng của bảng bên trái x số dòng của bảng bên phải. Ví dụ bảng Item có 6 dòng, bảng OrderDetail có 5 dòng, kết quả trả về là 30 dòng.

Dữ liệu được nhân lên do cứ tương ứng với 1 dòng trong Item thì sẽ nối với toàn bộ 5 dòng trong DetailItem mà không cần quan tâm đến tiêu chí so sánh nào (không có mệnh đề ON t1.ColumnA = t2.ColumnB như các kiểu JOIN khác).

Chạy script sau:

select t1.*, t2.*

from Item t1 cross join OrderDetail t2

Kết quả:

Chúng ta đã tìm hiểu về các loại JOIN trong SQL Server, chúng được sử dụng thường xuyên trong truy vấn dữ liệu và nhiều xử lý khác. Cho nên bạn cần thực hành cẩn thận và nắm thật rõ cách sử dụng các loại Join để khi làm việc với SQL Server được nhuần nhuyễn và hiệu quả hơn