SELECT SQL — 100 универсальных примеров для всех реализаций SQL

SELECT SQL. Введение

SELECT — центральная команда SQL из подмножества DQL, с помощью которой извлекают данные и формируют отчёты. Если вы искали практические примеры SELECT SQL — ниже собрана коллекция «живых» универсальных сценариев.

Ключевые элементы запроса: FROM, WHERE, JOIN, GROUP BY, HAVING, ORDER BY, UNION, DISTINCT, IN, EXISTS, BETWEEN, LIKE, CASE.

Все примеры в статье оформлены с подсветкой кода и возможностью скопировать в один клик.

Синтаксис

SELECT список_полей
FROM   источник
[WHERE условие]
[GROUP BY поля]
[HAVING условие_агрегации]
[ORDER BY поля [ASC|DESC]];

Типовая конструкция

SELECT c.id,
       c.full_name,
       COALESCE(SUM(o.amount), 0) AS total_amount
FROM   customers AS c
LEFT JOIN orders    AS o
       ON o.customer_id = c.id
WHERE  c.status = 'ACTIVE'
GROUP BY c.id, c.full_name
HAVING  COALESCE(SUM(o.amount), 0) > 0
ORDER BY total_amount DESC;

1. Получение всех столбцов из справочника стран:

SELECT *
FROM   countries;

2. Список имён сотрудников по алфавиту:

SELECT name
FROM   employees
ORDER BY name ASC;

3. Уникальные категории товаров через DISTINCT:

SELECT DISTINCT category
FROM   products
ORDER BY category;

4. Три самых дорогих товара:

SELECT id,
       title,
       price
FROM   products
ORDER BY price DESC;

5. Список активных клиентов с флагом:

SELECT id,
       full_name,
       is_active
FROM   customers
WHERE  is_active = 1;

6. Первые даты заказов каждого клиента (без агрегации):

SELECT order_date,
       customer_id
FROM   orders
ORDER BY order_date;

7. Список кодов стран и названий:

SELECT code,
       name
FROM   countries
ORDER BY code;

8. Товары с ненулевой ценой:

SELECT id,
       title,
       price
FROM   products
WHERE  price > 0;

9. Список сотрудников с указанием отдела:

SELECT e.id,
       e.name,
       e.department
FROM   employees AS e
ORDER BY e.department, e.name;

10. Клиенты с известным email (не NULL):

SELECT id,
       full_name,
       email
FROM   customers
WHERE  email IS NOT NULL;

11. Простая проекция колонок заказов:

SELECT id,
       customer_id,
       amount
FROM   orders;

12. Наименования курсов и их коды:

SELECT code,
       title
FROM   courses
ORDER BY title;

13. Города и код страны:

SELECT name,
       country_code
FROM   cities
ORDER BY country_code, name;

14. Список публикаций с датой:

SELECT title,
       published_on
FROM   posts
ORDER BY published_on DESC;

15. Номера счетов и баланс:

SELECT account_no,
       balance
FROM   accounts
ORDER BY account_no;

16. Клиенты, созданные в заданном году:

SELECT id,
       full_name,
       created_at
FROM   customers
WHERE  created_at BETWEEN DATE '2020-01-01' AND DATE '2020-12-31'
ORDER BY created_at;

17. Товары дороже заданного порога:

SELECT id,
       title,
       price
FROM   products
WHERE  price >= 100.00
ORDER BY price DESC;

18. Города страны по коду (равенство):

SELECT name
FROM   cities
WHERE  country_code = 'US'
ORDER BY name;

19. Поиск клиентов по началу имени (LIKE):

SELECT id,
       full_name
FROM   customers
WHERE  full_name LIKE 'Ann%'
ORDER BY full_name;

20. Фильтр по множеству статусов (IN):

SELECT id,
       status
FROM   orders
WHERE  status IN ('NEW','PAID','SHIPPED')
ORDER BY id;

21. Исключение пустых email (IS NOT NULL AND <> »):

SELECT id,
       email
FROM   customers
WHERE  email IS NOT NULL
AND    email <> ''
ORDER BY id;

22. Заказы выше среднего чека (с подзапросом):

SELECT o.id,
       o.amount
FROM   orders AS o
WHERE  o.amount > (
       SELECT AVG(amount)
       FROM   orders
)
ORDER BY o.amount DESC;

23. Сотрудники из указанного отдела:

SELECT id,
       name
FROM   employees
WHERE  department = 'IT'
ORDER BY name;

24. Транзакции в заданный месяц:

SELECT id,
       happened_at,
       amount
FROM   transactions
WHERE  happened_at BETWEEN TIMESTAMP '2024-05-01 00:00:00' AND TIMESTAMP '2024-05-31 23:59:59'
ORDER BY happened_at;

25. Посты с названием, содержащим фразу:

SELECT id,
       title
FROM   posts
WHERE  title LIKE '%SQL%'
ORDER BY id;

26. Платежи по клиентам, исключая возвраты:

SELECT id,
       customer_id,
       amount
FROM   payments
WHERE  type <> 'REFUND'
ORDER BY id;

27. Счета с нулевым или отрицательным балансом:

SELECT account_no,
       balance
FROM   accounts
WHERE  balance <= 0
ORDER BY balance;

28. Курсы уровня advanced или pro:

SELECT code,
       title,
       level
FROM   courses
WHERE  level IN ('ADVANCED','PRO')
ORDER BY title;

29. Сотрудники, у кого нет email:

SELECT id,
       name
FROM   employees
WHERE  email IS NULL
ORDER BY id;

30. Города, начинающиеся на ‘New ‘:

SELECT name
FROM   cities
WHERE  name LIKE 'New %'
ORDER BY name;

31. Сумма заказов по клиентам:

SELECT customer_id,
       SUM(amount) AS total_amount
FROM   orders
GROUP BY customer_id
ORDER BY total_amount DESC;

32. Количество заказов по статусам:

SELECT status,
       COUNT(*) AS cnt
FROM   orders
GROUP BY status
ORDER BY cnt DESC;

33. Средняя цена по категориям:

SELECT category,
       AVG(price) AS avg_price
FROM   products
GROUP BY category
ORDER BY category;

34. Минимальная/максимальная дата транзакций:

SELECT MIN(happened_at) AS first_tx,
       MAX(happened_at) AS last_tx
FROM   transactions;

35. Средний балл по курсам:

SELECT course_id,
       AVG(score) AS avg_score
FROM   grades
GROUP BY course_id
ORDER BY avg_score DESC;

36. Топ клиентов по сумме покупок (HAVING):

SELECT customer_id,
       SUM(amount) AS revenue
FROM   orders
GROUP BY customer_id
HAVING SUM(amount) >= 1000
ORDER BY revenue DESC;

37. Частота встречаемости домена email:

SELECT SUBSTRING(email FROM POSITION('@' IN email)+1) AS domain,
       COUNT(*) AS cnt
FROM   customers
WHERE  email IS NOT NULL
GROUP BY SUBSTRING(email FROM POSITION('@' IN email)+1)
ORDER BY cnt DESC;

38. Количество постов по авторам:

SELECT author,
       COUNT(*) AS posts_cnt
FROM   posts
GROUP BY author
ORDER BY posts_cnt DESC;

39. Средний остаток товара по складам:

SELECT warehouse_id,
       AVG(qty_on_hand) AS avg_qty
FROM   stock
GROUP BY warehouse_id
ORDER BY warehouse_id;

40. Сумма платежей по месяцам:

SELECT CAST(happened_at AS DATE) AS day,
       SUM(amount) AS day_sum
FROM   payments
GROUP BY CAST(happened_at AS DATE)
ORDER BY day;

41. Корзины: среднее количество позиций:

SELECT customer_id,
       AVG(qty) AS avg_qty
FROM   cart_items
GROUP BY customer_id
ORDER BY avg_qty DESC;

42. Города: количество по странам:

SELECT country_code,
       COUNT(*) AS city_cnt
FROM   cities
GROUP BY country_code
ORDER BY city_cnt DESC;

43. Курсы: сколько записей на курс:

SELECT course_id,
       COUNT(*) AS enroll_cnt
FROM   enrollment
GROUP BY course_id
ORDER BY enroll_cnt DESC;

44. Продукты: категория с максимумом товаров:

SELECT category,
       COUNT(*) AS cnt
FROM   products
GROUP BY category
ORDER BY cnt DESC;

45. Аккаунты: сколько активных контактов:

SELECT account_id,
       COUNT(*) AS contacts
FROM   contacts
WHERE  active = 1
GROUP BY account_id
ORDER BY contacts DESC;

46. Клиенты и их заказы (INNER JOIN):

SELECT c.id,
       c.full_name,
       o.id   AS order_id,
       o.amount
FROM   customers AS c
JOIN   orders    AS o
       ON o.customer_id = c.id
ORDER BY c.id, o.id;

47. Все клиенты и их заказы (LEFT JOIN):

SELECT c.id,
       c.full_name,
       o.id   AS order_id,
       o.amount
FROM   customers AS c
LEFT JOIN orders AS o
       ON o.customer_id = c.id
ORDER BY c.id, o.id;

