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 first published in September 2006

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close