Запит даних

Процес отримання або команда для отримання даних з бази даних називається запит. У мові SQL для визначення запитів використовується команда SELECT.

SELECT вибирає рядки з нуля або більше таблиць. Загальна обробка SELECT відбувається наступним чином:

Всі запити у списку WITH обчислюються. Це фактично слугує тимчасовими таблицями, на які можна посилатися у списку FROM.
Обчислюються всі елементи списку FROM. Якщо у списку FROM вказано більше одного елемента, вони об’єднуються разом.
Якщо вказано оператор WHERE, то всі рядки, які не задовольняють умові, вилучаються з виводу.
Якщо вказано речення GROUP BY або якщо є виклики агрегатних функцій, то вихідні дані об’єднуються в групи рядків, які збігаються за одним або декількома значеннями, і обчислюються результати агрегатних функцій. Якщо присутній оператор HAVING, то вилучаються групи, які не задовольняють заданій умові.
Фактичні вихідні рядки обчислюються за допомогою виразів SELECT для кожного рядка або рядка групи.
SELECT DISTINCT видаляє повторювані рядки з результату. SELECT DISTINCT ON вилучає рядки, які збігаються за всіма вказаними виразами.
За допомогою операторів UNION, INTERSECT і EXCEPT можна об’єднати результати виконання кількох інструкцій SELECT, щоб сформувати єдиний набір результатів.
- Оператор UNION повертає всі рядки, які є в одному або обох наборах результатів.
- Оператор INTERSECT повертає всі рядки, які строго знаходяться в обох наборах результатів.
- Оператор EXCEPT повертає рядки, які є в першому наборі результатів, але відсутні в другому.
Якщо вказано параметр ORDER BY, повернуті рядки буде відсортовано у певному порядку.
якщо вказано оператор LIMIT або OFFSET, то інструкція SELECT повертає лише підмножину рядків результату.
Якщо вказано FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE або FOR KEY SHARE, оператор SELECT блокує вибрані рядки від одночасного оновлення.

Виберіть рядки з таблиці 

Простий вид запиту має вигляд:

SELECT * FROM publisher;

Ця команда вилучить усі рядки і всі визначені користувачем стовпці з таблиці publisher. Список вибору може також вибирати підмножину доступних стовпців або виконувати обчислення за цими стовпцями.

SELECT title, page_count / 2
FROM book;

речення FROM є простим різновидом табличного виразу: воно зчитує лише одну таблицю. Взагалі, табличний вираз може бути складною конструкцією з базових таблиць, з’єднань і підзапитів. Але можна взагалі не використовувати табличний вираз і використовувати команду SELECT як калькулятор (або в інший спосіб):

SELECT 40 + 2;

Речення FROM виводить таблицю з однієї або кількох інших таблиць, наведених у списку посилань на таблиці, розділених комами. Посилання на таблицю може бути ім’ям таблиці або похідною таблицею, наприклад, підзапитом, конструкцією JOIN або їх складними комбінаціями. Якщо в реченні FROM вказано більше одного посилання на таблицю, то таблиці з’єднуються перехресним зв’язком. Результатом списку FROM є проміжна віртуальна таблиця, яка потім може бути піддана перетворенням за допомогою речень WHERE, GROUP BY і HAVING і, нарешті, є результатом загального табличного виразу.

Умови пошуку 

Після обробки оператора FROM кожен рядок отриманої віртуальної таблиці може бути перевірений на відповідність умові пошуку. Якщо результат умови рівний true, то рядок зберігається у вихідній таблиці, інакше він відкидається. Для перевірки умови пошуку використовується оператор WHERE.

Ось кілька прикладів речення WHERE:

Приклади використання речення WHERE

SELECT ... FROM tbl
WHERE c1 > 5;

SELECT ... FROM tbl
WHERE c1 IN (1, 2, 3);

SELECT ... FROM tbl
WHERE c1 IN (SELECT c1 FROM t2);

SELECT ... FROM tbl
WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = tbl.c1 + 10);

SELECT ... FROM tbl
WHERE c1 BETWEEN (SELECT c3 FROM t2 WHERE c2 = tbl.c1 + 10) AND 100;

SELECT ... FROM tbl
WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 > fdt.c1);

Дані для групування 

Після проходження фільтра WHERE отримана таблиця може бути згрупована за допомогою речення GROUP BY і видалена з групи рядків за допомогою речення HAVING. Речення GROUP BY використовується для групування тих рядків таблиці, які мають однакові значення у всіх перелічених стовпчиках. Порядок перерахування стовпчиків не має значення. Ефект полягає в об’єднанні кожного набору рядків зі спільними значеннями в один груповий рядок, який представляє всі рядки в групі. Це робиться для того, щоб усунути надмірність у вихідних даних та/або обчислювальних агрегатах, які застосовуються до цих груп.