48. Заказы и товары (соединение через детали):

SELECT o.id         AS order_id,
       od.product_id,
       p.title      AS product_title,
       od.qty
FROM   orders      AS o
JOIN   order_items AS od
       ON od.order_id  = o.id
JOIN   products    AS p
       ON p.id        = od.product_id
ORDER BY o.id, od.product_id;

49. Города с названием страны:

SELECT ci.name    AS city,
       co.name    AS country
FROM   cities    AS ci
JOIN   countries AS co
       ON co.code = ci.country_code
ORDER BY country, city;

50. Посты вместе с именем автора:

SELECT p.id,
       p.title,
       a.name AS author_name
FROM   posts   AS p
JOIN   authors AS a
       ON a.id = p.author_id
ORDER BY p.id;

51. Сотрудник и его текущий отдел:

SELECT e.id,
       e.name,
       d.title AS department
FROM   employees   AS e
JOIN   departments AS d
       ON d.id = e.department_id
ORDER BY e.id;

52. Сделка CRM и аккаунт:

SELECT d.deal_id,
       d.amount,
       a.name AS account
FROM   crm_deal   AS d
JOIN   crm_account AS a
       ON a.account_id = d.account_id
ORDER BY d.deal_id;

53. Ученики и их курсы:

SELECT s.id        AS student_id,
       s.name      AS student_name,
       c.title     AS course_title
FROM   students     AS s
JOIN   enrollment   AS e
       ON e.student_id = s.id
JOIN   courses      AS c
       ON c.id         = e.course_id
ORDER BY s.id;

54. Склады и остатки:

SELECT w.id        AS warehouse_id,
       w.location,
       st.product_id,
       st.qty_on_hand
FROM   warehouses AS w
JOIN   stock      AS st
       ON st.warehouse_id = w.id
ORDER BY w.id, st.product_id;

55. Пациенты и последние визиты (LEFT JOIN):

SELECT p.patient_id,
       p.full_name,
       v.visited_at,
       v.diagnosis
FROM   patients AS p
LEFT JOIN visits   AS v
       ON v.patient_id = p.patient_id
ORDER BY p.patient_id, v.visited_at DESC;

56. Заказы и платежи:

SELECT o.id     AS order_id,
       o.customer_id,
       pay.amount AS payment_amount
FROM   orders    AS o
LEFT JOIN payments AS pay
       ON pay.order_id = o.id
ORDER BY o.id;

57. Комментарии с заголовками постов:

SELECT c.id,
       c.body,
       p.title AS post_title
FROM   comments AS c
JOIN   posts    AS p
       ON p.id = c.post_id
ORDER BY c.id;

58. Транзакции и счёт:

SELECT t.id,
       t.amount,
       a.account_no
FROM   transactions AS t
JOIN   accounts     AS a
       ON a.id = t.account_id
ORDER BY t.id;

59. Города, страны и регионы:

SELECT c.name        AS city,
       co.name       AS country,
       r.title       AS region
FROM   cities    AS c
JOIN   countries AS co
       ON co.code = c.country_code
JOIN   regions   AS r
       ON r.id   = c.region_id
ORDER BY country, region, city;

60. Заказы с менеджерами:

SELECT o.id        AS order_id,
       m.full_name AS manager
FROM   orders   AS o
JOIN   managers AS m
       ON m.id = o.manager_id
ORDER BY o.id;

61. Клиенты, у которых есть заказы (EXISTS):

SELECT c.id,
       c.full_name
FROM   customers AS c
WHERE  EXISTS (
       SELECT 1
       FROM   orders AS o
       WHERE  o.customer_id = c.id
)
ORDER BY c.id;

62. Товары, которые встречаются в заказах (IN):

SELECT p.id,
       p.title
FROM   products AS p
WHERE  p.id IN (
       SELECT oi.product_id
       FROM   order_items AS oi
)
ORDER BY p.id;

63. Посты с комментариями (EXISTS):

SELECT p.id,
       p.title
FROM   posts AS p
WHERE  EXISTS (
       SELECT 1
       FROM   comments AS c
       WHERE  c.post_id = p.id
)
ORDER BY p.id;

64. Студенты, записанные хотя бы на один курс:

SELECT s.id,
       s.name
FROM   students AS s
WHERE  s.id IN (
       SELECT e.student_id
       FROM   enrollment AS e
)
ORDER BY s.id;

65. Склады, где есть положительный остаток:

SELECT w.id,
       w.location
FROM   warehouses AS w
WHERE  EXISTS (
       SELECT 1
       FROM   stock AS st
       WHERE  st.warehouse_id = w.id
       AND    st.qty_on_hand  > 0
)
ORDER BY w.id;

