CODEWITHSUNDEEP
t-sql
stackuser
stackuser

Reputation: 672

Issues with the OR clause

I am having some trouble with the OR clause. I am trying to join two large tables and given below is a sample data from the tables

Table1 (t1) 

      vendor        addr1           city         zip
       ADT      PO BOX 371956      PITSBURGH     15250

Table2 (t2)

      vendor    addr1       city             zip
      ADT   PO Box 371956   Pittsburgh      15250-7956

The first two select statements given below display one row of data from the two tables. In the third select, I have an OR clause and this does not display any rows. The OR clause should not affect the result set.

 select *
 from t1
 left join t2
 on t1.addr1 = t2.addr1


  select *
   from t1
  left join t2
  on t1.addr1 = t2.addr1
   and (t1.city = t2.city)

the select below does not display any data. WHY?

 select *
  from t1
 left join t2
 on t1.addr1 = t2.addr1
 and ((t1.city = t2.city) or (t1.zip = t2.zip))

Upvotes: 0

Views: 55

Answers (1)

Rubens Farias
Rubens Farias

Reputation: 57996

On your last query you have:

t1.addr1 = t2.addr2 AND
(
    t1.city = t2.city OR
    t1.zip  = t2.zip
)

Considering your sample data:

t1."PO BOX 371956" = t2."PO Box 371956" AND -- TRUE
(
    t1."PITSBURGH" = t2."Pittsburgh" OR     -- FALSE
    t1."15250" = t2."15250-7956"            -- FALSE
)

You got:

= TRUE AND (FALSE OR FALSE)
= TRUE AND FALSE
= FALSE

So, no soup for you.

Upvotes: 2

Related Questions