SQL Self JOIN

SELECT
    A.first_name AS FirstPerson,
    B.first_name AS SecondPerson
FROM Customers A, Customers B
WHERE A.last_name = B.last_name AND A.first_name != B.first_name;

Self JOIN Syntax

The basic syntax of the Self JOIN operation is as follows:

SELECT columns
FROM table1 T1,
JOIN table1 T2 ON 
WHERE condition;

Here,

• columns - specifies the columns we want to retrieve
• table1 T1 and table1 T2 - two instances T1 and T2 for the same table table1
• JOIN - connects two tables and is usually followed by an ON command that specifies the common columns used for linking the two tables.
• condition - specifies the condition specifying how the two instances of the same table should be joined

Example: SQL Self JOIN

-- retrieve Customers with the Same Country and Different Customer IDs

SELECT
    c1.first_name,
    c1.country,
    c2.first_name 
FROM Customers c1
JOIN Customers c2 ON c1.country = c2.country
WHERE c1.customer_id <> c2.customer_id;

Here, the SQL command uses self-join on the Customers table to find customers who have the same country but distinct customer IDs.

The WHERE clause filters the results and ensures that the customer IDs from two instances of the Customers table (c1 and c2) are not the same.

SQL Self JOIN With AS Aliases

We can use AS aliases with table names to make our query short and clean. For example,

-- retrieve Customers with the same Country and Different Customer IDs
-- use AS alias for better code readability

SELECT
    c1.first_name,
    c1.country,
    c2.first_name AS friend_name
FROM Customers c1
JOIN Customers c2 ON c1.country = c2.country
WHERE c1.customer_id <> c2.customer_id;

Here, the SQL command uses the alias friend_name to rename the first_name column from the second instance c2 of the Customers table.

This alias makes it clear that we are referring to the first name of another customer who shares the same country.