Last active
March 30, 2017 06:50
-
-
Save lenguyenthedat/e9036de66a2400bf2b2a870b2e10fd6e to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Bad use of aggregation | |
SELECT | |
users.id, | |
users.email, | |
users.name, | |
MAX(users.created_at) AS created_at, -- not necessary | |
COUNT(orders.id) AS orders_count | |
FROM orders | |
LEFT JOIN users | |
ON users.id = orders.user_id | |
GROUP BY users.id, users.email, users.name -- only users.id should be in here | |
-- Good but using sub-query | |
SELECT | |
users.id, | |
users.email, | |
users.name, | |
users.created_at, | |
user_orders.orders_count | |
FROM ( -- subqueries are hard to read | |
SELECT | |
orders.user_id, | |
COUNT(orders.id) AS orders_count | |
FROM orders | |
GROUP BY orders.user_id | |
) user_orders | |
LEFT JOIN users | |
ON users.id = user_orders.user_id | |
-- Better | |
WITH user_orders AS ( | |
SELECT | |
orders.user_id, | |
COUNT(orders.id) AS orders_count | |
FROM orders | |
GROUP BY orders.user_id | |
) | |
SELECT | |
users.id, | |
users.email, | |
users.name, | |
users.created_at, | |
user_orders.orders_count | |
FROM user_orders | |
LEFT JOIN users | |
ON users.id = user_orders.user_id |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment