SQL CROSS JOIN query of MySQL tables

There is no ON command to join tables using any column. No need to have common columns for CROSS JOIN of tables. The result of CROSS JOIN will have rows linking all rows of both tables. If Table one (products ) has 8 rows and Table two ( sales ) have 9 rows , then CROSS JOIN will have 8 x 9 = 72 rows of output.

Video Tutorial on CROSS JOIN with WHERE condition

Here we have two tables products and sales with some sample data.
SQL dump of sample tables with data

p_id	product	price
1	Hard Disk	80
2	RAM	90
3	Monitor	75
4	CPU	55
5	Keyboard	20
6	Mouse	10
7	Motherboard	50
8	Power supply	20

sale_id	c_id	p_id	product	qty	store
1	2	3	Monitor	2	ABC
2	2	4	CPU	1	DEF
3	1	3	Monitor	3	ABC
4	4	2	RAM	2	DEF
5	2	3	Monitor	3	ABC
6	3	3	Monitor	2	DEF
7	2	2	RAM	3	ABC
8	3	2	RAM	2	DEF
9	2	3	Monitor	2	ABC

SLECT * FROM products CROSS JOIN sales

Output

p_id	product	price	sale_id	c_id	p_id	product	qty	store
1	Hard Disk	80	1	2	3	Monitor	2	ABC
2	RAM	90	1	2	3	Monitor	2	ABC
3	Monitor	75	1	2	3	Monitor	2	ABC
There are total 72 rows, Click here to check the sample output

Using WHERE condition

By using WHERE condition we can restrict the records. Cross Join is used before adding WHERE condition.

SELECT * FROM products CROSS JOIN sales WHERE qty=2

In our sqles table there is a column qty, there are 5 rows having qty=2. In our products table there are 8 rows , so the result will have 8 x 5 = 40 rows of data. Sample output is here .

p_id	product	price	sale_id	c_id	p_id	product	qty	store
1	Hard Disk	80	1	2	3	Monitor	2	ABC
1	Hard Disk	80	4	4	2	RAM	2	DEF
1	Hard Disk	80	6	3	3	Monitor	2	DEF
1	Hard Disk	80	8	3	2	RAM	2	DEF
1	Hard Disk	80	9	2	3	Monitor	2	ABC
2	RAM	90	1	2	3	Monitor	2	ABC
There are 40 rows of data, click here to check all rows

Using WHERE in common columns

There are common column names in both the tables, so we have to use alias to specify the column name of the marked table.

SELECT *   FROM products a  CROSS JOIN sales b  WHERE a.p_id=2

Here We used p_id column of product table. There is only one row in our product table having p_id=2. We have 9 rows in our sales table. So our final result will have 1 x 9 = 9 rows of data.

p_id	product	price	sale_id	c_id	p_id	product	qty	store
2	RAM	90	1	2	3	Monitor	2	ABC
2	RAM	90	2	2	4	CPU	1	DEF
2	RAM	90	3	1	3	Monitor	3	ABC
2	RAM	90	4	4	2	RAM	2	DEF
2	RAM	90	5	2	3	Monitor	3	ABC
2	RAM	90	6	3	3	Monitor	2	DEF
2	RAM	90	7	2	2	RAM	3	ABC
2	RAM	90	8	3	2	RAM	2	DEF
2	RAM	90	9	2	3	Monitor	2	ABC

SELECT *   FROM products a  CROSS JOIN sales b  WHERE b.p_id=2

Our sales table has 3 rows having p_id=2. Our products table has 8 rows of data. So our final result table will have 8*3=24 rows of data.

p_id	product	price	sale_id	c_id	p_id	product	qty	store
1	Hard Disk	80	4	4	2	RAM	2	DEF
1	Hard Disk	80	7	2	2	RAM	3	ABC
1	Hard Disk	80	8	3	2	RAM	2	DEF
2	RAM	90	4	4	2	RAM	2	DEF
We have 24 rows of output, click here to read the full table

Exercise For better understanding of LEFT JOIN , check the sample code for generating reports on Sales.

RIGHT JOIN LEFT JOIN INNER JOIN : Table joining itself LEFT JOIN using Multiple Tables LINKING TABLES

Subscribe to our YouTube Channel here

SQL CROSS JOIN query

Video Tutorial on CROSS JOIN with WHERE condition

Using WHERE condition

Using WHERE in common columns

Subscribe