SELF-JOIN

Kirish: ushbu darsda siz bitta jadval ichidagi qatorlarni bir-biri bilan solishtirish uchun PostgreSQL o'ziga birlashtirish (self-join) usulidan qanday foydalanishni o'rganasiz.

PostgreSQL Self-join texnikasiga kirish

PostgreSQLda self-join — bu bitta jadvalni INNER JOIN, LEFT JOIN yoki RIGHT JOIN yordamida o'z-o'ziga birlashtirish imkonini beruvchi kuchli texnikadir. Jadval o'ziga birlashtirilgani sababli, bunday turdagi birlashtirish self-join (o'ziga birlashtirish) deb ataladi.

Self-join texnikasi bir xil jadval ichidagi qatorlarni bir-biri bilan solishtirish (masalan, iyerarxik munosabatlar, xodim va rahbar, kategoriya va uning ota kategoriyasi) kerak bo'lganda juda foydali hisoblanadi.

PostgreSQL bir xil SQL so'rovi ichida bir xil jadval nomini bir necha bor to'g'ridan-to'g'ri ishlatishga yo'l qo'ymaydi — aks holda sintaktik xatolik yuz beradi. Bitta so'rov ichida bitta jadvaldan bir necha marta foydalanish uchun siz jadvalga har xil jadval taxalluslari (table aliases) biriktirishingiz shart. Bunday holda, PostgreSQL ushbu taxalluslarni alohida jadvallar sifatida ko'rib chiqadi.

Mana self-join sintaksisi:

SELECT
  column1,
  column2,
  column3
FROM
  table_name t1
  INNER JOIN table_name t2 ON t1.column1 = t2.column2;

Ushbu sintaksisda:

• Birinchi, FROM bandida t1 taxallusi bilan jadval nomini ko'rsating.
• Ikkinchi, INNER JOIN bandida aynan o'sha jadval nomini boshqa t2 taxallusi bilan bering.
• Uchinchi, ON bandida jadval ichidagi qatorlarni moslashtirish shartini yozing.

INNER JOIN bandidan tashqari, SELECT so'rovida LEFT JOIN yoki RIGHT JOIN bandlaridan ham foydalanishingiz mumkin.

PostgreSQL Self-Join amaliy misollari

Keling, PostgreSQL self-join amaliyotini misollar orqali ko'rib chiqamiz.

Namunaviy jadvalni yaratish

Birinchi, iyerarxik daraxt tuzilmasiga ega categories nomli yangi jadval yarating:

CREATE TABLE categories (
  category_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  parent_id INT,
  FOREIGN KEY (parent_id) REFERENCES categories (category_id) ON DELETE CASCADE
);

categories jadvalida parent_id ustuni mavjud bo'lib, u o'sha jadvalning o'zidagi category_id asosiy kalitiga havola qiluvchi tashqi kalit ustunidir.

• Eng yuqori darajadagi asosiy kategoriyaning parent_id ustunida NULL qiymati bo'ladi.
• Quyi toifadagi (subkategoriya) qatorning parent_id ustunida esa o'zining ota toifasiga tegishli category_id qiymati saqlanadi.

Ikkinchi, categories jadvaliga ma'lumotlar kiriting:

INSERT INTO
  categories (category_id, name, parent_id)
VALUES
  (1, 'Electronics', NULL),
  (2, 'Mobile Devices', 1),
  (3, 'Smartphones', 2),
  (4, 'Tablets', 2),
  (5, 'Accessories', 2),
  (6, 'Wearables', 2),
  (7, 'Home Entertainment', 1),
  (8, 'Televisions', 7),
  (9, 'Audio Systems', 7),
  (10, 'Computers', 1),
  (11, 'Laptops', 10),
  (12, 'Desktops', 10)
RETURNING *;

Natija:

 category_id |        name        | parent_id 
-------------+--------------------+-----------
           1 | Electronics        |      NULL
           2 | Mobile Devices     |         1
           3 | Smartphones        |         2
           4 | Tablets            |         2
           5 | Accessories        |         2
           6 | Wearables          |         2
           7 | Home Entertainment |         1
           8 | Televisions        |         7
           9 | Audio Systems      |         7
          10 | Computers          |         1
          11 | Laptops            |        10
          12 | Desktops           |        10

1. INNER JOIN yordamida self-join

Quyidagi so'rov har bir kategoriyani va uning bevosita ota kategoriyasini (parent) tanlab olish uchun INNER JOIN dan foydalanadi:

SELECT
  c.name AS category,
  p.name AS parent
FROM
  categories c
  INNER JOIN categories p ON c.parent_id = p.category_id;

Natija:

      category      |       parent       
--------------------+--------------------
 Mobile Devices     | Electronics
 Smartphones        | Mobile Devices
 Tablets            | Mobile Devices
 Accessories        | Mobile Devices
 Wearables          | Mobile Devices
 Home Entertainment | Electronics
 Televisions        | Home Entertainment
 Audio Systems      | Home Entertainment
 Computers          | Electronics
 Laptops            | Computers
 Desktops           | Computers

Ushbu natijada eng yuqori darajadagi Electronics kategoriyasi ko'rinmaydi, chunki uning parent_id ustuni NULL bo'lgani sababli INNER JOIN shartiga mos kelmadi. Eng yuqori darajadagi kategoriyani ham natijaga kiritish uchun bizga LEFT JOIN bilan self-join kerak bo'ladi.

2. LEFT JOIN yordamida self-join

Quyidagi so'rov barcha kategoriyalarni, shu jumladan eng yuqori ota kategoriyani ham natijaga kiritish uchun LEFT JOIN dan foydalanadi:

SELECT
  c.name AS category,
  p.name AS parent
FROM
  categories c
  LEFT JOIN categories p ON c.parent_id = p.category_id;

Natija:

      category      |       parent       
--------------------+--------------------
 Electronics        | NULL
 Mobile Devices     | Electronics
 Smartphones        | Mobile Devices
 Tablets            | Mobile Devices
 Accessories        | Mobile Devices
 Wearables          | Mobile Devices
 Home Entertainment | Electronics
 Televisions        | Home Entertainment
 Audio Systems      | Home Entertainment
 Computers          | Electronics
 Laptops            | Computers
 Desktops           | Computers

Endi Electronics kategoriyasi ham ro'yxatda paydo bo'ldi va uning ota kategoriyasi (parent) yo'qligi sababli unga NULL ko'rsatildi.

Xulosa

• Self-join — bu jadvalning o'z-o'ziga birlashtirilishidir.
• Bitta jadval ichidagi qatorlarni bir-biri bilan solishtirish yoki iyerarxik ma'lumotlarni chiqarish uchun self-join texnikasidan foydalaning.
• Self-join ishlatilganda bir xil jadvalga turli jadval taxalluslari (masalan, c va p) berilishi shart.