Order of tables in INNER JOIN - how important is this?

Screen Link:
https://app.dataquest.io/m/190/building-and-organizing-complex-queries/4/creating-views

My Code:

CREATE VIEW chinook.customer_gt_90_dollars AS
    SELECT * 
        FROM chinook.customer AS c
        INNER JOIN chinook.invoice AS i ON i.customer_id = c.customer_id
        GROUP BY i.customer_id
        HAVING SUM(i.total) > 90; 

SELECT * 
    FROM chinook.customer_gt_90_dollars;

What I expected to happen: I expected this would return a table with the list of customers who made purchases of more than USD90

What actually happened:

The table returned, but it contained list of customers (and all related columns from “customer” table) with purchases of less than 90 dollars. See the screenshot below:

Replace this line with the output/error

I have compared my code with the correct answer and I only see a difference in that I have changed the order of joining the tables (“customer” and “invoice”) in INNER JOIN part. What I did is (you can see that in my code) I have selected all columns in “customer” table (as the assignment said: " Create a view called customer_gt_90_dollars: * The view should contain the columns from customers , in their original order.") and then I joined this table with the “invoice” table. Later I have grouped everything by “i.customer_id” but the table returned did not meet the >90 criterion. The difference with the correct answer in my view is only the order of joining the tables. Does it make such a huge difference? OR am I missing something important here?

Thanks for your assistance in this!

3 Likes

I took me writing a post here to immediately understand the issue. I should have used:

SELECT c.* instead of just “SELECT *”

It all worked even with the different order of INNER JOIN. AS I suspected the order does not matter.

Maybe my post will be helpful to anyone else, so I am leaving it here.

Good luck coding!

3 Likes