Виберіть списки 

Як показано у попередньому розділі, вираз table у команді SELECT створює проміжну віртуальну таблицю шляхом можливого об’єднання таблиць, представлень, вилучення рядків, групування тощо. Ця таблиця нарешті передається для обробки за допомогою списку вибору. Список вибору визначає, які саме стовпці проміжної таблиці буде виведено на екран.

Вибір елементів списку 

Найпростішим видом вибору є *, який виводить те, що створює табличний вираз. Інакше, список вибору - це список виразів значень, розділених комами. Наприклад:

SELECT first_name, last_name
FROM author;

Мітки стовпців 

Записам, переліченим у списку вибору, можна присвоїти імена для подальшої обробки.

SELECT title, page_count / 2 as sheets FROM book;

Якщо за допомогою AS не вказано назву вихідного стовпця, система призначить назву стовпця за замовчуванням. Для простих посилань на стовпці - це ім’я стовпця, на який є посилання. Для викликів функцій - це назва функції. Для складних виразів система згенерує загальну назву.

Примітка

Мітки часто називають «псевдонімами».

DISTINCT 

Після обробки списку вибірки таблиця результатів може бути піддана вилученню рядків, що повторюються. Для цього безпосередньо після команди SELECT записується ключове слово DISTINCT:

SELECT DISTINCT publisher FROM book;

Очевидно, що два рядки вважаються різними, якщо вони відрізняються принаймні в одному стовпчику. Нульові значення вважаються рівними при порівнянні. Крім того, довільний вираз може визначати, які рядки вважати різними.

SELECT DISTINCT ON (expression [, expression ...]) select_list ...

Сортування рядків 

Після того, як запит створив вихідну таблицю, її можна відсортувати за бажанням. Якщо сортування не вибрано, рядки будуть повернуті в невизначеному порядку. Фактичний порядок у цьому випадку залежатиме від типів планів сканування та об’єднання, а також порядку на диску, але на нього не можна покладатися.

Речення ORDER BY задає порядок сортування:

SELECT title, publisher_id FROM book ORDER BY title;

Вираз(и) сортування може бути будь-яким виразом, який буде допустимим у списку вибору запиту.

Якщо вказано більше одного виразу, пізніші значення використовуються для сортування рядків, які є рівними за попередніми значеннями. Кожен вираз може супроводжуватися ключовим словом ASC або DESC для встановлення напрямку сортування за зростанням або спаданням. за замовчуванням використовується порядок ASC.

Опції NULL FIRST та NULL LAST можна використовувати для визначення того, чи з’являтимуться нулі до або після ненульових значень у порядку сортування. За замовчуванням, нульові значення сортуються так, ніби вони більші за будь-яке ненульове значення; тобто NULL FIRST за замовчуванням для порядку DESC, а NULL LAST в іншому випадку.

Зверніть увагу, що варіанти впорядкування розглядаються незалежно для кожного стовпця сортування. Наприклад, ORDER BY x, y DESC означає ORDER BY x ASC, y DESC, що не те саме, що ORDER BY x DESC, y DESC.

Вираз сортування також може бути міткою стовпця або номером вихідного стовпця.

оператор ORDER BY можна застосувати до результату комбінації UNION, INTERSECT або EXCEPT, але в цьому випадку дозволяється сортування лише за іменами або номерами вихідних стовпців, а не за виразами.

Ліміт і зміщення 

LIMIT і OFFSET дозволяють вам отримати лише частину рядків, згенерованих рештою запиту.

SELECT * FROM publisher LIMIT 10;

Наведений вище запит витягує лише перші 10 рядків з таблиці «publisher».

Якщо вказано граничну кількість рядків, буде повернуто не більше цієї кількості. Але, можливо, і менше, якщо сам запит повертає менше рядків. LIMIT ALL - це те саме, що опустити пункт LIMIT, так само як і LIMIT з аргументом NULL.

OFFSET вказує пропустити стільки-то рядків перед початком повернення рядків. OFFSET 0 - це те саме, що опустити вираз OFFSET, так само як і OFFSET з аргументом NULL.

SELECT * FROM publisher LIMIT 10 OFFSET 10;

Наведений вище запит показує 10 видавництв, починаючи з 11-го.

При використанні LIMIT та/або OFFSET важливо використовувати речення ORDER BY, яке обмежує рядки результатів в унікальному порядку. Інакше ви отримаєте непередбачувану підмножину рядків запиту.

WITH 

Команда WITH надає можливість писати допоміжні оператори для використання у великих запитах. Ці оператори, які часто називають CTE, можна розглядати як визначення тимчасових таблиць, які існують лише для одного запиту. Кожен допоміжний оператор у реченні WITH може бути оператором SELECT, INSERT, UPDATE або DELETE; а саме речення WITH приєднується до основного оператора, який може бути оператором SELECT, INSERT, UPDATE, DELETE або MERGE.

Вибірка у WITH 

Основне значення SELECT у WITH полягає в тому, щоб розбивати складні запити на простіші частини.

Отримайте найприбутковіші місяці

-- select top revenue months
WITH monthly_revenue AS (SELECT EXTRACT(YEAR FROM date)  AS year,
                                EXTRACT(MONTH FROM date) AS month,
                                SUM(amount)              AS total_amount
                         FROM revenue
                         GROUP BY year, month
                         ORDER BY year, month)
SELECT *
FROM monthly_revenue
WHERE total_amount > (SELECT SUM(total_amount) / 100 FROM monthly_revenue)
ORDER BY total_amount DESC;

Рекурсивні запити 

Додатковий модифікатор RECURSIVE перетворює WITH з простої синтаксичної зручності на функцію, яка виконує речі, неможливі у стандартному SQL. Використовуючи RECURSIVE, запит WITH може посилатися на свій власний вивід.

Сума перших 10 чисел, починаючи з 1

WITH RECURSIVE t(n) AS (VALUES (1) UNION SELECT n + 1 FROM t WHERE n < 10)
SELECT sum(n)
FROM t;

Важливо

При роботі з рекурсивними запитами важливо бути впевненим, що рекурсивна частина запиту в кінцевому підсумку не поверне жодного кортежу, інакше запит зациклиться на невизначений час.

Оператори, що модифікують дані в операторі WITH 

Ви можете використовувати більшість операторів модифікації даних (INSERT, UPDATE, DELETE) в WITH. Це дозволяє виконувати кілька різних операцій одночасно в одному запиті.

Видаліть старі записи про доходи та збережіть їх у таблиці історії

WITH deleted_rows AS (
    DELETE FROM revenue
        WHERE date < '2020-01-01'
        RETURNING *)
INSERT
INTO revenue_log
SELECT *
FROM deleted_rows;

Об’єднання запитів 

Результати двох запитів можна об’єднати за допомогою операцій об’єднання, перетину та різниці.

query1 UNION [ALL] query2
query1 INTERSECT [ALL] query2
query1 EXCEPT [ALL] query2

UNION ефективно додає результат query2 до результату query1 (хоча немає жодних гарантій, що це саме той порядок, в якому повертаються рядки). Крім того, вона вилучає з результату повторювані рядки так само, як і DISTINCT, якщо не використовується UNION ALL.

WITH north_lib_member AS
         (SELECT *
          FROM (VALUES (1, 'Mariska', 'Syson'),
                       (2, 'Robenia', 'Dimitriades'),
                       (3, 'Loren', 'Goretti'),
                       (4, 'Dianna', 'Giacomuzzo'),
                       (5, 'Anett', 'Hayland'),
                       (6, 'Keeley', 'Grishelyov')) AS tbl(id, first_name, last_name)),
     south_lib_member AS
         (SELECT *
          FROM (VALUES (5, 'Anett', 'Hayland'),
                       (6, 'Keeley', 'Grishelyov'),
                       (7, 'Say', 'Kliner'),
                       (8, 'Charles', 'Ede'),
                       (9, 'Florentia', 'Core'),
                       (10, 'Tiffany', 'Tingly'))
                   AS tbl(id, first_name, last_name))
SELECT *
FROM north_lib_member
UNION
SELECT *
FROM south_lib_member
ORDER BY id;

id	first_name	last_name
1	Mariska	Syson
2	Robenia	Dimitriades
3	Loren	Goretti
4	Dianna	Giacomuzzo
5	Anett	Hayland
6	Keeley	Grishelyov
7	Say	Kliner
8	Charles	Ede
9	Florentia	Core
10	Tiffany	Tingly

INTERSECT повертає всі рядки, які є і в результаті query1, і в результаті query2. Повторювані рядки вилучаються, якщо не використовується INTERSECT ALL.

WITH north_lib_member AS
         (SELECT *
          FROM (VALUES (1, 'Mariska', 'Syson'),
                       (2, 'Robenia', 'Dimitriades'),
                       (3, 'Loren', 'Goretti'),
                       (4, 'Dianna', 'Giacomuzzo'),
                       (5, 'Anett', 'Hayland'),
                       (6, 'Keeley', 'Grishelyov')) AS tbl(id, first_name, last_name)),
     south_lib_member AS
         (SELECT *
          FROM (VALUES (5, 'Anett', 'Hayland'),
                       (6, 'Keeley', 'Grishelyov'),
                       (7, 'Say', 'Kliner'),
                       (8, 'Charles', 'Ede'),
                       (9, 'Florentia', 'Core'),
                       (10, 'Tiffany', 'Tingly'))
                   AS tbl(id, first_name, last_name))
SELECT *
FROM north_lib_member
INTERSECT
SELECT *
FROM south_lib_member
ORDER BY id;

id	first_name	last_name
5	Anett	Hayland
6	Keeley	Grishelyov

EXCEPT повертає всі рядки, які є в результаті query1, але не є в результаті query2. (Це іноді називають різницею між двома запитами.) Знову ж таки, дублікати вилучаються, якщо не використовується EXCEPT ALL.

WITH north_lib_member AS
         (SELECT *
          FROM (VALUES (1, 'Mariska', 'Syson'),
                       (2, 'Robenia', 'Dimitriades'),
                       (3, 'Loren', 'Goretti'),
                       (4, 'Dianna', 'Giacomuzzo'),
                       (5, 'Anett', 'Hayland'),
                       (6, 'Keeley', 'Grishelyov')) AS tbl(id, first_name, last_name)),
     south_lib_member AS
         (SELECT *
          FROM (VALUES (5, 'Anett', 'Hayland'),
                       (6, 'Keeley', 'Grishelyov'),
                       (7, 'Say', 'Kliner'),
                       (8, 'Charles', 'Ede'),
                       (9, 'Florentia', 'Core'),
                       (10, 'Tiffany', 'Tingly'))
                   AS tbl(id, first_name, last_name))
SELECT *
FROM north_lib_member
EXCEPT
SELECT *
FROM south_lib_member
ORDER BY id;

id	first_name	last_name
1	Mariska	Syson
2	Robenia	Dimitriades
3	Loren	Goretti
4	Dianna	Giacomuzzo

Об’єднання даних з різних таблиць 

Речення JOIN об’єднує два елементи FROM, які для зручності будемо називати «таблицями», хоча насправді це може бути будь-який тип елементів FROM. Доступні типи з’єднання:

[INNER] JOIN
LEFT [OUTER] JOIN
RIGHT [OUTER] JOIN
FULL [OUTER] JOIN

Примітка

Ключові слова в дужках вище є необов’язковими.

З’єднання між столами 

Визначення таблиць видавництв та книг

CREATE TABLE publisher
(
    id      INTEGER PRIMARY KEY,
    name    VARCHAR(128) NOT NULL CHECK (LENGTH(name) > 0),
    website VARCHAR(255) UNIQUE,
    email   VARCHAR(255),
    phone   VARCHAR(32) UNIQUE
);

CREATE TABLE book
(
    id               SERIAL PRIMARY KEY,
    title            VARCHAR(255) NOT NULL,
    synopsis         TEXT,
    isbn             VARCHAR(16),
    publisher_id     INTEGER      NOT NULL REFERENCES publisher ON DELETE RESTRICT,
    publication_date DATE,
    language         VARCHAR(64),
    page_count       INTEGER,
    keywords         TEXT
);

Наступні таблиці пов’язані між собою за допомогою обмеження FOREIGN KEY. Кожен запис у таблиці book посилається на деякий запис у таблиці publisher.

Важливо

Оскільки вихідні таблиці містять багато даних, перед об’єднанням запитів буде створено тимчасові таблиці. Повний вміст таблиць можна завантажити за посиланнями: publisher, book.

Підмножини з вихідних запитів визначаються як:

SELECT *
FROM (VALUES (1, 'Zoonoodle'),
             (3, 'Tanoodle'),
             (4, 'Skivee'))
         AS publisher(id, name);

id	name
1	Zoonoodle
3	Tanoodle

SELECT *
FROM (VALUES (22, 'pretium nisl', 2),
             (38, 'ac est lacinia nisi venenatis', 3),
             (69, 'vel accumsan tellus nisi', 3),
             (72, 'nam dui proin', 2),
             (85, 'nam tristique', 2))
         AS book(id, title, publisher_id);

id	title	publisher_id
22	pretium nisl	2
38	ac est lacinia nisi venenatis	3
69	vel accumsan tellus nisi	3
72	nam dui proin	2
85	nam tristique	2

