SQL - When & How to filter with JOIN

Question

Suppose I have two tables.. One is customers and the other is orders. Orders has a foreign key that joins to the customers table. How should I go about returning data from both tables:

1. Filtered on a field in the orders table and,

2. Filtered on a field in the customers table?

Is using WHERE to filter after the JOIN in my SELECT statement the correct way to go, or putting in an AND within the JOIN statement? And would I have to use one method for one of the above situations and the other for the other one?

For example,

SELECT customers.customer_type, orders.grant_date

FROM orders

JOIN customers ON customers.customer_id = orders.customer_id

WHERE orders.order_id = 3;

or

SELECT customers.customer_type, orders.grant_date

FROM orders

JOIN customers ON customers.customer_id = orders.customer_id

AND orders.order_id = 3;

I guess I can summarize my questions as:

a. Which table should I pair with my FROM statement? Should it be the one which has the foreign key i.e. orders? Or does it depend on the situation?

b. How should I filter the data? With a WHERE or an AND with the JOIN? And how is one different from the other i.e. when should I use one over the other in my two situations?

Answer 1

It doesn't matter whether you do

FROM orders
JOIN customers ON customers.customer_id = orders.customer_id

or

FROM customers 
JOIN orders ON customers.customer_id = orders.customer_id

Which table you make the first table is up to you. Here I would make orders the first table, because it is orders along with their customer information you are showing in your results.

With an inner join it doesn't make a difference either, whether you put criteria in your WHERE clause or ON clause.

However, it looks strange to join customers on a condition on orders:

JOIN customers ON customers.customer_id = orders.customer_id AND orders.order_id = 3

This is not how an ON clause is supposed to work. So either:

FROM orders
JOIN customers ON customers.customer_id = orders.customer_id
WHERE orders.order_id = 3

or

FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.order_id = 3

or

FROM customers
JOIN orders ON customers.customer_id = orders.customer_id AND orders.order_id = 3

Many people prefer the last query over the second last, because you can easily convert the inner join into an outer join. So the general advice is: put criteria on the first table in WHERE and the criteria on other tables in ON. Make this a rule of thumb.