How to Use WHERE Clause in Oracle Database 23ai Queries

The WHERE clause tells Oracle which rows to keep and which to skip. This tutorial shows practical ways to filter data using comparisons, ranges, pattern matching, NULL logic, subqueries, JSON paths, and vector-aware filtering in Oracle Database 23ai. You will see each query with a small, working dataset and a result table.

Prepare sample data

Before learning how to use the WHERE clause, let’s first create some simple tables with employees, departments, products, and documents. These sample datasets will be used in all examples.

-- Employees
DROP TABLE employees PURGE;
CREATE TABLE employees (
  employee_id   NUMBER PRIMARY KEY,
  first_name    VARCHAR2(50),
  last_name     VARCHAR2(50),
  hire_date     DATE,
  salary        NUMBER(10,2),
  department_id NUMBER,
  commission_pct NUMBER
);

INSERT INTO employees VALUES (1,'John','Smith',   DATE '2020-01-15', 8000, 10, NULL);
INSERT INTO employees VALUES (2,'Alice','Johnson',DATE '2019-03-10',12000, 20, 0.10);
INSERT INTO employees VALUES (3,'Michael','Brown',DATE '2021-06-20', 4500, 10, NULL);
INSERT INTO employees VALUES (4,'Sophia','Davis', DATE '2022-11-05', 6000, 30, 0.05);
INSERT INTO employees VALUES (5,'David','Wilson', DATE '2021-02-18', 9500, 20, NULL);

-- Departments
DROP TABLE departments PURGE;
CREATE TABLE departments (
  department_id NUMBER PRIMARY KEY,
  department_name VARCHAR2(100)
);
INSERT INTO departments VALUES (10,'Sales');
INSERT INTO departments VALUES (20,'HR');
INSERT INTO departments VALUES (30,'IT');

-- Products with JSON
DROP TABLE products PURGE;
CREATE TABLE products (
  product_id   NUMBER PRIMARY KEY,
  product_data JSON
);
INSERT INTO products VALUES (1,'{"name":"Laptop","specs":{"cpu":"Intel i7","ram":"16GB"},"price":1299}');
INSERT INTO products VALUES (2,'{"name":"Phone","specs":{"cpu":"Snapdragon","ram":"8GB"},"price":699}');

-- Documents with VECTORs
DROP TABLE documents PURGE;
CREATE TABLE documents (
  doc_id    NUMBER PRIMARY KEY,
  title     VARCHAR2(200),
  embedding VECTOR
);
INSERT INTO documents VALUES (1,'Intro to 23ai','[0.10,0.80,0.50]');
INSERT INTO documents VALUES (2,'Write SQL Fast','[0.20,0.70,0.60]');

COMMIT;

Basic comparison operators

The simplest use of WHERE is comparing values using operators like =, >, or <. This lets you filter rows that match specific numeric or text conditions.

SELECT first_name, last_name, salary
FROM employees
WHERE salary > 5000;

Result

Equality and not equal

You can use = to match exact values and <> or != to exclude certain rows. This is useful when you want to filter out or include only a specific category.

SELECT first_name, department_id
FROM employees
WHERE department_id <> 20;

Result

Combine conditions with AND and OR

The WHERE clause becomes powerful when you combine multiple conditions. AND means both must be true; OR means either one is enough. Parentheses help you control precedence.

SELECT first_name, salary, department_id
FROM employees
WHERE department_id = 10 AND salary >= 6000;

Result

SELECT first_name, salary, department_id
FROM employees
WHERE department_id = 10 OR salary >= 10000;

Result

IN for small value lists

The IN operator is a shortcut when checking a column against multiple values. It’s cleaner than writing many OR conditions.

SELECT first_name, department_id
FROM employees
WHERE department_id IN (10,30);

Result

BETWEEN for ranges

The BETWEEN keyword checks if a value falls between two limits. It works with numbers, dates, or text.

SELECT first_name, salary
FROM employees
WHERE salary BETWEEN 6000 AND 10000;

Result

LIKE for simple patterns

The LIKE operator is used for text matching. % means any sequence of characters and _ means exactly one character.

SELECT first_name, last_name
FROM employees
WHERE last_name LIKE 'J%';

Result

REGEXP_LIKE for advanced patterns

For complex string patterns, Oracle supports regular expressions.

SELECT first_name, last_name
FROM employees
WHERE REGEXP_LIKE(last_name, '^(Smith|Wilson)$');

Result

NULL checks

The WHERE clause can filter rows with missing values using IS NULL or IS NOT NULL.

SELECT first_name, commission_pct
FROM employees
WHERE commission_pct IS NULL;

Result

Date filters

Dates can be filtered using literals and comparison operators.

SELECT first_name, hire_date
FROM employees
WHERE hire_date >= DATE '2021-01-01';

Result

Subqueries with IN and EXISTS

The WHERE clause can contain subqueries, allowing decisions based on values from other tables.

-- Employees in departments that exist in departments table
SELECT first_name, department_id
FROM employees e
WHERE EXISTS (
  SELECT 1
  FROM departments d
  WHERE d.department_id = e.department_id
);

Result

Join plus WHERE

You can filter results of joins using the WHERE clause to restrict matching rows.

SELECT e.first_name, d.department_name
FROM employees e
JOIN departments d
  ON d.department_id = e.department_id
WHERE d.department_name = 'HR';

Result

JSON filters in WHERE (23ai)

Oracle 23ai supports native JSON types. You can filter using JSON keys directly.

SELECT JSON_VALUE(product_data,'$.name') AS name,
       JSON_VALUE(product_data,'$.price') AS price
FROM products
WHERE JSON_VALUE(product_data,'$.price') > 1000;

Result

Vector-aware querying in WHERE (23ai)

With the VECTOR data type, WHERE can filter candidates, while ORDER BY ranks them by similarity.

SELECT doc_id, title
FROM documents
WHERE title LIKE '%SQL%'
ORDER BY VECTOR_DISTANCE(embedding,'[0.18,0.72,0.58]', COSINE)
FETCH FIRST 1 ROW ONLY;

Result

Conclusion

The WHERE clause is your main tool for telling Oracle which rows matter. You now know how to filter by numbers, text, dates, lists, patterns, NULLs, related rows with subqueries, JSON keys, and even vector use cases. Combine clear conditions with good indexing and you get fast, precise answers.

See also:

1. How to Install Oracle Database 23ai on Windows
2. Installing Oracle SQL Developer on Windows