Xin chào các bạn trong bài viết này, chúng ta sẽ cùng tìm hiểu về mệnh đề MySQL CROSS JOIN và cách sử dụng nó một cách hiệu quả hơn.

Giới thiệu về mệnh đề MySQL CROSS JOIN

Giả sử bạn nối hai bảng bằng mệnh đề CROSS JOIN. Tập hợp kết quả sẽ bao gồm tất cả các hàng từ cả hai bảng, trong đó mỗi hàng là sự kết hợp của hàng trong bảng đầu tiên với hàng trong bảng thứ hai. Nói chung, nếu mỗi bảng có n và m hàng tương ứng, tập kết quả sẽ có n x m hàng.

Nói cách khác, mệnh đề CROSS JOIN trả về tích Descartes (n nhân m) của các hàng từ các bảng đã nối.

Cú pháp dưới đây sẽ minh họa cách mệnh đề CROSS JOIN nối hai bảng t1 và t2:

SELECT * FROM t1
CROSS JOIN t2;

Lưu ý rằng khác với các mệnh đề INNER JOIN,  LEFT JOIN, và RIGHT JOIN, CROSS JOIN không có vị từ nối. Nói cách khác, nó không có mệnh đề ON hoặc USING.

Nếu bạn thêm một mệnh đề WHERE , trong trường hợp t1 và t2 có một mối quan hệ, thì mệnh đề CROSS JOIN sẽ hoạt động giống như mệnh đề INNER JOIN như trong truy vấn sau:

SELECT * FROM t1
CROSS JOIN t2
WHERE t1.id = t2.id;

Các ví dụ về mệnh đề CROSS JOIN của MySQL

Hãy lập một số bảng để chứng minh mệnh đề CROSS JOIN.

Thiết lập bảng mẫu

Đầu tiên, tạo một cơ sở dữ liệu mới salesdb :

CREATE DATABASE IF NOT EXISTS salesdb;

Thứ hai, chuyển dữ liệu hiện tại sang cơ sở dữ liệu mới salesdb:

USE salesdb;

Thứ ba, tạo các bảng mới trong cơ sở dữ liệu salesdb:

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100),
    price DECIMAL(13,2 )
);

CREATE TABLE stores (
    id INT PRIMARY KEY AUTO_INCREMENT,
    store_name VARCHAR(100)
);

CREATE TABLE sales (
    product_id INT,
    store_id INT,
    quantity DECIMAL(13 , 2 ) NOT NULL,
    sales_date DATE NOT NULL,
    PRIMARY KEY (product_id , store_id),
    FOREIGN KEY (product_id)
        REFERENCES products (id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (store_id)
        REFERENCES stores (id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

Dưới đây là các mô tả của ba bảng:

• Bảng  products chứa dữ liệu chính của sản phẩm bao gồm id sản phẩm, tên sản phẩm và giá bán.
• Bảng stores chứa các cửa hàng bán sản phẩm.
• Bảng sales chứa các sản phẩm được bán trong một cửa hàng cụ thể theo số lượng và ngày.

Cuối cùng, chèn dữ liệu vào ba bảng. Giả sử rằng chúng ta có ba sản phẩm iPhone và được bán ở hai cửa hàng là iPad và .Macbook ProNorthSouth.

INSERT INTO products(product_name, price)
VALUES('iPhone', 699),
      ('iPad',599),
      ('Macbook Pro',1299);

INSERT INTO stores(store_name)
VALUES('North'),
      ('South');

INSERT INTO sales(store_id,product_id,quantity,sales_date)
VALUES(1,1,20,'2017-01-02'),
      (1,2,15,'2017-01-05'),
      (1,3,25,'2017-01-05'),
      (2,1,30,'2017-01-02'),
      (2,2,35,'2017-01-05');

MySQL CROSS JOIN ví dụ

Câu lệnh này trả về tổng doanh số cho từng cửa hàng và sản phẩm, bạn tính doanh số và nhóm chúng theo cửa hàng và sản phẩm như sau:

SELECT 
    store_name,
    product_name,
    SUM(quantity * price) AS revenue
FROM
    sales
        INNER JOIN
    products ON products.id = sales.product_id
        INNER JOIN
    stores ON stores.id = sales.store_id
GROUP BY store_name , product_name;

Kết quả:

Bây giờ, nếu bạn muốn biết cửa hàng nào không bán một sản phẩm cụ thể thì sao. Truy vấn trên không thể giải quyết vấn đề này.

Để giải quyết vấn đề, bạn cần sử dụng mệnh đề CROSS JOIN.

Đầu tiên, sử dụng mệnh đề CROSS JOIN để có được sự kết hợp của tất cả các cửa hàng và sản phẩm:

SELECT 
    store_name, product_name
FROM
    stores AS a
        CROSS JOIN
    products AS b;

Kết quả:

Tiếp theo, nối kết quả của truy vấn bên trên với một truy vấn trả về tổng doanh số bán hàng theo cửa hàng và sản phẩm. Truy vấn sau đây giải quyết cho vấn đề trên:

SELECT 
    b.store_name,
    a.product_name,
    IFNULL(c.revenue, 0) AS revenue
FROM
    products AS a
        CROSS JOIN
    stores AS b
        LEFT JOIN
    (SELECT 
        stores.id AS store_id,
        products.id AS product_id,
        store_name,
            product_name,
            ROUND(SUM(quantity * price), 0) AS revenue
    FROM
        sales
    INNER JOIN products ON products.id = sales.product_id
    INNER JOIN stores ON stores.id = sales.store_id
    GROUP BY stores.id, products.id, store_name , product_name) AS c ON c.store_id = b.id
        AND c.product_id= a.id
ORDER BY b.store_name;
Ngôn ngữ mã:  SQL (Ngôn ngữ truy vấn có cấu trúc)  ( sql )

Kết quả:

Lưu ý truy vấn sử dụng IFNULLhàm trả về 0 nếu doanh thu là NULL (trường hợp cửa hàng không có doanh thu).

Bằng cách sử dụng mệnh đề CROSS JOIN theo cách này, bạn có thể giải quyết nhiều vấn đề, chẳng hạn như tìm doanh thu bán hàng theo nhân viên bán hàng, tháng ngay cả khi nhân viên bán hàng không có doanh số nào trong một tháng cụ thể.

Bàn có thể xem chi tiết tại: https://www.mysqltutorial.org/mysql-cross-join/

Các bài viết liên quan:

• Mysql JOIN
• Mysql inner join
• Mysql left join