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?

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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: