CODEWITHSUNDEEP
sqlpostgresqljoin
rap-2-h
rap-2-h

Reputation: 32058

Does the result differs when writing condition in JOIN over in WHERE clause?

Is there a difference in terms of results in using a condition in where versus in join? In my actual query, I can put AND "noteReads"."id" IS NULL in where or in join. Does this matter?

First version with AND "noteReads"."userId" = 1 in where clause:

SELECT count(distinct("notes"."id")) FROM "notes"
LEFT OUTER JOIN "noteReads" ON "notes"."id" = "noteReads"."noteId" 
  AND "noteReads"."userId" = 1 
WHERE (
  "notes"."archivedAt" IS NULL
  AND "notes"."authorId" != 1
  AND "noteReads".id IS NULL
);

Second version with AND "noteReads"."userId" = 1 in join clause:

SELECT count(distinct("notes"."id")) FROM "notes"
LEFT OUTER JOIN "noteReads" ON "notes"."id" = "noteReads"."noteId" 
  AND "noteReads"."userId" = 1
  AND "noteReads"."id" IS NULL
WHERE (
  "notes"."archivedAt" IS NULL
  AND "notes"."authorId" != 1
);

Upvotes: 0

Views: 39

Answers (2)

kamal chhetri
kamal chhetri

Reputation: 51

here in this case we have to understand ,

  1. the where clause is on the result set of left join. that means the records which are not matching the joining criteria will populate right table field as null and you are filtering those record which are not matching.

  2. if you use the same condition in left join clause it will only match those records which are having null values because you set it as a matching criteria.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271023

Absolutely.

When you have the condition "noteReads"."id" IS NULL in the ON clause for a LEFT JOIN, then it is only considering rows where the id is NULL. And I'm guessing that is never true.

When you have the condition in the WHERE clause, then if filters out non-matching rows. That is very different.

Note: This explanation is specific to the second table in a LEFT JOIN (or the first table in a RIGHT JOIN, although I generally recommend using LEFT JOINs).

Upvotes: 4

Related Questions