[SQL] Gộp bảng - Các loại JOIN (inner join, left outer join, right outer join, full join, cross join) trong truy vấn SQL Server

i. Giới thiệu

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.

ii. 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')


create table OrderDetail
(
      ID int identity primary key,
      NgayDatHang datetime,
      MaHang nvarchar(20),
      SoLuong decimal(19,6) default 0 not null
)


insert OrderDetail(NgayDatHang, MaHang, SoLuong)
      values('20140401', 'NO1700', 5),
            ('20140402', 'NO1700', 1),
            ('20140402', 'IPHONE3', 2),
            ('20140401', 'IPHONE4', 1),
            ('20140401','NO0001', 1000) 

Chúng ta đã có 2 bảng dữ liệu với liên kết như trên, vậy nếu muốn truy xuất cùng lúc thông tin đặt hàng và thông tin hàng thì ta phải làm thế nào? Đây chính là nhiệm vụ của liên kết hay JOIN các bảng lại với nhau.
Trong SQL hỗ trợ các kiểu JOIN như sau:

1. INNER JOIN

INNER JOIN hay viết tắt là JOIN trả về kết quả là tập hợp dữ liệu từ 2 bảng trong đó giá trị dùng để join đều có ở cả 2 bảng, nói cách khác kiểu join này giống phép giao trong tập hợp
Hình minh họa

Câu truy vấn SQL cụ thể như sau:

SELECT t2.MaHang, t1.TenHang, t2.SoLuong
FROM Item t1 INNER JOIN OrderDetail t2 ON t1.MaHang = t2.MaHang

-- Hoặc dạng viết tắt

SELECT t2.MaHang, t1.TenHang, t2.SoLuong
FROM Item t1 JOIN OrderDetail t2 ON t1.MaHang = t2.MaHang

Kết quả là tập hợp sau:

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

2. OUTER JOIN

a. 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.".
Kết quả minh họa như hình sau, kết quả trả về là hình oval màu xanh:

Câu truy vấn như sau:

SELECT t1.MaHang, t1.TenHang, t2.SoLuong
FROM Item t1 LEFT JOIN OrderDetail 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).

b. 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.

c. 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:

Câu truy vấn:

SELECT t1.MaHang, t1.TenHang, t2.SoLuong
FROM Item t1 FULL JOIN OrderDetail 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.

d. 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 OrderDetail 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). Cái này là phép nhân Đề các 2 quan hệ với nhau.

Câu truy vấn:

SELECT t1.*, t2.*
FROM Item t1 CROSS JOIN OrderDetail t2

Tương tự khi như ta viết:

SELECT t1.*, t2.*
FROM Item t1, OrderDetail t2

Kết quả trả về:

Thường với những câu truy vấn này ta phải thêm điều kiện WHERE để hạn chế kết quả trả về.

Chỉ có liên kết các bảng với nhau mà đã có nhiều cách như vậy. Hy vọng bạn hiểu rõ chức năng của từng loại JOIN ở trên để sử dụng hợp lý.
Chúc bạn thành công!

(Nguồn: Big Data)

>> Xem chi tiết

[SQLExpress] Lấy bản ghi ngẫu nhiên với SQL và LinQ

Trong nhiều trường hợp ta phải chọn ngẫu nhiên 1 hoặc một số bản ghi trong CSDL như: chọn 5 bài đăng ngẫu nhiên trong blog, chọn 1 câu hỏi ngẫu nhiên cho chương trình trả lời câu hỏi...
Trong cú pháp của SQL, LinQ đều có cách để làm việc này

I. SQL query lấy bản ghi ngẫu nhiên

Cấu trúc

SELECT TOP 5 [IDpost]
  FROM [dbo].[tbPost]

  ORDER BY NEWID()  -- Trình tự sắp xếp ngẫu nhiên

Trong đó NEWID() quy định 1 trình tự sắp xếp mà các bản ghi được sắp xếp ngẫu nhiên, đây chính là cơ sở để lấy các bản ghi ngẫu nhiên.

