29 / 100

NATURAL JOIN

Tabiiy birlashtirish

Kirish: ushbu darsda siz bir xil nomli ustunlar asosida jadvallarni birlashtirish uchun PostgreSQL NATURAL JOIN bandidan qanday foydalanishni o'rganasiz.

PostgreSQL NATURAL JOIN haqida umumiy tushuncha

PostgreSQLda NATURAL JOIN ikkala jadvalda bir xil nomga ega bo'lgan ustunlar asosida jadvallarni avtomatik tarzda birlashtirish imkonini beradi.

Mana NATURAL JOIN ning sintaksisi:

query.sql

SELECT select_list
FROM table1
NATURAL [INNER, LEFT, RIGHT, FULL] JOIN table2;

Ushbu sintaksisda:

NATURAL JOIN ikkita jadvalni — table1 va table2 ni birlashtiradi. Moslashish uchun bu jadvallar bir xil ustun nomlariga va mos ma'lumotlar turlariga ega bo'lishi kerak.
NATURAL JOIN INNER JOIN, LEFT JOIN, RIGHT JOIN yoki FULL JOIN turlarida bo'lishi mumkin. Agar NATURAL kalit so'zidan keyin birlashtirish turi ko'rsatilmasa, PostgreSQL sukut bo'yicha INNER JOIN dan foydalanadi.
Agar table1 va table2 da hech qanday umumiy nomli ustun bo'lmasa, NATURAL JOIN xuddi CROSS JOIN kabi harakat qiladi.

Namunaviy jadvallarni tayyorlash

Birinchi, brands nomli yangi jadval yarating:

query.sql

CREATE TABLE brands (
  brand_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  brand_name VARCHAR(255) NOT NULL
);

Ikkinchi, brands jadvaliga qatorlar kiriting:

query.sql

INSERT INTO
  brands (brand_name)
VALUES
  ('Apple'),
  ('Samsung'),
  ('Google') 
RETURNING *;

Natija:

query.sql

 brand_id | brand_name 
----------+------------
        1 | Apple
        2 | Samsung
        3 | Google

Uchinchi, products nomli yangi jadval yarating:

query.sql

CREATE TABLE products (
  product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_name VARCHAR(100) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  brand_id INT,
  FOREIGN KEY (brand_id) REFERENCES brands (brand_id)
);

Nihoyat, products jadvaliga qatorlar qo'shing:

query.sql

INSERT INTO
  products (product_name, price, brand_id)
VALUES
  ('iPhone 14 Pro', 999.99, 1),
  ('iPhone 15 Pro', 1199.99, 1),
  ('Galaxy S23 Ultra', 1149.47, 2),
  ('Oppo Find Flip', 499.99, NULL) 
RETURNING *;

Natija:

query.sql

 product_id |   product_name   |  price  | brand_id 
------------+------------------+---------+----------
          1 | iPhone 14 Pro    |  999.99 |        1
          2 | iPhone 15 Pro    | 1199.99 |        1
          3 | Galaxy S23 Ultra | 1149.47 |        2
          4 | Oppo Find Flip   |  499.99 |     NULL

Ikkala jadvalda ham umumiy bo'lgan ustun — brand_id.

PostgreSQL NATURAL JOIN amaliy misollari

Biz brands va products jadvallarini birlashtirish uchun NATURAL JOIN dan foydalanamiz.

1. NATURAL INNER JOIN

Quyidagi so'rov products va brands jadvallari o'rtasida NATURAL INNER JOIN amalini bajaradi:

query.sql

SELECT
  product_name,
  price,
  brand_name
FROM
  products
  NATURAL INNER JOIN brands;

Natija:

query.sql

   product_name   |  price  | brand_name 
------------------+---------+------------
 iPhone 14 Pro    |  999.99 | Apple
 iPhone 15 Pro    | 1199.99 | Apple
 Galaxy S23 Ultra | 1149.47 | Samsung

2. NATURAL LEFT JOIN

Quyidagi misol products va brands jadvallarini NATURAL LEFT JOIN orqali birlashtiradi:

query.sql

SELECT
  product_name,
  price,
  brand_name
FROM
  products
  NATURAL LEFT JOIN brands;

Natija:

query.sql

   product_name   |  price  | brand_name 