При передачі декількох таблиць в речення FROM виконується запит CROSS JOIN (перехресне об’єднання). Це означає, що результуюча таблиця є декартовим добутком двох вихідних таблиць.

Поперечне з’єднання (декартовий добуток)

WITH publisher AS
         (SELECT *
          FROM (VALUES (1, 'Zoonoodle'),
                       (3, 'Tanoodle'),
                       (4, 'Skivee'))
              AS publisher(id, name)),
     book AS
         (SELECT *
          FROM (VALUES (22, 'pretium nisl', 2),
                       (38, 'ac est lacinia nisi venenatis', 3),
                       (69, 'vel accumsan tellus nisi', 3),
                       (72, 'nam dui proin', 2),
                       (85, 'nam tristique', 2))
                   AS book(id, title, publisher_id))
-- querying the joined result
SELECT *
FROM book,
     publisher;

Результат запиту
id	title	publisher_id	id	name
22	pretium nisl	2	1	Zoonoodle
22	pretium nisl	2	3	Tanoodle
22	pretium nisl	2	4	Skivee
38	ac est lacinia nisi venenatis	3	1	Zoonoodle
38	ac est lacinia nisi venenatis	3	3	Tanoodle
38	ac est lacinia nisi venenatis	3	4	Skivee
69	vel accumsan tellus nisi	3	1	Zoonoodle
69	vel accumsan tellus nisi	3	3	Tanoodle
69	vel accumsan tellus nisi	3	4	Skivee
72	nam dui proin	2	1	Zoonoodle
72	nam dui proin	2	3	Tanoodle
72	nam dui proin	2	4	Skivee
85	nam tristique	2	1	Zoonoodle
85	nam tristique	2	3	Tanoodle
85	nam tristique	2	4	Skivee

За допомогою оператора WHERE рядки, які не відповідають умові, можуть бути вилучені з результату.

SELECT *
FROM book, publisher
WHERE book.publisher_id = publisher.id;

id	title	publisher_id	id	name
38	ac est lacinia nisi venenatis	3	3	Tanoodle
69	vel accumsan tellus nisi	3	3	Tanoodle

Це називається INNER JOIN, що означає, що результуюча таблиця буде містити тільки ті рядки з лівої та правої таблиць, які мають відповідні записи один одному.

Той самий результат можна отримати за допомогою оператора JOIN. Всі опції JOIN є лише зручністю для нотації, оскільки вони не роблять нічого такого, чого не можна було б зробити за допомогою звичайних FROM і WHERE.

SELECT *
FROM book b
         JOIN publisher p ON b.publisher_id = p.id;

INNER JOIN використовується за замовчуванням при з’єднанні двох таблиць одна з одною.

Праве та ліве з’єднання 

LEFT OUTER JOIN повертає всі рядки кваліфікованого декартового добутку (тобто всі об’єднані рядки, які задовольняють його умову), плюс по одній копії кожного рядка лівої таблиці, для якого не було рядка правої таблиці, що задовольняє умову об’єднання.

SELECT *
FROM book b
         LEFT JOIN publisher p ON b.publisher_id = p.id;

id	title	publisher_id	id	name
22	pretium nisl	2	null	null
38	ac est lacinia nisi venenatis	3	3	Tanoodle
69	vel accumsan tellus nisi	3	3	Tanoodle
72	nam dui proin	2	null	null
85	nam tristique	2	null	null

І навпаки, RIGHT OUTER JOIN повертає всі об’єднані рядки, плюс один рядок для кожного невідповідного правого рядка. Це просто зручність позначення, оскільки ви можете перетворити його на LEFT OUTER JOIN, помінявши місцями ліву та праву таблиці.

SELECT *
FROM book b
         RIGHT JOIN publisher p ON b.publisher_id = p.id;

id	title	publisher_id	id	name
38	ac est lacinia nisi venenatis	3	3	Tanoodle
69	vel accumsan tellus nisi	3	3	Tanoodle
null	null	null	4	Skivee
null	null	null	1	Zoonoodle

Full outer join 

SELECT *
FROM book b
         FULL JOIN publisher p ON b.publisher_id = p.id;

id	title	publisher_id	id	name
22	pretium nisl	2	null	null
38	ac est lacinia nisi venenatis	3	3	Tanoodle
69	vel accumsan tellus nisi	3	3	Tanoodle
72	nam dui proin	2	null	null
85	nam tristique	2	null	null
null	null	null	4	Skivee
null	null	null	1	Zoonoodle