LEFT JOIN وRIGHT JOIN وعمليات الربط الخارجية

LEFT JOIN: الاحتفاظ بكل صفوف اليسار

SELECT u.id, u.email, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

-- Users without orders still appear, with order_id = NULL.

RIGHT JOIN

SELECT u.id, u.email, o.id
FROM orders o
RIGHT JOIN users u ON u.id = o.user_id;

-- Equivalent to LEFT JOIN with sides swapped:
SELECT u.id, u.email, o.id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

FULL OUTER JOIN

SELECT u.email, o.id AS order_id
FROM users u
FULL OUTER JOIN orders o ON o.user_id = u.id;

العثور على التطابقات المفقودة

SELECT u.id, u.email
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;        -- users who have never placed an order

LEFT JOIN مع دالة تجميعية

SELECT u.id, u.email, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email
ORDER BY order_count DESC;

موضع التصفية مهم

-- Drops users without paid orders entirely:
SELECT u.id, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid'
GROUP BY u.id;

-- Keeps users with no paid orders:
SELECT u.id, COUNT(o.id)
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id AND o.status = 'paid'
GROUP BY u.id;

عمليات LEFT JOIN متعددة

SELECT u.email, o.id, p.name
FROM users u
LEFT JOIN orders o   ON o.user_id = u.id
LEFT JOIN products p ON p.id = o.product_id;

أداء LEFT JOIN

الناتج NULL من الربط الخارجي

SELECT u.email, COALESCE(SUM(o.total), 0) AS revenue
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.email;

CROSS JOIN — عندما تريدون كل الأزواج

SELECT d.date, u.id
FROM users u
CROSS JOIN generate_series('2024-01-01', '2024-01-07', INTERVAL '1 day') AS d(date);

أنماط الربط المضاد

-- LEFT JOIN ... WHERE IS NULL
SELECT u.* FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

-- NOT EXISTS (often the planner's favourite)
SELECT u.* FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

مراجعة

تحقق سريع