WHERE в PostgreSQL

Статья основана на 15 видео из 30 тем курса SQL c 0 от Аристова Евгения. Ссылки на видео на платформах RUTUBE и VK видео.

Принцип WHERE

Как уже упоминалось в прошлой статье, основная работа с данными – это их чтение. В 99% случаев требуются не все записи, а только их часть, отобранная по определенным критериям. Условия накладываются командой WHERE. Условие – логическое выражение (boolean), которое будет проверено для каждой строки из набора. Если логическое условие верно, то строка добавляется в итоговую выборку, если не верно – не добавляется.

SELECT [ ALL | DISTINCT [ ON ( expression [, …] ) ] ]

    [ * | expression [ [ AS ] output_name ] [, …] ]

[ FROM from_item [, …] ]

[WHERE условие]

Как уже неоднократно упоминалось, при сравнении NULL полей возникает проблема NULL != NULL, поэтому рекомендуется не использовать NULL.

В условиях можно использовать логические выражения AND, OR, NOT.

Практика WHERE

Создадим таблицу Склад:

CREATE SCHEMA sales;
CREATE TABLE sales.warehouse (
    id serial UNIQUE,
    name text DEFAULT '',
    kolvo int NOT NULL DEFAULT 0,
    price numeric NOT NULL DEFAULT 0.0,
    salesTime timestamp with time zone NOT NULL DEFAULT     current_timestamp, -- UTC
    salesTime2 timestamp NOT NULL DEFAULT current_timestamp
);
comment on column sales.warehouse.salesTime2 is 'Дата продажи';

Заполним значения (в том числе NULL):

INSERT INTO sales.warehouse(name) VALUES 
    ('apple');
INSERT INTO sales.warehouse(name, price) VALUES 
    ('banana',2.1);
INSERT INTO sales.warehouse(name) VALUES 
    (NULL);
INSERT INTO sales.warehouse(name) VALUES 
    (NULL);

Напишем всегда верное условие (то есть вывод всех строк):

SELECT w.name
,w.kolvo
,w.price
FROM sales.warehouse w
WHERE 1=1;

Далее сделаем всегда ложное условие:

SELECT w.name
,w.kolvo
,w.price
FROM sales.warehouse w
WHERE 1=0;

Видим, что условие необязательно должно затрагивать выбранным поля.

Напишем условие для выбора всех яблок:

SELECT w.name
,w.kolvo
,w.price
FROM sales.warehouse w
WHERE w.name = 'apple';

Нужно обратить внимание, что ALIAS присваивается уже после того, как выборка сделана, поэтому внутри условия к нему обратиться нельзя (но можно в ORDER BY):

SELECT w.name AS n
,w.kolvo
,w.price
FROM sales.warehouse w
WHERE n = 'apple'; -- alias еще не существует

К полям можно обращаться по номерам, но не рекомендуется этого делать, так как при изменении структуры таблицы все запросы нужно будет менять.

Напишем следующий запрос:

SELECT w.name
,w.kolvo
,w.price
FROM sales.warehouse w
WHERE w.name = ‘apple’ or w.name=NULL; — не сработает

Запрос должен вернуть три строки – яблоки и две строки со значением NULL. Однако w.name=NULL не работает из-за вышеупомянутой проблемы NULL != NULL.

Для работы с NULL нужно использовать специальную конструкцию IS NULL:

SELECT w.name
,w.kolvo
,w.price
FROM sales.warehouse w
WHERE w.name = 'apple' or w.name IS NULL;

Теперь запрос выполняется корректно.

Рекомендуется использовать скобки при большом количестве условий во избежание ошибок приоритета:

SELECT w.name
,w.kolvo
,w.price
FROM sales.warehouse w
WHERE (w.name = 'apple') or (w.name IS NULL);

Использование функций в WHERE

Если вы используете функцию в условии, могут возникать сайд эффекты, в зависимости от свойств волатильности. Если установлено значение по умолчанию VOLATILE, то:

• запрос не кешируется
• вызывается каждый раз при обращении к ней (вычисляется)

Создадим запрос для базы данных Тайские перевозки (инструкция по скачиванию в этой статье):

SELECT floor(random()*5000000) as r \gset

SELECT id, fkRide, fio, contact, fkSeat FROM book.tickets WHERE id = :r;

Таким образом, мы сначала выбираем рандомное r, а затем проходимся по вей таблице в поисках совпадающего с r id.

Попробуем поместить вызов функции случайного числа сразу внутрь запроса:

SELECT id, fkRide, fio, contact, fkSeat FROM book.tickets WHERE id = floor(random()*5000000);

Такой запрос выполняется почти в 100 раз дольше и выводит непредсказуемый результат!

Причина в том, что при переборе строк функция random() вызывается каждый раз. То есть id каждой строки мы сравниваем с новым случайным числом.

Но если функция имеет свойства STABLE или IMMUTABLE, то она не будет вызываться для каждой строки, а будет закеширована.

ORDER BY

ORDER BY – конструкция для сортировки итоговой выборки (результата запроса). По умолчанию сортируется по возрастанию.

Сортировка – затратная по ресурсам процедура, поэтому имеет смысл переложить на бэкенд.

Варианты сортировки:

• По возрастанию (ASC) – по умолчанию
• По убыванию (DESC)

Выведем все поездки с ID до 10, отсортируем их по фамилии:

SELECT id
, fkRide
, fio
, contact
, fkSeat
FROM book.tickets
WHERE id < 10
ORDER BY fio;

Теперь отсортируем эти же данные по убыванию фамилии и возрастанию контактов:

SELECT id
, fkRide
, fio
, contact
, fkSeat
FROM book.tickets
WHERE id < 10
ORDER BY fio desc, contact asc;

Обратим внимание, что при сортировке по убыванию и фамилии, и контактов результат не изменится:

SELECT id
, fkRide
, fio
, contact
, fkSeat
FROM book.tickets
WHERE id < 10
ORDER BY fio desc, contact desc;

Результаты одинаковые, так как у нас нет совпадений в колонке fio, чтобы эти совпадения сортировать по контактам.

OFFSET LIMIT

OFFSET – смещение относительно начала выборки

LIMIT – ограничение выборки после смещения

Аналог в реальной жизни – вывод в поисковике нескольких страниц по 20 результатов на каждой.

Выведем записи с id меньше 10 со смещением 5:

SELECT id
, fkRide
, fio
, contact
, fkSeat
FROM book.tickets
WHERE id < 10
ORDER BY id
OFFSET 5;

В результате получаем последние 4 записи из выборки.

Выведем первые три записи той же выборки без смещения:

SELECT id
, fkRide
, fio
, contact
, fkSeat
FROM book.tickets
WHERE id < 10
ORDER BY id 
LIMIT 3;

Выведем 3 записи со смещением 2:

SELECT id
, fkRide
, fio
, contact
, fkSeat
FROM book.tickets
WHERE id < 10
ORDER BY id 
OFFSET 2
LIMIT 3;

Необходимо заметить, что сначала будут выбраны все данные, потом они будут сгруппированы, посчитаны оконные функции и только потом будут работать наши ограничения по количеству строк на выборку.

Важно

Порядок команд SELECT FROM WHERE ORDER BY OFFSET LIMIT строго определен!

Презентация к статье здесь.

16 из 30 тем будет скоро доступна. Если вы хотите быстрее получить доступ – присоединяйтесь к онлайн группе, ссылка доступна в описании курса.