JOIN SQL — по 20 универсальных «живых» примеров для каждого типа JOIN

JOIN SQL. Введение

JOIN — механизм объединения строк из разных таблиц по условию соответствия.
Эта статья по запросу join-sql содержит по 20 «живых» примеров для каждого типа соединения:
INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN и самосоединения (SELF JOIN).

Код форматирован по best‑practice: ключевые слова SELECT, FROM, ON, WHERE, GROUP BY, ORDER BY — с новой строки,
отступы выровнены, примеры универсальны.

Синтаксис

SELECT t1.колонки, t2.колонки
FROM   таблица1 AS t1
    JOIN таблица2 AS t2
    ON t1.key = t2.key;

Типовая конструкция

SELECT
    c.customer_id,
    c.full_name,
    o.order_id,
    o.total_amount
FROM
    customers AS c
    INNER JOIN orders AS o
        ON o.customer_id = c.customer_id
WHERE
    o.total_amount > 100
ORDER BY
    o.total_amount DESC;

INNER JOIN — точные совпадения (20)

1. Клиенты с оформленными заказами:

SELECT
    c.customer_id,
    c.full_name,
    o.order_id,
    o.total_amount
FROM
    customers AS c
    INNER JOIN orders AS o
        ON o.customer_id = c.customer_id
ORDER BY
    o.order_id;

2. Заказы и связанные платежи:

SELECT
    o.order_id,
    o.total_amount,
    p.payment_id,
    p.amount
FROM
    orders AS o
    INNER JOIN payments AS p
        ON p.order_id = o.order_id
WHERE
    p.amount > 0;

3. Покупатели и их адреса доставки:

SELECT
    c.customer_id,
    c.full_name,
    a.city,
    a.country
FROM
    customers AS c
    INNER JOIN addresses AS a
        ON a.customer_id = c.customer_id;

4. Студенты и сданные экзамены:

SELECT
    s.student_id,
    s.name,
    e.exam_code,
    r.score
FROM
    students AS s
    INNER JOIN results AS r
        ON r.student_id = s.student_id
    INNER JOIN exams AS e
        ON e.exam_id = r.exam_id
WHERE
    r.score >= 60;

5. Фильмы и участвующие актёры:

SELECT
    f.film_id,
    f.title,
    a.actor_name
FROM
    films AS f
    INNER JOIN film_cast AS fc
        ON fc.film_id = f.film_id
    INNER JOIN actors AS a
        ON a.actor_id = fc.actor_id;

6. Сотрудники и названия отделов:

SELECT
    e.emp_id,
    e.full_name,
    d.dept_name
FROM
    employees AS e
    INNER JOIN departments AS d
        ON d.dept_id = e.dept_id;

7. Товары и их категории:

SELECT
    p.product_id,
    p.title,
    c.category_name
FROM
    products AS p
    INNER JOIN categories AS c
        ON c.category_id = p.category_id;

8. Пациенты и даты визитов:

SELECT
    pt.patient_id,
    pt.full_name,
    v.visit_date
FROM
    patients AS pt
    INNER JOIN visits AS v
        ON v.patient_id = pt.patient_id
ORDER BY
    v.visit_date DESC;

9. Поставщики и их товары:

SELECT
    s.supplier_id,
    s.name,
    p.product_id
FROM
    suppliers AS s
    INNER JOIN products AS p
        ON p.supplier_id = s.supplier_id;

10. Посты и оставленные комментарии:

SELECT
    p.post_id,
    p.title,
    c.comment_id,
    c.author
FROM
    posts AS p
    INNER JOIN comments AS c
        ON c.post_id = p.post_id;

11. Клиенты и открытые счета:

SELECT
    c.customer_id,
    c.full_name,
    a.account_no
FROM
    customers AS c
    INNER JOIN accounts AS a
        ON a.customer_id = c.customer_id
WHERE
    a.status = 'OPEN';

12. Устройства и последние телеметрии:

SELECT
    d.device_id,
    d.model,
    t.value,
    t.collected_at
FROM
    devices AS d
    INNER JOIN telemetry AS t
        ON t.device_id = d.device_id;

