Day 5 - The WHERE Clause

📌 Key Takeaways

• BETWEEN a AND b includes both a and b. If a is greater than b, not a single row comes back.
• In LIKE, % is zero or more characters and _ is exactly one character. To search for these symbols themselves, define an escape character with ESCAPE.
• NULL can't be found with =. Use only IS NULL / IS NOT NULL.
• The precedence of logical operators is NOT > AND > OR. When in doubt, add parentheses.
• If a NOT IN (…) list contains even one NULL, the result is 0 rows.

WHERE Picks Rows

If SELECT picks columns, WHERE picks rows. Only rows where the condition is true (TRUE) stay in the result.

SELECT ENAME, SAL FROM EMP WHERE SAL >= 3000;

ENAME             SAL
---------- ----------
SCOTT            3000
KING             5000
FORD             3000

• String and date values are wrapped in single quotes. String values are case-sensitive.
• 'clerk' can't find CLERK. The result is 0 rows.

SELECT COUNT(*) FROM EMP WHERE JOB = 'clerk';

  COUNT(*)
----------
         0

Ranges and Lists — BETWEEN, IN

BETWEEN a AND b

Same as a <= value AND value <= b. Both endpoints are included.

SELECT ENAME, SAL FROM EMP WHERE SAL BETWEEN 1250 AND 1600;

ENAME             SAL
---------- ----------
ALLEN            1600
WARD             1250
MARTIN           1250
TURNER           1500
MILLER           1300

• WARD and MARTIN at 1250 and ALLEN at 1600 are all included.
• The smaller value must come first. BETWEEN 1600 AND 1250 is SAL >= 1600 AND SAL <= 1250, which can never be true.

SELECT COUNT(*) FROM EMP WHERE SAL BETWEEN 1600 AND 1250;

  COUNT(*)
----------
         0

NOT BETWEEN is outside the range, that is, value < a OR value > b. BETWEEN can be used with dates too. HIREDATE BETWEEN DATE '1982-01-01' AND DATE '1987-12-31' picks three employees: MILLER, SCOTT, and ADAMS.

IN (list)

IN is the same as joining = conditions with OR. DEPTNO IN (10, 40) is DEPTNO = 10 OR DEPTNO = 40.

SELECT ENAME, DEPTNO FROM EMP WHERE DEPTNO IN (10, 40);

ENAME          DEPTNO
---------- ----------
CLARK              10
KING               10
MILLER             10

• Department 40 has no employees, so only the three in department 10 appear. It's not an error if the list contains values that aren't there.

You can also group several columns and compare them as pairs. The (job, department) pair must exactly match a pair in the list.

SELECT ENAME, JOB, DEPTNO FROM EMP WHERE (JOB, DEPTNO) IN (('MANAGER', 20), ('CLERK', 30));

ENAME      JOB           DEPTNO
---------- --------- ----------
JONES      MANAGER           20
JAMES      CLERK             30

Pattern Search — LIKE

SELECT ENAME FROM EMP WHERE ENAME LIKE '_A%';

ENAME
----------
WARD
MARTIN
JAMES

• LIKE is case-sensitive too. LIKE 'k%' can't find KING.
• Writing LIKE 'KING' without any wildcard is the same as = 'KING'.

ESCAPE — Searching for % or _ Itself

Even if you want to find _ literally, LIKE '%_%' means "any string of one or more characters." With ESCAPE you define an escape character, and the _ or % right after that character is read as an ordinary character.

SELECT 'OK' R FROM DUAL WHERE 'SALES_2026' LIKE '%\_%' ESCAPE '\';

R
--
OK

SELECT COUNT(*) FROM DUAL WHERE 'SALES2026' LIKE '%\_%' ESCAPE '\';

  COUNT(*)
----------
         0

• SALES2026, which has no underscore, is not matched. It is a value that would have matched if written as '%_%' without ESCAPE.
• You can choose any character as the escape character. '50%' LIKE '%#%' ESCAPE '#' looks for strings that end with %.

NULL Is Found Only with IS NULL

