What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?
What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?
They count different things: rows, non-NULL values, and unique non-NULL values.
-- orders(id, coupon): (1,'A'), (2,'A'), (3,'B'), (4,NULL)
SELECT COUNT(*) AS all_rows, -- 4
COUNT(coupon) AS with_coupon, -- 3 (NULL ignored)
COUNT(DISTINCT coupon) AS distinct_coupons -- 2 ('A' and 'B')
FROM orders;
| Expression | Counts |
|---|---|
COUNT(*) |
every row, regardless of NULLs |
COUNT(1) |
same as COUNT(*) (the constant is never NULL); no speed difference |
COUNT(col) |
rows where col is not NULL |
COUNT(DISTINCT col) |
different non-NULL values of col |
LEFT JOIN, count a column from the right table:Đă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.