13. Курсы и закреплённые преподаватели:

SELECT
    c.course_id,
    c.title,
    t.teacher_name
FROM
    courses AS c
    INNER JOIN course_teachers AS ct
        ON ct.course_id = c.course_id
    INNER JOIN teachers AS t
        ON t.teacher_id = ct.teacher_id;

14. Магазины и активные акции:

SELECT
    s.store_id,
    s.city,
    pr.promo_code
FROM
    stores AS s
    INNER JOIN promos AS pr
        ON pr.store_id = s.store_id
WHERE
    pr.active = 1;

15. Рейсы и назначенные самолёты:

SELECT
    f.flight_no,
    a.aircraft_code
FROM
    flights AS f
    INNER JOIN aircrafts AS a
        ON a.aircraft_id = f.aircraft_id;

16. Сделки и ответственные менеджеры:

SELECT
    d.deal_id,
    u.full_name AS owner_name
FROM
    deals AS d
    INNER JOIN users AS u
        ON u.user_id = d.owner_id;

17. Складские остатки и товары:

SELECT
    st.product_id,
    st.qty_on_hand,
    p.title
FROM
    stock AS st
    INNER JOIN products AS p
        ON p.product_id = st.product_id;

18. Города и страны:

SELECT
    c.city_name,
    co.name AS country_name
FROM
    cities AS c
    INNER JOIN countries AS co
        ON co.iso2 = c.iso2;

19. Билеты и соответствующие заказы:

SELECT
    t.ticket_no,
    o.order_id
FROM
    tickets AS t
    INNER JOIN orders AS o
        ON o.order_id = t.order_id;

20. Кредитные карты клиентов:

SELECT
    c.customer_id,
    cc.card_last4
FROM
    customers AS c
    INNER JOIN credit_cards AS cc
        ON cc.customer_id = c.customer_id;

LEFT JOIN — всё слева, совпадения справа (20)

21. Все клиенты, в том числе без заказов:

SELECT
    c.customer_id,
    c.full_name,
    o.order_id
FROM
    customers AS c
    LEFT JOIN orders AS o
        ON o.customer_id = c.customer_id
ORDER BY
    c.customer_id;

22. Все товары и возможные скидки:

SELECT
    p.product_id,
    p.title,
    d.discount_pct
FROM
    products AS p
    LEFT JOIN discounts AS d
        ON d.product_id = p.product_id;

23. Сотрудники и проекты (даже без проектов):

SELECT
    e.emp_id,
    e.full_name,
    pr.project_name
FROM
    employees AS e
    LEFT JOIN project_members AS pm
        ON pm.emp_id = e.emp_id
    LEFT JOIN projects AS pr
        ON pr.project_id = pm.project_id;

24. Студенты и результаты экзаменов (включая пропуски):

SELECT
    s.student_id,
    s.name,
    r.score
FROM
    students AS s
    LEFT JOIN results AS r
        ON r.student_id = s.student_id;

25. Фильмы и отзывы (пустые тоже):

SELECT
    f.film_id,
    f.title,
    r.rating
FROM
    films AS f
    LEFT JOIN reviews AS r
        ON r.film_id = f.film_id;

26. Посты и комментарии (без комментариев тоже):

SELECT
    p.post_id,
    p.title,
    c.comment_id
FROM
    posts AS p
    LEFT JOIN comments AS c
        ON c.post_id = p.post_id;

27. Пациенты и визиты (включая без визитов):

SELECT
    pt.patient_id,
    pt.full_name,
    v.visit_id
FROM
    patients AS pt
    LEFT JOIN visits AS v
        ON v.patient_id = pt.patient_id;

28. Склады и остатки (даже если ноль записей):

SELECT
    w.warehouse_id,
    w.city,
    s.qty_on_hand
FROM
    warehouses AS w
    LEFT JOIN stock AS s
        ON s.warehouse_id = w.warehouse_id;

29. Поставщики и заказы на поставку (если были):

SELECT
    s.supplier_id,
    s.name,
    po.po_id
