WHERE Conditional Query

WHERE Overview

WHERE is a conditional query clause in Oracle, used to filter data and return only rows that satisfy the search criteria.

Key syntax order:
WHERE comes after FROM and before ORDER BY.

Full Syntax

SELECT
    column1,
    column2,
    ...
FROM
    tablename
WHERE
    search_condition
ORDER BY
    column1,
    column2;

Comparison Operators

Operator	Description
=	Equals
!= , <>	Not equal to
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
IN	Matches any value in the list
NOT IN	Does not match any value in the list
[NOT] BETWEEN n AND m	Check whether within a value range
[NOT] EXISTS	Check whether sub‑query returns rows
IS [NOT] NULL	Check for NULL values

Code Examples

Equality Query

SELECT
    id,
    name,
    sex
FROM
    student
WHERE
    name = 'tom';Code language: JavaScript (javascript)

Oracle execution output:

SQL> select * from student where name = 'tom';

        ID NAME       SEX
---------- ---------- ----
         1 tom   MaleCode language: JavaScript (javascript)

Numeric comparison, id greater than 20

SELECT
    *
FROM
    student
WHERE
    id > 20;

Logical OR

SQL> select * from student where id>1 or name = 'jim';

        ID NAME       SEX
---------- ---------- ----
         2 jim       Male
         3 lisa      FemaleCode language: JavaScript (javascript)

IN Set Matching

SELECT
    *
FROM
    student
WHERE
    id IN(1,4)
ORDER BY
    name;

Execution output:

SQL> select * from student where id in(1,3);

        ID NAME       SEX
---------- ---------- ----
         1 tom        Male
         3 lisa       FemaleCode language: JavaScript (javascript)

LIKE Fuzzy Query

SELECT
    *
FROM
    student
WHERE
    name LIKE 'l%'
ORDER BY
    name;Code language: JavaScript (javascript)

% is a wildcard. 'l%' matches strings starting with the letter “l”.

Important Notes:

1. String condition values must be wrapped in single quotes, for example name = 'jim'.
2. WHERE is placed before ORDER BY, do not swap their positions.
3. Do not use = NULL to check null values; use IS NULL instead.

WHERE Conditional Query