Làm cách nào để trừ hai bảng trong MySQL?

Vì MySQL không hỗ trợ MINUS/EXCEPT và INTERSECT, nên giải pháp thay thế là sử dụng THAM GIA để đạt được hiệu quả tương tự

Nếu bạn muốn bắt tay vào thực hiện các truy vấn trong hướng dẫn này, bạn cần xem trang này để chuẩn bị dữ liệu trước

Dưới đây là 4 bảng được sử dụng trong hướng dẫn này

Bảng 1

ban 2

Bảng 1. với cột tổng kiểm tra

ban 2. với cột tổng kiểm tra

1. TRỪ/TRỪ

MINUS là một hoạt động của bộ SQL Oracle chọn các hàng từ bảng đầu tiên và sau đó loại bỏ các hàng cũng được tìm thấy trong bảng thứ hai. Trong Microsoft SQL Server, EXCEPT là tương đương để làm điều tương tự

Định dạng SQL tiêu chuẩn như dưới đây

tiên tri

SELECT * FROM suppliers_1_md5
MINUS
select * FROM suppliers_2_md5;

Máy chủ SQL

SELECT * FROM suppliers_1_md5
EXCEPT
SELECT * FROM suppliers_2_md5;

Sơ đồ bên dưới hiển thị kết quả truy vấn cho Bảng 1 TRỪ/TRỪ Bảng 2

MySQL không hỗ trợ MINUS/EXCEPT, cách giải quyết là sử dụng LEFT JOIN. Vì MINUS/EXCEPT so sánh mọi cột giữa Bảng 1 và Bảng 2 nên mệnh đề liên kết cần chứa tất cả 4 cột SupplierID, CompanyName, ContactName, ContactTitle. Mệnh đề where chọn các giá trị null trong ID nhà cung cấp trong Bảng 2, chỉ giới hạn các hàng tồn tại trong Bảng 1

SELECT t1.* FROM suppliers_1 AS t1
LEFT JOIN suppliers_2 AS t2 ON
    t1.SupplierID=t2.SupplierID
    AND t1.CompanyName=t2.CompanyName
    AND t1.ContactName=t2.ContactName
    AND t1.ContactTitle=t2.ContactTitle
WHERE t2.SupplierID IS NULL;

Kết quả. 2 hàng trả về

Trong bảng 1 có 2 hàng không tồn tại trong bảng 2

Truy vấn bên dưới trả về các hàng giống như trên, nhưng mệnh đề nối chỉ chứa 1 cột được gọi là Tổng kiểm tra. Nó thể hiện cách sử dụng thực tế của hàm MD5 để tạo giá trị tổng kiểm tra để so sánh. Vì MINUS/EXCEPT so sánh/nối tất cả các cột giữa Bảng 1 và Bảng 2, nên nếu bảng có một trăm cột, thì các giá trị trong tất cả các cột này cần phải được so sánh. Trong các ứng dụng trong thế giới thực, nó có thể làm tăng đáng kể thời gian phát triển và bảo trì cũng như giảm hiệu suất truy vấn. Cách tốt nhất là tính giá trị tổng kiểm tra bằng cách nối tất cả các cột như được hiển thị ở đây, md5[concat[SupplierID, CompanyName, ContactName, ContactTitle]]. Sau đó, chỉ có 1 cột được nối

SELECT t1.* FROM suppliers_1_md5 AS t1
LEFT JOIN suppliers_2_md5 AS t2 ON t1.checksum=t2.checksum
WHERE t2.SupplierID IS NULL;

Kết quả. 2 hàng trả về

2. GIAO NHAU

INTERSECT trả về các hàng riêng biệt tồn tại trong cả hai bảng

Đây là định dạng chuẩn nhưng MySQL không hỗ trợ cú pháp

SELECT * FROM suppliers_1_md5
INTERSECT
SELECT * FROM suppliers_2_md5;

Sơ đồ bên dưới hiển thị kết quả truy vấn cho Bảng 1 INTERSECT Bảng 2

Dưới đây là cách MySQL sẽ thực hiện INTERSECT bằng cách sử dụng phép nối bên trong. Lưu ý rằng cả 4 cột của bảng 1 đều giao nhau với 4 cột của bảng 2

SELECT t1.* FROM suppliers_1 AS t1
JOIN suppliers_2 AS t2 ON
    t1.SupplierID=t2.SupplierID
    AND t1.CompanyName=t2.CompanyName
    AND t1.ContactName=t2.ContactName
    AND t1.ContactTitle=t2.ContactTitle;

Kết quả. 3 hàng trả lại

Ở đây EXISTS được sử dụng và trả về các hàng giống như trên

SELECT *
FROM suppliers_1 as t1
where EXISTS [SELECT * FROM suppliers_2 as t2
           WHERE t1.SupplierID=t2.SupplierID
        AND t1.CompanyName=t2.CompanyName
        AND t1.ContactName=t2.ContactName
        AND t1.ContactTitle=t2.ContactTitle];

Bạn cũng có thể sử dụng truy vấn phụ với từ khóa IN để thực hiện INTERSECT trong MySQL

SELECT * FROM suppliers_1
WHERE [SupplierID, CompanyName, ContactName, ContactTitle]
IN [SELECT SupplierID, CompanyName, ContactName, ContactTitle FROM suppliers_2];

Nhưng chúng tôi vẫn muốn sử dụng cột tổng kiểm tra của mình để có hiệu suất tốt hơn và mã SQL sạch hơn - chỉ so sánh 1 cột thay vì 4 cột giao nhau

Sử dụng với mệnh đề WHERE. Để chọn các bản ghi của bảng có chênh lệch giá trị cột Var1 và Var2 lớn hơn 20, truy vấn được đưa ra bên dưới

________số 8

Truy vấn sẽ tạo ra kết quả sau

DataVar1Var2Data4254Data5305Data6356

SELECT *, [Var1 - Var2] AS Diff FROM Sample;