FROM
    suppliers AS s
    LEFT JOIN purchase_orders AS po
        ON po.supplier_id = s.supplier_id;

30. Клиенты и транзакции (с нулевой активностью):

SELECT
    c.customer_id,
    c.full_name,
    t.tx_id
FROM
    customers AS c
    LEFT JOIN transactions AS t
        ON t.customer_id = c.customer_id;

31. Учителя и курсы (учителя без курсов):

SELECT
    t.teacher_id,
    t.teacher_name,
    c.course_id
FROM
    teachers AS t
    LEFT JOIN course_teachers AS ct
        ON ct.teacher_id = t.teacher_id
    LEFT JOIN courses AS c
        ON c.course_id = ct.course_id;

32. Устройства и последняя телеметрия (может отсутствовать):

SELECT
    d.device_id,
    d.model,
    t.value
FROM
    devices AS d
    LEFT JOIN telemetry AS t
        ON t.device_id = d.device_id
ORDER BY
    d.device_id;

33. Города и статистика населения:

SELECT
    c.city_name,
    s.population
FROM
    cities AS c
    LEFT JOIN stats_city AS s
        ON s.city_id = c.city_id;

34. Счета и привязанные карты (счета без карт):

SELECT
    a.account_no,
    cc.card_last4
FROM
    accounts AS a
    LEFT JOIN credit_cards AS cc
        ON cc.account_no = a.account_no;

35. Рейсы и доступные ворота посадки:

SELECT
    f.flight_no,
    g.gate_no
FROM
    flights AS f
    LEFT JOIN gates AS g
        ON g.flight_no = f.flight_no;

36. Сделки и активности (без активностей тоже):

SELECT
    d.deal_id,
    a.activity_id
FROM
    deals AS d
    LEFT JOIN activities AS a
        ON a.deal_id = d.deal_id;

37. Магазины и текущие акции (могут отсутствовать):

SELECT
    s.store_id,
    pr.promo_code
FROM
    stores AS s
    LEFT JOIN promos AS pr
        ON pr.store_id = s.store_id AND pr.active = 1;

38. Курсы и аудитории (даже без назначений):

SELECT
    c.course_id,
    r.room_no
FROM
    courses AS c
    LEFT JOIN rooms AS r
        ON r.course_id = c.course_id;

39. Пользователи и роли (пользователи без ролей):

SELECT
    u.user_id,
    ur.role
FROM
    users AS u
    LEFT JOIN user_roles AS ur
        ON ur.user_id = u.user_id;

40. Посты и метки (даже без меток):

SELECT
    p.post_id,
    t.tag
FROM
    posts AS p
    LEFT JOIN post_tags AS t
        ON t.post_id = p.post_id;

RIGHT JOIN — всё справа, совпадения слева (20)

41. Заказы и соответствующие клиенты:

SELECT
    o.order_id,
    c.customer_id,
    c.full_name
FROM
    customers AS c
    RIGHT JOIN orders AS o
        ON o.customer_id = c.customer_id;

42. Платежи и их заказы:

SELECT
    p.payment_id,
    o.order_id,
    o.total_amount
FROM
    orders AS o
    RIGHT JOIN payments AS p
        ON p.order_id = o.order_id;

43. Адреса и владельцы адресов:

SELECT
    a.address_id,
    c.customer_id,
    c.full_name
FROM
    customers AS c
    RIGHT JOIN addresses AS a
        ON a.customer_id = c.customer_id;

44. Экзамены и сдававшие студенты:

SELECT
    e.exam_id,
    s.student_id,
    s.name
FROM
    students AS s
    RIGHT JOIN results AS r
        ON r.student_id = s.student_id
    RIGHT JOIN exams AS e
        ON e.exam_id = r.exam_id;

45. Актёры и связанные фильмы:

SELECT
    a.actor_id,
    f.film_id,
    f.title
FROM
    films AS f
    RIGHT JOIN film_cast AS fc
        ON fc.film_id = f.film_id
    RIGHT JOIN actors AS a
        ON a.actor_id = fc.actor_id;

46. Проекты и их участники:

