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, SQL Consultant, r937.com
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.