Get result set from a single column table in sql

Question

I have a table country with single column that has the below values

India
Pakistan
Australia
Srilanka

My result should be like this

India vs Pakistan 
India vs Australia
India vs Srilanka
Pakistan vs Srilanka
Pakistan vs Australia
Australia vs Srilanka

How do we achieve this with self join?

Tried with self join but unable to know how to retrieve first value and second value from column

Answer 1

Use the self join with a condition so that a country cannot play against itself:

CREATE TABLE counties (
    name varchar(50)
);


INSERT INTO counties (name)
VALUES ('India'), ('Pakistan'), ('Australia'), ('Srilanka');

SELECT
     CONCAT(c1.name, ' vs ', c2.name) AS matchup
FROM counties c1
JOIN counties c2 ON c1.name < c2.name
ORDER BY
    matchup;

matchup
Australia vs India
Australia vs Pakistan
Australia vs Srilanka
India vs Pakistan
India vs Srilanka
Pakistan vs Srilanka

fiddle

More combinations can be achieved with <> in the condition:

SELECT
     CONCAT(c1.name, ' vs ', c2.name) AS matchup
FROM counties c1
JOIN counties c2 ON c1.name <> c2.name
ORDER BY
    matchup;

matchup
Australia vs India
Australia vs Pakistan
Australia vs Srilanka
India vs Australia
India vs Pakistan
India vs Srilanka
Pakistan vs Australia
Pakistan vs India
Pakistan vs Srilanka
Srilanka vs Australia
Srilanka vs India
Srilanka vs Pakistan

fiddle

Answer 2

You can use CROSS JOIN, and WHERE c1.name < c2.name:

SELECT CONCAT(c1.name, ' vs ', c2.name) AS Combination
FROM counties c1
CROSS JOIN counties c2
WHERE c1.name < c2.name
ORDER BY Combination

Demo here