SQL CROSS JOIN query

Cross Join of 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_idproductprice
1Hard Disk80
2RAM90
3Monitor75
4CPU55
5Keyboard20
6Mouse10
7Motherboard50
8Power supply20
sale_idc_idp_idproductqtystore
123Monitor2ABC
224CPU1DEF
313Monitor3ABC
442RAM2DEF
523Monitor3ABC
633Monitor2DEF
722RAM3ABC
832RAM2DEF
923Monitor2ABC
SLECT * FROM products CROSS JOIN sales 
Output
p_idproductpricesale_idc_idp_idproductqtystore
1Hard Disk80123Monitor2ABC
2RAM90123Monitor2ABC
3Monitor75123Monitor2ABC
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_idproductpricesale_idc_idp_idproductqtystore
1Hard Disk80123Monitor2ABC
1Hard Disk80442RAM2DEF
1Hard Disk80633Monitor2DEF
1Hard Disk80832RAM2DEF
1Hard Disk80923Monitor2ABC
2RAM90123Monitor2ABC
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_idproductpricesale_idc_idp_idproductqtystore
2RAM90123Monitor2ABC
2RAM90224CPU1DEF
2RAM90313Monitor3ABC
2RAM90442RAM2DEF
2RAM90523Monitor3ABC
2RAM90633Monitor2DEF
2RAM90722RAM3ABC
2RAM90832RAM2DEF
2RAM90923Monitor2ABC
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_idproductpricesale_idc_idp_idproductqtystore
1Hard Disk80442RAM2DEF
1Hard Disk80722RAM3ABC
1Hard Disk80832RAM2DEF
2RAM90442RAM2DEF
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


plus2net.com






Post your comments , suggestion , error , requirements etc here
















We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2020 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer