2.4 WHERE Clause – DCodeMan

WHERE is a reserved word in Oracle and is used to filter rows during selection. It can be used with SELECT, UPDATE, DELETE statements and so known as WHERE clause.

Whenever WHERE clause is implemented, the condition is always evaluated to either a TRUE or a FALSE value or UNKNOWN. WHERE clause can take one condition or can even operate on multiple component conditions together evaluated to TRUE/FALSE/UNKNOWN.

WHERE Deptno = 10;   --Condition Results True

WHERE Deptno = -10;  --Condition Results False

WHERE Deptno = NULL; --Condition Results UNKNOWN

Any operator either ANSI or SQL * PLUS operator can participate in WHERE clause condition. The different ANSI comparison operators are:

- Equality operator: =
- Not Equality operator: <>, !=, ^=
- Greater or Less Than: >, >=, <, <=

Q : Get the employee name, designation, department number, salary and join date for employees who are working in department 10?

Ans:

SELECT Ename, Job, Deptno, Sal, HireDate
  FROM Emp
 WHERE Deptno = 10;

Oracle never demands the column in the WHERE clause condition, to be a part of the projection list in the SELECT statement. But putting the condition column in SELECT list helps in cross verifying the result.

SELECT Ename, Deptno, Sal
  FROM Emp
 WHERE Job = 'MANAGER';

SELECT Ename, Job, Deptno, Sal
  FROM Emp
 WHERE Job = 'MANAGER';

WHERE clause can accept multiple conditions through logical operators (AND/OR). Each component condition is evaluated to TRUE/FALSE/UNKNOWN and all conditions together is evaluated to TRUE/FALSE/UNKNOWN.

Q : Get the employee name, designation, department number and salary for all managers who are working in department 10?

Ans:

SELECT Ename, Job, Deptno, Sal
  FROM Emp
 WHERE Deptno = 10 AND Job = 'MANAGER';

Q : Get the employee name, designation, department number and salary for all managers who are working in department 10 and earning more than 2500?

Ans:

SELECT Ename, Job, Deptno, Sal
  FROM Emp
 WHERE Deptno = 10 
   AND Job = 'MANAGER'
   AND Sal > 2500;

The character strings and dates participating in the WHERE clause condition, should be enclosed in single quotation marks. Character values are case sensitive and date values are format sensitive ‘DD-MON-YY’ or ‘DD-MON-YYYY’.

SELECT Ename, Job, Deptno, Sal, HireDate
  FROM Emp
 WHERE Job <> 'MANAGERS';

SELECT Ename, Job, Deptno, Sal, HireDate
  FROM Emp
 WHERE HireDate = '01-MAY-81';

--Oracle raises error if invalid date format is specified

SELECT Ename, Job, Deptno, Sal, HireDate
  FROM Emp
 WHERE HireDate = '01-05-81';

Oracle DATE datatypes can be compared with less than “<” or greater than “>” operators.

SELECT Ename, Job, Deptno, Sal, HireDate
  FROM Emp
 WHERE HireDate <= '01-MAY-85';

SELECT Ename||' Works in '||Deptno||'th Department'||' and is a '||Job "Employee Info"
  FROM Emp
 WHERE HireDate >= '31-DEC-1981';

You can specify expressions on either side or both sides of comparison operator in the WHERE clause.

SELECT Ename, Job, Deptno, Sal
  FROM Emp
 WHERE Sal = 1000 + (100 * 5);

SELECT Ename, Job, Deptno, Sal
  FROM Emp
 WHERE Sal + 1000 <= (3000 - 500);

Table columns in WHERE clause can appear on either side of equal to operator.

SELECT Ename, Job, Deptno, Sal
  FROM Emp
 WHERE Job = 'CLERK';

SELECT Ename, Job, Deptno, Sal
  FROM Emp
 WHERE 'CLERK' = Job;

There is no restriction that the condition in WHERE clause should contain anything related to the table columns. The condition can be anything, but must be evaluated to TRUE/ FALSE.

SELECT Ename, Job, Deptno, Sal
  FROM Emp
 WHERE 1 = 1;

WHERE 1 = 1 always evaluates to TRUE. So, a query having WHERE 1 = 1 outputs all rows. Similarly WHERE 1 = 2 always evaluates FALSE and results no rows for a SELECT query.

SELECT Ename, Job, Deptno, Sal
  FROM Emp
 WHERE 1 = 2;

SELECT Ename, Job, Deptno, Sal
  FROM Emp
 WHERE 1 <> 2;

WHERE clause condition can contain expressional columns but can not contain the column alias in the same query. Expressional columns are also called virtual columns or calculated columns.

SELECT Ename, Job, Deptno, Sal, (Sal + NVL(Comm, 0)) * 12 TotalSal
  FROM Emp
 WHERE (Sal + NVL(Comm, 0)) * 12 <= 15000;

--Oracle reports error- Column alias is not accepted in WHERE clause

SELECT Ename, Job, Deptno, Sal, (Sal + NVL(Comm, 0)) * 12 TotalSal
  FROM Emp
 WHERE TotalSal <= 15000;
WHERE TotalSal <= 15000
*
ERROR at line 3:
ORA-00904: "TOTALSAL": invalid identifier

Handling NULL in filter condition

NULL in WHERE clause is handled only through IS NULL and IS NOT NULL operators. Any other operators with NULL produces UNKNOWN which is different than FALSE.

Go through following examples where we have illustrated how NULL in Oracle behaves with different conditions.

SELECT Ename, Job, Deptno, Sal
  FROM Emp
 WHERE NULL = 10;

SELECT Ename, Job, Deptno, Sal
  FROM Emp
 WHERE NULL <> 10;

SELECT Ename, Job, Deptno, Sal
  FROM Emp
 WHERE NULL < 10;

SELECT Ename, Job, Deptno, Sal
  FROM Emp
 WHERE NULL >= 10;

SELECT Ename, Job, Deptno, Sal
  FROM Emp
 WHERE NULL = NULL;

SELECT Ename, Job, Deptno, Sal
  FROM Emp
 WHERE NULL <> NULL;

SELECT Ename, Job, Deptno, Sal
  FROM Emp
 WHERE NULL IS NULL;

SELECT Ename, Job, Deptno, Sal
  FROM Emp
 WHERE NULL IS NOT NULL;

Words from Oracle documentation
Oracle Database (19c) currently treats empty strings (a character value with a length of zero ) as NULL. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.

SELECT Ename, Job, Deptno, Sal
  FROM Emp
 WHERE '' IS NULL;

SELECT Ename, Job, Deptno, Sal
  FROM Emp
 WHERE '' IS NOT NULL;

SELECT Ename, Job, Deptno, Sal
  FROM Emp
 WHERE 10 IS NULL;

SELECT Ename, Job, Deptno, Sal
  FROM Emp
 WHERE 10 IS NOT NULL;

SELECT Ename, Job, Deptno, Sal
  FROM Emp
 WHERE Comm IS NULL;

SELECT Ename, Job, Deptno, Sal
  FROM Emp
 WHERE Comm IS NOT NULL;

Q : Which are the functions in Oracle that can handle NULL?

Ans: Oracle provides NVL, NVL2, COALESCE, NULLIF to handle NULL. Check out below examples.

SELECT Ename, Job, Deptno, Sal
  FROM Emp
 WHERE NVL(NULL, 0) = 0;

SELECT Ename, Job, Deptno, Sal
  FROM Emp
 WHERE coalesce(NULL, 0) = 0;

Oracle 19c SQL

Handling NULL in filter condition

How can we help?