Comparing NULL with anything gives UNKNOWN, neither true nor false. WHERE keeps only true rows, so rows that are UNKNOWN drop out.

⚠️ COMM = NULL is not an error; it gives 0 rows. The syntax is valid, so it runs, but no row ever becomes true. When looking for NULL, always use IS NULL, and IS NOT NULL for the opposite.

SELECT COUNT(*) FROM EMP WHERE COMM = NULL;

  COUNT(*)
----------
         0

SELECT ENAME, COMM FROM EMP WHERE COMM IS NOT NULL;

ENAME            COMM
---------- ----------
ALLEN             300
WARD              500
MARTIN           1400
TURNER              0

For the same reason, NULL rows drop out of negative conditions too. The 10 employees whose COMM is NULL are not included in COMM <> 300.

SELECT ENAME, COMM FROM EMP WHERE COMM <> 300;

ENAME            COMM
---------- ----------
WARD              500
MARTIN           1400
TURNER              0

Logical Operators and Precedence

Without parentheses, AND binds before OR.

SELECT ENAME, JOB, SAL FROM EMP WHERE JOB = 'SALESMAN' OR JOB = 'MANAGER' AND SAL > 2900;

ENAME      JOB              SAL
---------- --------- ----------
ALLEN      SALESMAN        1600
WARD       SALESMAN        1250
JONES      MANAGER         2975
MARTIN     SALESMAN        1250
TURNER     SALESMAN        1500

• It is actually read as JOB = 'SALESMAN' OR (JOB = 'MANAGER' AND SAL > 2900). That's why all four low-paid SALESMEN came out.

If you wanted "SALESMAN or MANAGER with salary over 2900," group the OR first with parentheses.

SELECT ENAME, JOB, SAL FROM EMP WHERE (JOB = 'SALESMAN' OR JOB = 'MANAGER') AND SAL > 2900;

ENAME      JOB              SAL
---------- --------- ----------
JONES      MANAGER         2975

Negation Operators and the NOT IN Pitfall

NOT IN (a, b) is column <> a AND column <> b. If the list contains NULL, column <> NULL becomes UNKNOWN, and since the parts are joined with AND, the whole thing can never be true.

SELECT COUNT(*) FROM EMP WHERE MGR NOT IN (7698, 7839, NULL);

  COUNT(*)
----------
         0

Conversely, IN is joined with OR, so even with NULL mixed in, the search works normally on the remaining values.

SELECT ENAME, MGR FROM EMP WHERE MGR IN (7566, NULL);

ENAME             MGR
---------- ----------
SCOTT            7566
FORD             7566

This pitfall is even harder to spot in subqueries. Because KING's MGR is NULL, the SQL below, which looks for "employees who are nobody's manager," returns 0.

SELECT COUNT(*) FROM EMP WHERE EMPNO NOT IN (SELECT MGR FROM EMP);

  COUNT(*)
----------
         0

If you remove NULL from the subquery result, you get the expected 8. Subqueries come up again in Week 5.

SELECT COUNT(*) FROM EMP WHERE EMPNO NOT IN (SELECT MGR FROM EMP WHERE MGR IS NOT NULL);

  COUNT(*)
----------
         8

• ROWNUM, which picks only the top N rows, is also used in the WHERE clause, but its behavior is distinctive, so it is covered in the Top-N queries of Week 6.

Next week we move on to GROUP BY and aggregate functions, which group rows and summarize them.

📖 Glossary

• WHERE clause : The clause that keeps only rows where the condition is true in the result.
• BETWEEN : A range condition between two values. Both endpoints are included.
• IN : A condition that checks whether a value equals one in a list. Same as joining = with OR.
• Wildcard : A symbol in LIKE that stands for any character. % is zero or more characters, and _ is one character.
• ESCAPE : The clause in LIKE that defines the escape character that makes % and _ be read as ordinary characters.
• UNKNOWN : The third logical value produced when comparing with NULL. In WHERE it is filtered out like false.
• Operator precedence : The order in which things are evaluated first when there are no parentheses. NOT, AND, OR, in that order.