Generate all possible combinations in SQL?

Question

I have one table Ingredients with column Name:

Name
________
Cheese
Beans
Potato
etc

I want to display all possible combinations of those values like (cheese, beans) (cheese, potato), (beans, potato), etc in two columns. Is this possible?

Answer 1

That's a self-join:

select t1.name name1, t2.name name2
from ingredients t1
inner join ingredients t2 on t2.name > t1.name

The inequality condition is there to ensure that we do not generate "mirror" records (like "cheese/beans" vs "beans/cheese").

If you want the mirror records, change that to t2.name <> t1.name.

If you also want "duplicate" records (like "cheese/cheese"), then use a cross join instead.