66. Аккаунты с активными контактами:

SELECT a.account_id,
       a.name
FROM   crm_accounts AS a
WHERE  EXISTS (
       SELECT 1
       FROM   contacts AS c
       WHERE  c.account_id = a.account_id
       AND    c.active     = 1
)
ORDER BY a.account_id;

67. Клиенты без заказов (NOT EXISTS):

SELECT c.id,
       c.full_name
FROM   customers AS c
WHERE  NOT EXISTS (
       SELECT 1
       FROM   orders AS o
       WHERE  o.customer_id = c.id
)
ORDER BY c.id;

68. Заказы с платежами на сумму выше 100:

SELECT o.id,
       o.customer_id
FROM   orders AS o
WHERE  EXISTS (
       SELECT 1
       FROM   payments AS p
       WHERE  p.order_id = o.id
       AND    p.amount   > 100
)
ORDER BY o.id;

69. Посты без комментариев (NOT IN через подзапрос):

SELECT p.id,
       p.title
FROM   posts AS p
WHERE  p.id NOT IN (
       SELECT c.post_id
       FROM   comments AS c
)
ORDER BY p.id;

70. Клиенты с заказами в 2024 году:

SELECT c.id,
       c.full_name
FROM   customers AS c
WHERE  EXISTS (
       SELECT 1
       FROM   orders AS o
       WHERE  o.customer_id = c.id
       AND    o.order_date BETWEEN DATE '2024-01-01' AND DATE '2024-12-31'
)
ORDER BY c.id;

71. Классификация суммы заказа через CASE:

SELECT id,
       amount,
       CASE
           WHEN amount >= 1000 THEN 'LARGE'
           WHEN amount >= 100  THEN 'MEDIUM'
           ELSE 'SMALL'
       END AS size_bucket
FROM   orders
ORDER BY amount DESC;

72. Нормализация пустых email через COALESCE:

SELECT id,
       COALESCE(email, 'n/a') AS email_norm
FROM   customers
ORDER BY id;

73. Сортировка клиентов: сначала активные:

SELECT id,
       full_name,
       is_active
FROM   customers
ORDER BY is_active DESC, full_name;

74. Округление сумм до целого:

SELECT id,
       CAST(amount AS INTEGER) AS amount_int
FROM   payments
ORDER BY amount_int DESC;

75. Формирование метки уровня курса:

SELECT code,
       title,
       CASE level
           WHEN 'BEGINNER' THEN 'BASIC'
           WHEN 'ADVANCED' THEN 'PRO'
           ELSE 'OTHER'
       END AS level_label
FROM   courses
ORDER BY title;

76. Приоритет очереди: VIP > REG через CASE:

SELECT id,
       customer_id,
       CASE kind
           WHEN 'VIP' THEN 1
           ELSE 2
       END AS priority
FROM   tickets
ORDER BY priority, id;

77. Категория возраста по дате рождения:

SELECT id,
       birth_date,
       CASE
           WHEN birth_date < DATE '1980-01-01' THEN '40+'
           WHEN birth_date < DATE '2005-01-01' THEN '18-39'
           ELSE '0-17'
       END AS age_bucket
FROM   persons
ORDER BY birth_date;

78. Генерация метки для остатков:

SELECT product_id,
       qty_on_hand,
       CASE
           WHEN qty_on_hand <= 0 THEN 'OUT'
           WHEN qty_on_hand <  10 THEN 'LOW'
           ELSE 'OK'
       END AS stock_label
FROM   stock
ORDER BY product_id;

79. Классификация постов по длине заголовка:

SELECT id,
       title,
       CASE
           WHEN LENGTH(title) > 60 THEN 'LONG'
           WHEN LENGTH(title) > 30 THEN 'MID'
           ELSE 'SHORT'
       END AS title_len
FROM   posts
ORDER BY id;

80. Безопасная сортировка с NULLS в конце (условная):

SELECT id,
       COALESCE(email, 'zzz') AS email_sort
FROM   customers
ORDER BY email_sort;

81. Объединение активных и новых клиентов (UNION ALL):

SELECT id,
       full_name
FROM   customers
WHERE  status = 'ACTIVE'
UNION ALL
SELECT id,
       full_name
FROM   customers
WHERE  status = 'NEW'
ORDER BY id;

82. Список всех авторов: из справочника и по постам (UNION):

SELECT name
FROM   authors
UNION
SELECT DISTINCT author
FROM   posts
ORDER BY name;

83. Единый список идентификаторов объектов:

SELECT id
FROM   products
UNION
SELECT id
FROM   customers
UNION
SELECT id
FROM   orders
ORDER BY id;

