What is a Cartesian product in SQL?

Writen by Devil 8:59 PM - 0 Comments

Cartesian product or commonly referred as a Cross Join actually returns all the rows in all the tables listed in the query. Each row in the first table is paired with all the rows in the second table. 

Mostly it is used when there is no relationship exits between the two tables.

Example :

Let's take an example of two Tables Region & Country and make a cross join between them**

Region Table:

Country table:

SELECT * FROM regions CROSS JOIN countries WHERE country_id='AU';

Output :

REGION_ID REGION_NAME               CO COUNTRY_NAME  REGION_ID
---------- ------------------------- -- --------------------------
         1 Europe                    AU Australia    3
         2 Americas                  AU Australia    3
         3 Asia                      AU Australia    3
         4 Middle East and Africa    AU Australia    3 

• Facebook
• Tweeter
• Google+
• Whatsapp Pinterest