SELECT
    pr.project_id,
    e.emp_id
FROM
    employees AS e
    RIGHT JOIN project_members AS pm
        ON pm.emp_id = e.emp_id
    RIGHT JOIN projects AS pr
        ON pr.project_id = pm.project_id;

47. Категории и принадлежащие товары:

SELECT
    c.category_id,
    p.product_id
FROM
    products AS p
    RIGHT JOIN categories AS c
        ON c.category_id = p.category_id;

48. Визиты и соответствующие пациенты:

SELECT
    v.visit_id,
    pt.patient_id
FROM
    patients AS pt
    RIGHT JOIN visits AS v
        ON v.patient_id = pt.patient_id;

49. Товары и поставщики:

SELECT
    p.product_id,
    s.supplier_id
FROM
    suppliers AS s
    RIGHT JOIN products AS p
        ON p.supplier_id = s.supplier_id;

50. Комментарии и их посты:

SELECT
    c.comment_id,
    p.post_id
FROM
    posts AS p
    RIGHT JOIN comments AS c
        ON c.post_id = p.post_id;

51. Счета и владельцы:

SELECT
    a.account_no,
    c.customer_id
FROM
    customers AS c
    RIGHT JOIN accounts AS a
        ON a.customer_id = c.customer_id;

52. Телеметрии и устройства:

SELECT
    t.telemetry_id,
    d.device_id
FROM
    devices AS d
    RIGHT JOIN telemetry AS t
        ON t.device_id = d.device_id;

53. Преподаватели и курсы:

SELECT
    t.teacher_id,
    c.course_id
FROM
    courses AS c
    RIGHT JOIN course_teachers AS ct
        ON ct.course_id = c.course_id
    RIGHT JOIN teachers AS t
        ON t.teacher_id = ct.teacher_id;

54. Акции и магазины:

SELECT
    pr.promo_id,
    s.store_id
FROM
    stores AS s
    RIGHT JOIN promos AS pr
        ON pr.store_id = s.store_id;

55. Рейсы и самолёты:

SELECT
    f.flight_no,
    a.aircraft_id
FROM
    aircrafts AS a
    RIGHT JOIN flights AS f
        ON f.aircraft_id = a.aircraft_id;

56. Сделки и владельцы:

SELECT
    d.deal_id,
    u.user_id
FROM
    users AS u
    RIGHT JOIN deals AS d
        ON d.owner_id = u.user_id;

57. Склады и остатки:

SELECT
    s.stock_id,
    w.warehouse_id
FROM
    warehouses AS w
    RIGHT JOIN stock AS s
        ON s.warehouse_id = w.warehouse_id;

58. Города и страны:

SELECT
    c.city_id,
    co.iso2
FROM
    countries AS co
    RIGHT JOIN cities AS c
        ON c.iso2 = co.iso2;

59. Билеты и заказы:

SELECT
    t.ticket_no,
    o.order_id
FROM
    orders AS o
    RIGHT JOIN tickets AS t
        ON t.order_id = o.order_id;

60. Карты и счета:

SELECT
    cc.card_id,
    a.account_no
FROM
    accounts AS a
    RIGHT JOIN credit_cards AS cc
        ON cc.account_no = a.account_no;

FULL OUTER JOIN — объединить всё (20)

61. Клиенты и заказы — полный охват:

SELECT
    c.customer_id,
    o.order_id
FROM
    customers AS c
    FULL JOIN orders AS o
        ON o.customer_id = c.customer_id;

62. Транзакции и счета — полное соединение:

SELECT
    t.tx_id,
    a.account_no
FROM
    transactions AS t
    FULL JOIN accounts AS a
        ON a.account_no = t.account_no;

63. Студенты и экзамены — показать всех:

SELECT
    s.student_id,
    e.exam_id
FROM
    students AS s
    FULL JOIN exams AS e
        ON e.major = s.major;

64. Фильмы и актёры — полный список связей:

SELECT
    f.film_id,
    a.actor_id
FROM
    films AS f
    FULL JOIN film_cast AS fc
        ON fc.film_id = f.film_id
    FULL JOIN actors AS a
        ON a.actor_id = fc.actor_id;

