WHERE в Oracle SQL — это ключевое зарезервированное слово, которое используется для фильтрации данных. Оно позволяет задавать условия, по которым отбираются строки из таблиц. Эта статья научит тебя, как использовать WHERE с числами, строками, датами, NULL, подзапросами, логикой и множеством других условий. Внутри — 50 разнообразных примеров на практике.
🖋️ Синтаксис условия WHERE в Oracle SQL
SELECT [столбцы | *]
FROM [таблица]
WHERE [условие]
Условие может быть логическим выражением, сравнением, подзапросом, функцией или комбинацией этих элементов.
🔄 Где используется WHERE
- Фильтрация строк в SELECT
- Условия в UPDATE и DELETE
- Использование подзапросов
- Совмещение с логическими операторами
- Проверка значений, NULL, диапазонов, списков
📊 Примеры использования WHERE в Oracle SQL
- Простое условие:
SELECT * FROM employees WHERE salary > 5000;
- Равно конкретному значению:
SELECT * FROM employees WHERE department_id = 10;
- Несовпадение:
SELECT * FROM employees WHERE job_id != 'IT_PROG';
- BETWEEN:
SELECT * FROM employees WHERE salary BETWEEN 3000 AND 6000;
- IN:
SELECT * FROM employees WHERE department_id IN (10, 20, 30);
- NOT IN:
SELECT * FROM employees WHERE job_id NOT IN ('HR', 'SA_REP');
- IS NULL:
SELECT * FROM employees WHERE manager_id IS NULL;
- IS NOT NULL:
SELECT * FROM employees WHERE commission_pct IS NOT NULL;
- LIKE:
SELECT * FROM employees WHERE first_name LIKE 'A%';
- NOT LIKE:
SELECT * FROM employees WHERE first_name NOT LIKE '%son';
- Подзапрос в WHERE:
SELECT * FROM employees WHERE department_id = (
SELECT department_id FROM departments WHERE location_id = 1700
);
- Сравнение с подзапросом:
SELECT * FROM employees WHERE salary > (
SELECT AVG(salary) FROM employees
);
- WHERE с логическим AND:
SELECT * FROM employees WHERE department_id = 10 AND salary > 4000;
- WHERE с OR:
SELECT * FROM employees WHERE job_id = 'IT_PROG' OR job_id = 'SA_MAN';
- Комбинация AND и OR:
SELECT * FROM employees WHERE (department_id = 10 OR department_id = 20) AND salary > 4000;
- Сравнение строк:
SELECT * FROM employees WHERE first_name = 'John';
- Сравнение дат:
SELECT * FROM employees WHERE hire_date > TO_DATE('2020-01-01', 'YYYY-MM-DD');
- Использование SYSDATE:
SELECT * FROM employees WHERE hire_date < SYSDATE;
- Сложное логическое условие:
SELECT * FROM employees WHERE (salary > 5000 AND department_id = 20) OR job_id = 'AD_PRES';
- WHERE с NOT:
SELECT * FROM employees WHERE NOT (department_id = 10);
- WHERE в UPDATE:
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 50;
- WHERE в DELETE:
DELETE FROM employees WHERE hire_date < TO_DATE('2000-01-01', 'YYYY-MM-DD');
- WHERE с числовым выражением:
SELECT * FROM employees WHERE salary + commission_pct > 6000;
- WHERE с функцией LENGTH:
SELECT * FROM employees WHERE LENGTH(first_name) > 5;
- WHERE с LOWER:
SELECT * FROM employees WHERE LOWER(last_name) = 'smith';
- WHERE с UPPER:
SELECT * FROM employees WHERE UPPER(job_id) = 'IT_PROG';
- WHERE с CASE:
SELECT * FROM employees WHERE
CASE WHEN department_id = 10 THEN 'Y' ELSE 'N' END = 'Y';
- WHERE с NVL:
SELECT * FROM employees WHERE NVL(commission_pct, 0) > 0;
- WHERE с COALESCE:
SELECT * FROM employees WHERE COALESCE(manager_id, 0) = 100;
- WHERE с регулярным выражением:
SELECT * FROM employees WHERE REGEXP_LIKE(first_name, '^A.*');
- WHERE с EXISTS:
SELECT * FROM departments d WHERE EXISTS (
SELECT 1 FROM employees e WHERE e.department_id = d.department_id
);
- WHERE с NOT EXISTS:
SELECT * FROM departments d WHERE NOT EXISTS (
SELECT 1 FROM employees e WHERE e.department_id = d.department_id
);
- WHERE с ALL:
SELECT * FROM employees WHERE salary > ALL (
SELECT salary FROM employees WHERE department_id = 10
);
- WHERE с ANY:
SELECT * FROM employees WHERE salary > ANY (
SELECT salary FROM employees WHERE department_id = 30
);
- WHERE с DUAL:
SELECT * FROM dual WHERE 'A' = 'A';
- WHERE с пустым результатом:
SELECT * FROM employees WHERE department_id = 9999;
- WHERE с арифметикой и датой:
SELECT * FROM employees WHERE hire_date + 365 < SYSDATE;
- WHERE с подзапросом и IN:
SELECT * FROM employees WHERE department_id IN (
SELECT department_id FROM departments WHERE location_id = 1700
);
- WHERE с датами и BETWEEN:
SELECT * FROM employees WHERE hire_date BETWEEN TO_DATE('2020-01-01','YYYY-MM-DD') AND SYSDATE;
- WHERE с NULLIF:
SELECT * FROM employees WHERE NULLIF(salary, 0) > 1000;
- WHERE с DECODE:
SELECT * FROM employees WHERE DECODE(department_id, 10, 'Y', 'N') = 'Y';
- WHERE с SYSDATE и INTERVAL:
SELECT * FROM employees WHERE hire_date < SYSDATE - INTERVAL '1' YEAR;
- WHERE с CASE и подзапросом:
SELECT * FROM employees WHERE
CASE WHEN department_id = (SELECT department_id FROM departments WHERE department_name = 'IT') THEN 'Y' ELSE 'N' END = 'Y';
- WHERE с IN и строками:
SELECT * FROM employees WHERE first_name IN ('John', 'Jane', 'Jake');
- WHERE с LIKE и ESCAPE:
SELECT * FROM employees WHERE first_name LIKE '%\_%' ESCAPE '\';
- WHERE с аналитикой в подзапросе:
SELECT * FROM (
SELECT *, RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees
) WHERE rnk <= 3;
- WHERE в WITH CTE:
WITH high_salaries AS (
SELECT * FROM employees WHERE salary > 10000
)
SELECT * FROM high_salaries WHERE department_id = 90;
- WHERE с вложенными SELECT:
SELECT * FROM employees WHERE salary > (
SELECT AVG(salary) FROM employees WHERE department_id = 10
);
- WHERE с REPLACE:
SELECT * FROM employees WHERE REPLACE(first_name, 'a', 'A') LIKE 'A%';
- WHERE с SUBSTR:
SELECT * FROM employees WHERE SUBSTR(first_name, 1, 1) = 'J';
📆 Заключение: зачем изучать WHERE в Oracle SQL
Условие WHERE — основа фильтрации в SQL. Без него невозможно точно выбирать, изменять или удалять строки. Команда используется в почти каждом запросе, от простейших до аналитических.
Зная WHERE, ты сможешь создавать гибкие запросы, комбинировать условия и точно контролировать результат.