SQL CROSS JOIN is used to match all the rows from one table to all the rows from other table irrespective of any matching condition. So if Table1 has ‘m’ number of records and Table2 has ‘n’ number of records, the CROSS JOIN will result in m*n number of records in the final result set provided that there is no filter condition specified in SQL Query. SQL CROSS JOIN is not preferred in the actual SQL environment as it may lead to unexpected result set. Also, it is very resource intensive process hence SQL CROSS JOIN query may result in huge processing time. SQL CROSS JOIN has very limited use in real life scenarios and we will see later in which situation , it is considered as good option. The general query for CROSS JOIN is:
SELECT tb1.column1 , tb1.column2, tb2.column1 FROM Table1 tb1 , Table2 tb2
Here we can see that we have not specified any condition as JOIN CONDITION. Also, instead of ‘,’ we could have used CROSS JOIN keyword. The result will consist of all rows of table1 joined to all rows from table2.
SELECT OZON.STATE, OZON.CITY, CUST.CUST_ID, CUST.CUST_NAME, CUST.CUST_OPERATOR
FROM OPRT_ZONE OZON
CROSS JOIN CUSTOMERS CUST;
SQL CROSS JOIN is also called as SQL PRODUCT JOIN as it results in result set consisting of product of rows from both the table. SQL CARTESIAN PRODUCT join is when the output of PRODUCT join is unrestricted and there is no Filter condition specified in the SQL query. Hence, it results in m*n number of rows.