USING в Oracle SQL — как объединять таблицы с помощью USING

USING в Oracle SQL. Введение

Оператор USING в Oracle SQL используется в JOIN-выражениях для объединения таблиц по одному или нескольким столбцам с одинаковыми именами. Он упрощает синтаксис и делает запросы более читаемыми, особенно при естественных соединениях. В этой статье ты найдёшь 45 примеров применения USING для объединений таблиц и подзапросов.

🖋️ Синтаксис оператора USING

SELECT ...
FROM таблица1
JOIN таблица2 USING (общий_столбец);

• Столбец в USING должен быть одинаково назван в обеих таблицах.
• Oracle возвращает этот столбец один раз (в отличие от ON, где может быть два).

Поддерживаются типы соединений:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL OUTER JOIN

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

• Объединение таблиц по ключам с одинаковыми названиями
• Упрощение INNER JOIN с коротким синтаксисом
• Создание подзапросов с общими связями
• Улучшение читаемости SQL-запросов

📊 Примеры использования USING в Oracle SQL (45 примеров)

1. Простой INNER JOIN с USING:

SELECT first_name, department_name
FROM employees
JOIN departments USING (department_id);

2. LEFT JOIN с USING:

SELECT e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d USING (department_id);

3. RIGHT JOIN с USING:

SELECT e.first_name, d.department_name
FROM employees e
RIGHT JOIN departments d USING (department_id);

4. FULL OUTER JOIN с USING:

SELECT e.first_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d USING (department_id);

5. JOIN с более чем одним столбцом в USING:

SELECT *
FROM orders o
JOIN shipments s USING (order_id, product_id);

6. Фильтрация после USING:

SELECT *
FROM employees JOIN departments USING (department_id)
WHERE location_id = 1700;

7. Псевдонимы с USING:

SELECT e.first_name, d.department_name
FROM employees e JOIN departments d USING (department_id);

8. USING с WHERE и ORDER BY:

SELECT * FROM employees JOIN departments USING (department_id)
WHERE department_name LIKE 'S%'
ORDER BY first_name;

9. JOIN с агрегатами:

SELECT department_id, COUNT(*) AS total
FROM employees JOIN departments USING (department_id)
GROUP BY department_id;

10. JOIN в подзапросе:

SELECT * FROM (
  SELECT e.first_name, d.department_name
  FROM employees e JOIN departments d USING (department_id)
);

11. JOIN с аналитикой:

SELECT first_name, department_name,
  RANK() OVER (PARTITION BY department_name ORDER BY salary DESC) AS rnk
FROM employees JOIN departments USING (department_id);

12. JOIN и CASE:

SELECT first_name,
  CASE department_name
    WHEN 'IT' THEN 'Техотдел'
    ELSE 'Прочее'
  END AS отдел
FROM employees JOIN departments USING (department_id);

13. USING с OUTER JOIN и фильтром:

SELECT *
FROM employees e FULL OUTER JOIN departments d USING (department_id)
WHERE d.department_name IS NOT NULL;

14. JOIN + USING с подзапросом:

SELECT *
FROM (SELECT * FROM employees WHERE salary > 5000) e
JOIN departments d USING (department_id);

15. JOIN + USING в WITH:

WITH ed AS (
  SELECT * FROM employees JOIN departments USING (department_id)
)
SELECT * FROM ed WHERE location_id = 1700;

16. Сравнение ON vs USING:

-- USING чище и короче:
SELECT * FROM e JOIN d USING (id);
-- ON требует дублирования:
SELECT * FROM e JOIN d ON (e.id = d.id);

17. JOIN + USING в CREATE VIEW:

CREATE OR REPLACE VIEW v_emp_dept AS
SELECT e.first_name, d.department_name
FROM employees e JOIN departments d USING (department_id);

18. Несколько JOIN с USING:

SELECT *
FROM employees e
JOIN departments d USING (department_id)
JOIN locations l USING (location_id);

19. PIVOT + USING:

SELECT * FROM (
  SELECT department_id, salary FROM employees
) PIVOT (
  COUNT(salary) FOR department_id IN (10, 20, 30)
);

20. JOIN с функцией LENGTH:

SELECT first_name, LENGTH(department_name) AS длина
FROM employees JOIN departments USING (department_id);

21. JSON_VALUE + USING:

SELECT e.first_name, JSON_VALUE(e.data, '$.job') AS job
FROM employees_json e
JOIN departments d USING (department_id);

22. JOIN с выражением в SELECT:

SELECT first_name || ' (' || department_name || ')' AS full_info
FROM employees JOIN departments USING (department_id);

