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
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading