CODEWITHSUNDEEP
mysqlsqljoin
Eloy Fernández Franco
Eloy Fernández Franco

Reputation: 1372

What is the best way to filter result on JOIN (sql)

I have a doubt since a little time ago... what is the best way to filter the entries on a sql join.... i mean, is better on the WHERE clause or on the ON of the join??

For example, what is better? This snippet using the WHERE

SELECT 
    customer.users_id AS CustID, 
    FROM customer 
    LEFT JOIN visit ON customer.users_id = visit.CustID
    WHERE visit.active = 1

Or this other way (that filter on the ON):

SELECT 
    customer.users_id AS CustID, 
    FROM customer 
    LEFT JOIN visit ON (customer.users_id = visit.CustID AND visit.active=1)

Thank you.

Upvotes: 1

Views: 106

Answers (2)

Brandon
Brandon

Reputation: 70

Historically for me, its more efficient to use the and statement on the join instead of adding it to the where clause. 

SELECT 
customer.users_id AS CustID, 
FROM customer 
LEFT JOIN visit ON (customer.users_id = visit.CustID AND visit.active=1)

There are cases where both cases could be more efficient, but for me I prefer using the and in the join statement. I believe if its used in the where clause when the query runs it will have to index everything in the visit table then filter the results based on visit.active =1. This where in my experience its quicker to use the and statement and the performance is to do with how the query optimizer indexes the data.

Update: For me when dealing with millions of records I notice better performance where said above. If the # of records are smaller maybe there is no difference for you.

Upvotes: 1

Rakesh Kumar
Rakesh Kumar

Reputation: 4420

It depends on type of situation whatever is. Here is some text on both:-

First one works as Inner Join, without where it returns all rows from left table including those are null in right table. Where condition return all those rows which have visit 1. Means it reduce all those rows from result which have active other then 1 or null rows correspondence to left table

Example:-

custome_id visit
1          1
2          1

Second one returns rows which have null entries corresponding to right table.

Example:-

custome_id visit
1          1
2          1
3          null
4          null

Note:- In your both statements customer_id can't be null as it have o/p from left table only.

Upvotes: 1

Related Questions