CODEWITHSUNDEEP
sqloracle-databasejoin
Snowy Coder Girl
Snowy Coder Girl

Reputation: 5518

AND Statements in JOIN vs WHERE

I am trying to understand why the following happens (example code below).

-- Returns 1,000
SELECT COUNT(*)
FROM TABLE_ONE t1
WHERE t1.FIELD_ONE = 'Hello';

-- Returns 1,000
SELECT COUNT(*)
FROM TABLE_ONE t1
LEFT OUTER JOIN TABLE_TWO t2 ON t2.TABLE_ONE_ID = t1.ID
WHERE t1.FIELD_ONE = 'Hello'
AND t2.FIELD_TWO = 'Goodbye';

-- Returns 83,500
SELECT COUNT(*)
FROM TABLE_ONE t1
LEFT OUTER JOIN TABLE_TWO t2 ON (t2.TABLE_ONE_ID = t1.ID AND t2.FIELD_TWO = 'Goodbye')
WHERE t1.FIELD_ONE = 'Hello';

I understand that left outer joins will always include the left value even if a right value is not found, etc. However, I thought that adding additional conditions to JOIN clauses would restrict what is done in the JOIN.

So for example if I had entry A from TABLE_ONE, then it would look for a TABLE_TWO value that met the 2 conditions. If one wasn't found, it would just be blank. But that entry A would only appear once in the result set. So I am confused why I am getting so many more results then what is actually in the original TABLE_ONE query.

UPDATE

To explain more what I am trying to understand. The resulting query (not using the COUNT) results in something like this.

Table_One_Name    Table_Two_Value
+++++++++++++++++++++++++++++++++++
Entry A           Goodbye
Entry A           
Entry A
Entry A
Entry B           Goodbye
Entry B

I don't understand why the JOIN is adding rows when the 2 conditions are not met.

Upvotes: 1

Views: 90

Answers (2)

FrankPl
FrankPl

Reputation: 13315

The WHERE condition is applied after the JOIN condition, and hence removes all the records containing NULL in t2.FIELD_TWO, thus effectively denying the purpose of the "OUTER" part of the JOIN.

Upvotes: 0

Sankara
Sankara

Reputation: 1479

Where clause

filters the records, first case has an additional condition AND t2.FIELD_TWO = 'Goodbye' which filters more records(about 73,500 records)

-- Returns 1,000
SELECT COUNT(*)
FROM TABLE_ONE t1
LEFT OUTER JOIN TABLE_TWO t2 ON t2.TABLE_ONE_ID = t1.ID
WHERE t1.FIELD_ONE = 'Hello'
AND t2.FIELD_TWO = 'Goodbye';

Left outer join brings all the records even it does not have a match In your second case AND t2.FIELD_TWO = 'Goodbye this is part of the left outer join so it brings matching and non matching records

Upvotes: 1

Related Questions