CODEWITHSUNDEEP
sqloracle-database
krise
krise

Reputation: 535

Oracle: In Operator for null values?

Lets say I have two tables A and B which have the exact same columns. I want to write a query that selects all rows from A that also exist in B, so I would want to write something like:

select att1, att2, att3 from A 
where (att1, att2, att3) in (select att1, att2, att3 from B)

However, this does not work when my tables are allowed to contain null values, as the in operator checks equality using =, so for example att1 = att1. This won't work on null values as null = ... always results in false.

Whats the best/easiest way to do the same thing as the query above but that also works when there are null values (returns true if the 2 compared attributes are null)

Upvotes: 0

Views: 105

Answers (1)

Littlefoot
Littlefoot

Reputation: 142958

How about INTERSECT set operator, instead?

select att1, att2, att3 from A 
intersect
select att1, att2, att3 from B

Upvotes: 4

Related Questions