Introduction SQL

Basic SQL

SQL Constraints

Advance SQL

SQL Joins

SQL Function


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

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 Statement :

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 Statement :

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