What is the difference between IN, EXISTS and BETWEEN?
What is the difference between IN, EXISTS and BETWEEN?
They answer different questions: IN tests membership in a list or subquery, EXISTS tests whether a subquery returns any row, and BETWEEN tests a range.
SELECT * FROM orders WHERE status IN ('paid', 'shipped');
SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE total > 500);
x IN (a, b) is shorthand for x = a OR x = b.
SELECT c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.total > 500);
EXISTS is a semi-join: it stops at the first matching row and returns only TRUE or FALSE, so the select list of the subquery is irrelevant. It does not multiply rows like a join can.
SELECT * FROM products WHERE price BETWEEN 10 AND 20; -- 10 <= price <= 20, both ends included
Đă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.