65. Сотрудники и проекты — всё и сразу:

SELECT
    e.emp_id,
    p.project_id
FROM
    employees AS e
    FULL JOIN project_members AS pm
        ON pm.emp_id = e.emp_id
    FULL JOIN projects AS p
        ON p.project_id = pm.project_id;

66. Товары и категории — полный охват:

SELECT
    p.product_id,
    c.category_id
FROM
    products AS p
    FULL JOIN categories AS c
        ON c.category_id = p.category_id;

67. Пациенты и визиты — все варианты:

SELECT
    pt.patient_id,
    v.visit_id
FROM
    patients AS pt
    FULL JOIN visits AS v
        ON v.patient_id = pt.patient_id;

68. Посты и комментарии — любые сочетания:

SELECT
    p.post_id,
    c.comment_id
FROM
    posts AS p
    FULL JOIN comments AS c
        ON c.post_id = p.post_id;

69. Склады и товары — любые записи:

SELECT
    w.warehouse_id,
    s.product_id
FROM
    warehouses AS w
    FULL JOIN stock AS s
        ON s.warehouse_id = w.warehouse_id;

70. Курсы и преподаватели — показать всех:

SELECT
    c.course_id,
    t.teacher_id
FROM
    courses AS c
    FULL JOIN course_teachers AS ct
        ON ct.course_id = c.course_id
    FULL JOIN teachers AS t
        ON t.teacher_id = ct.teacher_id;

71. Магазины и акции — полное объединение:

SELECT
    s.store_id,
    p.promo_id
FROM
    stores AS s
    FULL JOIN promos AS p
        ON p.store_id = s.store_id;

72. Рейсы и самолёты — все сочетания:

SELECT
    f.flight_no,
    a.aircraft_id
FROM
    flights AS f
    FULL JOIN aircrafts AS a
        ON a.aircraft_id = f.aircraft_id;

73. Сделки и ответственные — полный охват:

SELECT
    d.deal_id,
    u.user_id
FROM
    deals AS d
    FULL JOIN users AS u
        ON u.user_id = d.owner_id;

74. Склады и остатки — включить пустые:

SELECT
    s.stock_id,
    w.warehouse_id
FROM
    stock AS s
    FULL JOIN warehouses AS w
        ON s.warehouse_id = w.warehouse_id;

75. Города и страны — полный охват:

SELECT
    c.city_id,
    co.iso2
FROM
    cities AS c
    FULL JOIN countries AS co
        ON co.iso2 = c.iso2;

76. Билеты и заказы — любые пары:

SELECT
    t.ticket_no,
    o.order_id
FROM
    tickets AS t
    FULL JOIN orders AS o
        ON o.order_id = t.order_id;

77. Карты и счета — включить одиночные:

SELECT
    cc.card_id,
    a.account_no
FROM
    credit_cards AS cc
    FULL JOIN accounts AS a
        ON a.account_no = cc.account_no;

78. Учителя и курсы — показать всех:

SELECT
    t.teacher_id,
    c.course_id
FROM
    teachers AS t
    FULL JOIN course_teachers AS ct
        ON ct.teacher_id = t.teacher_id
    FULL JOIN courses AS c
        ON c.course_id = ct.course_id;

79. Телеметрия и устройства — полный охват:

SELECT
    t.telemetry_id,
    d.device_id
FROM
    telemetry AS t
    FULL JOIN devices AS d
        ON d.device_id = t.device_id;

80. Пользователи и роли — полный набор:

SELECT
    u.user_id,
    r.role
FROM
    users AS u
    FULL JOIN user_roles AS r
        ON r.user_id = u.user_id;

CROSS JOIN — декартово произведение (20)

81. Комбинация всех клиентов и акций:

SELECT
    c.customer_id,
    p.promo_code
FROM
    customers AS c
    CROSS JOIN promos AS p;

82. Все товары в каждом регионе:

SELECT
    pr.product_id,
    rg.region_code
FROM
    products AS pr
    CROSS JOIN regions AS rg;