84. Уникальные категории из товаров и постов:

SELECT category
FROM   products
UNION
SELECT DISTINCT category
FROM   posts
ORDER BY category;

85. Список email из двух источников (без дублей):

SELECT email
FROM   customers
WHERE  email IS NOT NULL
UNION
SELECT email
FROM   contacts
WHERE  email IS NOT NULL
ORDER BY email;

86. Сводный набор тегов:

SELECT tag
FROM   product_tags
UNION
SELECT tag
FROM   post_tags
ORDER BY tag;

87. Имена людей из нескольких таблиц:

SELECT full_name
FROM   employees
UNION
SELECT full_name
FROM   students
UNION
SELECT full_name
FROM   customers
ORDER BY full_name;

88. Список кодов и справочников:

SELECT code
FROM   countries
UNION
SELECT code
FROM   regions
ORDER BY code;

89. Единый поток операций (UNION ALL):

SELECT created_at AS ts,
       'ORDER'  AS kind
FROM   orders
UNION ALL
SELECT happened_at AS ts,
       'PAYMENT'   AS kind
FROM   payments
ORDER BY ts;

90. Сводная лента действий пользователей:

SELECT occurred_at AS ts,
       'LOGIN'  AS kind
FROM   logins
UNION ALL
SELECT occurred_at AS ts,
       'LOGOUT' AS kind
FROM   logouts
ORDER BY ts;

91. E‑commerce: заказы клиента за последние 30 дней:

SELECT o.id,
       o.order_date,
       o.amount
FROM   orders AS o
WHERE  o.customer_id = :customer_id
AND    o.order_date >= CURRENT_DATE - INTERVAL '30' DAY
ORDER BY o.order_date DESC;

92. Финансы: дневной оборот по счёту:

SELECT CAST(t.happened_at AS DATE) AS day,
       SUM(t.amount) AS turnover
FROM   transactions AS t
WHERE  t.account_id = :account_id
GROUP BY CAST(t.happened_at AS DATE)
ORDER BY day;

93. Логистика: загрузка склада по товарам:

SELECT s.product_id,
       SUM(s.qty_on_hand) AS total_qty
FROM   stock AS s
GROUP BY s.product_id
ORDER BY total_qty DESC;

94. Образование: посещаемость урока:

SELECT e.lesson_id,
       COUNT(*) AS attendees
FROM   enrollment AS e
GROUP BY e.lesson_id
ORDER BY attendees DESC;

95. Медицина: список визитов пациента:

SELECT v.visited_at,
       v.doctor_name,
       v.diagnosis
FROM   visits AS v
WHERE  v.patient_id = :patient_id
ORDER BY v.visited_at DESC;

96. HR: сотрудники, нанятые за квартал:

SELECT e.id,
       e.full_name,
       e.hire_date
FROM   employees AS e
WHERE  e.hire_date BETWEEN DATE '2025-01-01' AND DATE '2025-03-31'
ORDER BY e.hire_date;

97. CRM: сделки на стадии ‘Negotiation’:

SELECT d.deal_id,
       d.amount,
       a.name AS account
FROM   crm_deal    AS d
JOIN   crm_account AS a
       ON a.account_id = d.account_id
WHERE  d.stage = 'NEGOTIATION'
ORDER BY d.amount DESC;

98. Контент: посты без комментариев:

SELECT p.id,
       p.title
FROM   posts AS p
WHERE  NOT EXISTS (
       SELECT 1
       FROM   comments AS c
       WHERE  c.post_id = p.id
)
ORDER BY p.id;

99. Безопасность: неуспешные логины за сутки:

SELECT l.user_id,
       l.occurred_at,
       l.ip
FROM   login_events AS l
WHERE  l.success = 0
AND    l.occurred_at >= CURRENT_TIMESTAMP - INTERVAL '1' DAY
ORDER BY l.occurred_at DESC;

100. IoT: последние показания по устройству:

SELECT r.observed_at,
       r.metric,
       r.value
FROM   readings AS r
WHERE  r.device_id = :device_id
ORDER BY r.observed_at DESC;

Заключение

Мы рассмотрели 100 универсальных примеров SELECT SQL: от простых выборок и фильтров до объединений и подзапросов.
Команда SELECT лежит в основе отчётности, аналитики и интеграций. В связке с CREATE VIEW и CTAS (AS SELECT) она формирует устойчивые витрины данных, а корректная индексация (INDEX) и продуманные условия WHERE / JOIN обеспечивают производительность.
Следуйте единым правилам форматирования, используйте осмысленные алиасы и проверяйте планы выполнения запросов.