DB Where Condition Based on Alphabetical Comparison of 2 columns

Question

I have SQL query of self Join products table

id | product_name

I need to make 2 sets:

A vs B and B vs A.

A vs B means alphabetically first will come first.

B vs A means alphabetically first will come last.

Following giving all combinations with a's id less than b'id but I need based on alphabetically first

SELECT
a.product_name || b.product_name
from 
products a
JOIN products b ON a.id != b.id  -- same product combination not needed 
WHERE
   a.id < b.id   

Sample Data

1 | Apple
2 | Apricots
3 | Bananas
4 | Blueberries
5 | Cherries
6 | Cucumbers
7 | Dates
8 | Dragon Fruit
9 | Eggfruit
10 | Mango

Total set will have Every fruit will have combination with every one without where condition.

I need 2 separate sets.

First set will have: A vs B - Alphabetically First

Apple Vs Apricots

Second set will have: B Vs A - Alphabetically last

Apricots Vs Apple 

Answer 1

You can use two queries to get the two result sets you want, they are fundamentally the same with just the JOIN condition being changed for the second query:

SELECT a.product_name || ' - ' ||  b.product_name AS products
FROM products a
JOIN products b ON a.product_name < b.product_name
ORDER BY a.product_name, b.product_name

For the second query the JOIN condition is:

JOIN products b ON a.product_name > b.product_name

Output (for first query):

Apple - Apricots
Apple - Bananas
Apple - Blueberries
...
Dragon Fruit - Eggfruit
Dragon Fruit - Mango
Eggfruit - Mango

Output (for second query):

Apricots - Apple
Bananas - Apple
Bananas - Apricots
...
Mango - Dates
Mango - Dragon Fruit
Mango - Eggfruit

Demo on SQLFiddle