II. Lấy bản ghi ngẫu nhiên bằng code LINQ

Dùng phương thức SKIP() của IEnumerable để nhảy qua n bản ghi, với n là một số ngẫu nhiên. Thực chất cách này không phải là lấy ngẫu nhiên một số bản ghi mà nếu có thì cũng chỉ hiệu quả với bài toán lấy ngẫu nhiên 1 bản ghi duy nhất.

Cấu trúc của cách làm này như sau:

var p = from post in db.tbPosts
  select new { post.IDpost, post.title };  // Chọn dữ liệu từ LinQ

int count = p.Count();
var random = new System.Random();                   // Khởi tạo biến random
var postRD = p.Skip(random.Next(count)).Take(5);    // Tiến hành random và chọn 5 record đầu tiên
dataSet1BindingSource.DataSource = postRD;          // Điền dữ liệu vào datagridview

Theo code này thì bạn sẽ lấy được 5 bản ghi liền nhau ở vị trí ngẫu nhiên > sẽ có lúc nhận được không đủ 5 bản ghi. Không đúng với bài toán lắm.
Vậy giải pháp ở đây là chạy câu lệnh SQL chửa NEWID() bằng LINQ

var postRD = db.ExecuteQuery<tbPost>("SELECT * FROM tbPost ORDER BY NEWID()").Take(5);
dataSet1BindingSource.DataSource = postRD;          // Thiết lập dữ liệu cho binddingridview

>> Xem chi tiết

[LINQ] LinQ là gì và tại sao nên dùng LinQ?

Với SQLExpress để truy vấn dữ liệu ta phải dùng đến các câu lệnh - Query khá phức tạp. Hơn nữa để sử dụng trong ứng dụng C# lại thêm 1 tầng phức tạp nữa với các câu lệnh: dùng ConnectionString khởi tạo kết nối tới DataBase, tự khai báo các biến để chạy 1 lệnh - command, rồi còn phải tính toán đầu ra của câu lệnh... Thật là quá phức tạp nếu như ta có 1 chương trình "khủng".

Vậy làm sao để giải quyết vấn đề này? 

Một đề xuất là sử dụng Linq to SQL.

Không chỉ áp dụng cho truy vấn SQL mà LinQ còn có khả năng hỗ trợ trên nhiều nền tảng khác: XML, SQLite, Excel...

I. LinQ là gì?

Để giảm gánh nặng thao tác trên nhiều ngôn ngữ khác nhau và cải thiện năng suất lập trình, Microsoft đã phát triển giải pháp tích hợp dữ liệu cho .NET Framework có tên gọi là LINQ (Language Integrated Query), đây là thư viện mở rộng cho các ngôn ngữ lập trình C# và Visual Basic.NET (có thể mở rộng cho các ngôn ngữ khác) cung cấp khả năng truy vấn trực tiếp dữ liệu Object, CSDL và XML.

LINQ là một tập hợp các thành phần mở rộng cho phép viết các câu truy vấn dữ liệu ngay trong một ngôn ngữ lập trình, như C# hoặc VB.NET. Khi tạo một đối tượng LINQ thì Visual Studio sẽ tự động sinh ra các lớp có các thành phần tương ứng với CSDL của chúng ta. Khi muốn truy vấn, làm việc với CSDL ta chỉ việc gọi và truy xuất các hàm, thủ tục tương ứng của LINQ mà không cần quan tâm đến các câu lệnh SQL thông thường.

Tóm lại LINQ ra đời để giảm công sức cho những quá trình đơn giản và “chung chung” trước đây.

Điểm mạnh (chưa chắc về độ mạnh, nhưng hay) của LINQ là “viết truy vấn cho rất nhiều các đối tượng dữ liệu”. Từ CSDL, XML Data Object … thậm chí là viết truy vấn cho một biến mảng đã tạo ra trước đó. Vì vậy mới có các khái niệm LinQ to SQL, LinQ to XML, blo bla ….

