CODEWITHSUNDEEP
sqlpostgresql
readytotaste
readytotaste

Reputation: 299

SQL - When & How to filter with JOIN

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?

Upvotes: 4

Views: 2412

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

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.

Upvotes: 4

J Sidhu
J Sidhu

Reputation: 677

I my experience with SQL, when using an inner join (aka. join ), order of tables after the FROM clause has never made a difference in results. Also, it has not made any difference if I used a 'where' clause or an 'and' clause after the join. But this behavior is limited ONLY and ONLY to Inner joins. If you are using left or right joins, the order as well as Where/And clauses make a lot of difference in the results returned.

I'd like to add that if you have a lot of nulls in your keys(which should never be the case), that might affect the result set in inner joins when switching between Where and And or changing the sequence of tables after from.

Upvotes: 2

Related Questions