IN وNOT IN للمجموعات

IN: المطابقة مقابل قائمة

SELECT * FROM users WHERE country IN ('US', 'CA', 'GB');

-- Equivalent to:
SELECT * FROM users
WHERE country = 'US' OR country = 'CA' OR country = 'GB';

استخدام IN مع استعلام فرعي

SELECT * FROM orders
WHERE user_id IN (
  SELECT id FROM users WHERE country = 'US'
);

NOT IN: استبعاد قائمة

SELECT * FROM users WHERE status NOT IN ('banned', 'deleted');

مشكلة NULL في NOT IN

-- Suppose blocked_names contains NULL:
SELECT name FROM users
WHERE name NOT IN (SELECT excluded_name FROM blocked_names);
-- → 0 rows, silently

-- Safer: filter out NULLs explicitly, or use NOT EXISTS:
SELECT name FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM blocked_names b WHERE b.excluded_name = u.name
);

لماذا يكون NOT EXISTS أكثر أمانًا

IN مقابل OR — سهولة القراءة

-- Hard to read:
WHERE country = 'US' OR country = 'CA' OR country = 'GB' OR ...

-- Clear:
WHERE country IN ('US', 'CA', 'GB', ...)

استخدام IN مع الصفوف المركبة

SELECT * FROM order_items
WHERE (order_id, product_id) IN (
  (1, 42), (2, 99), (3, 17)
);

استخدام الفهارس

-- Plan: Bitmap Index Scan + Bitmap Heap Scan
SELECT * FROM products WHERE id IN (1, 5, 99, 121, 4421);

قوائم IN الكبيرة

SELECT * FROM products WHERE id = ANY(ARRAY[1,2,3,...]);

استخدام IN مع قائمة فارغة

-- Use empty array trick in PostgreSQL:
SELECT * FROM products WHERE id = ANY($1::bigint[]);
-- Pass {} to get zero matches without rewriting the SQL.

IN مقابل JOIN

-- With IN:
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE pro);

-- With JOIN:
SELECT o.*
FROM orders o
JOIN users  u ON u.id = o.user_id
WHERE u.pro;

مراجعة

تحقق سريع