DB version: 10.2, 11.2, 12.1
I came across the following post from last week.
https://community.oracle.com/message/14181326#14181326
I am an Oracle DBA with 5 years of experience and I never knew about this basic rule. Since this rule applies to SQL as well, I think I should have known this.
In the internet, I found the notation of DeMorgan's law a bit difficult to understand. But from the SQL example provided by PeaceMonger, I got the concept.
From Sven's informative post, I gather that DeMorgan's law applies to situations involving double negation . ie the Following query (Query1) is wrong.
Query1 shown below involves double negation (department != 'IT' or JOB != 'ACCOUNTANT' )
You will think Query1 will return only those employees whose salary is more than 800 and whose department is not IT and whose JOB title is not Accountant. But, see the result below . KATE is returned despite her department is IT . HUANG is returned despite his job is ACCOUNTANT. From all those informative posts , I learnt that it is because expressions across OR operator evaluates to TRUE which in unintended
My question: Are there any scenarios similar to 'DeMorgan's Law' which I should be aware of ? If so, could you please demonstrate that with the sample data given below. It is similar to the sample data provided by PeaceMonger
create table employees (emp_name varchar2(35) , department varchar2(30), job varchar2(30), salary number) ;
insert into employees VALUES ('SANDRA', 'FINANCE', 'MANAGER', 2000);
insert into employees VALUES ('JOHN', 'HR', 'CLERK', 1000);
insert into employees VALUES ('KATE', 'IT', 'PROGRAMMER', 1700);
insert into employees VALUES ('HUANG', 'FINANCE', 'ACCOUNTANT', 1200);
insert into employees VALUES ('JIM', 'MARKETING', 'SALESMAN' , 700);
commit;
set lines 200
col job format a10
col emp_name format a10
SQL> select * from employees;
EMP_NAME DEPARTMENT JOB SALARY
---------- ------------------------------ ---------- ----------
SANDRA FINANCE MANAGER 2000
JOHN HR CLERK 1000
KATE IT PROGRAMMER 1700
HUANG FINANCE ACCOUNTANT 1200
JIM MARKETING SALESMAN 700
Query1 ( Query is wrong because expressions across OR operator evaluates to TRUE which in unintended)
SQL> select * from employees
where salary > 800
and ( department != 'IT' or JOB != 'ACCOUNTANT' );
EMP_NAME DEPARTMENT JOB SALARY
---------- ------------------------------ ---------- ----------
SANDRA FINANCE MANAGER 2000
JOHN HR CLERK 1000
KATE IT PROGRAMMER 1700
HUANG FINANCE ACCOUNTANT 1200