What happens when you add a where/filter clause on a select view when the view already contains the where/filter clause inside?

Question

I am using a union inside a view and filtering out all items that has EQ or NECF as shown below:

REPLACE VIEW X.VIEW_NAME
AS
LOCKING ROW FOR ACCESS
SELECT
*
FROM X.TABLENAME A
WHERE A.SIS <> 'EQ' OR A.SERVICE_NUMBER <> 'NECF'
UNION ALL
SELECT
*
FROM X.TABLENAME B
WHERE B.SIS <> 'EQ' OR B.SERVICE_NUMBER <> 'NECF';

Now if I filter on the view again as

SEL *
FROM X.VIEWNAME A
WHERE A.SIS = 'EQ' OR A.SERVICE_NUMBER = 'NECF';

I do get records! Was not the view supposed to filter out the EQ or NECF. When I do the second select query on the view, I should have received 0 records?

sticky bit · Accepted Answer · 2021-07-09T02:30:32.943

Your view contains another then you anticipate.

...
WHERE A.SIS <> 'EQ'
       OR A.SERVICE_NUMBER <> 'NECF'
...

retains a row when sis is 'EQ' but service_number isn't 'NECF' and also when service_number is 'NECF' but sis isn't EQ. Note that you used the OR operator. If you want to filter out any row where sis is 'EQ' or service_number is 'NECF', you need to use AND:

...
WHERE A.SIS <> 'EQ'
      AND A.SERVICE_NUMBER <> 'NECF'
...

Also note that you can easily see that by using De Morgan's laws:
We want to filter out rows where

sis = 'EQ'
 OR service_number = 'NECF'

That means we want to retain rows where the negation of that is true (to have that in the WHERE clause). And the negation is:

sis <> 'EQ'
 AND service_number <> 'NECF'

That is negating the operators and change OR to AND.

What's also funny is that you do a UNION ALL of the same set -- the queries, semantically, are the same (the different aliases change nothing).

What happens when you add a where/filter clause on a select view when the view already contains the where/filter clause inside?

1 Answers1