I would like to get a cartesian product of several tables in SQL (which are actually only one column, so no common key). For example: I tried CROSS JOIN, but I couldn't find an example with multiple tables. Is the only way to do it is nesting? What if we have 15 tables to join that way... it creates a

t-SQL cartesian production of several tables

I would like to get a cartesian product of several tables in SQL (which are actually only one column, so no common key). For example:

TABLE A

Robert
Pierre
Samuel

TABLE B

Montreal
Chicago

TABLE C

KLM
AIR FRANCE


FINAL TABLE (CROSS PRODUCT)

Robert | Montreal  | KLM
Pierre | Montreal  | KLM
Samuel | Montreal  | KLM
Robert | Chicago   | KLM
Pierre | Chicago   | KLM
Samuel | Chicago   | KLM
Robert | Montreal  | AIR FRANCE
Pierre | Montreal  | AIR FRANCE
Samuel | Montreal  | AIR FRANCE
Robert | Chicago   | AIR FRANCE
Pierre | Chicago   | AIR FRANCE
Samuel | Chicago   | AIR FRANCE

​x
 
TABLE A​RobertPierreSamuel​TABLE B​MontrealChicago​TABLE C​KLMAIR FRANCE​​FINAL TABLE (CROSS PRODUCT)​Robert | Montreal  | KLMPierre | Montreal  | KLMSamuel | Montreal  | KLMRobert | Chicago   | KLMPierre | Chicago   | KLMSamuel | Chicago   | KLMRobert | Montreal  | AIR FRANCEPierre | Montreal  | AIR FRANCESamuel | Montreal  | AIR FRANCERobert | Chicago   | AIR FRANCEPierre | Chicago   | AIR FRANCESamuel | Chicago   | AIR FRANCE​

I tried CROSS JOIN, but I couldn’t find an example with multiple tables. Is the only way to do it is nesting? What if we have 15 tables to join that way… it creates a very long code.

Thank you!

Answer

You would simply use:

select *
from a cross join b cross join c;

 
select *from a cross join b cross join c;​

Do note that if any of the tables are empty (i.e. no rows), you will get no results.

Advertisement

Answer