SQL - Self, Cross Join - Match Fixture

Cross Join

Cross join allows us to join each and every row of both the tables. It is similar to the cartesian product that joins all the rows.

Self Join

Self-join allows us to join a table itself. It is useful when a user wants to compare the data (rows) within the same table.

These are special joins that don't needs on condition.

Problem

An interview question to do a inner join on table A and B with duplicate keys

suppose that in a Table named Team contains 4 records,
we need to schedule a match between each team vs opposite team:

case1: Each team has to play with one another but only once.

Case2: Each team has to play with another team twice.

Could you please tell me the SQl query for both the cases?

case1: Each team has to play with one another but only once.

Answer:

-- USING POSTGRESQL
drop schema if exists temp cascade;
-- cascade Automatically drop objects (tables, functions, etc.)
-- that are contained in the schema.

create schema temp;

set schema 'temp';

drop table if exists team;

create table team (
	country VARCHAR(15) unique not null
);

insert into team values
('India'),
('Pakistan'),
('Srilanka'),
('Australia');

case1: Each team has to play with one another but only once.

-- Cross Join
select a.country || ' vs ' ||  b.country fixture
from team a
cross join team b
where a.country < b.country
order by fixture asc

-- Self Join
select a.country || ' vs ' ||  b.country fixture
from team a, team b
where a.country < b.country
order by fixture asc

Output

fixture
========
Australia vs India
Australia vs Pakistan
Australia vs Srilanka
India vs Pakistan
India vs Srilanka
Pakistan vs Srilanka

Case2: Each team has to play with another team twice.


-- Cross Join
select a.country || ' vs ' ||  b.country fixture
from team a
cross join team b
where a.country <> b.country
order by fixture asc

-- Self Join
select a.country || ' vs ' ||  b.country fixture
from team a, team b
where a.country <> b.country
order by fixture asc

Output

fixture
========
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