Q

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.

This was last published in September 2006

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close