AND в Oracle SQL — 50 примеров объединения условий

AND в Oracle SQL — это логический оператор, позволяющий соединять два и более условия в одном выражении. Он возвращает TRUE только в случае, если все условия истинны. В этой статье ты узнаешь, как использовать AND в SELECT, UPDATE, DELETE, с числами, строками, датами, подзапросами и логикой. Мы рассмотрим 50 реальных примеров.

🖋️ Синтаксис логического оператора AND

SELECT [столбцы | *]
FROM [таблица]
WHERE условие1 AND условие2 [...]

Оператор AND используется внутри WHERE, JOIN, CASE и других логических блоков. Он позволяет уточнять выборку, применяя сразу несколько фильтров.

🔄 Где используется AND

• Фильтрация данных по нескольким критериям
• Проверка пересечений условий
• Совмещение диапазонов, списков и NULL
• Использование в UPDATE, DELETE и подзапросах
• Уточнение выборки в аналитике

📊 Примеры использования AND в Oracle SQL

1. Два условия:

SELECT * FROM employees WHERE department_id = 10 AND salary > 4000;

2. Три условия:

SELECT * FROM employees WHERE department_id = 20 AND salary > 3000 AND job_id = 'IT_PROG';

3. Число и строка:

SELECT * FROM employees WHERE salary > 5000 AND job_id = 'SA_REP';

4. Строка и дата:

SELECT * FROM employees WHERE job_id = 'HR' AND hire_date > TO_DATE('2020-01-01','YYYY-MM-DD');

5. Сравнение и BETWEEN:

SELECT * FROM employees WHERE department_id = 50 AND salary BETWEEN 3000 AND 8000;

6. IN и IS NOT NULL:

SELECT * FROM employees WHERE department_id IN (10, 20) AND commission_pct IS NOT NULL;

7. AND с подзапросом:

SELECT * FROM employees WHERE department_id = (
  SELECT department_id FROM departments WHERE department_name = 'IT'
) AND salary > 5000;

8. Сравнение дат:

SELECT * FROM employees WHERE hire_date >= TO_DATE('2015-01-01','YYYY-MM-DD') AND hire_date <= SYSDATE;

9. С использованием функций:

SELECT * FROM employees WHERE LENGTH(first_name) > 3 AND UPPER(last_name) LIKE 'S%';

10. Комбинация NOT и AND:

SELECT * FROM employees WHERE NOT department_id = 30 AND salary > 4000;

11. AND с LIKE и числом:

SELECT * FROM employees WHERE first_name LIKE 'A%' AND salary > 4000;

12. AND с BETWEEN и IN:

SELECT * FROM employees WHERE salary BETWEEN 3000 AND 6000 AND job_id IN ('SA_REP', 'IT_PROG');

13. AND с OR (в скобках):

SELECT * FROM employees WHERE (department_id = 10 OR department_id = 20) AND salary > 4000;

14. AND с NULL-проверкой:

SELECT * FROM employees WHERE manager_id IS NOT NULL AND job_id = 'IT_PROG';

15. AND в UPDATE:

UPDATE employees SET salary = salary * 1.1 WHERE job_id = 'SA_REP' AND department_id = 80;

16. AND в DELETE:

DELETE FROM employees WHERE department_id = 50 AND hire_date < TO_DATE('2010-01-01','YYYY-MM-DD');

17. AND в MERGE:

MERGE INTO employees e USING new_employees n
ON (e.employee_id = n.employee_id AND e.department_id = n.department_id)
WHEN MATCHED THEN UPDATE SET e.salary = n.salary;

18. AND с аналитической функцией:

SELECT * FROM (
  SELECT *, RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees
) WHERE rnk <= 5 AND department_id = 60;

19. AND с INTERVAL:

SELECT * FROM employees WHERE hire_date < SYSDATE - INTERVAL '5' YEAR AND salary > 5000;

20. AND с подзапросом в EXISTS:

SELECT * FROM departments d WHERE EXISTS (
  SELECT 1 FROM employees e WHERE e.department_id = d.department_id AND e.salary > 7000
);

21. AND с подзапросом и строкой:

SELECT * FROM employees WHERE job_id = 'SA_REP' AND department_id IN (
  SELECT department_id FROM departments WHERE location_id = 1700
);

22. AND с REGEXP:

SELECT * FROM employees WHERE REGEXP_LIKE(first_name, '^A') AND LENGTH(first_name) > 3;

23. AND с NVL:

SELECT * FROM employees WHERE NVL(commission_pct, 0) > 0 AND department_id = 80;

24. AND в WITH-CTE:

WITH high_paid AS (
  SELECT * FROM employees WHERE salary > 10000 AND department_id = 90
)
SELECT * FROM high_paid;

25. AND в подзапросе в SELECT:

SELECT * FROM employees WHERE employee_id IN (
  SELECT employee_id FROM job_history WHERE start_date < TO_DATE('2015-01-01','YYYY-MM-DD') AND end_date IS NOT NULL
);

26. AND с длиной строки:

SELECT * FROM employees WHERE LENGTH(first_name) > 4 AND LENGTH(last_name) > 5;

27. AND с функцией TO_CHAR:

SELECT * FROM employees WHERE TO_CHAR(hire_date, 'YYYY') = '2020' AND job_id = 'IT_PROG';

28. AND в DELETE с подзапросом:

DELETE FROM employees WHERE employee_id IN (
  SELECT employee_id FROM job_history WHERE department_id = 50 AND job_id = 'HR'
);

29. AND с LOWER и LIKE:

SELECT * FROM employees WHERE LOWER(first_name) LIKE 'a%' AND department_id = 90;

30. AND с подстановочными символами:

SELECT * FROM employees WHERE first_name LIKE '_a%' AND department_id = 80;

31. AND с ROWNUM:

SELECT * FROM employees WHERE department_id = 60 AND ROWNUM <= 3;

32. AND в фильтре GROUP BY через HAVING:

SELECT department_id, COUNT(*) FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5 AND AVG(salary) > 5000;

33. AND с функцией CASE в WHERE:

SELECT * FROM employees WHERE
  CASE WHEN department_id = 10 THEN 'Y' ELSE 'N' END = 'Y' AND salary > 5000;

34. AND с UNION ALL:

SELECT * FROM employees WHERE department_id = 10 AND job_id = 'IT_PROG'
UNION ALL
SELECT * FROM employees WHERE department_id = 20 AND job_id = 'SA_REP';

35. AND с аналитикой в подзапросе:

SELECT * FROM (
  SELECT *, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dr
  FROM employees
) WHERE dr = 1 AND job_id = 'IT_PROG';

36. AND с фильтрацией по CUBE:

SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY CUBE (department_id, job_id)
HAVING department_id IS NOT NULL AND job_id IS NOT NULL;

37. AND с модификацией даты:

SELECT * FROM employees WHERE TO_CHAR(hire_date + 30, 'YYYY-MM-DD') = '2023-01-30' AND department_id = 40;

38. AND в VIEW:

CREATE OR REPLACE VIEW v_high_paid AS
SELECT * FROM employees WHERE salary > 8000 AND department_id = 60;

39. AND с JSON_VALUE:

SELECT * FROM employees_json
WHERE JSON_VALUE(data, '$.region') = 'North' AND JSON_VALUE(data, '$.active') = 'true';

40. AND с модулем MOD:

SELECT * FROM employees WHERE MOD(salary, 1000) = 0 AND department_id = 80;

41. AND с математическим округлением:

SELECT * FROM employees WHERE ROUND(salary, -2) >= 5000 AND ROUND(salary, -2) <= 10000;

42. AND с CAST:

SELECT * FROM employees WHERE CAST(hire_date AS DATE) < SYSDATE AND department_id = 70;

43. AND с COALESCE:

SELECT * FROM employees WHERE COALESCE(manager_id, 0) = 101 AND salary > 4000;

44. AND с DUMP:

SELECT * FROM employees WHERE DUMP(job_id) LIKE '%73%' AND salary > 4000;

45. AND с SYS_CONTEXT:

SELECT * FROM employees WHERE department_id = 60 AND SYS_CONTEXT('USERENV', 'SESSION_USER') = 'HR';

46. AND с dual:

SELECT 'ok' FROM dual WHERE 1=1 AND 'A'='A';

47. AND в UPDATE с подзапросом:

UPDATE employees SET salary = salary * 1.05
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales') AND job_id = 'SA_REP';

48. AND в DELETE с EXISTS:

DELETE FROM employees e WHERE EXISTS (
  SELECT 1 FROM job_history j
  WHERE j.employee_id = e.employee_id AND j.department_id = 60
);

49. AND с SUBSTR и строкой:

SELECT * FROM employees WHERE SUBSTR(last_name, 1, 1) = 'S' AND department_id = 90;

50. AND с IS NOT NULL и BETWEEN:

SELECT * FROM employees WHERE commission_pct IS NOT NULL AND salary BETWEEN 3000 AND 9000;

📆 Заключение: зачем изучать AND в Oracle SQL

Оператор AND — незаменимый инструмент для точной фильтрации и логического объединения условий. Он делает запросы более конкретными, исключает лишние строки и позволяет строить мощные фильтры.

Зная AND, ты сможешь уверенно описывать сложные логики, включая вложенные подзапросы, фильтры по датам, спискам и другим значениям.

🕸️ Следующая статья:

OR в Oracle SQL — выбор по любому из условий