------------------+---------+------------
 iPhone 14 Pro    |  999.99 | Apple
 iPhone 15 Pro    | 1199.99 | Apple
 Galaxy S23 Ultra | 1149.47 | Samsung
 Oppo Find Flip   |  499.99 | NULL

3. NATURAL RIGHT JOIN

Quyidagi so'rov products va brands jadvallarini NATURAL RIGHT JOIN yordamida birlashtiradi:

query.sql

SELECT
  product_name,
  price,
  brand_name
FROM
  products
  NATURAL RIGHT JOIN brands;

Natija:

query.sql

   product_name   |  price  | brand_name 
------------------+---------+------------
 iPhone 14 Pro    |  999.99 | Apple
 iPhone 15 Pro    | 1199.99 | Apple
 Galaxy S23 Ultra | 1149.47 | Samsung
 NULL             |    NULL | Google

4. NATURAL FULL JOIN

Quyidagi misol products va brands jadvallarini NATURAL FULL JOIN orqali birlashtiradi:

query.sql

SELECT
  product_name,
  price,
  brand_name
FROM
  products
  NATURAL FULL JOIN brands;

Natija:

query.sql

   product_name   |  price  | brand_name 
------------------+---------+------------
 iPhone 14 Pro    |  999.99 | Apple
 iPhone 15 Pro    | 1199.99 | Apple
 Galaxy S23 Ultra | 1149.47 | Samsung
 Oppo Find Flip   |  499.99 | NULL
 NULL             |    NULL | Google

PostgreSQL NATURAL JOIN xavfi va ogohlantirishlar (Caveats)

PostgreSQL NATURAL JOIN qatorlarni moslashtirish uchun bir xil nomli ustunlarga tayanadi. Agar kelajakda jadvallar tuzilishi o'zgartirilsa yoki yangi ustun qo'shilsa, so'rov kutilmaganda noto'g'ri ishlashi mumkin.

Masalan:

query.sql

DROP TABLE products, brands;

CREATE TABLE brands (
  brand_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE products (
  product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  brand_id INT,
  FOREIGN KEY (brand_id) REFERENCES brands (brand_id)
);

INSERT INTO
  brands (name)
VALUES
  ('Apple'),
  ('Samsung'),
  ('Google') 
RETURNING *;

INSERT INTO
  products (name, price, brand_id)
VALUES
  ('iPhone 14 Pro', 999.99, 1),
  ('iPhone 15 Pro', 1199.99, 1),
  ('Galaxy S23 Ultra', 1149.47, 2),
  ('Oppo Find Flip', 499.99, NULL) 
RETURNING *;

Biz quyidagi so'rov ikki jadvalni faqat brand_id ustuni bo'yicha birlashtiradi deb kutamiz:

query.sql

SELECT
  p.name product_name,
  p.price,
  b.name brand_name
FROM
  products p
  NATURAL INNER JOIN brands b;

Biroq, u bo'sh natija qaytaradi:

query.sql

 product_name | price | brand_name 
--------------+-------+------------

Sababi: endi products va brands jadvallarining ikkalasida ham brand_id hamda name ustunlari mavjud. Bunday holda NATURAL JOIN qatorlarni moslashtirish uchun ikkala ustun qiymatlarini ham solishtiradi. Ya'ni so'rov quyidagicha harakat qiladi:

query.sql

SELECT
  p.name AS product_name,
  p.price,
  b.name AS brand_name
FROM
  products p
  INNER JOIN brands b ON b.brand_id = p.brand_id
  AND b.name = p.name;

Mahsulot nomi hech qachon brend nomiga teng bo'lmagani sababli ('iPhone 14 Pro' != 'Apple'), birlashtirish sharti yolg'on (false) bo'lib, natija butunlay bo'sh chiqadi.

Xulosa

PostgreSQL NATURAL JOIN umumiy nomli ustunlar orqali ikkita jadvalni avtomatik ravishda birlashtiradi.
NATURAL JOIN dan ehtiyotkorlik bilan foydalaning, ishlab chiqarish (production) muhitida aniq INNER JOIN ... ON yoki USING dan foydalanish xavfsizroq hisoblanadi.

QBilimingizni sinab ko'ring

1 / 5

1. NATURAL JOIN qaysi ustunlar bo'yicha jadvallarni birlashtiradi?

Oldingi

Keyingi