WHERE

Kirish: ushbu darsda siz SELECT, UPDATE va DELETE kabi so'rov va buyruqlarda ma'lumot oluvchi qatorlarni filtrlash uchun PostgreSQL WHERE bandidan qanday foydalanishni o'rganasiz.

PostgreSQL WHERE bandi

WHERE bandi SELECT, UPDATE va DELETE so'rov va buyruqlarida shartlarga mos keluvchi qatorlarni filtrlash imkonini beradi. WHERE bandi bilan SELECT so'rovining sintaksisi:

SELECT
  column1,
  column2,
  ...
FROM
  table_name
WHERE
  condition;

Bu sintaksisda:

• Birinchi, FROM bandida ma'lumot olishni istagan jadval nomini ko'rsating.
• Ikkinchi, SELECT bandida natija to'plamiga kiritilishi kerak bo'lgan ustunlar ro'yxatini bering.
• Uchinchi, WHERE bandida SELECT so'rovi qaytarishi kerak bo'lgan qatorlarni belgilovchi shartni ko'rsating.

condition — bu true, false yoki unknown qiymatini baholaydigan mantiqiy ifodadir. SELECT so'rovi faqat condition true bo'lgan qatorlarni qaytaradi.

PostgreSQL so'rovidagi bandlarni quyidagi tartibda baholaydi:

1. FROM
2. WHERE
3. SELECT

PostgreSQL WHERE bandi namunasi

WHERE bandi bilan ishlash uchun inventories jadvalidan foydalanamiz:

CREATE TABLE inventories (
  name VARCHAR(255),
  brand VARCHAR(50),
  quantity INT,
  price DECIMAL(19, 2)
);

INSERT INTO inventories (name, brand, quantity, price)
VALUES
  ('iPhone 14 Pro', 'Apple', 10, 999.99),
  ('Galaxy S23 Ultra', 'Samsung', 15, 1199.99),
  ('Pixel 7 Pro', 'Google', 8, 899.99),
  ('Xperia 1 IV', 'Sony', 7, 1299.99);

Quyidagi so'rov WHERE bandidan foydalanib, inventories jadvalidan brendi Apple bo'lgan mahsulotlarni qaytaradi:

SELECT
  name,
  brand,
  price
FROM
  inventories
WHERE
  brand = 'Apple';

Natija:

 name          | brand | price
---------------+-------+--------
 iPhone 14 Pro | Apple | 999.99

brand = 'Apple' sharti Apple ga teng bo'lmagan brendlardagi qatorlar uchun false, Apple uchun true qiymatini qaytaradi.

Taqqoslash operatorlari

WHERE bandida shartlarni aniqlash uchun taqqoslash operatorlaridan foydalanish mumkin:

Quyidagi so'rov narxi 1000 dan katta bo'lgan mahsulotlarni qaytaradi:

SELECT
  name,
  price
FROM
  inventories
WHERE
  price > 1000;

Natija:

 name             | price
------------------+---------
 Galaxy S23 Ultra | 1199.99
 Xperia 1 IV      | 1299.99

AND operatori bilan bir nechta shartni birlashtirish mumkin:

SELECT
  name,
  brand,
  price
FROM
  inventories
WHERE
  brand = 'Apple' AND price > 500;

Natija:

 name          | brand | price
---------------+-------+--------
 iPhone 14 Pro | Apple | 999.99

WHERE bandi va ustun taxallusi

WHERE bandi SELECT bandidan oldin baholanadi. Shuning uchun WHERE bandida SELECT da e'lon qilingan ustun taxalluslaridan foydalanib bo'lmaydi. Masalan, quyidagi so'rov xatolik beradi:

SELECT
  name,
  quantity * price AS amount
FROM
  inventories
WHERE
  amount > 10000;  -- XATO: amount WHERE da ishlamaydi

amount WHERE bandida mavjud bo'lmaydi, chunki WHERE bandi SELECT bandidan oldin baholanadi. Bu so'rovni to'g'irlash uchun WHERE bandida to'liq ifodadan foydalaning:

SELECT
  name,
  quantity * price AS amount
FROM
  inventories
WHERE
  quantity * price > 10000;

Natija:

 name             | amount
------------------+----------
 Galaxy S23 Ultra | 17999.85

Xulosa

• Ma'lumotlarni filtrlash uchun SELECT so'rovida WHERE bandidan foydalaning.
• PostgreSQL FROM, WHERE, SELECT tartibida baholaydi.
• WHERE bandida SELECT da e'lon qilingan ustun taxalluslaridan foydalanib bo'lmaydi.