Tuy nhiên so với mô hình Entity (Entity Framework), LINQ có yếu điểm là chậm và thiếu nhất quán (hiện đại tất phải hại điện).

LINQ có từ bản .NET 3.5, vậy nên tối thiểu chương trình của bạn phải chạy trên nền tảng này.

Visual Studio 2008, hoặc các phiên bản Express của nó là các bộ công cụ phát triển tiêu biểu cho ứng dụng dùng LINQ.

II. Sử dụng LinQ

Câu lệnh SELECT trong SQL được thực hiện bởi LINQ

Trong phần này tôi sẽ sử dụng 1 project demo để các bạn thấy được cách làm việc với LINQ như thế nào?!

CSDL là 1 bảng tbTest với 2 trường: id (kiểu int, tự động tăng - Identity) và Feild1 (kiểu nvarchar(50)).

1. Khởi tạo đối tượng LinQ to SQL

Trong project chọn Add > Data > LinQ to SQL classes

Ở đây tôi tạo 1 file DB.dbml trong thư mục DB để dễ quản lý.

Sau khi có được file ta tiến hành kéo các table cần thiết vào để tự động sinh các thủ tục LinQ

Vậy là xong, ta đã tạo được các thủ tục cần thiết để làm việc với LINQ. Easy??!!   :)

2. Truy vấn dữ liệu

Tương tự như Entity ta phải khai báo 1 biến DataContext để tương tác với LinQ

DB.DBDataContext db = new DB.DBDataContext();  

Có 2 kiểu truy vấn dữ liệu:
Query Syntax

var abc = from p in db.tbTests
          where p.id > 10
          select p;

// Hoặc chọn một số trường
var abc = from p in db.tbTests
          where p.id > 10
          select new
          {
              p.id,
              p.Feild1
          };

Method Syntax

var xyz = db.tbTests.Where(p => p.id > 10).Select(p => new { p.id, p.Feild1 });

Câu truy vấn SQL tương ứng

SELECT [id]
      ,[Feild1]
  FROM [dbo].[tbTest]
  WHERE [id] > 10

Chọn từ nhiều bảng theo kiểu Inner Join (lấy những bản ghi có điều kiện thỏa mãn)

// Query Syntax
var result = from p in products
             join c in categories on p.CategoryID equals c. CategoryID
             select new
             {
                 ProductName=p.ProductName,
                 CategoryName=c.CategoryName
             };

// Hoặc chọn từ 2 bảng bằng cách from 2 lần  :)
var result = from p in products
             from c in categories
             where p.ProductName equals c.CategoryName
             select new
             {
                 ProductName=p.ProductName,
                 CategoryName=c.CategoryName
             };

// Method Syntax
var result = products.Join(
             categories,
             p=>p.CategoryID,
             c=>c.CategoryID,
             (p,c) => new
             {
                 ProductName=p.ProductName,
                 CategoryName=c.CategoryName
             });

Cú pháp khác tương tự các câu lệnh trong SQL.
Các biến ở trên trả về có kiểu IEnumrable, 1 loại kiểu dữ liệu giống như List. Các thao tác cơ bản với loại biến này:

• xyz.FirstOrDefault(): Chọn bản ghi đầu tiên hoặc mặc định
• xyz.Skip(5): Nhảy qua n bản ghi
• xyz.Take(5): Lấy n bản ghi đầu tiên
• xyz.ToList(): Chuyển sang kiểu List
• xyz.Count(): đếm số bản ghi
• xyz.Select(...), xyz.Where(...), xyz.Join(...): Các câu lệnh truy vấn theo kiểu Method Syntax
• bla bla

Đôi khi bạn không tìm được cú pháp thích hợp hoặc LinQ không hỗ trợ loại truy vấn mà vốn có trong SQL thì bạn có thể thực hiện trực tiếp câu lệnh đó thông qua LinQ:

