Is there any difference between where and join?

Question

I have two identical queries. In first I use Where

SELECT dings.id, doorbots.id
FROM dings
INNER JOIN doorbots ON dings.doorbot_id = doorbots.id
WHERE doorbots.id = 1615131 AND 
      deleted_at is NULL
ORDER BY dings.created_at;

In second I put all filters in join

SELECT dings.id, doorbots.id
FROM dings
INNER JOIN doorbots ON dings.doorbot_id = doorbots.id AND 
           doorbots.id = 1615131 AND 
           deleted_at is NULL
ORDER BY dings.created_at;

I have a composite index: doorbot_id_idx btree (doorbot_id) WHERE deleted_at IS NULL

Is there any performance difference between this two queries? What is the best way to do?

Answer 1

It is very likely that both approaches will give a closer performance, if not the same. Let the query planner do the dirty job. :)

The question is to maintain a good readability in your SQL, so in this case you must:

• Put filter expression on WHERE clause;
• Put only joining expression on JOIN. That is, only the relationship between the tables goes here.