83. Курсы, умноженные на семестры:

SELECT
    c.course_id,
    s.semester
FROM
    courses AS c
    CROSS JOIN semesters AS s;

84. Сотрудники и навыки — все пары:

SELECT
    e.emp_id,
    sk.skill
FROM
    employees AS e
    CROSS JOIN skills AS sk;

85. Студенты и учебные группы:

SELECT
    s.student_id,
    g.group_code
FROM
    students AS s
    CROSS JOIN groups AS g;

86. Фильмы и жанры:

SELECT
    f.film_id,
    g.genre
FROM
    films AS f
    CROSS JOIN genres AS g;

87. Пользователи и роли — все сочетания:

SELECT
    u.user_id,
    r.role
FROM
    users AS u
    CROSS JOIN roles AS r;

88. Визиты и услуги клиники:

SELECT
    v.visit_id,
    svc.service_code
FROM
    visits AS v
    CROSS JOIN services AS svc;

89. Проекты и задачи — планирование:

SELECT
    p.project_id,
    t.task_type
FROM
    projects AS p
    CROSS JOIN task_types AS t;

90. Поставщики и регионы присутствия:

SELECT
    s.supplier_id,
    r.region
FROM
    suppliers AS s
    CROSS JOIN regions AS r;

91. Устройства и метрики IoT:

SELECT
    d.device_id,
    m.metric
FROM
    devices AS d
    CROSS JOIN metrics AS m;

92. Клиники и страховые планы:

SELECT
    cl.clinic_id,
    pl.plan_code
FROM
    clinics AS cl
    CROSS JOIN insurance_plans AS pl;

93. Банковские продукты и валюты:

SELECT
    bp.product_code,
    cur.code AS currency
FROM
    bank_products AS bp
    CROSS JOIN currencies AS cur;

94. Игры и уровни сложности:

SELECT
    g.game_id,
    l.level_name
FROM
    games AS g
    CROSS JOIN levels AS l;

95. Магазины и сезоны распродаж:

SELECT
    s.store_id,
    season.season_name
FROM
    stores AS s
    CROSS JOIN sale_seasons AS season;

96. Курсы и аудитории:

SELECT
    c.course_id,
    r.room_no
FROM
    courses AS c
    CROSS JOIN rooms AS r;

97. Отделы и рабочие смены:

SELECT
    d.dept_id,
    s.shift_name
FROM
    departments AS d
    CROSS JOIN shifts AS s;

98. Товар и размерная сетка:

SELECT
    p.product_id,
    sz.size_code
FROM
    products AS p
    CROSS JOIN sizes AS sz;

99. Клиенты и способы доставки:

SELECT
    c.customer_id,
    sp.ship_method
FROM
    customers AS c
    CROSS JOIN ship_methods AS sp;

100. Рекламные каналы и регионы:

SELECT
    ch.channel,
    rg.region
FROM
    channels AS ch
    CROSS JOIN regions AS rg;

SELF JOIN — соединение таблицы с самой собой (20)

101. Сотрудник и его менеджер:

SELECT
    e.emp_id,
    e.full_name AS employee,
    m.full_name AS manager
FROM
    employees AS e
    INNER JOIN employees AS m
        ON m.emp_id = e.manager_id;

102. Клиент и приглашённый им друг:

SELECT
    c.customer_id,
    c.full_name AS inviter,
    r.full_name AS invited
FROM
    customers AS c
    INNER JOIN customers AS r
        ON r.invited_by = c.customer_id;

103. Товар и его аксессуар:

SELECT
    p.product_id,
    p.title AS product,
    a.title AS accessory
FROM
    products AS p
    INNER JOIN products AS a
        ON a.parent_product_id = p.product_id;

104. Фильм и его продолжение:

SELECT
    f1.film_id,
    f1.title AS original,
    f2.title AS sequel
FROM
    films AS f1
    INNER JOIN films AS f2
        ON f2.prequel_id = f1.film_id;

105. Студент и назначенный наставник:

SELECT
    s.student_id,
    s.name AS student,
    t.name AS mentor
