I just wanted to share another way to correctly write the query for Lesson 9 in Multiple Tables, WITH Clause.
SELECT
customer_name AS name,
COUNT(subscription_id) AS subscriptions
FROM orders
LEFT JOIN customers
ON orders.customer_id = customers.customer_id
GROUP BY name
;
Not arguing it’s better, just using what we’ve recently learned in the other lessons to provide an alternate solution.
Great point!
That is an alternate solution here, but it might not be once the dataset grows. Let me explain.
For this particular dataset, the query results are the same, albeit in a different order. However, with different data, there would be 2 distinct differences between the results of your query and the results of the Codecademy query.
First, your query uses a LEFT JOIN, where the CC query uses a JOIN (the standard join is an INNER JOIN). Here, the results were the same, but only because the orders tables doesn’t have orders from people who are not in the customers table. A JOIN would leave out any record that wasn’t in both tables, while a LEFT JOIN would keep all records in orders, but leave out any records that were only in customers. With the data provided for this exercise, both joins only leave out the customer with the id of 10 (they did not make an order), but with the result could be different with other data.
The other thing to take note of is that in your query you are grouping by customer_name rather than customer_id. While this works here, you can imagine that it wouldn’t work in datasets where multiple people have the same name. An id is always unique to the user, while a name may not be.
So yes, you are correct that in this exercise the WITH...AS syntax wasn’t necessary. However, it’s good to understand how your alternate solution might not work as the data grows.
Great observation and happy coding!
Thanks for pointing all that out. Knowing when a particular method is the more general and flexible option is what’s going to take the longest to bake in.
cheers
matt