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