Q
Problem solve Get help with specific problems with your technologies, process and projects.

All possible combinations of values

I have a table with a column of datatype number, with values as 1,2,3,4. Now I need all the possible combinations of these values in a query. Can you please help me?

I have a table with a column of datatype number, with values as 1,2,3,4. Now I need all the possible combinations...

of these values in a query. Can you please help me?

This is a great example, if somewhat simplistic, of when to use a cross join.

select t1.col as t1_col
     , t2.col as t2_col
  from daTable as t1
     , daTable as t2

Here we see the older, "comma list" syntax for a cross join, in which there is no WHERE clause. It is a self join because the table is cross-joined to itself. Notice that in any self-join, we need to use both table aliases and column aliases.

select t1.col as t1_col
     , t2.col as t2_col
  from daTable as t1
cross  
  join daTable as t2

Here we see the newer JOIN syntax for a cross join, in which there is no ON clause. It produces exactly the same results.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close