FROM
    students AS s
    INNER JOIN students AS t
        ON t.mentor_of = s.student_id;

106. Проект и вложенный подпроект:

SELECT
    p.project_id,
    p.title AS parent,
    c.title AS child
FROM
    projects AS p
    INNER JOIN projects AS c
        ON c.parent_id = p.project_id;

107. Комментарий и ответ на него:

SELECT
    c1.comment_id,
    c1.body AS original,
    c2.body AS reply
FROM
    comments AS c1
    INNER JOIN comments AS c2
        ON c2.reply_to = c1.comment_id;

108. Пользователь и его дубликат-аккаунт:

SELECT
    u1.user_id,
    u1.email AS primary_email,
    u2.email AS duplicate_email
FROM
    users AS u1
    INNER JOIN users AS u2
        ON u2.primary_user = u1.user_id;

109. Курс и его последующая версия:

SELECT
    c1.course_id,
    c1.title AS v1,
    c2.title AS v2
FROM
    courses AS c1
    INNER JOIN courses AS c2
        ON c2.prev_course = c1.course_id;

110. Пациент и указанный родственник:

SELECT
    p1.patient_id,
    p1.full_name AS patient,
    p2.full_name AS relative
FROM
    patients AS p1
    INNER JOIN patients AS p2
        ON p2.related_to = p1.patient_id;

111. Город и его город-побратим:

SELECT
    c1.city_id,
    c1.city_name AS city,
    c2.city_name AS twin_city
FROM
    cities AS c1
    INNER JOIN cities AS c2
        ON c2.twin_of = c1.city_id;

112. Счёт и дочерний субсчёт:

SELECT
    a1.account_no AS parent_acc,
    a2.account_no AS child_acc
FROM
    accounts AS a1
    INNER JOIN accounts AS a2
        ON a2.parent_acc = a1.account_no;

113. Устройство и запасная единица:

SELECT
    d1.device_id AS primary_dev,
    d2.device_id AS spare_dev
FROM
    devices AS d1
    INNER JOIN devices AS d2
        ON d2.replaces = d1.device_id;

114. Сделка и предшествующая сделка клиента:

SELECT
    d1.deal_id AS current_deal,
    d2.deal_id AS prev_deal
FROM
    deals AS d1
    INNER JOIN deals AS d2
        ON d2.next_deal = d1.deal_id;

115. Магазин и франчайзи:

SELECT
    s1.store_id AS hq,
    s2.store_id AS franchise
FROM
    stores AS s1
    INNER JOIN stores AS s2
        ON s2.parent_store = s1.store_id;

116. Рейс и предыдущий сегмент:

SELECT
    f1.flight_no AS current_seg,
    f2.flight_no AS prev_seg
FROM
    flights AS f1
    INNER JOIN flights As f2
        ON f2.next_flight = f1.flight_no;

117. Категория и подкатегория:

SELECT
    c1.category_id AS parent_cat,
    c2.category_id AS child_cat
FROM
    categories AS c1
    INNER JOIN categories AS c2
        ON c2.parent_id = c1.category_id;

118. Группа и подгруппа:

SELECT
    g1.group_id AS parent_group,
    g2.group_id AS child_group
FROM
    groups AS g1
    INNER JOIN groups AS g2
        ON g2.parent_id = g1.group_id;

119. Пост и переопубликованный пост:

SELECT
    p1.post_id AS original_post,
    p2.post_id AS repost
FROM
    posts AS p1
    INNER JOIN posts AS p2
        ON p2.repost_of = p1.post_id;

120. Сотрудник и преемник:

SELECT
    e1.emp_id AS current_holder,
    e2.emp_id AS successor
FROM
    employees AS e1
    INNER JOIN employees AS e2
        ON e2.succeeds = e1.emp_id;

Заключение

join sql даёт полный набор инструментов для объединения данных: INNER, LEFT, RIGHT, FULL, CROSS и SELF JOIN. Выбирайте тип соединения по задаче, индексируйте ключи соединений, проверяйте условия ON и используйте фильтрацию в WHERE и упорядочивание через ORDER BY.