Oracle: In Operator for null values?

Question

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)

Littlefoot · Accepted Answer · 2020-08-10 08:10:07Z

4

How about INTERSECT set operator, instead?

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

answered Aug 10, 2020 at 8:10

Littlefoot

144k15 gold badges42 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

krise Over a year ago

thank you, this works. Another question: Is there a way to use this in a DELETE statement? So delete all from A that also exist in B ?

Atif Over a year ago

DELETE a.*, b.* FROM table1 as a, table2 as b WHERE (A.ATT1, A.ATT2, A.ATT3) IN (select att1, att2, att3 from A intersect select att1, att2, att3 from B);

Gordon Linoff Over a year ago

@Krise . . . Note that this doesn't do exactly what you are requesting, because INTERSECT removes duplicates. That said, it is probably what you want, but worth pointing out.

Collectives™ on Stack Overflow

Oracle: In Operator for null values?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related