{"id":5685,"date":"2017-02-13T02:59:07","date_gmt":"2017-02-13T09:59:07","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=5685"},"modified":"2023-10-29T02:48:15","modified_gmt":"2023-10-29T09:48:15","slug":"mysql-cross-join","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-cross-join\/","title":{"rendered":"MySQL CROSS JOIN"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about the MySQL CROSS JOIN<\/code> clause and how to use it more effectively.<\/p>\n\n\n\n

Introduction to MySQL CROSS JOIN clause<\/h2>\n\n\n\n

Suppose you join two tables using the CROSS JOIN<\/code> clause. The result set will include all rows from both tables, where each row is the combination of the row in the first table with the row in the second table. In general, if each table has n<\/code> and m<\/code> rows respectively, the result set will have nxm<\/code> rows.<\/p>\n\n\n\n

In other words, the CROSS JOIN<\/code> clause returns a Cartesian product<\/strong> of rows from the joined tables.<\/p>\n\n\n\n

The following illustrates the syntax of the CROSS JOIN<\/code> clause that joins two tables t1<\/code> and t2<\/code>:<\/p>\n\n\n

SELECT<\/span> select_list \nFROM<\/span> t1\nCROSS<\/span> JOIN<\/span> t2;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that different from the INNER JOIN<\/code><\/a>, LEFT JOIN<\/code><\/a> , and RIGHT JOIN<\/a><\/code> clauses, the CROSS JOIN<\/code> clause does not have a join predicate. In other words, it does not have the ON<\/code> or USING<\/code> clause.<\/p>\n\n\n\n
If you add a WHERE<\/code> clause, in case table t1<\/code> and t2<\/code> has a relationship, the CROSS JOIN<\/code> works like the INNER JOIN<\/code> clause as shown in the following query:<\/p>\n\n\n
SELECT<\/span> select_list \nFROM<\/span> t1\nCROSS<\/span> JOIN<\/span> t2\nWHERE<\/span> t1.id = t2.id;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL CROSS JOIN clause examples<\/h2>\n\n\n\n
Let’s take some examples to understand how the cross join works.<\/p>\n\n\n\n
1) Simple cross join example<\/h3>\n\n\n\n
We’ll use a cross join to create a deck of 52 playing cards.<\/p>\n\n\n\n
First, create a table that stores suits:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> suits (\n    suit_id INT<\/span>,\n    suit_name VARCHAR<\/span>(10<\/span>)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, create a table to store ranks:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> ranks (\n    rank_id INT<\/span>,\n    rank_name VARCHAR<\/span>(5<\/span>)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, insert data into the suits and ranks table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> suits (suit_id, suit_name) VALUES<\/span>\n    (1<\/span>, 'Hearts'<\/span>),\n    (2<\/span>, 'Diamonds'<\/span>),\n    (3<\/span>, 'Clubs'<\/span>),\n    (4<\/span>, 'Spades'<\/span>);\n\nINSERT<\/span> INTO<\/span> ranks (rank_id, rank_name) VALUES<\/span>\n    (1<\/span>, 'Ace'<\/span>),\n    (2<\/span>, '2'<\/span>),\n    (3<\/span>, '3'<\/span>),\n    (4<\/span>, '4'<\/span>),\n    (5<\/span>, '5'<\/span>),\n    (6<\/span>, '6'<\/span>),\n    (7<\/span>, '7'<\/span>),\n    (8<\/span>, '8'<\/span>),\n    (9<\/span>, '9'<\/span>),\n    (10<\/span>, '10'<\/span>),\n    (11<\/span>, 'Jack'<\/span>),\n    (12<\/span>, 'Queen'<\/span>),\n    (13<\/span>, 'King'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, use a cross to combine the suits and ranks to create a deck of 52 playing cards:<\/p>\n\n\n
SELECT<\/span> \n  suit_name, \n  rank_name \nFROM<\/span> \n  suits CROSS<\/span> \n  JOIN<\/span> ranks \nORDER<\/span> BY<\/span> \n  suit_name, \n  rank_name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+-----------+-----------+\n| suit_name | rank_name |\n+-----------+-----------+\n| Clubs     | 10        |\n| Clubs     | 2         |\n| Clubs     | 3         |\n| Clubs     | 4         |\n| Clubs     | 5         |\n| Clubs     | 6         |\n| Clubs     | 7         |\n| Clubs     | 8         |\n| Clubs     | 9         |\n| Clubs     | Ace       |\n| Clubs     | Jack      |\n| Clubs     | King      |\n| Clubs     | Queen     |\n| Diamonds  | 10        |\n| Diamonds  | 2         |\n| Diamonds  | 3         |\n| Diamonds  | 4         |\n| Diamonds  | 5         |\n| Diamonds  | 6         |\n| Diamonds  | 7         |\n| Diamonds  | 8         |\n| Diamonds  | 9         |\n| Diamonds  | Ace       |\n| Diamonds  | Jack      |\n| Diamonds  | King      |\n| Diamonds  | Queen     |\n| Hearts    | 10        |\n| Hearts    | 2         |\n| Hearts    | 3         |\n| Hearts    | 4         |\n| Hearts    | 5         |\n| Hearts    | 6         |\n| Hearts    | 7         |\n| Hearts    | 8         |\n| Hearts    | 9         |\n| Hearts    | Ace       |\n| Hearts    | Jack      |\n| Hearts    | King      |\n| Hearts    | Queen     |\n| Spades    | 10        |\n| Spades    | 2         |\n| Spades    | 3         |\n| Spades    | 4         |\n| Spades    | 5         |\n| Spades    | 6         |\n| Spades    | 7         |\n| Spades    | 8         |\n| Spades    | 9         |\n| Spades    | Ace       |\n| Spades    | Jack      |\n| Spades    | King      |\n| Spades    | Queen     |\n+-----------+-----------+\n52 rows in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
In this query, we use a cross join to combine each suit from the suits table with each rank from the ranks table, resulting in a Cartesian product that pairs every suit with every rank.<\/p>\n\n\n\n
2) A complex cross join example<\/h3>\n\n\n\n
First, create a new database<\/a> salesdb<\/code>:<\/p>\n\n\n
CREATE<\/span> DATABASE<\/span> IF<\/span> NOT<\/span> EXISTS<\/span> salesdb;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, switch the current data to the new database salesdb<\/code>:<\/p>\n\n\n
USE<\/span> salesdb;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
CREATE<\/span> TABLE<\/span> products (\n    id<\/span> INT<\/span> PRIMARY KEY<\/span> AUTO_INCREMENT,\n    product_name VARCHAR<\/span>(100<\/span>),\n    price DECIMAL<\/span>(13<\/span>,2<\/span> )\n);\n\nCREATE<\/span> TABLE<\/span> stores (\n    id<\/span> INT<\/span> PRIMARY KEY<\/span> AUTO_INCREMENT,\n    store_name VARCHAR<\/span>(100<\/span>)\n);\n\nCREATE<\/span> TABLE<\/span> sales (\n    product_id INT<\/span>,\n    store_id INT<\/span>,\n    quantity DECIMAL<\/span>(13<\/span> , 2<\/span> ) NOT<\/span> NULL<\/span>,\n    sales_date DATE<\/span> NOT<\/span> NULL<\/span>,\n    PRIMARY KEY<\/span> (product_id , store_id),\n    FOREIGN<\/span> KEY<\/span> (product_id)\n        REFERENCES<\/span> products (id<\/span>)\n        ON<\/span> DELETE<\/span> CASCADE<\/span> ON<\/span> UPDATE<\/span> CASCADE<\/span>,\n    FOREIGN<\/span> KEY<\/span> (store_id)\n        REFERENCES<\/span> stores (id<\/span>)\n        ON<\/span> DELETE<\/span> CASCADE<\/span> ON<\/span> UPDATE<\/span> CASCADE<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here are the descriptions of the tables:<\/p>\n\n\n\n
Third, create new tables<\/a> in the salesdb<\/code> database:<\/p>\n\n\n