lesson 22 Joins & sets · intermediate

Self-joins & referral relationships

Join a table to itself to explore relationships.

the idea

A self-join uses the same table in two roles. Use different aliases for the member and referrer. LEFT JOIN retains members who were not referred.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

SELECT m.id AS member_id,r.id AS referrer_id
FROM members m
LEFT JOIN members r ON r.id=m.referrer_id;

Watch for: The member’s country and the referrer’s country are different attributes.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Read members and their referrer ids. referrer_id points at another row in the same table. A self-join uses two aliases for those two roles.

   step-1.sql Run

   sql›

   SELECT id, email, country, referrer_id
   FROM members
   ORDER BY id;

2. 2

   Join the table to itself. Alias m is the member and r is the referrer. LEFT JOIN keeps members who have no referrer.

   step-2.sql Run

   sql›

   SELECT m.id AS member_id, r.id AS referrer_id
   FROM members m
   LEFT JOIN members r ON r.id = m.referrer_id;