SQL CROSS JOIN

SQL CROSS JOIN joining tables rows and return Cartesian product(each row from Table A with each row of Table B) record set result.

SQL CROSS JOIN write two different way:

Explicit Cross Join and
Implicit Cross Join

Example Table

Considering following category, product is our example table.

SQL> SELECT * FROM category;

CATEGORY_ID	CATEGORY_NAME
1	Mobiles
2	Laptops
3	Laptops
4	Cameras
5	Gaming

SQL> SELECT * FROM product;

CATEGORY_ID	PRODUCT_NAME
1	Nokia
1	Samsung
2	HP
2	Dell
3	Apple
4	Nikon
Null	Playstation

Run it... »

Explicit Example

SQL> SELECT * 
    FROM product 
    CROSS JOIN category;

CATEGORY_ID PRODUCT_NAME              CATEGORY_ID CATEGORY_NAME
----------- ------------------------- ----------- -------------------------
          1 Nokia                               1 Mobiles
          1 Samsung                             1 Mobiles
          2 HP                                  1 Mobiles
          2 Dell                                1 Mobiles
          3 Apple                               1 Mobiles
          4 Nikon                               1 Mobiles
       NULL Playstation                         1 Mobiles
          1 Nokia                               2 Laptops
          1 Samsung                             2 Laptops
          2 HP                                  2 Laptops
          2 Dell                                2 Laptops
          3 Apple                               2 Laptops
          4 Nikon                               2 Laptops
       NULL Playstation                         2 Laptops
          1 Nokia                               3 Tablet
          1 Samsung                             3 Tablet
          2 HP                                  3 Tablet
          2 Dell                                3 Tablet
          3 Apple                               3 Tablet
          4 Nikon                               3 Tablet
       NULL Playstation                         3 Tablet
          1 Nokia                               4 Cameras
          1 Samsung                             4 Cameras
          2 HP                                  4 Cameras
          2 Dell                                4 Cameras
          3 Apple                               4 Cameras
          4 Nikon                               4 Cameras
       NULL Playstation                         4 Cameras
          1 Nokia                               5 Gaming
          1 Samsung                             5 Gaming
          2 HP                                  5 Gaming
          2 Dell                                5 Gaming
          3 Apple                               5 Gaming
          4 Nikon                               5 Gaming
       NULL Playstation                         5 Gaming

35 rows selected.

Run it... »

Implicit Example

SQL> SELECT *
    FROM product, category;

CATEGORY_ID PRODUCT_NAME              CATEGORY_ID CATEGORY_NAME
----------- ------------------------- ----------- -------------------------
          1 Nokia                               1 Mobiles
          1 Samsung                             1 Mobiles
          2 HP                                  1 Mobiles
          2 Dell                                1 Mobiles
          3 Apple                               1 Mobiles
          4 Nikon                               1 Mobiles
       NULL Playstation                         1 Mobiles
          1 Nokia                               2 Laptops
          1 Samsung                             2 Laptops
          2 HP                                  2 Laptops
          2 Dell                                2 Laptops
          3 Apple                               2 Laptops
          4 Nikon                               2 Laptops
       NULL Playstation                         2 Laptops
          1 Nokia                               3 Tablet
          1 Samsung                             3 Tablet
          2 HP                                  3 Tablet
          2 Dell                                3 Tablet
          3 Apple                               3 Tablet
          4 Nikon                               3 Tablet
       NULL Playstation                         3 Tablet
          1 Nokia                               4 Cameras
          1 Samsung                             4 Cameras
          2 HP                                  4 Cameras
          2 Dell                                4 Cameras
          3 Apple                               4 Cameras
          4 Nikon                               4 Cameras
       NULL Playstation                         4 Cameras
          1 Nokia                               5 Gaming
          1 Samsung                             5 Gaming
          2 HP                                  5 Gaming
          2 Dell                                5 Gaming
          3 Apple                               5 Gaming
          4 Nikon                               5 Gaming
       NULL Playstation                         5 Gaming

35 rows selected.

Run it... »