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

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;

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

Advertisement

Answer