SQL Cross Join (Cartesian Product)

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


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_IDCATEGORY_NAME
1Mobiles
2Laptops
3Laptops
4Cameras
5Gaming
SQL> SELECT * FROM product;
CATEGORY_IDPRODUCT_NAME
1Nokia
1Samsung
2HP
2Dell
3Apple
4Nikon
NullPlaystation

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