Clever Geek Handbook

Is there a difference between place and connection?

I have two identical requests. At the beginning I useWhere 

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 the 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 a performance difference between these two queries? What's the best way?

+3


source to share


2 answers


It is highly likely that both approaches will yield closer performance, if not the same. Let the query planner do the dirty work. :)

The question is to keep good readability in your SQL, so in this case, you should:



  • Place a filter expression in a sentence WHERE

    ;
  • Put just the join expression on JOIN

    . That is, only the relationship between tables goes here.
+1


source


The INNER JOIN

alternatives are equivalent, and any decent SQL engine will execute it in the most efficient way, no matter how you write it.



Personally, I prefer to only compare IDs in a sentence ON

, while additional filters go into a sentence WHERE

.

+1


source






More articles:


All Articles