var result = db.ExecuteQuery<int>("SELECT NEXT VALUE FOR seq_tbCanBo")

3. Thêm, sửa, xóa dữ liệu thông qua LinQ

Thêm dữ liệu

DB.tbTest a = new DB.tbTest();      // Khai báo đối tượng mới
a.Feild1 = txtFeild1.Text;      // id là giá trị tự động tăng > ko cần thay đổi
db.tbTests.InsertOnSubmit(a);   // Thêm đối tượng a vào
db.SubmitChanges();     // Lưu thay đổi

Sửa dữ liệu

var a = (from p in db.tbTests
         where p.id == int.Parse(txtId.Text)
         select p).FirstOrDefault();
a.Feild1 = txtFeild1.Text;
db.SubmitChanges();

Xóa dữ liệu

var a = (from p in db.tbTests
         where p.id == int.Parse(txtId.Text)
         select p).FirstOrDefault();
db.tbTests.DeleteOnSubmit(a);
db.SubmitChanges();

III. Bindding

Bindding là cách thuận tiện để thay đổi, cập nhật, thêm mới bản ghi ở CSDL một cách dễ dàng trực quan, sử dụng LinQ.
Giả sử ta thực hiện Bindding ở 1 bảng, có 2 chế độ (kiểu hiển thị) là GridView và Detail:

• Chế độ GridView: có 1 gridview để hiển thị các bản ghi trong bảng, tương tự như khi chúng ta chạy câu truy vấn SELECT * trong SQL
• Detail: các Control cho phép thay đổi giá trị của bản ghi đang được chọn ở Gridview. Khi click vào 1 hàng ở Gridview thì dữ liệu của hàng đó sẽ được điền tự động vào các Control này.

Cách dùng Bindding:

Trong chế độ Design form, mở cửa sổ Data Sources, ở đây các bảng trong file DB.dbml (được tạo khi khởi tạo LinQ) sẽ được hiển thị, giúp ta có thể kéo vào trong form của mình

Với mỗi bảng có các tùy chọn để chọn chế độ xem: Gridview, Detail,...

Tạo Bindding bằng cách kéo thả

Sau khi kéo như vậy thì nó sinh ra 1 đối tượng là tbTestBinddingSource, đây chính là đối tượng tương tác với SQL, LinQ.

Quy định nguồn dữ liệu cho Bindding:

// DataSource là 1 bảng có sẵn
tbTestBindingSource.DataSource = db.tbTests;

// Datasource là một đối tượng IEnumrable lấy từ truy vấn LINQ
tbTestBindingSource.DataSource = result;

Bạn có thể kéo 1 BinddingNavigator để thực hiện các thao tác thêm, xóa dễ dàng

Khi đã có Bindding như thế này rồi thì việc thêm sửa xóa cực kỳ đơn giản, không cần code nhiều, sau khi thay đổi trên gridview, detail để lưu tất cả các thay đổi chỉ cần

tbTestBindingSource.EndEdit();
db.SubmitChanges();

Các thao tác có thể làm với BinddingSource

tbTestBindingSource.RemoveCurrent();    // Xóa hàng hiện tại

// Di chuyển trên gridview
tbTestBindingSource.MoveFirst();
tbTestBindingSource.MoveLast();
tbTestBindingSource.MoveNext();
tbTestBindingSource.MovePrevious();

tbTestBindingSource.Position = 5;   // Xác định vị trí cho Bindding

Sau số thao tác thay đổi trên grid view, bạn muốn xem số hàng thêm mới, sửa xóa??

int insert = db.GetChangeSet().Inserts.Count;
int update = db.GetChangeSet().Updates.Count;
int delete = db.GetChangeSet().Deletes.Count;

Tham khảo project demo trên tại đây.
Các bạn có thể truy cập liên kết để có thêm thông tin, các thao tác với LINQ.

>> Xem chi tiết