23. JOIN + USING с агрегатом:

SELECT department_name, AVG(salary) AS avg_salary
FROM employees JOIN departments USING (department_id)
GROUP BY department_name;

24. JOIN + USING в MATERIALIZED VIEW:

CREATE MATERIALIZED VIEW mv_emp_dept AS
SELECT * FROM employees JOIN departments USING (department_id);

25. JOIN с фильтрацией по ROWNUM:

SELECT * FROM employees JOIN departments USING (department_id)
WHERE ROWNUM <= 5;

26. USING в INSERT SELECT:

INSERT INTO emp_archive (employee_id, name, department_name)
SELECT employee_id, first_name, department_name
FROM employees JOIN departments USING (department_id);

27. JOIN с подстановкой псевдонима в SELECT:

SELECT e.employee_id AS id, d.department_name AS dept
FROM employees e JOIN departments d USING (department_id);

28. JOIN и NULL в полях:

SELECT * FROM employees e FULL JOIN departments d USING (department_id)
WHERE e.first_name IS NULL OR d.department_name IS NULL;

29. JOIN с числовыми условиями:

SELECT * FROM employees JOIN departments USING (department_id)
WHERE salary > 4000;

30. JOIN + USING в аналитике COUNT:

SELECT department_name, COUNT(*) OVER () FROM employees JOIN departments USING (department_id);

31. JOIN + USING в динамическом SQL:

EXECUTE IMMEDIATE 'SELECT * FROM employees JOIN departments USING (department_id)';

32. USING с ORDER BY:

SELECT * FROM employees JOIN departments USING (department_id)
ORDER BY department_name;

33. JOIN + USING + UPPER:

SELECT UPPER(first_name), UPPER(department_name)
FROM employees JOIN departments USING (department_id);

34. JOIN с CASE и USING:

SELECT first_name,
  CASE WHEN department_name = 'IT' THEN 'Техник' ELSE 'Сотрудник' END AS роль
FROM employees JOIN departments USING (department_id);

35. JOIN с аналитикой RANK:

SELECT first_name, department_name,
  RANK() OVER (PARTITION BY department_name ORDER BY salary DESC)
FROM employees JOIN departments USING (department_id);

36. JOIN + USING с группировкой:

SELECT department_id, COUNT(*) FROM employees JOIN departments USING (department_id)
GROUP BY department_id;

37. JOIN + USING с FETCH:

SELECT * FROM employees JOIN departments USING (department_id)
FETCH FIRST 10 ROWS ONLY;

38. JOIN + USING в JSON_TABLE:

SELECT e.employee_id, j.city
FROM employees e
JOIN JSON_TABLE(e.data, '$.location'
  COLUMNS (city VARCHAR2(50) PATH '$.city')) j
USING (employee_id);

39. JOIN + USING в WITH RECURSIVE:

WITH RECURSIVE org_chart AS (
  SELECT employee_id, manager_id FROM employees
  UNION ALL
  SELECT e.employee_id, e.manager_id
  FROM employees e JOIN org_chart o USING (manager_id)
)
SELECT * FROM org_chart;

40. JOIN + USING в INLINE VIEW:

SELECT * FROM (
  SELECT first_name, department_name FROM employees JOIN departments USING (department_id)
) WHERE department_name IS NOT NULL;

41. JOIN и функция INITCAP:

SELECT INITCAP(first_name), INITCAP(department_name)
FROM employees JOIN departments USING (department_id);

42. JOIN + USING + TO_CHAR:

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD'), department_name
FROM employees JOIN departments USING (department_id);

43. JOIN с группировкой и аналитикой:

SELECT department_id, COUNT(*) AS cnt, RANK() OVER (ORDER BY COUNT(*) DESC)
FROM employees JOIN departments USING (department_id)
GROUP BY department_id;

44. JOIN + USING + SIGN:

SELECT department_id, SIGN(AVG(salary) - 5000)
FROM employees JOIN departments USING (department_id)
GROUP BY department_id;

45. JOIN + USING в DELETE с подзапросом:

DELETE FROM employees
WHERE employee_id IN (
  SELECT e.employee_id FROM employees e JOIN departments d USING (department_id)
  WHERE d.department_name = 'Closed'
);

📆 Заключение: зачем использовать USING в Oracle SQL

Оператор USING делает SQL-код чище, избегает дублирования имён столбцов и идеально подходит для JOIN по одноимённым полям. Он помогает избежать многословного ON и упрощает чтение сложных соединений. Это особенно удобно, когда структура таблиц продумана с одинаковыми именами ключей.

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

VALUES в Oracle SQL: как правильно передавать данные при вставке