JOIN से पंक्तियाँ क्यों बढ़ती हैं: कुंजियाँ, संबंध और परिणाम का स्तर

WITH t1(num) AS (VALUES (1), (2), (3)),
     t2(num) AS (VALUES (1), (3), (5))
SELECT t1.num AS left_num, t2.num AS right_num
FROM t1 CROSS JOIN t2;
-- Nine pairs in this illustrative dataset.

WITH orders(order_id) AS (VALUES (1), (2)),
     order_items(item_id, order_id) AS (VALUES (10, 1), (11, 1), (12, 2))
SELECT o.order_id, i.item_id
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
ORDER BY o.order_id, i.item_id;
-- Three pairs: order 1 appears with two different items.

WITH t1(num) AS (VALUES (1), (2), (3)),
     t2(num) AS (VALUES (1), (3), (5))
SELECT t1.num AS left_num, t2.num AS right_num
FROM t1 LEFT JOIN t2 ON t1.num = t2.num
ORDER BY t1.num;
-- Three rows; the right value for left key 2 is NULL.

WITH order_items(item_id, order_id) AS (VALUES (10, 1), (11, 1), (12, 2))
SELECT order_id, COUNT(*) AS item_count
FROM order_items
WHERE order_id IS NOT NULL
GROUP BY order_id
HAVING COUNT(*) > 1;
-- Order 1 has two items in this illustrative dataset.

CREATE TABLE orders (
    order_id integer PRIMARY KEY
);
CREATE TABLE order_items (
    item_id integer PRIMARY KEY,
    order_id integer NOT NULL REFERENCES orders(order_id)
);

SELECT o.order_id, i.item_count
FROM orders AS o
JOIN (
    SELECT order_id, COUNT(*) AS item_count
    FROM order_items
    GROUP BY order_id
) AS i ON i.order_id = o.order_id;

WITH t1(num) AS (VALUES (1), (2), (3)),
     t2(num) AS (VALUES (1), (3), (5))
SELECT t1.num
FROM t1 INNER JOIN t2 ON t1.num = t2.num
ORDER BY t1.num;
-- Two rows: matching keys 1 and 3.