What are a self join, a cross join and a natural join, and when would you use each?
What are a self join, a cross join and a natural join, and when would you use each?
These are special uses of the join family beyond the basic inner and outer joins.
A table joined to itself (use two aliases). It fits hierarchies and "compare rows in the same table".
-- employee and manager names
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
-- products with the same price as another product
SELECT a.name, b.name
FROM products a
JOIN products b ON a.price = b.price AND a.id < b.id; -- a.id < b.id removes mirrored pairs
Returns the cartesian product: every row of A paired with every row of B (n x m rows). It is intentional for generating combinations:
SELECT s.size, c.color
FROM sizes s CROSS JOIN colors c; -- all size and color combinations
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.