SQL CROSS JOIN

Write SQL CROSS JOIN queries.

Cross joins are used to return every combination of rows from two tables, In other words, it will produce rows that combine each row from the first table with each row from the second table

Schema and Tables


Table: Customer
+----+-------+
| Id | Name  |    
+----+-------+
1  | Ram   |
2  | Henry |
3  | Sam   |
4  | Shyam |
+----+-------+

Table: Order                             
+----+-------------+----------+--------+--------------+      
| Id | CustomerID  | Product  | Price  | OrderDate    |      
+----+-------------+----------+--------+--------------+      
1  | 1           | Chair    | 500    | 01/02/2018   |      
2  | 2           | Press    | 8000   | 01/04/2018   |      
3  | 3           | Table    | 6000   | 01/04/2018   |      
4  | 2           | Table    | 6000   | 01/06/2018   |
+----+-------------+----------+--------+--------------+      


Query:


SELECT CS.ID CUSTOMERID, CS.NAME CUSTOMERNAME, 
    OD.ID ORDERID, OD.PRODUCT, OD.PRICE, OD.ORDERDATE 
FROM CUSTOMER CS CROSS JOIN ORDERS OD;


Result:


|CUSTOMERID |CUSTOMERNAME |ORDERID |PRODUCT |PRICE |ORDERDATE |
|1          |Ram          |1       |Chair   |500   |01-FEB-18 |
|2          |Henry        |1       |Chair   |500   |01-FEB-18 |
|3          |Sam          |1       |Chair   |500   |01-FEB-18 |
|4          |Shyam        |1       |Chair   |500   |01-FEB-18 |
|1          |Ram          |3       |Table   |6000  |01-APR-18 |
|2          |Henry        |3       |Table   |6000  |01-APR-18 |
|3          |Sam          |3       |Table   |6000  |01-APR-18 |
|4          |Shyam        |3       |Table   |6000  |01-APR-18 |
|1          |Ram          |4       |Table   |6000  |01-JUN-18 |
|2          |Henry        |4       |Table   |6000  |01-JUN-18 |
|3          |Sam          |4       |Table   |6000  |01-JUN-18 | 
|4          |Shyam        |4       |Table   |6000  |01-JUN-18 |
|1          |Ram          |2       |Press   |8000  |01-APR-18 |
|2          |Henry        |2       |Press   |8000  |01-APR-18 |
|3          |Sam          |2       |Press   |8000  |01-APR-18 |
|4          |Shyam        |2       |Press   |8000  |01-APR-18 |
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


No comments:

Post a Comment