CODEWITHSUNDEEP
t-sql
Felipe Ramalho
Felipe Ramalho

Reputation: 47

What's the difference between these joins?

What's the difference between

SELECT COUNT(*)
FROM TOOL T
    LEFT OUTER JOIN PREVENT_USE P ON T.ID = P.TOOL_ID
WHERE
    P.ID IS NULL

and

SELECT COUNT(*)
FROM TOOL T
    LEFT OUTER JOIN PREVENT_USE P ON T.ID = P.TOOL_ID AND P.ID IS NULL

?

Upvotes: 0

Views: 33

Answers (1)

Alexander Toptygin
Alexander Toptygin

Reputation: 407

The bottom query is equivalent to

SELECT COUNT(*)
FROM TOOL T

since it is not limiting the result set but rather producing a joined table with a lot of null fields for the right part of the join.

The first query is a left anti join.

Upvotes: 1

Related Questions