Какие инструкции могут быть использованы в операторах выборки данных

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

Синтаксис оператора SELECT использует следующие основные предложения:

SELECT <список столбцов>
FROM <список таблиц>
[WHERE <условие выбора строк>]
[GROUP BY <условие группировки>]
[HAVING <условие выбора групп>]
[ORDER BY <условие сортировки>]

Кратко пояснить смысл предложений оператора SELECT можно следующим образом:

  • SELECT — выбрать данные из указанных столбцов и (если необходимо) выполнить перед выводом их преобразование в соответствии с указанными выражениями и (или) функциями
  • FROM — из перечисленных таблиц, в которых расположены эти столбцы
  • WHERE — где строки из указанных таблиц должны удовлетворять указанному перечню условий отбора строк
  • GROUP BY — группируя по указанному перечню столбцов с тем, чтобы получить для каждой группы единственное значение
  • HAVING — имея в результате лишь те группы, которые удовлетворяют указанному перечню условий отбора групп
  • ORDER BY — сортируя по указанному перечню столбцов

Как видно из синтаксиса рассматриваемого оператора, обязательными являются только два первых предложения: SELECT и FROM.

Рассмотрим каждое предложение оператора SELECT.

Спонсор поста

База данных для примеров

Дальше будет много примеров и логично постоянно использовать одну и ту же БД. Так что на основании базы данных ниже будут продемонстрированы все примеры, не только в этой статье, но и в других.

Постановка задачи: пусть требуется разработать БД для предметной области «Поставка деталей»!

Требуется хранить следующую информацию:

  • О поставщиках (P) pnum, pname
  • О деталях (D) pnum, dname, dprice
  • О поставках (PD) volume

Значения таблицы P

pnum pname
1 Иванов
2 Петров
3 Сидоров
4 Кузнецов

Значения таблицы D

pnum dname dprice
1 Болт 10
2 Гайка 20
3 Винт 30

Значения таблицы PD

pnum dnum volume
1 1 100
1 2 100
1 3 300
2 1 150
1 2 250
3 1 1000

После служебного слова SELECT перечисляются имена столбцов, значения которых будут входить в результат выполнения запроса.

Столбцы в результирующей таблице размещаются в том порядке, в котором они были указаны в предложении SELECT. Имена столбцов указываются через запятую.

Если имя столбца содержит пробелы или разделители, то его необходимо заключить в квадратные скобки.

При обработке данных из разных таблиц может возникнуть ситуация, когда столбцы разных таблиц имеют одинаковые имена. В этом случае имя столбца необходимо записывать как составное, указывая перед ним имя соответствующей таблицы: <Имя таблицы>.<Имя столбца>

Предложение FROM

В предложении FROM перечисляются имена таблиц, которые содержат столбцы, указанные после слова SELECT.

Пример 1.
Вывести список наименований деталей из таблицы D (“Детали”).

SELECT dname
FROM D

Пример 2.
Получить всю информацию из таблицы D (“Детали”).

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

  • Явным указанием всех столбцов таблицы.

    SELECT dnum, dname, dprice
FROM D

  • Полный список столбцов таблицы заменяет символ *.

    SELECT *
FROM D

В результате и первого и второго запроса получаем новую таблицу, представляющую собой полную копию таблицы D (“Детали”).

Можно осуществить выбор отдельных столбцов и их перестановку.

Пример 3.
Получить информацию о наименовании и номере поставщика.

SELECT pname, pnum
FROM P

Пример 4.
Определить номера поставщиков, которые поставляют детали в настоящее время (то есть номера тех поставщиков, которые присутствуют в таблице PD (“Поставки”)).

SELECT pnum
FROM PD

Результат:

pnum
1
1
1
2
2
3

Дополнительно о SELECT

Теперь, когда мы научились делать простые запросы с SELECT и FROM, можно ненадолго снова вернуться к SELECT.

Агрегатные функции

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

Агрегатная функция записывается в следующем виде: <имя функции>(<имя столбца>)

Пользователю доступны следующие агрегатные функции:

  • SUM ‑ вычисляет сумму множества значений указанного столбца;
  • COUNT ‑ вычисляет количество значений указанного столбца;
  • MIN / MAX ‑ определяет минимальное/максимальное значение в указанном столбце;
  • AVG ‑ вычисляет среднее арифметическое значение множества значений столбца;
  • FIRST / LAST ‑ определяет первое/последнее значение в указанном столбце.

Пример 5.
Определить общий объем поставляемых деталей.

SELECT SUM(volume)
FROM PD
Expr1000
2000

Вычисляемые столбцы

Столбцы результирующей таблицы, которых не существовало в исходных таблицах, называются вычисляемыми. Таким столбцам СУБД присваивает системные имена, что не всегда является удобным.

При вычислении результатов любой агрегатной функции СУБД сначала исключает все NULL-значения, после чего требуемая операция применяется к оставшимся значениям.

Для функции COUNT возможен особый вариант использования — COUNT(*). Его назначение состоит в подсчете всех строк в результирующей таблице, включая NULL-значения.

Следует запомнить, что агрегатные функции нельзя вкладывать друг в друга. Такая конструкция работать не будет: MAX(SUM(VOLUME))

Переименование столбца

Язык SQL позволяет задавать новые имена столбцам результирующей таблицы, для чего используется операция AS. Переименование также используют для изменения сложных имен столбцов таблицы.

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

SELECT SUM(volume) AS SUM
FROM PD
Sum
2000

Пример 6.
Определить количество поставщиков, которые поставляют детали в настоящее время.

SELECT COUNT(pnum) AS COUNT
FROM PD
Count
6

Несмотря на то, что реальное число поставщиков деталей в таблице PD равно 3, СУБД возвращает число 6. Такой результат объясняется тем, что СУБД подсчитывает все строки в таблице PD, не обращая внимание на то, что в строках есть одинаковые значения.

Операция DISTINCT

Если до применения агрегатной функции необходимо исключить дублирующиеся значения, следует перед именем столбца указать ключевое слово DISTINCT.

SELECT COUNT(DISTINCT pnum) AS COUNT
FROM PD
Count
3

DISTINCT можно задать только один раз для одного предложения SELECT.

Противоположностью DISTINCT является операция ALL. Она имеет противоположное действие «показать все строки таблицы» и предполагается по умолчанию.

Операция TOP

Итоговый набор записей, получаемых после выполнения запроса можно ограничить первыми N строками или первыми N процентами от общего количества строк результата.

Для этого используется операция TOP, которая записывается в предложении SELECT следующим образом: SELECT TOP N [PERCENT] <список столбцов>

Пример 7.
Определить номера первых двух деталей таблицы D.

SELECT TOP 2 dnum
FROM D

Стандарт SQL требует, чтобы при сортировке NULL-значения трактовались либо как превосходящие, либо как уступающие по сравнению со всеми остальными значениями. Так как конкретный вариант стандартом не оговаривается, то в зависимости от используемой СУБД при сортировке NULL-значения следуют до или после остальных значений. В MS SQL Server NULL-значения считаются уступающими по сравнению с остальными значениями.

Предложение WHERE

После служебного слова WHERE указываются условия выбора строк, помещаемых в результирующую таблицу. Существуют различные типы условий выбора:

Типы условий выбора:

  • Сравнение значений атрибутов со скалярными выражениями, другими атрибутами или результатами вычисления выражений.
  • Проверка значения на принадлежность множеству.
  • Проверка значения на принадлежность диапазону.
  • Проверка строкового значения на соответствие шаблону.
  • Проверка на наличие null-значения.

Сравнение

В языке SQL используются традиционные операции сравнения =,<>,<,<=,>,>=.

В качестве условия в предложении WHERE можно использовать сложные логические выражения, использующие атрибуты таблиц, константы, скобки, операции AND, OR, отрицание NOT.

Пример 8.
Определить номера деталей, поставляемых поставщиком с номером 2.

SELECT dnum
FROM PD
WHERE pnum = 2

Пример 9.
Получить информацию о поставщиках Иванов и Петров.

SELECT *
FROM P
WHERE pname='Иванов' OR pname='Петров'

Строковые значения атрибутов заключаются в апострофы.

Проверка на принадлежность множеству

Операция IN проверяет, принадлежит ли значение атрибута заданному множеству.

Пример 10.
Получить информацию о поставщиках ‘Иванов’ и ‘Петров’.

SELECT *
FROM P
WHERE pname IN ('Иванов','Петров')

Пример 11.
Получить информацию о деталях с номерами 1 и 2.

SELECT *
FROM D
WHERE dnum IN (1, 2)

Проверка на принадлежность диапазону

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

Пример 12.
Определить номера деталей, с ценой от 10 до 20 рублей.

SELECT dnum
FROM D
WHERE dprice BETWEEN 10 AND 20

Пример 13.
Вывести наименования поставщиков, начинающихся с букв от ‘К’ по ‘П’.

SELECT pname
FROM P
WHERE pname BETWEEN 'К' AND 'Р'

Сравнение символов

Буква Р в условии запроса объясняется тем, что строки сравниваются посимвольно. Для каждого символа при этом определяется код. Для нашего случая справедливо условие: П < Петров < Р

Проверка строкового значения на соответствие шаблону

Операция LIKE используется для поиска подстрок. Значения столбца, указываемого перед служебным словом LIKE сравниваются с задаваемым после него шаблоном. Форматы шаблонов различаются в конкретных СУБД.

Для СУБД MS SQL Server:

  • Символ % заменяет любое количество любых символов.
  • Символ _ заменяет один любой символ.
  • [<множество символов>] ‑ вместо символа строки может быть подставлен один любой символ из множества возможных, указанных в ограничителях.
  • [^<множество символов>] ‑ вместо символа строки может быть подставлен любой из символов кроме символов из множества, указанного в ограничителях.

Множество символов в квадратных скобках можно указывать через запятую, либо в виде диапазона.

Пример 14.
Вывести фамилии поставщиков, начинающихся с буквы И.

SELECT pname
FROM P
WHERE pname LIKE 'И%'

Пример 15.
Вывести фамилии поставщиков, начинающихся с букв от К по П.

SELECT pname
FROM P
WHERE dname LIKE '[К-П]%'

Проверка на наличие null-значения

Операции IS NULL и IS NOT NULL используются для сравнения значения атрибута со значением NULL.

Пример 16.
Определить наименования деталей, для которых не указана цена.

SELECT dname
FROM D
WHERE dprice IS NULL

Пример 17.
Определить номера поставщиков, для которых указано наименование.

SELECT pnum
FROM P
WHERE pname IS NOT NULL

Предложение GROUP BY

Использование GROUP BY позволяет разбивать таблицу на логические группы и применять агрегатные функции к каждой из этих групп. В результате получим единственное значение для каждой группы.

Обычно предложение GROUP BY применяют, если формулировка задачи содержит фразу «для каждого…», «каждому..» и т.п.

Пример 18.
Определить суммарный объем деталей, поставляемых каждым поставщиком.

SELECT pnum, SUM(VOLUME) AS SUM
FROM PD
GROUP BY pnum
pnum sum
1 600
2 400
3 1000

Выполнение запроса можно описать следующим образом: СУБД разбивает таблицу PD на три группы, в каждую из групп помещаются строки с одинаковым значением номера поставщика. Затем к каждой из полученных групп применяется агрегатная функция SUM, что дает единственное итоговое значение для каждой группы.

Рассмотрим два похожих примера. В примере 19 определяется минимальный объем поставки каждого поставщика. В примере 20 определяется объем минимальной поставки среди всех поставщиков.

Пример 19:

SELECT pnum, MIN(VOLUME) AS MIN
FROM PD
GROUP BY pnum

Пример 20:

SELECT MIN(VOLUME) AS MIN
FROM P

Результаты запросов представлены в следующей таблице:

pnum min max
1 100 100
2 150
3 1000

Следует обратить внимание, что в первом примере мы можем вывести номера поставщиков, соответствующие объемам поставок, а во втором примере – не можем.

Все имена столбцов, перечисленные после ключевого слова SELECT должны присутствовать и в предложении GROUP BY, за исключением случая, когда имя столбца является аргументом агрегатной функции.

Однако в предложении GROUP BY могут быть указаны имена столбцов, не перечисленные в списке вывода после ключевого слова SELECT.

Если предложение GROUP BY расположено после предложения WHERE, то группы создаются из строк, выбранных после применения WHERE.

Пример 21.
Для каждой из деталей с номерами 1 и 2 определить количество поставщиков, которые их поставляют, а также суммарный объем поставок деталей.

SELECT dnum, COUNT(pnum) AS COUNT, SUM(volume) AS SUM
FROM PD
WHERE dnum=1 OR dnum=2
GROUP BY dnum

Результат запроса:

dnum COUNT SUM
1 3 1250
2 2 450

Чтобы организовать вложенные группировки, после GROUP BY следует указать несколько группирующих столбцов через запятую. В этом случае реальный подсчет данных будет происходить по той группе, которая указана последней.

Предложение HAVING

Предложение HAVING определяет критерий, согласно которому, определенные группы, сформированные с помощью предложения GROUP BY, исключаются из результирующей таблицы.

Выполнение предложения HAVING сходно с выполнением предложения WHERE. Но предложение WHERE исключает строки до того, как выполняется группировка, а предложение HAVING — после. Поэтому предложение HAVING может содержать агрегатные функции, а предложение WHERE — не может.

Пример 22.
Определить номера поставщиков, поставляющих в сумме более 500 деталей.

SELECT pnum, SUM(volume) AS SUM
FROM PD
GROUP BY pnum
HAVING SUM(volume) > 500
pnum SUM
1 600
3 1000

Пример 23.
Определить номера поставщиков, которые поставляют только одну деталь.

SELECT pnum, COUNT(dnum) AS COUNT
FROM PD
GROUP BY pnum
HAVING COUNT(dnum) = 1
pnum SUM
3 1

Предложение ORDER BY

При выполнении запроса СУБД возвращает строки в случайном порядке. Предложение ORDER BY позволяет упорядочить выходные данные запроса в соответствии со значениями одного или нескольких выбранных столбцов.

Можно задать возрастающий — ASC (от слова Ascend) или убывающий — DESC (от слова Descend) порядок сортировки. По умолчанию принят возрастающий порядок сортировки.

Пример 24.
Отсортировать таблицу PD в порядке возрастания номеров поставщиков, а строки с одинаковыми значениями pnum отсортировать в порядке убывания объема поставок.

SELECT pnum, volume, dnum
FROM PD
ORDER BY pnum ASC, volume DESC
pnum volume dnum
1 300 3
1 200 2
1 100 1
2 250 2
2 150 1
3 1000 1

Операцию TOP удобно применять после сортировки результирующего набора с помощью предложения ORDER BY.

Пример 25.
Определить номера первых двух деталей с наименьшей стоимостью.

SELECT TOP 2 dnum
FROM D
ORDER BY dprice ASC

Следует отметить, что если в таблице D будут две детали без указания цены, то именно их и отобразит предыдущий запрос. Поэтому при наличии NULL-значений их необходимо исключать с помощью предложения WHERE.

SELECT TOP 2 dnum
FROM D
WHERE dprice IS NOT NULL
ORDER BY dprice ASC

Заключение

В статье было рассмотрен оператор выборки SELECT. Знание оператора SELECT является ключевым при написании любых SQL-запросов. Он позволяет производить выборку данных из таблиц и преобразовывать результаты в соответствии с нужными выражениями и функциями.

Результатом выполнения оператора SELECT является таблица, которую можно вложить в другой оператор SELECT в качестве подзапроса.

Синтаксис оператора SELECT содержит несколько предложений, из которых обязательными являются только SELECT и FROM. Остальные предложения, такие как WHERE, GROUP BY, HAVING и ORDER BY, могут использоваться по желанию для уточнения выборки данных.

Источник

Одной из главных
функций SQL считается выполнение выборки.
Поэтому рассмотрим этот процесс подробно.

Выборка – это
обращение к БД с целью извлечь данные
в виде, удобном для пользователя. Для
выборки применяются запросы к БД. Иногда
в SQL выделяют даже раздел, который
называют языком запросов к данным DQL
(Data Query Language). Фактически этот раздел
языка ANSI SQL представлен только одной
командой – SELECT. Но эта команда достаточно
обширна. Она является ядром языка SQL. и
используется для реализации операций
проекции, ограничения, расширения.

Для пользователя
РБД оператор SELECT является, пожалуй,
одним из наиболее главных и полезных
операторов языка SQL. Этот оператор
позволяет производить:

  • выбор
    данных (отбор записей и полей);

  • вычисления
    и сравнения;

  • упорядочение
    записей при выводе содержимого таблиц;

  • группирование
    данных и применение к этим группам
    специальных групповых операций.

Источником данных
(ИД) для запроса могут быть РТ или ранее
созданные запросы.

После выполнения
запроса на выборку создается набор
записей в виде временной рабочей таблицы,
содержащей данные, которые отбираются
из ИД согласно заданным условиям.

В
большинстве случаев с набором записей
можно работать точно так же, как с
таблицей: можно просматривать, выбирать
и даже обновлять информацию. Однако в
отличие от реальной таблицы, этот набор
записей физически не существует в БД.
Запрос с точки зрения пользователя
можно рассматривать как шаблон, который
создает набор записей из ИД только во
время своего выполнения. При отсутствии
данных результат представляет пустой
набор записей.

Синтаксис
инструкции SELECT определяется конструкциями,
используемыми при реализации функций
выборки. Инструкция в общем виде
использует пять частей, которые делятся
на две группы:

основная часть

SELECT [предикат]
<список полей>
выбрать

FROM <список
ИД>

из

дополнительные
части

[WHERE <спецификация
выбора записей>]
где

[[GROUP BY <спецификация
группировки>]
группируя по

[HAVING <спецификация
выбора групп>]]
имея

[ORDER BY <спецификация
сортировки>]
упорядочить по.

Рассмотрим синтаксис
инструкции по частям. При этом учтем
следующее:

  • инструкция языка
    SQL – это предложение (команда, оператор);

  • отдельные составные
    части инструкции (список полей,
    спецификация) – это опции предложения;

  • любая спецификация
    – это фраза, отвечающая требованиям
    синтаксиса предложения.

Из синтаксиса
видно, что

  • основная
    часть команды SELECT …FROM обязательна;

  • опция
    предикат необязательна

  • дополнительные
    части WHERE,
    GROUP
    BY,
    ORDER
    BY
    н    еобязательны,
    они следуют за FROM;

  • опция
    HAVING не может применяться без GROUP BY.

Рассмотрим опцию

SELECT [предикат]
<список полей>.

Эта опция в основной
части предложения позволяет выбрать
данные из указанных столбцов и выполнить
перед выводом их преобразование в
соответствии с указанными выражениями
или функциями.

Предикат предназначен
для ограничения числа возвращаемых
записей.

Предикат::=

[
[ALL]
все
(обычно по умолчанию)

|
DISTINCT позволяет отобрать различные
записи, исключает

записи,
содержащие повторяющиеся данные в
отдель-

ных
полях; в
результат включаются только уникаль-

ные
значения каждого из полей, указанных в
списке

| TOP
N ]
отображение N первых записей.

Список
полей предназначен
для определения
тех полей, которіе отражаются в результате.

<Список полей>
::=

{ эл_SELECT
элемент списка

[, эл2_SELECT] …}

Отметим, что для
разделения элементов списка используются
запятые.

Рассмотрим
синтаксис конструкции для элемента
списка

эл_SELECT::=

[ИД.]

*|
отбор всех полей

| значение |
SQL_функция | системная_переменная

В свою очередь

Значение ::=

[ИД.] поле
имя
поля

[AS псевдоним]
заголовок
поля

[,[ИД.] полеК
имя
К-ого поля

[AS псевдонимК] …
] заголовок
К-ого поля

|
(выражение)

|
переменная

|
константа

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

Выражение::=
({[[+] |–] {значение | функция_СУБД}[+|–|*|**]}
… )

Функция_СУБД –
это любая существующая функция. Для
преобразования или вычисления значений
могут применяться общеизвестные
функции
или выражения,
содержащие такие функции.

В качестве
функции_СУБД могут также применяться
специальные групповые (агрегирующие,
статистические)
SQL-функции,
которые
определяют одно значение по множеству
значений поля-аргумента.

SQL_функция ::=

{SUM
сумма

|
AVG
среднее
значение

|
MIN минимальное
значение

|
MAX максимальное
значение

|
COUNT} количество

(
[[ALL]

|
DISTINCT]

[ИД.]
поле ) аргумент

| ([ALL] выражение).

Из SQL-функций можно
составлять любые выражения, но их
вложенность не допускается. Если
вычисляются SQL-функции или выражения,
содержащие такие функции, в список полей
могут только те поля, которые являются
аргументами SQL-функций. Наличие других
полей в списке не допустимо.

Ключевое
слово DISTINCT используется для исключения
полей-дубликатов перед применением
функций. Для функций МАХ и MIN это слово
излишне.

Для подсчета всех
без исключения записей в таблице, включая
дубликаты, используется специальная
функция

SQL_функция ::=
COUNT(*).

С
этой функцией слово
DISTINCT не допускается.

Отметим
специфику обработки неопределенных
(пустых) значений (Null-значения).
Если значение аргумента – пустое
множество, то

  • при
    наличии слова DISTINCT эти записи не
    учитываются;

  • функция
    COUNT возвратит значение нуль;

  • функция
    COUNT (*) обработает все записи так же, как
    обычные значения;

  • другие
    функции обычно возвращают Null-значение.

Опция
FROM <список
ИД>

определяет перечень тех ИД,
из которых
берутся поля для включения в результат
запроса.

Рассмотрим
примеры применения команды SELECT c опциями
SELECT и FROM.
В
качестве списка
ИД
будем
рассматривать
только один ИД,
а именно тС.
Таким образом,

Список
ИД::= тС.

В
процессе изучения команды выборки
студент
должен
самостоятельно определить результаты
выполнения команд, т.е. составить
заголовок таблицы-результата и ее тело.
Рекомендуется также указать, какую
команду РА реализует соответствующая
команда.

Итак,
наша
первая
команда выборки на
SQL

10) SELECT *

FROM
тС → выборка
всех сотрудников, выводятся
все поля и все записи из тС, порядок
вывода полей соответствует структуре
тС, результат приведен в табл.
3.2.

Определение
“выборка сотрудников” конечно
означает
не выборку самих сотрудников, а выборку
информации о них.

Так как в качестве
ИД взята тС, опция FROM
тС будет присутствовать во всех командах
данного подраздела

Приведем примеры
реализации операции проекции.

11)
SELECT Код_с, Фам, Имя, От FROM тС

12)
SELECT Фам
FROM тС

13)
SELECT DISTINCT Фам
FROM тС

Приведем примеры
реализации операции расширения путем
формирования
вычисляемых полей.

Рассчитаем
возраст сотрудников по формуле

год(дата_текущая)
– год(д_рожд).

14)
SELECT Код_с, Фам,

(год(дата_текущая)
– год(д_рожд)) “ вычисление возраста

FROM
тС

Третье
поле фактически не имеет имени. Задание
имени результирующего поля не обязательно,
но рекомендуется.

В
данном предложении год( ) – это встроенная
функция конкректной СУБД. Обычно эта
функция реализуется как year( ), а текущая
дата – как date().

Таким
образом обычно
для реальных
СУБД

15)
SELECT Код_с,
Фам,

year(date())
– year(д_рожд)

FROM
тС

или
с заданием заголовка

16)
SELECT Код_с, Фам,

’возраст=’,
year(date()) – year(д_рожд)

FROM
тС

Наиболее
удобный результат дает команда

17)
SELECT Код_с, Фам,

year(date())
– year(д_рожд) Аs Возраст

FROM
тС

18)
SELECT Фам,
Сount(Код_с)
FROM тС

Данная
команда ошибочна, так как список полей
наряду с SQL-функцией
содержит поле Фам, которое не является
аргументом SQL-функции.
Правильный синтаксис имеет команда

19)
SELECT Count(Код_с)
FROM тС.

10

20)
SELECT Count(Код_с)
AS КвоС,

10

Max([Д_рожд])
AS MaxДр,

Count([Д_ув])
AS КвоУвол,
3

Min([Д_ув])
AS MinДув,

FROM
тС.

Рассмотрим
синтаксис других опций.

Сначала
рассмотрим опцию, которая используется
для упорядочения записей

ORDER BY <спецификация
сортировки>.

Спецификация
сортировки задается фразой, определяющей
список полей для упорядочения. Фраза
имеет следующий синтаксис:

<спецификация
сортировки>::=

{[ИД.]
поле | ном_элем_SELECT}

[[ASC]
| DESC]

[,{[ИД.]
поле2 | ном_элем_SELECT2}

[[ASC]
| DESC]] …

Рассмотрим примеры
применения
опции
ORDER.

21)
SELECT Код_с, Фам, Имя, От FROM тС

ORDER
BY Код_с

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

22)
SELECT Код_с, Фам, Имя, От FROM тС

ORDER
BY Фам, Имя, От.

Для
реализации операции ограничения
за
счет задания условий
отбора
(выбора)
записей
используется
опция

WHERE <спецификация
выбора записей>.

Спецификация
выбора записей задается фразой, которая
включает набор условий для отбора
записей

<спецификация
выбора записей>::=

[NOT]
WHERE_усл1 [[AND|OR][NOT] WHERE_усл2]…

Как видно из
синтаксиса, критерий
отбора строк может формироваться из
одного условия или из нескольких условий,
соединенных логическими операторами
AND, OR, [NOT].

Для случая двух
условий назначение логических операторов
следующее:

AND
– должны удовлетворяться оба условия
;

OR
– должно удовлетворяться одно из
условий;

AND NOT – должно
удовлетворяться первое условие (усл1)
и не должно второе условие (усл2);

OR NOT – или должно
удовлетворяться усл1 или не должно
удовлетворяться усл2.

При отборе существует
приоритет AND над OR: сначала выполняются
все операции AND и только после этого
выполняются операции OR.

Для условия отбора
можно записать следубщий синтаксис

WHERE_усл
::=

знач
{ = | <> | < | <= | | = }

{
знач | (подзапрос) }

знач_1
[NOT] BETWEEN знач_2
AND знач_3
между

знач
[NOT] IN { ( конст [,конст]… ) | (подзапрос) }
принадлежит

знач
IS [NOT] NULL
не определено

[ИД.]
поле [NOT] LIKE ‘строка_символов’
похоже на

[ESCAPE
‘символ’] не включает

EXISTS
(подзапрос)
существует

При сравнении
обычно действуют следующие правила
обработки условий:

  • числа сравниваются
    алгебраически; отрицательные числа
    считаются меньшими, чем положительные,
    независимо от их абсолютной величины;

  • строки символов
    сравниваются в соответствии с их
    представлением в коде, используемом в
    конкретной СУБД, например, в коде ASCII;

  • если сравниваются
    две строки символов, имеющих разные
    длины, то перед выполнением операции
    сравнения их длина уравнивается до
    большей за счет добавления пробелов
    справа в короткой строке.

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

Рассмотрим примеры
применения опции WHERE.

23)
SELECT Фам,
Д_рожд
FROM тС

WHERE
Д_рожд = ’01-01-1980′
символьная строка

24)
SELECT Фам,
Д_рожд
FROM тС

WHERE
Д_рожд
BETWEEN ’01-01-1980′ AND ’31-12-1980′

В
операторе BETWEEN знач_2 должно быть меньше
или равно знач_3.

25)
SELECT Фам FROM тС

WHERE
Д_рожд
= ’01-01-1980′ AND Город
= “Донецк”;

26)
SELECT * FROM тС

WHERE
Город= “Донецк” OR Город= “Макеевка”.

В последнем примере
для краткой записи последовательности
отдельных сравнений, соединенных
операторами OR, можно применить форму
IN

27) SELECT
* FROM тС

WHERE
Город
IN (“Донецк”,
“Макеевка”).

При использовании
в условии формы LIKE ‘строка_символов’
интерпретация зависит от заданных
символов так:

  • символ _
    (подчеркивание) заменяет любой одиночный
    символ;

  • символ % (процент)
    заменяет любую последовательность из
    N символов, где N может быть нулем;

  • все другие символы
    означают сами себя.

Например

28)
SELECT * FROM тС

WHERE
Город
LIKE “М%”

Очень редко, но в
поля вносятся знаки “_” и “%”. В этом
случае для их поиска применяются
дополнительные escape-символы, которые
должны предшествовать знакам.

Рассмотрим
образец поиска вида

LIKE
‘_/_а’ ESCAPE ‘/’.

В
этом выражении символ ‘/’ объявлен
escape-символом. Первый символ “_” в
заданном шаблоне поиска будет
соответствовать, как и ранее, любому
символу в проверяемой строке. Второй
символ “_”, следующий после escape-символа,
будет интерпретироваться как обычное
подчеркивание. Аналогично, символ ‘а’
будет интерпретироваться как буква а.

Для проверки
содержимого поля на наличие в нем
Null-значения
предназначены специальные операторы
IS NUll
(является пустым) и IS NOT NULL (является не
пустым). Другие операторы сравнения
использовать нельзя.

29) SELECT * FROM тС

WHERE Д_ув
Like ‘ %’ результат
Null

30) SELECT
* FROM тС

WHERE
Д_ув
IS NULL

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

31)
SELECT Фам,
COUNT(Код_с)
FROM тС

WHERE
Город
= “Донецк”
ошибка

32)
SELECT COUNT(Код_с)
As Количество
FROM тС

WHERE
Город = “Донецк”

Рассмотрим опцию,
которая применяется для группировки
записей

GROUP BY <спецификация
группировки>.

Спецификация
группировки записей используется при
создании группировочных запросов и
задается фразой вида

<спецификация
группировки>::=

[ИД.]
поле имя
поля

[,[ИД.]
полеK] … имя
K-го поля

Группирование
записей инициирует перекомпоновку
записей по группам, каждая из которых
имеет одинаковое значение в полях,
включенных в спецификации групировки.

33)
SELECT Фам
FROM тС
GROUP BY Фам

В
результате
этой команды происходит
исключение записей-дубликатов.

34)
SELECT Город
FROM тС
GROUP BY Город.

К
группам данных можно применить
агрегирующие SQL-функции. Для этого их
нужно указать в списке полей вывода.
Применение SQL-функций приводит к замене
всех значений группы на единственное
значение, определенной SQL-функцией
(сумма, количество и т.п.). Группирование
записей позволяет реализовать реляционную
операцию подведения итогов.

35)
SELECT Фам, Count(Код_с) AS Кол FROM тС

GROUP
BY Фам

В
этой
команде
происходит
группировка
по полю Фам, которое включено в список
полей вывода.

36)
SELECT Город,
COUNT(Код_с)
FROM тС

GROUP
BY Город

Надо
учитывать, что опция GROUP BY не предполагает
упорядочение. Поэтому рекомендуется
одновременно с ней применять и опцию
ORDER BY.

37)
SELECT Город,
COUNT(Код_с)
FROM тС

GROUP
BY Город

ORDER
BY Город

Рассмотрим
опцию
отбора групп записей:

HAVING <спецификация
выбора групп>.

В результат попадают
только те группы, которые удовлетворяют
заданной
спецификации выбора групп.
Ее синтаксис
подобен синтаксису спецификации выбора
записей:

<спецификация
выбора групп>::=

[NOT]
HAVING_усл
[[AND|OR][NOT] HAVING_усл2]…

Синтаксис HAVING_усл
почти не отличается от синтаксиса
WHERE_усл. Только наряду со значениями в
этих условиях могут использоваться
SQL-функции.

HAVING_усл
::=

знач
{ = | < | < | <= | | = }

{
знач | (подзапрос) | SQL_функция }

{знач_1
| SQL_функция_1} [NOT] BETWEEN

{знач_2
| SQL_функция_2} AND {знач_3 | SQL_функция_3}

{знач
| SQL_функция} [NOT] IN { ( конст [,конст]… ) |
(подзапрос) }

{знач
| SQL_функция}
IS [NOT] NULL

[табл.]
поле [NOT] LIKE ‘строка_символов’ [ESCAPE
‘символ’]

EXISTS
(подзапрос)

Рассмотрим
пример с опцией HAVING.

38) SELECT
* FROM тС

GROUP
BY Город

HAVING COUNT (*) < 5

Мы рассмотрели
команды с одним ИД – тС.

В общем виде в
качестве источника запроса могут
использоваться несколько
ИД. Причем, в качестве ИД могут
использоваться не
только базовые таблицы РБД, хранящиеся
в физической памяти машины, но и результаты
выполнения ранее созданных запросов,
которые в
основном представляются в виде виртуальных
(временных,
рабочих)
таблиц. Некоторые СУБД дают возможность
создавать представления БД или курсоры,
которые фактически являются хранимыми
в РБД запросами с именованными полями.
С их помощью создаются виртуальные
таблицы, позволяющие пользователям
иметь свой взгляд на данные без увеличения
их объема в БД.

В
списке источников команды
SELECT
должны
быть перечислены
все ИД, из которых берутся поля для
формирования запроса:

Список ИД
::={таблица | запрос |
представление}
[псевдоним]

[,{таблица2
| запрос2 | представление2} [псевдоним2]]
…

где
псевдоним служит для временного (на
момент выполнения запроса) переименования
и (или) создания рабочей копии ИД.

При включении в
запрос полей нескольких ИД небходимо
указывать полные имена полей в виде
<имя
ИД>. <имя
поля>,
например, тС.Код_с.

При
включении в запрос нескольких ИД
можно
нестрого утверждать, что опция FROM
соответствует их произведению.
Следовательно,
можно сделать
вывод,
что команда SELECT
языка
SQL
реализует значительную часть операций
РА. При этом можно определить
следующее
соответствие
опций команды SELECT
и
операций РА:

  • SELECT
    ↔
    проекция
    и расширение;

  • FROM
    ↔ произведение;

  • WHERE
    ↔ ограничение.

Иными
словами, совместная
инструкция SELECT-FROM-WHERE представляет собой
проекцию выборки произведения.

В общем случае
выборка может
содержать объединение нескольких других
ранее созданных выборок. Стандарт SQL
дает следующую расширенную формулировку:

Оператор SELECT
::=

подзапрос

[UNION

объединить

[ALL]
подзапрос] …

В
качестве подзапросов могут участвовать
объекты, перечисленные в списке ИД.
Команда допускает общую опцию сортировки
ORDER
BY.

Соседние файлы в папке Обработка РБД-relalg

  • #
  • #
  • #
  • #
  • #
  • #
  • #
  • #
  • #
Источник

Вступление и DDL – Data Definition Language (язык описания данных)

Часть первая — habrahabr.ru/post/255361

DML – Data Manipulation Language (язык манипулирования данными)

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

Рассказывать про DML я буду по своей последовательности выработанной на личном опыте. По ходу, так же постараюсь рассказать про «скользкие» места, на которые стоит акцентировать внимание, эти «скользкие» места, схожи во многих диалектах языка SQL.

Т.к. учебник посвящается широкому кругу читателей (не только программистам), то и объяснение, порой будет соответствующее, т.е. долгое и нудное. Это мое видение материала, которое в основном получено на практике в результате профессиональной деятельности.

Основная цель данного учебника, шаг за шагом, выработать полное понимание сути языка SQL и научить правильно применять его конструкции. Профессионалам в этой области, может тоже будет интересно пролистать данный материал, может и они смогут вынести для себя что-то новое, а может просто, будет полезно почитать в целях освежить память. Надеюсь, что всем будет интересно.

Т.к. DML в диалекте БД MS SQL очень сильно связан с синтаксисом конструкции SELECT, то я начну рассказывать о DML именно с нее. На мой взгляд конструкция SELECT является самой главной конструкцией языка DML, т.к. за счет нее или ее частей осуществляется выборка необходимых данных из БД.

Язык DML содержит следующие конструкции:

  • SELECT – выборка данных
  • INSERT – вставка новых данных
  • UPDATE – обновление данных
  • DELETE – удаление данных
  • MERGE – слияние данных

В данной части, мы рассмотрим, только базовый синтаксис команды SELECT, который выглядит следующим образом:

SELECT [DISTINCT] список_столбцов или *
FROM источник
WHERE фильтр
ORDER BY выражение_сортировки

Тема оператора SELECT очень обширная, поэтому в данной части я и остановлюсь только на его базовых конструкциях. Я считаю, что, не зная хорошо базы, нельзя приступать к изучению более сложных конструкций, т.к. дальше все будет крутиться вокруг этой базовой конструкции (подзапросы, объединения и т.д.).

Также в рамках этой части, я еще расскажу о предложении TOP. Это предложение я намерено не указал в базовом синтаксисе, т.к. оно реализуется по-разному в разных диалектах языка SQL.

Если язык DDL больше статичен, т.е. при помощи него создаются жесткие структуры (таблицы, связи и т.п.), то язык DML носит динамический характер, здесь правильные результаты вы можете получить разными путями.

Обучение так же будет продолжаться в режиме Step by Step, т.е. при чтении нужно сразу же своими руками пытаться выполнить пример. После делаете анализ полученного результата и пытаетесь понять его интуитивно. Если что-то остается непонятным, например, значение какой-нибудь функции, то обращайтесь за помощью в интернет.

Примеры будут показываться на БД Test, которая была создана при помощи DDL+DML в первой части.

Для тех, кто не создавал БД в первой части (т.к. не всех может интересовать язык DDL), может воспользоваться следующим скриптом:

Скрипт создания БД Test

-- создание БД
CREATE DATABASE Test
GO

-- сделать БД Test текущей
USE Test
GO

-- создаем таблицы справочники
CREATE TABLE Positions(
  ID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Positions PRIMARY KEY,
  Name nvarchar(30) NOT NULL
)

CREATE TABLE Departments(
  ID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Departments PRIMARY KEY,
  Name nvarchar(30) NOT NULL
)
GO

-- заполняем таблицы справочники данными
SET IDENTITY_INSERT Positions ON
INSERT Positions(ID,Name)VALUES
(1,N'Бухгалтер'),
(2,N'Директор'),
(3,N'Программист'),
(4,N'Старший программист')
SET IDENTITY_INSERT Positions OFF
GO

SET IDENTITY_INSERT Departments ON
INSERT Departments(ID,Name)VALUES
(1,N'Администрация'),
(2,N'Бухгалтерия'),
(3,N'ИТ')
SET IDENTITY_INSERT Departments OFF
GO

-- создаем таблицу с сотрудниками
CREATE TABLE Employees(
  ID int NOT NULL,
  Name nvarchar(30),
  Birthday date,
  Email nvarchar(30),
  PositionID int,
  DepartmentID int,
  HireDate date NOT NULL CONSTRAINT DF_Employees_HireDate DEFAULT SYSDATETIME(),
  ManagerID int,
CONSTRAINT PK_Employees PRIMARY KEY (ID),
CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERENCES Departments(ID),
CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID),
CONSTRAINT FK_Employees_ManagerID FOREIGN KEY (ManagerID) REFERENCES Employees(ID),
CONSTRAINT UQ_Employees_Email UNIQUE(Email),
CONSTRAINT CK_Employees_ID CHECK(ID BETWEEN 1000 AND 1999),
INDEX IDX_Employees_Name(Name)
)
GO

-- заполняем ее данными
INSERT Employees (ID,Name,Birthday,Email,PositionID,DepartmentID,ManagerID)VALUES
(1000,N'Иванов И.И.','19550219','i.ivanov@test.tt',2,1,NULL),
(1001,N'Петров П.П.','19831203','p.petrov@test.tt',3,3,1003),
(1002,N'Сидоров С.С.','19760607','s.sidorov@test.tt',1,2,1000),
(1003,N'Андреев А.А.','19820417','a.andreev@test.tt',4,3,1000)

Все, теперь мы готовы приступить к изучению языка DML.

SELECT – оператор выборки данных

Первым делом, для активного редактора запроса, сделаем текущей БД Test, выбрав ее в выпадающем списке или же командой «USE Test».

Начнем с самой элементарной формы SELECT:

SELECT *
FROM Employees

В данном запросе мы просим вернуть все столбцы (на это указывает «*») из таблицы Employees – можно прочесть это как «ВЫБЕРИ все_поля ИЗ таблицы_сотрудники». В случае наличия кластерного индекса, возвращенные данные, скорее всего будут отсортированы по нему, в данном случае по колонке ID (но это не суть важно, т.к. в большинстве случаев сортировку мы будем указывать в явном виде сами при помощи ORDER BY …):

ID Name Birthday Email PositionID DepartmentID HireDate ManagerID
1000 Иванов И.И. 1955-02-19 i.ivanov@test.tt 2 1 2015-04-08 NULL
1001 Петров П.П. 1983-12-03 p.petrov@test.tt 3 3 2015-04-08 1003
1002 Сидоров С.С. 1976-06-07 s.sidorov@test.tt 1 2 2015-04-08 1000
1003 Андреев А.А. 1982-04-17 a.andreev@test.tt 4 3 2015-04-08 1000

Вообще стоит сказать, что в диалекте MS SQL самая простая форма запроса SELECT может не содержать блока FROM, в этом случае вы можете использовать ее, для получения каких-то значений:

SELECT
	5550/100*15,
	SYSDATETIME(), -- получение системной даты БД
	SIN(0)+COS(0)

(No column name) (No column name) (No column name)
825 2015-04-11 12:12:36.0406743 1

Обратите внимание, что выражение (5550/100*15) дало результат 825, хотя если мы посчитаем на калькуляторе получится значение (832.5). Результат 825 получился по той причине, что в нашем выражении все числа целые, поэтому и результат целое число, т.е. (5550/100) дает нам 55, а не (55.5).

Запомните следующее, что в MS SQL работает следующая логика:

  • Целое / Целое = Целое (т.е. в данном случае происходит целочисленное деление)
  • Вещественное / Целое = Вещественное
  • Целое / Вещественное = Вещественное

Т.е. результат преобразуется к большему типу, поэтому в 2-х последних случаях мы получаем вещественное число (рассуждайте как в математике – диапазон вещественных чисел больше диапазона целых, поэтому и результат преобразуется к нему):

SELECT
  123/10, -- 12
  123./10, -- 12.3
  123/10. -- 12.3

Здесь (123.) = (123.0), просто в данном случае 0 можно отбросить и оставить только точку.

При других арифметических операциях действует та же самая логика, просто в случае деления этот нюанс более актуален.

Поэтому обращайте внимание на тип данных числовых столбцов. В том случае если он целый, а результат вам нужно получить вещественный, то используйте преобразование, либо просто ставьте точку после числа указанного в виде константы (123.).

Для преобразования полей можно использовать функцию CAST или CONVERT. Для примера воспользуемся полем ID, оно у нас типа int:

SELECT
  ID,
  ID/100, -- здесь произойдет целочисленное деление
  CAST(ID AS float)/100, -- используем функцию CAST для преобразования в тип float
  CONVERT(float,ID)/100, -- используем функцию CONVERT для преобразования в тип float
  ID/100. -- используем преобразование за счет указания что знаменатель вещественное число
FROM Employees

ID (No column name) (No column name) (No column name) (No column name)
1000 10 10 10 10.000000
1001 10 10.01 10.01 10.010000
1002 10 10.02 10.02 10.020000
1003 10 10.03 10.03 10.030000

На заметку. В БД ORACLE синтаксис без блока FROM недопустим, там для этой цели используется системная таблица DUAL, которая содержит одну строку:

SELECT
	5550/100*15, -- а в ORACLE результат будет равен 832.5
	sysdate,
	sin(0)+cos(0)
FROM DUAL

Примечание. Имя таблицы во многих РБД может предваряться именем схемы:

SELECT *
FROM dbo.Employees -- dbo – имя схемы

Схема – это логическая единица БД, которая имеет свое наименование и позволяет сгруппировать внутри себя объекты БД такие как таблицы, представления и т.д.

Определение схемы в разных БД может отличатся, где-то схема непосредственно связанна с пользователем БД, т.е. в данном случае можно сказать, что схема и пользователь – это синонимы и все создаваемые в схеме объекты по сути являются объектами данного пользователя. В MS SQL схема – это независимая логическая единица, которая может быть создана сама по себе (см. CREATE SCHEMA).

По умолчанию в базе MS SQL создается одна схема с именем dbo (Database Owner) и все создаваемые объекты по умолчанию создаются именно в данной схеме. Соответственно, если мы в запросе указываем просто имя таблицы, то она будет искаться в схеме dbo текущей БД. Если мы хотим создать объект в конкретной схеме, мы должны будем так же предварить имя объекта именем схемы, например, «CREATE TABLE имя_схемы.имя_таблицы(…)».

В случае MS SQL имя схемы может еще предваряться именем БД, в которой находится данная схема:

SELECT *
FROM Test.dbo.Employees -- имя_базы.имя_схемы.таблица

Такое уточнение бывает полезным, например, если:

  • в одном запросе мы обращаемся к объектам расположенных в разных схемах или базах данных
  • требуется сделать перенос данных из одной схемы или БД в другую
  • находясь в одной БД, требуется запросить данные из другой БД
  • и т.п.

Схема – очень удобное средство, которое полезно использовать при разработке архитектуры БД, а особенно крупных БД.

Так же не забываем, что в тексте запроса мы можем использовать как однострочные «— …», так и многострочные «/* … */» комментарии. Если запрос большой и сложный, то комментарии могут очень помочь, вам или кому-то другому, через некоторое время, вспомнить или разобраться в его структуре.

Если столбцов в таблице очень много, а особенно, если в таблице еще очень много строк, плюс к тому если мы делаем запросы к БД по сети, то предпочтительней будет выборка с непосредственным перечислением необходимых вам полей через запятую:

SELECT ID,Name
FROM Employees

Т.е. здесь мы говорим, что нам из таблицы нужно вернуть только поля ID и Name. Результат будет следующим (кстати оптимизатор здесь решил воспользоваться индексом, созданным по полю Name):

ID Name
1003 Андреев А.А.
1000 Иванов И.И.
1001 Петров П.П.
1002 Сидоров С.С.

На заметку. Порой бывает полезным посмотреть на то как осуществляется выборка данных, например, чтобы выяснить какие индексы используются. Это можно сделать если нажать кнопку «Display Estimated Execution Plan – Показать расчетный план» или установить «Include Actual Execution Plan – Включить в результат актуальный план выполнения запроса» (в данном случае мы сможем увидеть уже реальный план, соответственно, только после выполнения запроса):

Анализ плана выполнения очень полезен при оптимизации запроса, он позволяет выяснить каких индексов не хватает или же какие индексы вообще не используются и их можно удалить.

Если вы только начали осваивать DML, то сейчас для вас это не так важно, просто возьмите на заметку и можете спокойно забыть об этом (может это вам никогда и не пригодится) – наша первоначальная цель изучить основы языка DML и научится правильно применять их, а оптимизация это уже отдельное искусство. Порой важнее, чтобы на руках просто был правильно написанный запрос, который возвращает правильные результат с предметной точки зрения, а его оптимизацией уже занимаются отдельные люди. Для начала вам нужно научиться просто правильно писать запросы, используя любые средства для достижения цели. Главная цель которую вы сейчас должны достичь – чтобы ваш запрос возвращал правильные результаты.

Задание псевдонимов для таблиц

При перечислении колонок их можно предварять именем таблицы, находящейся в блоке FROM:

SELECT Employees.ID,Employees.Name
FROM Employees

Но такой синтаксис обычно использовать неудобно, т.к. имя таблицы может быть длинным. Для этих целей обычно задаются и применяются более короткие имена – псевдонимы (alias):

SELECT emp.ID,emp.Name
FROM Employees AS emp

или

SELECT emp.ID,emp.Name
FROM Employees emp -- ключевое слово AS можно отпустить (я предпочитаю такой вариант)

Здесь emp – псевдоним для таблицы Employees, который можно будет использоваться в контексте данного оператора SELECT. Т.е. можно сказать, что в контексте этого оператора SELECT мы задаем таблице новое имя.

Конечно, в данном случае результаты запросов будут точно такими же как и для «SELECT ID,Name FROM Employees». Для чего это нужно будет понятно дальше (даже не в этой части), пока просто запоминаем, что имя колонки можно предварять (уточнять) либо непосредственно именем таблицы, либо при помощи псевдонима. Здесь можно использовать одно из двух, т.е. если вы задали псевдоним, то и пользоваться нужно будет им, а использовать имя таблицы уже нельзя.

На заметку. В ORACLE допустим только вариант задания псевдонима таблицы без ключевого слова AS.

DISTINCT – отброс строк дубликатов

Ключевое слово DISTINCT используется для того чтобы отбросить из результата запроса строки дубликаты. Грубо говоря представьте, что сначала выполняется запрос без опции DISTINCT, а затем из результата выбрасываются все дубликаты. Продемонстрируем это для большей наглядности на примере:

-- создадим для демонстрации временную таблицу
CREATE TABLE #Trash(
  ID int NOT NULL PRIMARY KEY,
  Col1 varchar(10),
  Col2 varchar(10),
  Col3 varchar(10)
)

-- наполним данную таблицу всяким мусором
INSERT #Trash(ID,Col1,Col2,Col3)VALUES
(1,'A','A','A'), (2,'A','B','C'), (3,'C','A','B'), (4,'A','A','B'),
(5,'B','B','B'), (6,'A','A','B'), (7,'A','A','A'), (8,'C','A','B'),
(9,'C','A','B'), (10,'A','A','B'), (11,'A',NULL,'B'), (12,'A',NULL,'B')

-- посмотрим что возвращает запрос без опции DISTINCT
SELECT Col1,Col2,Col3
FROM #Trash

-- посмотрим что возвращает запрос с опцией DISTINCT
SELECT DISTINCT Col1,Col2,Col3
FROM #Trash

-- удалим временную таблицу
DROP TABLE #Trash

Наглядно это будет выглядеть следующим образом (все дубликаты помечены одним цветом):

Теперь давайте рассмотрим где это можно применить, на более практичном примере – вернем из таблицы Employees только уникальные идентификаторы отделов (т.е. узнаем ID отделов в которых числятся сотрудники):

SELECT DISTINCT DepartmentID
FROM Employees

DepartmentID
1
2
3

Здесь мы получили три строки, т.к. 2 сотрудника у нас числятся в одном отделе (ИТ).

Теперь узнаем в каких отделах, какие должности фигурируют:

SELECT DISTINCT DepartmentID,PositionID
FROM Employees

DepartmentID PositionID
1 2
2 1
3 3
3 4

Здесь мы получили 4 строчки, т.к. повторяющихся комбинаций (DepartmentID, PositionID) в нашей таблице нет.

Ненадолго вернемся к DDL

Так как данных для демонстрационных примеров начинает не хватать, а рассказать хочется более обширно и понятно, то давайте чуть расширим нашу таблицу Employess. К тому же немного вспомним DDL, как говорится «повторение – мать учения», и плюс снова немного забежим вперед и применим оператор UPDATE:

-- создаем новые колонки
ALTER TABLE Employees ADD
  LastName nvarchar(30), -- фамилия
  FirstName nvarchar(30), -- имя
  MiddleName nvarchar(30), -- отчество
  Salary float, -- и конечно же ЗП в каких-то УЕ
  BonusPercent float -- процент для вычисления бонуса от оклада
GO

-- наполняем их данными (некоторые данные намерено пропущены)
UPDATE Employees
SET
  LastName=N'Иванов',FirstName=N'Иван',MiddleName=N'Иванович',
  Salary=5000,BonusPercent= 50
WHERE ID=1000 -- Иванов И.И.

UPDATE Employees
SET
  LastName=N'Петров',FirstName=N'Петр',MiddleName=N'Петрович',
  Salary=1500,BonusPercent= 15
WHERE ID=1001 -- Петров П.П.

UPDATE Employees
SET
  LastName=N'Сидоров',FirstName=N'Сидор',MiddleName=NULL,
  Salary=2500,BonusPercent=NULL
WHERE ID=1002 -- Сидоров С.С.

UPDATE Employees
SET
  LastName=N'Андреев',FirstName=N'Андрей',MiddleName=NULL,
  Salary=2000,BonusPercent= 30
WHERE ID=1003 -- Андреев А.А.

Убедимся, что данные обновились успешно:

SELECT *
FROM Employees

ID Name … LastName FirstName MiddleName Salary BonusPercent
1000 Иванов И.И. Иванов Иван Иванович 5000 50
1001 Петров П.П. Петров Петр Петрович 1500 15
1002 Сидоров С.С. Сидоров Сидор NULL 2500 NULL
1003 Андреев А.А. Андреев Андрей NULL 2000 30

Задание псевдонимов для столбцов запроса

Думаю, здесь будет проще показать, чем написать:

SELECT
  -- даем имя вычисляемому столбцу
  LastName+' '+FirstName+' '+MiddleName AS ФИО,
  -- использование двойных кавычек, т.к. используется пробел
  HireDate AS "Дата приема",
  -- использование квадратных скобок, т.к. используется пробел
  Birthday AS [Дата рождения],
  -- слово AS не обязательно
  Salary ZP
FROM Employees

ФИО Дата приема Дата рождения ZP
Иванов Иван Иванович 2015-04-08 1955-02-19 5000
Петров Петр Петрович 2015-04-08 1983-12-03 1500
NULL 2015-04-08 1976-06-07 2500
NULL 2015-04-08 1982-04-17 2000

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

Обратите внимание, т.к. у последних 2-х сотрудников не указано отчество (NULL значение), то результат выражения «LastName+’ ‘+FirstName+’ ‘+MiddleName» так же вернул нам NULL.

Для соединения (сложения, конкатенации) строк в MS SQL используется символ «+».

Запомним, что все выражения в которых участвует NULL (например, деление на NULL, сложение с NULL) будут возвращать NULL.

На заметку.
В случае ORACLE для объединения строк используется оператор «||» и конкатенация будет выглядеть как «LastName||’ ‘||FirstName||’ ‘||MiddleName». Для ORACLE стоит отметить, что у него для строковых типов есть исключение, для них NULL и пустая строка » это одно и тоже, поэтому в ORACLE такое выражение вернет для последних 2-х сотрудников «Сидоров Сидор » и «Андреев Андрей ». На момент версии ORACLE 12c, насколько я знаю, опции которая изменяет такое поведение нет (если не прав, прошу поправить меня). Здесь мне сложно судить хорошо это или плохо, т.к. в одних случаях удобнее поведение NULL-строки как в MS SQL, а в других как в ORACLE.

В ORACLE тоже допустимы все перечисленные выше псевдонимы столбцов, кроме […].

Для того чтобы не городить конструкцию с использованием функции ISNULL, в MS SQL мы можем применить функцию CONCAT. Рассмотрим и сравним 3 варианта:

SELECT
  LastName+' '+FirstName+' '+MiddleName FullName1,
  -- 2 варианта для замены NULL пустыми строками '' (получаем поведение как и в ORACLE)
  ISNULL(LastName,'')+' '+ISNULL(FirstName,'')+' '+ISNULL(MiddleName,'') FullName2,
  CONCAT(LastName,' ',FirstName,' ',MiddleName) FullName3
FROM Employees

FullName1 FullName2 FullName3
Иванов Иван Иванович Иванов Иван Иванович Иванов Иван Иванович
Петров Петр Петрович Петров Петр Петрович Петров Петр Петрович
NULL Сидоров Сидор Сидоров Сидор
NULL Андреев Андрей Андреев Андрей

В MS SQL псевдонимы еще можно задавать при помощи знака равенства:

SELECT
  'Дата приема'=HireDate, -- помимо "…" и […] можно использовать '…'
  [Дата рождения]=Birthday,
  ZP=Salary
FROM Employees

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

Напоследок скажу, что для псевдонимов имена лучше задавать, используя только символы латиницы и цифры, избегая применения ‘…’, «…» и […], то есть использовать те же правила, что мы использовали при наименовании таблиц. Дальше, в примерах я буду использовать только такие наименования и никаких ‘…’, «…» и […].

Основные арифметические операторы SQL

Оператор Действие
+ Сложение (x+y) или унарный плюс (+x)
— Вычитание (x-y) или унарный минус (-x)
* Умножение (x*y)
/ Деление (x/y)
% Остаток от деления (x%y). Для примера 15%10 даст 5

Приоритет выполнения арифметических операторов такой же, как и в математике. Если необходимо, то порядок применения операторов можно изменить используя круглые скобки — (a+b)*(x/(y-z)).

И еще раз повторюсь, что любая операция с NULL дает NULL, например: 10+NULL, NULL*15/3, 100/NULL – все это даст в результате NULL. Т.е. говоря просто неопределенное значение не может дать определенный результат. Учитывайте это при составлении запроса и при необходимости делайте обработку NULL значений функциями ISNULL, COALESCE:

SELECT
  ID,Name,
  Salary/100*BonusPercent AS Result1, -- без обработки NULL значений
  Salary/100*ISNULL(BonusPercent,0) AS Result2, -- используем функцию ISNULL
  Salary/100*COALESCE(BonusPercent,0) AS Result3 -- используем функцию COALESCE
FROM Employees

ID Name Result1 Result2 Result3
1000 Иванов И.И. 2500 2500 2500
1001 Петров П.П. 225 225 225
1002 Сидоров С.С. NULL 0 0
1003 Андреев А.А. 600 600 600
1004 Николаев Н.Н. NULL 0 0
1005 Александров А.А. NULL 0 0

Немного расскажу о функции COALESCE:

COALESCE (expr1, expr2, ..., exprn) - Возвращает первое не NULL значение из списка значений.

Пример:

SELECT COALESCE(f1, f1*f2, f2*f3) val -- в данном случае вернется третье значение
FROM (SELECT null f1, 2 f2, 3 f3) q

В основном, я сосредоточусь на рассказе конструкций языка DML и по большей части не буду рассказывать о функциях, которые будут встречаться в примерах. Если вам непонятно, что делает та или иная функция поищите ее описание в интернет, можете даже поискать информацию сразу по группе функций, например, задав в поиске Google «MS SQL строковые функции», «MS SQL математические функции» или же «MS SQL функции обработки NULL». Информации по функциям очень много, и вы ее сможете без труда найти. Для примера, в библиотеке MSDN, можно узнать больше о функции COALESCE:

Вырезка из MSDN Сравнение COALESCE и CASE

Выражение COALESCE — синтаксический ярлык для выражения CASE. Это означает, что код COALESCE(expression1,…n) переписывается оптимизатором запросов как следующее выражение CASE:

CASE
   WHEN (expression1 IS NOT NULL) THEN expression1
   WHEN (expression2 IS NOT NULL) THEN expression2
   ...
   ELSE expressionN
END

Для примера рассмотрим, как можно воспользоваться остатком от деления (%). Данный оператор очень полезен, когда требуется разбить записи на группы. Например, вытащим всех сотрудников, у которых четные табельные номера (ID), т.е. те ID, которые делятся на 2:

SELECT ID,Name
FROM Employees
WHERE ID%2=0 -- остаток от деления на 2 равен 0

ID Name
1000 Иванов И.И.
1004 Николаев Н.Н.
1002 Сидоров С.С.

ORDER BY – сортировка результата запроса

Предложение ORDER BY используется для сортировки результата запроса.

SELECT
  LastName,
  FirstName,
  Salary
FROM Employees
ORDER BY LastName,FirstName -- упорядочить результат по 2-м столбцам – по Фамилии, и после по Имени

LastName FirstName Salary
Андреев Андрей 2000
Иванов Иван 5000
Петров Петр 1500
Сидоров Сидор 2500

После имя поля в предложении ORDER BY можно задать опцию DESC, которая служит для сортировки этого поля в порядке убывания:

SELECT LastName,FirstName,Salary
FROM Employees
ORDER BY -- упорядочить в порядке
  Salary DESC, -- 1. убывания Заработной Платы
  LastName, -- 2. по Фамилии
  FirstName -- 3. по Имени

LastName FirstName Salary
Иванов Иван 5000
Сидоров Сидор 2500
Андреев Андрей 2000
Петров Петр 1500

Для заметки. Для сортировки по возрастанию есть ключевое слово ASC, но так как сортировка по возрастанию применяется по умолчанию, то про эту опцию можно забыть (я не помню случая, чтобы я когда-то использовал эту опцию).

Стоит отметить, что в предложении ORDER BY можно использовать и поля, которые не перечислены в предложении SELECT (кроме случая, когда используется DISTINCT, об этом случае я расскажу ниже). Для примера забегу немного вперед используя опцию TOP и покажу, как например, можно отобрать 3-х сотрудников у которых самая высокая ЗП, с учетом что саму ЗП в целях конфиденциальности я показывать не должен:

SELECT TOP 3 -- вернуть только 3 первые записи из всего результата
  ID,LastName,FirstName
FROM Employees
ORDER BY Salary DESC -- сортируем результат по убыванию Заработной Платы

ID LastName FirstName
1000 Иванов Иван
1002 Сидоров Сидор

Конечно здесь есть случай, что у нескольких сотрудников может быть одинаковая ЗП и тут сложно сказать каких именно трех сотрудников вернет данный запрос, это уже нужно решать с постановщиком задачи. Допустим, после обсуждения с постановщиком данной задачи, вы согласовали и решили использовать следующий вариант – сделать дополнительную сортировку по полю даты рождения (т.е. молодым у нас дорога), а если и дата рождения у нескольких сотрудников может совпасть (ведь такое тоже не исключено), то можно сделать третью сортировку по убыванию значений ID (в последнюю очередь под выборку попадут те, у кого ID окажется максимальным – например, те кто был принят последним, допустим табельные номера у нас выдаются последовательно):

SELECT TOP 3 -- вернуть только 3 первые записи из всего результата
  ID,LastName,FirstName
FROM Employees
ORDER BY
  Salary DESC, -- 1. сортируем результат по убыванию Заработной Платы
  Birthday, -- 2. потом по Дате рождения
  ID DESC -- 3. и для полной однозначности результата добавляем сортировку по ID

Т.е. вы должны стараться чтобы результат запроса был предсказуемым, чтобы вы могли в случае разбора полетов объяснить почему в «черный список» попали именно эти люди, т.е. все было выбрано честно, по утверждённым правилам.

Сортировать можно так же используя разные выражения в предложении ORDER BY:

SELECT LastName,FirstName
FROM Employees
ORDER BY CONCAT(LastName,' ',FirstName) -- используем выражение

Так же в ORDER BY можно использовать псевдонимы заданные для колонок:

SELECT CONCAT(LastName,' ',FirstName) fi
FROM Employees
ORDER BY fi -- используем псевдоним

Стоит отметить что в случае использования предложения DISTINCT, в предложении ORDER BY могут использоваться только колонки, перечисленные в блоке SELECT. Т.е. после применения операции DISTINCT мы получаем новый набор данных, с новым набором колонок. По этой причине, следующий пример не отработает:

SELECT DISTINCT
  LastName,FirstName,Salary
FROM Employees
ORDER BY ID -- ID отсутствует в итоговом наборе, который мы получили при помощи DISTINCT

Т.е. предложение ORDER BY применяется уже к итоговому набору, перед выдачей результата пользователю.

Примечание 1. Так же в предложении ORDER BY можно использовать номера столбцов, перечисленных в SELECT:

SELECT LastName,FirstName,Salary
FROM Employees
ORDER BY -- упорядочить в порядке
  3 DESC, -- 1. убывания Заработной Платы
  1, -- 2. по Фамилии
  2 -- 3. по Имени

Для начинающих выглядит удобно и заманчиво, но лучше забыть и никогда не использовать такой вариант сортировки.

Если в данном случае (когда поля явно перечислены), такой вариант еще допустим, то для случая с использованием «*» такой вариант лучше никогда не применять. Почему – потому что, если кто-то, например, поменяет в таблице порядок столбцов, или удалит столбцы (и это нормальная ситуация), ваш запрос может так же работать, но уже неправильно, т.к. сортировка уже может идти по другим столбцам, и это коварно тем что данная ошибка может обнаружиться очень нескоро.

В случае, если бы столбы были явно перечислены, то в вышеуказанной ситуации, запрос либо бы продолжал работать, но также правильно (т.к. все явно определено), либо бы он просто выдал ошибку, что данного столбца не существует.

Так что можете смело забыть, о сортировке по номерам столбцов.

Примечание 2.
В MS SQL при сортировке по возрастанию NULL значения будут отображаться первыми.

SELECT BonusPercent FROM Employees ORDER BY BonusPercent

Соответственно при использовании DESC они будут в конце

SELECT BonusPercent FROM Employees ORDER BY BonusPercent DESC

Если необходимо поменять логику сортировки NULL значений, то используйте выражения, например:

SELECT BonusPercent FROM Employees ORDER BY ISNULL(BonusPercent,100)

В ORACLE для этой цели предусмотрены 2 опции NULLS FIRST и NULLS LAST (применяется по умолчанию). Например:

SELECT BonusPercent FROM Employees ORDER BY BonusPercent DESC NULLS LAST

Обращайте на это внимание при переходе на ту или иную БД.

TOP – возврат указанного числа записей

Вырезка из MSDN. TOP – ограничивает число строк, возвращаемых в результирующем наборе запроса до заданного числа или процентного значения. Если предложение TOP используется совместно с предложением ORDER BY, то результирующий набор ограничен первыми N строками отсортированного результата. В противном случае возвращаются первые N строк в неопределенном порядке.

Обычно данное выражение используется с предложением ORDER BY и мы уже смотрели примеры, когда нужно было вернуть N-первых строк из результирующего набора.

Без ORDER BY обычно данное предложение применяется, когда нужно просто посмотреть на неизвестную нам таблицу, в которой может быть очень много записей, в этом случае мы можем, для примера, попросить вернуть нам только первые 10 строк, но для наглядности мы скажем только 2:

SELECT TOP 2
  *
FROM Employees

Так же можно указать слово PERCENT, для того чтобы вернулось соответствуй процент строк из результирующего набора:

SELECT TOP 25 PERCENT
  *
FROM Employees

На моей практике чаше применяется именно выборка по количеству строк.

Так же с TOP можно использовать опцию WITH TIES, которая поможет вернуть все строки в случае неоднозначной сортировки, т.е. это предложение вернет все строки, которые равны по составу строкам, которые попадают в выборку TOP N, в итоге строк может быть выбрано больше чем N. Давайте для демонстрации добавим еще одного «Программиста» с окладом 1500:

INSERT Employees(ID,Name,Email,PositionID,DepartmentID,ManagerID,Salary)
VALUES(1004,N'Николаев Н.Н.','n.nikolayev@test.tt',3,3,1003,1500)

и введем еще одного сотрудника без указания должности и отдела с окладом 2000:

INSERT Employees(ID,Name,Email,PositionID,DepartmentID,ManagerID,Salary)
VALUES(1005,N'Александров А.А.','a.alexandrov@test.tt',NULL,NULL,1000,2000)

Теперь давайте выберем при помощи опции WITH TIES всех сотрудников, у которых оклад совпадает с окладами 3-х сотрудников, с самым маленьким окладом (надеюсь дальше будет понятно, к чему я клоню):

SELECT TOP 3 WITH TIES
  ID,Name,Salary
FROM Employees
ORDER BY Salary

Здесь хоть и указано TOP 3, но запрос вернул 4 записи, т.к. значение Salary которое вернуло TOP 3 (1500 и 2000) оказалось у 4-х сотрудников. Наглядно это работает примерно следующим образом:

На заметку.
В разных БД TOP реализуется разными способами, в MySQL для этого есть предложение LIMIT, в котором дополнительно можно задать начальное смещение.

В ORACLE 12c, тоже ввели свой аналог совмещающий функциональность TOP и LIMIT – ищите по словам «ORACLE OFFSET FETCH». До версии 12c для этой цели обычно использовался псевдостолбец ROWNUM.

А что же будет если применить одновременно предложения DISTINCT и TOP? На такие вопросы легко ответить, проводя эксперименты. В общем, не бойтесь и не ленитесь экспериментировать, т.к. большая часть познается именно на практике. Порядок слов в операторе SELECT следующий, первым идет DISTINCT, а после него идет TOP, т.е. если рассуждать логически и читать слева-направо, то первым применится отброс дубликатов, а потом уже по этому набору будет сделан TOP. Что-ж проверим и убедимся, что так и есть:

SELECT DISTINCT TOP 2
  Salary
FROM Employees
ORDER BY Salary

Salary
1500
2000

Т.е. в результате мы получили 2 самые маленькие зарплаты из всех. Конечно может быть случай что ЗП для каких-то сотрудников может быть не указанной (NULL), т.к. схема нам это позволяет. Поэтому в зависимости от задачи принимаем решение либо обработать NULL значения в предложении ORDER BY, либо просто отбросить все записи, у которых Salary равна NULL, а для этого переходим к изучению предложения WHERE.

WHERE – условие выборки строк

Данное предложение служит для фильтрации записей по заданному условию. Например, выберем всех сотрудников работающих в «ИТ» отделе (его ID=3):

SELECT ID,LastName,FirstName,Salary
FROM Employees
WHERE DepartmentID=3 -- ИТ
ORDER BY LastName,FirstName

ID LastName FirstName Salary
1004 NULL NULL 1500
1003 Андреев Андрей 2000
1001 Петров Петр 1500

Предложение WHERE пишется до команды ORDER BY.

Порядок применения команд к исходному набору Employees следующий:

  1. WHERE – если указано, то первым делом из всего набора Employees идет отбор только удовлетворяющих условию записей
  2. DISTINCT – если указано, то отбрасываются все дубликаты
  3. ORDER BY – если указано, то делается сортировка результата
  4. TOP – если указано, то из отсортированного результата возвращается только указанное число записей

Рассмотрим для наглядности пример:

SELECT DISTINCT TOP 1
  Salary
FROM Employees
WHERE DepartmentID=3
ORDER BY Salary

Наглядно это будет выглядеть следующим образом:

Стоит отметить, что проверка на NULL делается не знаком равенства, а при помощи операторов IS NULL и IS NOT NULL. Просто запомните, что на NULL при помощи оператора «=» (знак равенства) сравнивать нельзя, т.к. результат выражения будет так же равен NULL.

Например, выберем всех сотрудников, у которых не указан отдел (т.е. DepartmentID IS NULL):

SELECT ID,Name
FROM Employees
WHERE DepartmentID IS NULL

ID Name
1005 Александров А.А.

Теперь для примера посчитаем бонус для всех сотрудников у которых указано значение BonusPercent (т.е. BonusPercent IS NOT NULL):

SELECT ID,Name,Salary/100*BonusPercent AS Bonus
FROM Employees
WHERE BonusPercent IS NOT NULL

Да, кстати, если подумать, то значение BonusPercent может равняться нулю (0), а так же значение может быть внесено со знаком минус, ведь мы не накладывали на данное поле никаких ограничений.

Хорошо, рассказав о проблеме, нам пока сказали считать, что если (BonusPercent<=0 или BonusPercent IS NULL), то это означает что у сотрудника так же нет бонуса. Для начала, как нам сказали, так и сделаем, реализуем это при помощи логического оператора OR и NOT:

SELECT ID,Name,Salary/100*BonusPercent AS Bonus
FROM Employees
WHERE NOT(BonusPercent<=0 OR BonusPercent IS NULL)

Т.е. здесь мы начали изучать булевы операторы. Выражение в скобках «(BonusPercent<=0 OR BonusPercent IS NULL)» проверяет на то что у сотрудника нет бонуса, а NOT инвертирует это значение, т.е. говорит «верни всех сотрудников которые не сотрудники у которых нет бонуса».

Так же данное выражение можно переписать и сразу сказав сразу «верни всех сотрудников, у которых есть бонус» выразив это выражением (BonusPercent>0 и BonusPercent IS NOT NULL):

SELECT ID,Name,Salary/100*BonusPercent AS Bonus
FROM Employees
WHERE BonusPercent>0 AND BonusPercent IS NOT NULL

Также в блоке WHERE можно делать проверку разного рода выражений с применением арифметических операторов и функций. Например, аналогичную проверку можно сделать, использовав выражение с функцией ISNULL:

SELECT ID,Name,Salary/100*BonusPercent AS Bonus
FROM Employees
WHERE ISNULL(BonusPercent,0)>0

Булевы операторы и простые операторы сравнения

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

Булевых операторов в языке SQL всего 3 – AND, OR и NOT:

AND логическое И. Ставится между двумя условиями (условие1 AND условие2). Чтобы выражение вернуло True, нужно, чтобы истинными были оба условия
OR логическое ИЛИ. Ставится между двумя условиями (условие1 OR условие2). Чтобы выражение вернуло True, достаточно, чтобы истинным было только одно условие
NOT инвертирует условие/логическое_выражение. Накладывается на другое выражение (NOT логическое_выражение) и возвращает True, если логическое_выражение = False и возвращает False, если логическое_выражение = True

Для каждого булева оператора можно привести таблицы истинности где дополнительно показано какой будет результат, когда условия могут быть равны NULL:

Есть следующие простые операторы сравнения, которые используются для формирования условий:

Условие Значение
= Равно
< Меньше
> Больше
<= Меньше или равно
>= Больше или равно
<>
!=		 Не равно

Плюс имеются 2 оператора для проверки значения/выражения на NULL:

IS NULL Проверка на равенство NULL
IS NOT NULL Проверка на неравенство NULL

Приоритет: 1) Все операторы сравнения; 2) NOT; 3) AND; 4) OR.

При построении сложных логических выражений используются круглые скобки:

((условие1 AND условие2) OR NOT(условие3 AND условие4 AND условие5)) OR (…)

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

Здесь я постарался дать представление о булевой алгебре в достаточном для работы объеме. Как видите, чтобы писать условия посложнее без логики уже не обойтись, но ее здесь немного (AND, OR и NOT) и придумывали ее люди, так что все достаточно логично.

Идем к завершению второй части

Как видите даже про базовый синтаксис оператора SELECT можно говорить очень долго, но, чтобы остаться в рамках статьи, напоследок я покажу дополнительные логических операторы – BETWEEN, IN и LIKE.

BETWEEN – проверка на вхождение в диапазон

Этот оператор имеет следующий вид:

проверяемое_значение [NOT] BETWEEN начальное_ значение AND конечное_ значение

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

Разберем на примере:

SELECT ID,Name,Salary
FROM Employees
WHERE Salary BETWEEN 2000 AND 3000 -- у кого ЗП в диапазоне 2000-3000

ID Name Salary
1002 Сидоров С.С. 2500
1003 Андреев А.А. 2000
1005 Александров А.А. 2000

Собственно, BETWEEN это упрощенная запись вида:

SELECT ID,Name,Salary
FROM Employees
WHERE Salary>=2000 AND Salary<=3000 -- все у кого ЗП в диапозоне 2000-3000

Перед словом BETWEEN может использоваться слово NOT, которое будет осуществлять проверку значения на не вхождение в указанный диапазон:

SELECT ID,Name,Salary
FROM Employees
WHERE Salary NOT BETWEEN 2000 AND 3000 -- аналогично выражению NOT(Salary>=2000 AND Salary<=3000)

Соответственно, в случае использования BETWEEN, IN, LIKE вы можете так же объединять их с другими условиями при помощи AND и OR:

SELECT ID,Name,Salary
FROM Employees
WHERE Salary BETWEEN 2000 AND 3000 -- у кого ЗП в диапазоне 2000-3000
  AND DepartmentID=3 -- учитывать сотрудников только отдела 3

IN – проверка на вхождение в перечень значений

Этот оператор имеет следующий вид:

проверяемое_значение [NOT] IN (значение1, значение2, …)

Думаю, проще показать на примере:

SELECT ID,Name,Salary
FROM Employees
WHERE PositionID IN(3,4) -- у кого должность равна 3 или 4

ID Name Salary
1001 Петров П.П. 1500
1003 Андреев А.А. 2000
1004 Николаев Н.Н. 1500

Т.е. по сути это аналогично следующему выражению:

SELECT ID,Name,Salary
FROM Employees
WHERE PositionID=3 OR PositionID=4 -- у кого должность равна 3 или 4

В случае NOT это будет аналогично (получим всех кроме тех, кто из отдела 3 и 4):

SELECT ID,Name,Salary
FROM Employees
WHERE PositionID NOT IN(3,4) -- аналогично выражению NOT(PositionID=3 OR PositionID=4)

Так же запрос с NOT IN можно выразить и через AND:

SELECT ID,Name,Salary
FROM Employees
WHERE PositionID<>3 AND PositionID<>4 -- равносильно PositionID NOT IN(3,4)

Учтите, что искать NULL значения при помощи конструкции IN не получится, т.к. проверка NULL=NULL вернет так же NULL, а не True:

SELECT ID,Name,DepartmentID
FROM Employees
WHERE DepartmentID IN(1,2,NULL) -- NULL записи не войдут в результат

В этом случае разбивайте проверку на несколько условий:

SELECT ID,Name,DepartmentID
FROM Employees
WHERE DepartmentID IN(1,2) -- 1 или 2
   OR DepartmentID IS NULL -- или NULL

Или же можно написать что-то вроде:

SELECT ID,Name,DepartmentID
FROM Employees
WHERE ISNULL(DepartmentID,-1) IN(1,2,-1) -- если вы уверены, что в нет и не будет департамента с ID=-1

Думаю, первый вариант, в данном случае будет более правильным и надежным. Ну ладно, это всего лишь пример, для демонстрации того какие еще конструкции можно строить.

Так же стоит упомянуть еще более коварную ошибку, связанную с NULL, которую можно допустить при использовании конструкции NOT IN. Для примера, давайте попробуем выбрать всех сотрудников, кроме тех, у которых отдел равен 1 или у которых отдел вообще не указан, т.е. равен NULL. В качестве решения напрашивается вариант:

SELECT ID,Name,DepartmentID
FROM Employees
WHERE DepartmentID NOT IN(1,NULL)

Но выполнив запрос, мы не получим ни одной строки, хотя мы ожидали увидеть следующее:

ID Name DepartmentID
1001 Петров П.П. 3
1002 Сидоров С.С. 2
1003 Андреев А.А. 3
1004 Николаев Н.Н. 3

Опять же шутку здесь сыграло NULL указанное в списке значений.

Разберем почему в данном случае возникла логическая ошибка. Разложим запрос при помощи AND:

SELECT ID,Name,DepartmentID
FROM Employees
WHERE DepartmentID<>1
  AND DepartmentID<>NULL -- проблема из-за этой проверки на NULL - это условие всегда вернет NULL

Правое условие (DepartmentID<>NULL) нам всегда здесь даст неопределенность, т.е. NULL. Теперь вспомним таблицу истинности для оператора AND, где (TRUE AND NULL) дает NULL. Т.е. при выполнении левого условия (DepartmentID<>1) из-за неопределенного правого условия в результате мы получим неопределенное значение всего выражения (DepartmentID<>1 AND DepartmentID<>NULL), поэтому строка не войдет в результат.

Переписать условие правильно можно следующим образом:

SELECT ID,Name,DepartmentID
FROM Employees
WHERE DepartmentID NOT IN(1) -- или в данном случае просто DepartmentID<>1
  AND DepartmentID IS NOT NULL -- и отдельно проверяем на NOT NULL

IN еще можно использовать с подзапросами, но к такой форме мы вернемся, уже в последующих частях данного учебника.

LIKE – проверка строки по шаблону

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

Этот оператор имеет следующий вид:

проверяемая_строка [NOT] LIKE строка_шаблон [ESCAPE отменяющий_символ]

В «строке_шаблон» могут применятся следующие специальные символы:

  1. Знак подчеркивания «_» — говорит, что на его месте может стоять любой единичный символ
  2. Знак процента «%» — говорит, что на его месте может стоять сколько угодно символов, в том числе и ни одного

Рассмотрим примеры с символом «%» (на практике, кстати он чаще применяется):

SELECT ID,Name
FROM Employees
WHERE Name LIKE 'Пет%' -- у кого имя начинается с букв "Пет"

SELECT ID,LastName
FROM Employees
WHERE LastName LIKE '%ов' -- у кого фамилия оканчивается на "ов"

SELECT ID,LastName
FROM Employees
WHERE LastName LIKE '%ре%' -- у кого фамилия содержит сочетание "ре"

Рассмотрим примеры с символом «_»:

SELECT ID,LastName
FROM Employees
WHERE LastName LIKE '_етров' -- у кого фамилия состоит из любого первого символа и последующих букв "етров"

SELECT ID,LastName
FROM Employees
WHERE LastName LIKE '____ов' -- у кого фамилия состоит из четырех любых символов и последующих букв "ов"

При помощи ESCAPE можно задать отменяющий символ, который отменяет проверяющее действие специальных символов «_» и «%». Данное предложение используется, когда в строке нужно непосредственно проверить наличие знака процента или знака подчеркивания.

Для демонстрации ESCAPE давайте занесем в одну запись мусор:

UPDATE Employees
SET
  FirstName='Это_мусор, содержащий %'
WHERE ID=1005

И посмотрим, что вернут следующие запросы:

SELECT *
FROM Employees
WHERE FirstName LIKE '%!%%' ESCAPE '!' -- строка содержит знак "%"

SELECT *
FROM Employees
WHERE FirstName LIKE '%!_%' ESCAPE '!' -- строка содержит знак "_"

В случае, если требуется проверить строку на полное совпадение, то вместо LIKE лучше использовать просто знак «=»:

SELECT *
FROM Employees
WHERE FirstName='Петр'

На заметку.
В MS SQL в шаблоне оператора LIKE так же можно задать поиск по регулярным выражениям, почитайте о нем в интернете, в том случае, если вам станет недостаточно стандартных возможностей данного оператора.

В ORACLE для поиска по регулярным выражениям применяется функция REGEXP_LIKE.

Немного о строках

В случае проверки строки на наличие Unicode символов, нужно будет ставить перед кавычками символ N, т.е. N’…’. Но так как у нас в таблице все символьные поля в формате Unicode (тип nvarchar), то для этих полей можно всегда использовать такой формат. Пример:

SELECT ID,Name
FROM Employees
WHERE Name LIKE N'Пет%'

SELECT ID,LastName
FROM Employees
WHERE LastName=N'Петров'

Если делать правильно, при сравнении с полем типа varchar (ASCII) нужно стараться использовать проверки с использованием ‘…’, а при сравнении поля с типом nvarchar (Unicode) нужно стараться использовать проверки с использованием N’…’. Это делается для того, чтобы избежать в процессе выполнения запроса неявных преобразований типов. То же самое правило используем при вставке (INSERT) значений в поле или их обновлении (UPDATE).

При сравнении строк стоит учесть момент, что в зависимости от настройки БД (collation), сравнение строк может быть, как регистро-независимым (когда ‘Петров’=’ПЕТРОВ’), так и регистро-зависимым (когда ‘Петров'<>’ПЕТРОВ’).
В случае регистро-зависимой настройки, если требуется сделать поиск без учета регистра, то можно, например, сделать предварительное преобразование правого и левого выражения в один регистр – верхний или нижний:

SELECT ID,Name
FROM Employees
WHERE UPPER(Name) LIKE UPPER(N'Пет%') -- или LOWER(Name) LIKE LOWER(N'Пет%') 

SELECT ID,LastName
FROM Employees
WHERE UPPER(LastName)=UPPER(N'Петров') -- или LOWER(LastName)=LOWER(N'Петров')

Немного о датах

При проверке на дату, вы можете использовать, как и со строками одинарные кавычки ‘…’.

Вне зависимости от региональных настроек в MS SQL можно использовать следующий синтаксис дат ‘YYYYMMDD’ (год, месяц, день слитно без пробелов). Такой формат даты MS SQL поймет всегда:

SELECT ID,Name,Birthday
FROM Employees
WHERE Birthday BETWEEN '19800101' AND '19891231' -- сотрудники 80-х годов
ORDER BY Birthday

В некоторых случаях, дату удобнее задавать при помощи функции DATEFROMPARTS:

SELECT ID,Name,Birthday
FROM Employees
WHERE Birthday BETWEEN DATEFROMPARTS(1980,1,1) AND DATEFROMPARTS(1989,12,31)
ORDER BY Birthday

Так же есть аналогичная функция DATETIMEFROMPARTS, которая служит для задания Даты и Времени (для типа datetime).

Еще вы можете использовать функцию CONVERT, если требуется преобразовать строку в значение типа date или datetime:

SELECT
  CONVERT(date,'12.03.2015',104),
  CONVERT(datetime,'2014-11-30 17:20:15',120)

Значения 104 и 120, указывают какой формат даты используется в строке. Описание всех допустимых форматов вы можете найти в библиотеке MSDN задав в поиске «MS SQL CONVERT».

Функций для работы с датами в MS SQL очень много, ищите «ms sql функции для работы с датами».

Примечание. Во всех диалектах языка SQL свой набор функций по работе с датами и применяется свой подход по работе с ними.

Немного о числах и их преобразованиях

Информация этого раздела наверно больше будет полезна ИТ-специалистам. Если вы таковым не являетесь, а ваша цель просто научится писать запросы для получения из БД необходимой вам информации, то такие тонкости вам возможно и не понадобятся, но в любом случае можете бегло пройтись по тексту и взять что-то на заметку, т.к. если вы взялись за изучение SQL, то вы уже приобщаетесь к ИТ.

В отличие от функции преобразования CAST, в функции CONVERT можно задать третий параметр, который отвечает за стиль преобразования (формат). Для разных типов данных может использоваться свой набор стилей, которые могут повлиять на возвращаемый результат. Использование стилей мы уже затрагивали при рассмотрении преобразования строки функцией CONVERT в типы date и datetime.

Подробней про функции CAST, CONVERT и стили можно почитать в MSDN – «Функции CAST и CONVERT (Transact-SQL)»: msdn.microsoft.com/ru-ru/library/ms187928.aspx

Для упрощения примеров здесь будут использованы инструкции языка Transact-SQL – DECLARE и SET.

Конечно, в случае преобразования целого числа в вещественное (которое я привел вначале данного урока, в целях демонстрации разницы между целочисленным и вещественным делением), знание нюансов преобразования не так критично, т.к. там мы делали преобразование целого числа в вещественное (диапазон которого намного больше диапазона целых):

DECLARE @min_int int SET @min_int=-2147483648
DECLARE @max_int int SET @max_int=2147483647

SELECT
  -- (-2147483648)
  @min_int,CAST(@min_int AS float),CONVERT(float,@min_int),

  -- 2147483647
  @max_int,CAST(@max_int AS float),CONVERT(float,@max_int),

  -- numeric(16,6)
  @min_int/1., -- (-2147483648.000000)
  @max_int/1. -- 2147483647.000000

Возможно не стоило указывать способ неявного преобразования, получаемого делением на (1.), т.к. желательно стараться делать явные преобразования, для большего контроля типа получаемого результата. Хотя, в случае, если мы хотим получить результат типа numeric, с указанным количеством цифр после запятой, то мы можем в MS SQL применить трюк с умножением целого значения на (1., 1.0, 1.00 и т.д):

DECLARE @int int SET @int=123

SELECT
  @int*1., -- numeric(12, 0) - 0 знаков после запятой
  @int*1.0, -- numeric(13, 1) - 1 знак
  @int*1.00, -- numeric(14, 2) - 2 знака

  -- хотя порой лучше сделать явное преобразование
  CAST(@int AS numeric(20, 0)), -- 123
  CAST(@int AS numeric(20, 1)), -- 123.0
  CAST(@int AS numeric(20, 2))  -- 123.00

В некоторых случаях детали преобразования могут быть действительно важны, т.к. они влияют на правильность полученного результата, например, в случае, когда делается преобразование числового значения в строку (varchar). Рассмотрим примеры по преобразованию значений типа money и float в varchar:

-- поведение при преобразовании money в varchar
DECLARE @money money
SET @money = 1025.123456789 -- произойдет неявное преобразование в 1025.1235, т.к. тип money хранит только 4 цифры после запятой

SELECT
  @money, -- 1025.1235
  -- по умолчанию CAST и CONVERT ведут себя одинаково (т.е. грубо говоря применяется стиль 0)
  CAST(@money as varchar(20)), -- 1025.12
  CONVERT(varchar(20), @money), -- 1025.12
  CONVERT(varchar(20), @money, 0), -- 1025.12 (стиль 0 - без разделителя тысячных и 2 цифры после запятой (формат по умолчанию))

  CONVERT(varchar(20), @money, 1), -- 1,025.12 (стиль 1 - используется разделитель тысячных и 2 цифры после запятой)
  CONVERT(varchar(20), @money, 2) -- 1025.1235 (стиль 2 - без разделителя и 4 цифры после запятой)

-- поведение при преобразовании float в varchar
DECLARE @float1 float SET @float1 = 1025.123456789
DECLARE @float2 float SET @float2 = 1231025.123456789

SELECT
  @float1, -- 1025.123456789
  @float2, -- 1231025.12345679
  -- по умолчанию CAST и CONVERT ведут себя одинаково (т.е. грубо говоря применяется стиль 0)
  -- стиль 0 - Не более 6 разрядов. По необходимости используется экспоненциальное представление чисел
  -- при преобразовании в varchar здесь творятся действительно страшные вещи
  CAST(@float1 as varchar(20)), -- 1025.12
  CONVERT(varchar(20), @float1), -- 1025.12
  CONVERT(varchar(20), @float1, 0), -- 1025.12

  CAST(@float2 as varchar(20)), -- 1.23103e+006
  CONVERT(varchar(20), @float2), -- 1.23103e+006
  CONVERT(varchar(20), @float2, 0), -- 1.23103e+006
  
  -- стиль 1 - Всегда 8 разрядов. Всегда используется экспоненциальное представление чисел.
  -- этот стиль для float тоже не очень точен
  CONVERT(varchar(20), @float1, 1), -- 1.0251235e+003
  CONVERT(varchar(20), @float2, 1), -- 1.2310251e+006

  -- стиль 2 - Всегда 16 разрядов. Всегда используется экспоненциальное представление чисел.
  -- здесь с точностью уже получше
  CONVERT(varchar(30), @float1, 2), -- 1.025123456789000e+003 - OK
  CONVERT(varchar(30), @float2, 2) -- 1.231025123456789e+006 - OK

Как видно из примера, плавающие типы float, real в некоторых случаях действительно могут создать большую погрешность, особенно при перегонке в строку и обратно (такое может быть при разного рода интеграциях, когда данные, например, передаются в текстовых файлах из одной системы в другую).

Если нужно явно контролировать точность до определенного знака, более 4-х, то для хранения данных, порой лучше использовать тип decimal/numeric. Если хватает 4-х знаков, то можно использовать и тип money – он примерно соотвествует numeric(20,4).

-- decimal и numeric
DECLARE @money money SET @money = 1025.123456789 -- 1025.1235
DECLARE @float1 float SET @float1 = 1025.123456789
DECLARE @float2 float SET @float2 = 1231025.123456789

DECLARE @numeric numeric(28,9) SET @numeric = 1025.123456789

SELECT
  CAST(@numeric as varchar(20)), -- 1025.12345679
  CONVERT(varchar(20), @numeric), -- 1025.12345679

  CAST(@money as numeric(28,9)), -- 1025.123500000
  CAST(@float1 as numeric(28,9)), -- 1025.123456789
  CAST(@float2 as numeric(28,9)) -- 1231025.123456789

Примечание.
С версии MS SQL 2008, можно использовать вместо конструкции:

DECLARE @money money
SET @money = 1025.123456789

Более короткий синтаксис инициализации переменных:

DECLARE @money money = 1025.123456789

Заключение второй части

В этой части, я постарался вспомнить и отразить наиболее важные моменты, касающиеся базового синтаксиса. Базовая конструкция – это костяк, без которого нельзя приступать к изучению более сложных конструкций языка SQL.

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

Удачи в изучении и применении на практике данного языка.

Часть третья — habrahabr.ru/post/255825

Источник



О чем речь? Выборка данных – это, как следует из названия, отбор информации из базы данных по заданным критериям. За этот процесс отвечают определенные операторы, которые формируют тип запроса и необходимые критерии.



Как сделать? Данная задача является не самой простой, так как приходится разбираться в сложном синтаксисе. Однако уловив последовательность команд, дело остается за малым – получить и обработать необходимую информацию.

В статье рассказывается:

  1. Суть выборки данных
  2. Выборка данных через оператор SELECT
  3. Группировка данных при выборке
  4. Нюансы выборки данных из ORM систем

  5. Пройди тест и узнай, какая сфера тебе подходит:
    айти, дизайн или маркетинг.

    Бесплатно от Geekbrains

Суть выборки данных

Любая реляционная СУБД имеет такую функцию, как выборка данных (команда SELECT). Она является одной из самых востребованных, но при этом и сложнейших в плане синтаксиса. Однако, при всей сложности и объёмности предложений SQL, выборка данных из базы не представляет какой-то проблемы.

Суть выборки данных

Суть выборки данных

Чтобы успешно произвести выборку, необходимо чётко понимать, какая последовательность ключевых слов в запросе необходима и каким будет результат по каждому ключевому слову. Мы будем рассматривать примеры по мере усложнения. Начнём с самых простых случаев выборки данных из базы и пока не будем использовать какие-либо клаузулы или предикаты (уточняющие фразы) для определения условий, фильтрации данных в выборке и сортировке отфильтрованных значений.

Приступая к работе с выборками данных, всегда помните одно важное правило: команда SELECT в SQL-запросе всегда вернёт вам данные в формате таблицы. И неважно, насколько сложный у вас запрос. SQLite и любая другая РСУБД будет возвращать результат выборки данных в виде таблицы.

Кроме того, необходимо располагать ключевые слова в правильном порядке:

  • Начинаем с ключевого слова SELECT.
  • После него идут круглые скобки, где мы указываем колонки, из которых нам необходимо получить значения.
  • Затем следует ключевое слово FROM.
  • Пишем имя таблицы, к которой обращаемся за данными.
  • Прописываем остальные ключевые слова (тоже в строгой последовательности, но сейчас не будем останавливаться на этом подробно, дабы не запутаться).

Скачать
файл

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

Выборка данных через оператор SELECT

Элементами оператора SELECT в SQL являются блоки, определяющие параметры выражения.

Для MySQL обязательный блок — первый, сам SELECT.

Всего в SELECT есть три блока:

  • Собственно SELECT: те данные, которые мы хотим получить из базы. В каком-то смысле аналогичен переименованию и проекции в реляционной алгебре.
  • FROM: устанавливает диапазон данных в выборке (сообщает, откуда начинать выбирать). По аналогии с реляционной алгеброй это аргумент операции.
  • WHERE: обязательное условие выборки данных, которому они должны соответствовать. В реляционной алгебре подобное называется операцией выборки.

Блок SELECT

Наподобие проекции:

SELECT col1, col2, …

Помещает в выборку только данные из указанных столбцов. Чтобы выбрать все без исключения столбцы, применяем синтаксис SELECT *.

Наподобие переименования:

SELECT col1 as name1, col2 as name2, …

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

Это самые базовые варианты использования SELECT, но его возможности намного шире. Например, можно подставить значение или функцию (в том числе оператор). Если написать:

SELECT ‘Hello World!’ as Hello;

То получим следующую выборку:

При наличии в данных таблицы operands

запрос к базе будет иметь вид:

SELECT a, b, a+b as c FROM operands

Результат:

a b c
1 10 11
2 15 17
3 20 23

В SQL имеется масса встроенных функций, которые могут работать с временны́ми данными, преобразовывать типы, обрабатывать статистику и т. п.

pdf иконка

Топ-30 самых востребованных и высокооплачиваемых профессий 2023

Поможет разобраться в актуальной ситуации на рынке труда

doc иконка

Подборка 50+ ресурсов об IT-сфере

Только лучшие телеграм-каналы, каналы Youtube, подкасты, форумы и многое другое для того, чтобы узнавать новое про IT

pdf иконка

ТОП 50+ сервисов и приложений от Geekbrains

Безопасные и надежные программы для работы в наши дни

Уже скачали 21678 pdf иконка

Блок FROM

Этот блок используется для того, чтобы уточнить аргумент SELECT. Если брать самые простые случаи, то во FROM указывают имя таблицы (отношения).

Блок FROM

Блок FROM

Согласно принципам реляционной алгебры, можно указать в качестве аргумента FROM подзапрос — выборку данных из другого запроса. Для этого подзапросу присваивают псевдоним:

SELECT a+b FROM (SELECT 1 as a, 2 as b) as tbl1;

Тестирование программного обеспечения: разбираемся в деталях

Читайте также

Кроме того, посредством блока FROM можно вычислять декартовы произведения и делать конкатенацию. В этом нам поможет JOIN, бинарный оператор.

Предположим, у нас есть таблица bin:

По запросу в базу

SELECT * FROM bin b1 JOIN bin b2 JOIN bin b3;

получим декартово произведение bin×bin×bin:

a a a
0 0 0
1 0 0
0 1 0
1 1 0
0 0 1
1 0 1
0 1 1
1 1 1

JOIN и является оператором декартова произведения. Есть несколько вариантов JOIN: INNER JOIN, используемый по умолчанию, NATURAL, OUTER RIGHT JOIN, OUTER LEFT JOIN, OUTER FULL JOIN.

Блок WHERE

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

К примеру, по запросу:

SELECT * FROM bin WHERE a>0;

вы получите:

Помните, что любое переименование осуществляется только после выборки. Поэтому, например, выражение.

SELECT a as b FROM bin WHERE b>0;

неправильное: здесь блок WHERE видит только аргумент FROM, а переименование — ещё не видит.

Исходя из этого правила, блок WHERE можно использовать только совместно с блоком FROM, и выражение вроде:

SELECT 1 WHERE TRUE;

просто не будет работать.

Но, если очень нужно, можно задействовать dual («пустую» таблицу, из которой напрямую ничего нельзя выбрать — вернётся ошибка):

SELECT 1 FROM dual WHERE TRUE;

Этот вариант вполне рабочий.

Dual можно указывать, если по синтаксису SQL требуется именно таблица.

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

Группировка данных при выборке

Чтобы сгруппировать данные в SELECT-запросе при формировании выборки, применяют конструкцию group by, где перечисляются те же колонки таблицы, что и в SELECT. Рассмотрим пример выборки данных в таблицу bills по группам:

— все счета в таблице

create table bills(

id integer,

d date, — дата выставления счета

summ double precision ,— сумма счета

constraint pk_bills primary key (id)

);

— вставляем данные

insert into bills

values(1, date ‘2008-01-01’, 5.5);

insert into bills

values(2, date ‘2008-02-01’, 3.14);

insert into bills

values(3, date ‘2008-03-01’, 10.14);

insert into bills

values(4, date ‘2008-01-01’, 7.2);

insert into bills

values(5, date ‘2008-02-01’, 6.4);

insert into bills

values(6, date ‘2008-03-01’, 2.5);

commit;

— выводим данные в сгруппированном виде

select t.d, t.summ from bills t

group by t.d, t.summ

Вообще-то группы в выборках данных используются не так часто. Можно переписать вышеприведённый пример по-другому, с сортировкой. Но всё меняется, если нам нужна одна из групповых (агрегатных) функций:

  • avg([DISTINCT|ALL] column) — среднее арифметическое по всей выбранной колонке;
  • count(*|[DISTINCT|ALL] соlumn) — число элементов в выборке данныхлибо в группе, которую определяет указанная колонка;
  • sum([DISTINCT | ALL] соlumn) — сумма всех значений в выбранной колонке;
  • max(соlumn) — максимальное значение в колонке;
  • min(соlumn) — минимальное значение в колонке.

С помощью ключевого слова DISTINCT можно убрать из колонки повторяющиеся значения. ALL означает, что нужно по умолчанию обработать все значения. Ключевое слово * используется, когда поля со значением null тоже нужно обрабатывать.

Следите за тем, чтобы в вашем коде для MySQL не было пробелов между скобкой и названием функции.

Рассмотрим случай, когда выбираемыми данными являются агрегатные функции. Если такая функция применяется без group by, то она охватит абсолютно все элементы выборки; в противном же случае — будет использована для каждой группы данных по отдельности. Как бы то ни было, в SELECT групповые колонки таблицы не должны смешиваться с негрупповыми.

— статистика по всем месяцам года

select count(*) as «количество записей

max(t.summ) as «макс. сумма»,

min(t.summ) as «мин. сумма»,

avg(t.summ) as «средняя сумма»,

sum(t.summ) as «общая сумма»

from bills t;

— статистика по каждому конкретному месяцу

select t.d as «месяц», count(1) as «количество записей»,

max(t.summ) as «макс. сумма»,

min(t.summ) as «мин. сумма»,

avg(t.summ) as «средняя сумма»,

sum(t.summ) as «общая сумма»

from bills t

group by t.d

Условные выражения и конструкция having (отбирающая группу) тоже могут содержать агрегатные функции.

Группировка данных при выборке

Группировка данных при выборке

— выбираем те группы элементов, чья общая сумма превышает 12

select t.d as «месяц», count(*) as «количество записей»,

max(t.summ) as «макс. сумма»,

min(t.summ) as «мин. сумма»,

avg(t.summ) as «средняя сумма»,

sum(t.summ) as «общая сумма»

from bills t

group by t.d

having sum(t.summ)>12

Выборка данных любого объёма представляет собой их множество. А это значит, что над ней можно производить операции для множества, а именно:

  • UNION — объединять в итоговой выборке данных элементы двух запросов;
  • INTERSECT — выводить только пересекающиеся записи (которые соответствуют обоим запросам);
  • EXCEPT — исключать из конечной выборки элементы, присутствующие лишь в первом запросе.

К запросам, которые участвуют в этих операциях, предъявляются несколько требований.

Количество столбцов в них должно совпадать, причём столбцы, стоящие на одинаковых позициях, должны ещё иметь одинаковый тип.

Допускаются только данные простых типов в столбцах (то есть, никаких blob и т. п.).

В MySQL5 есть только поддержка UNION. Oracle отличается тем, что EXCEPT в ней используется для иных целей, а исключение записей производится командой MINUS.

— from dual работает только в Oracle

— в MySQL запросы не могут быть заключены в круглые скобки.

select 1 as i from dual

UNION

select 2 as i from dual

UNION — можно также применить INTERSECT и EXCEPT

select 2 as i from dual

UNION

select 3 as i from dual;

Нюансы выборки данных из ORM систем

При работе с моделями данных, содержащими только одну сущность, никаких сложностей с ORM не возникает. Разберём простой пример. Предположим, у нас есть сущность Пользователь (User) с двумя атрибутами — именем (Name) и ID.

public class User {

@Id

@GeneratedValue

private int id;

private String name;

//Getters and Setters here

}

Как же вытащить из базы данных экземпляр данной сущности? Очень просто: с помощью одного метода объекта EntityManager:

EntityManager em = entityManagerFactory.createEntityManager();

User user = em.find(User.class, id);

А вот в случае, когда есть отношение «один-ко-многим», всё становится намного интереснее:

public class User {

@Id

@GeneratedValue

private int id;

private String name;

@OneToMany

private List<Address> addresses;

//Getters and Setters here

}

Наверное, вы уже задаётесь вопросом, а нужно ли делать выборку данных по адресам, извлекая экземпляр пользователя. Верный ответ — по-разному: если эти адреса нам нужны, то да, делаем, если нет — то нет. Как правило, в ORM доступны два способа выбрать зависимую запись: жадный и ленивый. Последний применяется по умолчанию во многих ORM. Однако если ваш код выглядит вот так:

EntityManager em = entityManagerFactory.createEntityManager();

User user = em.find(User.class, 1);

em.close();

System.out.println(user.getAddresses().get(0));

то вы получите исключение “LazyInitException”. Оно всегда вызывает недоумение у начинающих программистов, испытывающих недостаток опыта работы с ORM. Пора вводить новые понятия — сессия в транзакции, Detached и Attached экземпляры сущности.

Нам нужно присоединить сущность к сессии, чтобы зависимые данные оказались в выборке. Казалось бы, самое простое решение — не закрывать транзакции сразу. Но оно порождает другую проблему: транзакции удлиняются, и риск взаимной блокировки растёт. Попробовать сократить транзакции? Это возможно, однако множество коротких транзакций порождает ситуацию, когда стая крохотных комариков способна закусать огромного медведя.

С базами данных такое тоже, увы, возможно. Возрастание мелких транзакций создаёт проблемы с производительностью.

Но, как уже говорилось, адреса далеко не всегда требуются при получении данных о пользователе, и только бизнес-логика определяет, попадут адреса в запрос на выборку данных или нет. То есть, придётся прописывать дополнительные проверки. Как-то всё слишком сложно получается, не правда ли?

Но можно пойти иным путём и просто сменить тип выборки:

public class User {

@Id

@GeneratedValue

private int id;

private String name;

@OneToMany(fetch = FetchType.EAGER)

private List<Address> addresses;

//Getters and Setters here

}

Не то чтобы это сильно помогло. Конечно, мы обойдётся без надоевшего LazyInit и постоянных проверок на то, прикреплена ли сущность к сессии. Но вот проблем с производительностью таким образом не решим: даже если адреса нам требуются не всегда, мы всё равно каждый раз их запрашиваем из памяти сервера.

Усталость от ORM и переход на другие фреймворки — нередкое явление среди бэкенд-разработчиков. Многие выбирают Spring JDBC, в котором реляционные данные можно преобразовать в объектные, причём в полуавтоматическом режиме. Необходимо писать запросы под каждую ситуацию, где требуется та или иная совокупность атрибутов. А если нужны одни и те же структуры данных, то код можно переиспользовать.

Это обеспечивает большую степень гибкости. К примеру, не нужно создавать новый объект-сущность, достаточно выбрать всего один атрибут:

String name = this.jdbcTemplate.queryForObject(

«select name from t_user where id = ?»,

new Object[]{1L}, String.class);

Хотя можно выбрать и объект, как обычно:

User user = this.jdbcTemplate.queryForObject(

«select id, name from t_user where id = ?»,

new Object[]{1L},

new RowMapper<User>() {

public User mapRow(ResultSet rs, int rowNum) throws SQLException {

User user = new User();

user.setName(rs.getString(«name»));

user.setId(rs.getInt(«id»));

return user;

}

});

Если дописать в этот код ещё несколько строк и грамотно составить запрос к SQL (так, чтобы исключить проблему n+1 запроса), то можно получить и список адресов, необходимых пользователю.

Квантовый компьютер: что такое и зачем нужны

Читайте также

Подведём итог всего вышесказанного. Запросы к БД позволяют осуществлять операции выборки данных, их фильтрации, сортировки. Посредством запроса в базу можно делать расчёты, объединять данные из нескольких таблиц, удалять, редактировать, добавлять записи в таблицу. Типов запросов довольно много, и это делает запрос гибким мощным инструментом, подходящим для различных нужд (тип выбирается по назначению запроса).

Источник

Description

SELECT retrieves rows from zero or more tables. The general processing of SELECT is as follows:

  1. All queries in the WITH list are computed. These effectively serve as temporary tables that can be referenced in the FROM list. A WITH query that is referenced more than once in FROM is computed only once, unless specified otherwise with NOT MATERIALIZED. (See WITH Clause below.)

  2. All elements in the FROM list are computed. (Each element in the FROM list is a real or virtual table.) If more than one element is specified in the FROM list, they are cross-joined together. (See FROM Clause below.)

  3. If the WHERE clause is specified, all rows that do not satisfy the condition are eliminated from the output. (See WHERE Clause below.)

  4. If the GROUP BY clause is specified, or if there are aggregate function calls, the output is combined into groups of rows that match on one or more values, and the results of aggregate functions are computed. If the HAVING clause is present, it eliminates groups that do not satisfy the given condition. (See GROUP BY Clause and HAVING Clause below.)

  5. The actual output rows are computed using the SELECT output expressions for each selected row or row group. (See SELECT List below.)

  6. SELECT DISTINCT eliminates duplicate rows from the result. SELECT DISTINCT ON eliminates rows that match on all the specified expressions. SELECT ALL (the default) will return all candidate rows, including duplicates. (See DISTINCT Clause below.)

  7. Using the operators UNION, INTERSECT, and EXCEPT, the output of more than one SELECT statement can be combined to form a single result set. The UNION operator returns all rows that are in one or both of the result sets. The INTERSECT operator returns all rows that are strictly in both result sets. The EXCEPT operator returns the rows that are in the first result set but not in the second. In all three cases, duplicate rows are eliminated unless ALL is specified. The noise word DISTINCT can be added to explicitly specify eliminating duplicate rows. Notice that DISTINCT is the default behavior here, even though ALL is the default for SELECT itself. (See UNION Clause, INTERSECT Clause, and EXCEPT Clause below.)

  8. If the ORDER BY clause is specified, the returned rows are sorted in the specified order. If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce. (See ORDER BY Clause below.)

  9. If the LIMIT (or FETCH FIRST) or OFFSET clause is specified, the SELECT statement only returns a subset of the result rows. (See LIMIT Clause below.)

  10. If FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE or FOR KEY SHARE is specified, the SELECT statement locks the selected rows against concurrent updates. (See The Locking Clause below.)

You must have SELECT privilege on each column used in a SELECT command. The use of FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE or FOR KEY SHARE requires UPDATE privilege as well (for at least one column of each table so selected).

Parameters

WITH Clause

The WITH clause allows you to specify one or more subqueries that can be referenced by name in the primary query. The subqueries effectively act as temporary tables or views for the duration of the primary query. Each subquery can be a SELECT, TABLE, VALUES, INSERT, UPDATE or DELETE statement. When writing a data-modifying statement (INSERT, UPDATE or DELETE) in WITH, it is usual to include a RETURNING clause. It is the output of RETURNING, not the underlying table that the statement modifies, that forms the temporary table that is read by the primary query. If RETURNING is omitted, the statement is still executed, but it produces no output so it cannot be referenced as a table by the primary query.

A name (without schema qualification) must be specified for each WITH query. Optionally, a list of column names can be specified; if this is omitted, the column names are inferred from the subquery.

If RECURSIVE is specified, it allows a SELECT subquery to reference itself by name. Such a subquery must have the form

non_recursive_term UNION [ ALL | DISTINCT ] recursive_term

where the recursive self-reference must appear on the right-hand side of the UNION. Only one recursive self-reference is permitted per query. Recursive data-modifying statements are not supported, but you can use the results of a recursive SELECT query in a data-modifying statement. See Section 7.8 for an example.

Another effect of RECURSIVE is that WITH queries need not be ordered: a query can reference another one that is later in the list. (However, circular references, or mutual recursion, are not implemented.) Without RECURSIVE, WITH queries can only reference sibling WITH queries that are earlier in the WITH list.

When there are multiple queries in the WITH clause, RECURSIVE should be written only once, immediately after WITH. It applies to all queries in the WITH clause, though it has no effect on queries that do not use recursion or forward references.

The optional SEARCH clause computes a search sequence column that can be used for ordering the results of a recursive query in either breadth-first or depth-first order. The supplied column name list specifies the row key that is to be used for keeping track of visited rows. A column named search_seq_col_name will be added to the result column list of the WITH query. This column can be ordered by in the outer query to achieve the respective ordering. See Section 7.8.2.1 for examples.

The optional CYCLE clause is used to detect cycles in recursive queries. The supplied column name list specifies the row key that is to be used for keeping track of visited rows. A column named cycle_mark_col_name will be added to the result column list of the WITH query. This column will be set to cycle_mark_value when a cycle has been detected, else to cycle_mark_default. Furthermore, processing of the recursive union will stop when a cycle has been detected. cycle_mark_value and cycle_mark_default must be constants and they must be coercible to a common data type, and the data type must have an inequality operator. (The SQL standard requires that they be Boolean constants or character strings, but PostgreSQL does not require that.) By default, TRUE and FALSE (of type boolean) are used. Furthermore, a column named cycle_path_col_name will be added to the result column list of the WITH query. This column is used internally for tracking visited rows. See Section 7.8.2.2 for examples.

Both the SEARCH and the CYCLE clause are only valid for recursive WITH queries. The with_query must be a UNION (or UNION ALL) of two SELECT (or equivalent) commands (no nested UNIONs). If both clauses are used, the column added by the SEARCH clause appears before the columns added by the CYCLE clause.

The primary query and the WITH queries are all (notionally) executed at the same time. This implies that the effects of a data-modifying statement in WITH cannot be seen from other parts of the query, other than by reading its RETURNING output. If two such data-modifying statements attempt to modify the same row, the results are unspecified.

A key property of WITH queries is that they are normally evaluated only once per execution of the primary query, even if the primary query refers to them more than once. In particular, data-modifying statements are guaranteed to be executed once and only once, regardless of whether the primary query reads all or any of their output.

However, a WITH query can be marked NOT MATERIALIZED to remove this guarantee. In that case, the WITH query can be folded into the primary query much as though it were a simple sub-SELECT in the primary query’s FROM clause. This results in duplicate computations if the primary query refers to that WITH query more than once; but if each such use requires only a few rows of the WITH query’s total output, NOT MATERIALIZED can provide a net savings by allowing the queries to be optimized jointly. NOT MATERIALIZED is ignored if it is attached to a WITH query that is recursive or is not side-effect-free (i.e., is not a plain SELECT containing no volatile functions).

By default, a side-effect-free WITH query is folded into the primary query if it is used exactly once in the primary query’s FROM clause. This allows joint optimization of the two query levels in situations where that should be semantically invisible. However, such folding can be prevented by marking the WITH query as MATERIALIZED. That might be useful, for example, if the WITH query is being used as an optimization fence to prevent the planner from choosing a bad plan. PostgreSQL versions before v12 never did such folding, so queries written for older versions might rely on WITH to act as an optimization fence.

See Section 7.8 for additional information.

FROM Clause

The FROM clause specifies one or more source tables for the SELECT. If multiple sources are specified, the result is the Cartesian product (cross join) of all the sources. But usually qualification conditions are added (via WHERE) to restrict the returned rows to a small subset of the Cartesian product.

The FROM clause can contain the following elements:

table_name

The name (optionally schema-qualified) of an existing table or view. If ONLY is specified before the table name, only that table is scanned. If ONLY is not specified, the table and all its descendant tables (if any) are scanned. Optionally, * can be specified after the table name to explicitly indicate that descendant tables are included.

alias

A substitute name for the FROM item containing the alias. An alias is used for brevity or to eliminate ambiguity for self-joins (where the same table is scanned multiple times). When an alias is provided, it completely hides the actual name of the table or function; for example given FROM foo AS f, the remainder of the SELECT must refer to this FROM item as f not foo. If an alias is written, a column alias list can also be written to provide substitute names for one or more columns of the table.

TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ]

A TABLESAMPLE clause after a table_name indicates that the specified sampling_method should be used to retrieve a subset of the rows in that table. This sampling precedes the application of any other filters such as WHERE clauses. The standard PostgreSQL distribution includes two sampling methods, BERNOULLI and SYSTEM, and other sampling methods can be installed in the database via extensions.

The BERNOULLI and SYSTEM sampling methods each accept a single argument which is the fraction of the table to sample, expressed as a percentage between 0 and 100. This argument can be any real-valued expression. (Other sampling methods might accept more or different arguments.) These two methods each return a randomly-chosen sample of the table that will contain approximately the specified percentage of the table’s rows. The BERNOULLI method scans the whole table and selects or ignores individual rows independently with the specified probability. The SYSTEM method does block-level sampling with each block having the specified chance of being selected; all rows in each selected block are returned. The SYSTEM method is significantly faster than the BERNOULLI method when small sampling percentages are specified, but it may return a less-random sample of the table as a result of clustering effects.

The optional REPEATABLE clause specifies a seed number or expression to use for generating random numbers within the sampling method. The seed value can be any non-null floating-point value. Two queries that specify the same seed and argument values will select the same sample of the table, if the table has not been changed meanwhile. But different seed values will usually produce different samples. If REPEATABLE is not given then a new random sample is selected for each query, based upon a system-generated seed. Note that some add-on sampling methods do not accept REPEATABLE, and will always produce new samples on each use.

select

A sub-SELECT can appear in the FROM clause. This acts as though its output were created as a temporary table for the duration of this single SELECT command. Note that the sub-SELECT must be surrounded by parentheses, and an alias must be provided for it. A VALUES command can also be used here.

with_query_name

A WITH query is referenced by writing its name, just as though the query’s name were a table name. (In fact, the WITH query hides any real table of the same name for the purposes of the primary query. If necessary, you can refer to a real table of the same name by schema-qualifying the table’s name.) An alias can be provided in the same way as for a table.

function_name

Function calls can appear in the FROM clause. (This is especially useful for functions that return result sets, but any function can be used.) This acts as though the function’s output were created as a temporary table for the duration of this single SELECT command. If the function’s result type is composite (including the case of a function with multiple OUT parameters), each attribute becomes a separate column in the implicit table.

When the optional WITH ORDINALITY clause is added to the function call, an additional column of type bigint will be appended to the function’s result column(s). This column numbers the rows of the function’s result set, starting from 1. By default, this column is named ordinality.

An alias can be provided in the same way as for a table. If an alias is written, a column alias list can also be written to provide substitute names for one or more attributes of the function’s composite return type, including the ordinality column if present.

Multiple function calls can be combined into a single FROM-clause item by surrounding them with ROWS FROM( ... ). The output of such an item is the concatenation of the first row from each function, then the second row from each function, etc. If some of the functions produce fewer rows than others, null values are substituted for the missing data, so that the total number of rows returned is always the same as for the function that produced the most rows.

If the function has been defined as returning the record data type, then an alias or the key word AS must be present, followed by a column definition list in the form ( column_name data_type [, ... ]). The column definition list must match the actual number and types of columns returned by the function.

When using the ROWS FROM( ... ) syntax, if one of the functions requires a column definition list, it’s preferred to put the column definition list after the function call inside ROWS FROM( ... ). A column definition list can be placed after the ROWS FROM( ... ) construct only if there’s just a single function and no WITH ORDINALITY clause.

To use ORDINALITY together with a column definition list, you must use the ROWS FROM( ... ) syntax and put the column definition list inside ROWS FROM( ... ).

join_type

One of

  • [ INNER ] JOIN

  • LEFT [ OUTER ] JOIN

  • RIGHT [ OUTER ] JOIN

  • FULL [ OUTER ] JOIN

For the INNER and OUTER join types, a join condition must be specified, namely exactly one of ON join_condition, USING (join_column [, ...]), or NATURAL. See below for the meaning.

A JOIN clause combines two FROM items, which for convenience we will refer to as “tables”, though in reality they can be any type of FROM item. Use parentheses if necessary to determine the order of nesting. In the absence of parentheses, JOINs nest left-to-right. In any case JOIN binds more tightly than the commas separating FROM-list items. All the JOIN options are just a notational convenience, since they do nothing you couldn’t do with plain FROM and WHERE.

LEFT OUTER JOIN returns all rows in the qualified Cartesian product (i.e., all combined rows that pass its join condition), plus one copy of each row in the left-hand table for which there was no right-hand row that passed the join condition. This left-hand row is extended to the full width of the joined table by inserting null values for the right-hand columns. Note that only the JOIN clause’s own condition is considered while deciding which rows have matches. Outer conditions are applied afterwards.

Conversely, RIGHT OUTER JOIN returns all the joined rows, plus one row for each unmatched right-hand row (extended with nulls on the left). This is just a notational convenience, since you could convert it to a LEFT OUTER JOIN by switching the left and right tables.

FULL OUTER JOIN returns all the joined rows, plus one row for each unmatched left-hand row (extended with nulls on the right), plus one row for each unmatched right-hand row (extended with nulls on the left).

ON join_condition

join_condition is an expression resulting in a value of type boolean (similar to a WHERE clause) that specifies which rows in a join are considered to match.

USING ( join_column [, ...] ) [ AS join_using_alias ]

A clause of the form USING ( a, b, ... ) is shorthand for ON left_table.a = right_table.a AND left_table.b = right_table.b .... Also, USING implies that only one of each pair of equivalent columns will be included in the join output, not both.

If a join_using_alias name is specified, it provides a table alias for the join columns. Only the join columns listed in the USING clause are addressable by this name. Unlike a regular alias, this does not hide the names of the joined tables from the rest of the query. Also unlike a regular alias, you cannot write a column alias list — the output names of the join columns are the same as they appear in the USING list.

NATURAL

NATURAL is shorthand for a USING list that mentions all columns in the two tables that have matching names. If there are no common column names, NATURAL is equivalent to ON TRUE.

CROSS JOIN

CROSS JOIN is equivalent to INNER JOIN ON (TRUE), that is, no rows are removed by qualification. They produce a simple Cartesian product, the same result as you get from listing the two tables at the top level of FROM, but restricted by the join condition (if any).

LATERAL

The LATERAL key word can precede a sub-SELECT FROM item. This allows the sub-SELECT to refer to columns of FROM items that appear before it in the FROM list. (Without LATERAL, each sub-SELECT is evaluated independently and so cannot cross-reference any other FROM item.)

LATERAL can also precede a function-call FROM item, but in this case it is a noise word, because the function expression can refer to earlier FROM items in any case.

A LATERAL item can appear at top level in the FROM list, or within a JOIN tree. In the latter case it can also refer to any items that are on the left-hand side of a JOIN that it is on the right-hand side of.

When a FROM item contains LATERAL cross-references, evaluation proceeds as follows: for each row of the FROM item providing the cross-referenced column(s), or set of rows of multiple FROM items providing the columns, the LATERAL item is evaluated using that row or row set’s values of the columns. The resulting row(s) are joined as usual with the rows they were computed from. This is repeated for each row or set of rows from the column source table(s).

The column source table(s) must be INNER or LEFT joined to the LATERAL item, else there would not be a well-defined set of rows from which to compute each set of rows for the LATERAL item. Thus, although a construct such as X RIGHT JOIN LATERAL Y is syntactically valid, it is not actually allowed for Y to reference X.

WHERE Clause

The optional WHERE clause has the general form

WHERE condition

where condition is any expression that evaluates to a result of type boolean. Any row that does not satisfy this condition will be eliminated from the output. A row satisfies the condition if it returns true when the actual row values are substituted for any variable references.

GROUP BY Clause

The optional GROUP BY clause has the general form

GROUP BY [ ALL | DISTINCT ] grouping_element [, ...]

GROUP BY will condense into a single row all selected rows that share the same values for the grouped expressions. An expression used inside a grouping_element can be an input column name, or the name or ordinal number of an output column (SELECT list item), or an arbitrary expression formed from input-column values. In case of ambiguity, a GROUP BY name will be interpreted as an input-column name rather than an output column name.

If any of GROUPING SETS, ROLLUP or CUBE are present as grouping elements, then the GROUP BY clause as a whole defines some number of independent grouping sets. The effect of this is equivalent to constructing a UNION ALL between subqueries with the individual grouping sets as their GROUP BY clauses. The optional DISTINCT clause removes duplicate sets before processing; it does not transform the UNION ALL into a UNION DISTINCT. For further details on the handling of grouping sets see Section 7.2.4.

Aggregate functions, if any are used, are computed across all rows making up each group, producing a separate value for each group. (If there are aggregate functions but no GROUP BY clause, the query is treated as having a single group comprising all the selected rows.) The set of rows fed to each aggregate function can be further filtered by attaching a FILTER clause to the aggregate function call; see Section 4.2.7 for more information. When a FILTER clause is present, only those rows matching it are included in the input to that aggregate function.

When GROUP BY is present, or any aggregate functions are present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or when the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.

Keep in mind that all aggregate functions are evaluated before evaluating any “scalar” expressions in the HAVING clause or SELECT list. This means that, for example, a CASE expression cannot be used to skip evaluation of an aggregate function; see Section 4.2.14.

Currently, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE and FOR KEY SHARE cannot be specified with GROUP BY.

HAVING Clause

The optional HAVING clause has the general form

HAVING condition

where condition is the same as specified for the WHERE clause.

HAVING eliminates group rows that do not satisfy the condition. HAVING is different from WHERE: WHERE filters individual rows before the application of GROUP BY, while HAVING filters group rows created by GROUP BY. Each column referenced in condition must unambiguously reference a grouping column, unless the reference appears within an aggregate function or the ungrouped column is functionally dependent on the grouping columns.

The presence of HAVING turns a query into a grouped query even if there is no GROUP BY clause. This is the same as what happens when the query contains aggregate functions but no GROUP BY clause. All the selected rows are considered to form a single group, and the SELECT list and HAVING clause can only reference table columns from within aggregate functions. Such a query will emit a single row if the HAVING condition is true, zero rows if it is not true.

Currently, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE and FOR KEY SHARE cannot be specified with HAVING.

WINDOW Clause

The optional WINDOW clause has the general form

WINDOW window_name AS ( window_definition ) [, ...]

where window_name is a name that can be referenced from OVER clauses or subsequent window definitions, and window_definition is

[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]

If an existing_window_name is specified it must refer to an earlier entry in the WINDOW list; the new window copies its partitioning clause from that entry, as well as its ordering clause if any. In this case the new window cannot specify its own PARTITION BY clause, and it can specify ORDER BY only if the copied window does not have one. The new window always uses its own frame clause; the copied window must not specify a frame clause.

The elements of the PARTITION BY list are interpreted in much the same fashion as elements of a GROUP BY clause, except that they are always simple expressions and never the name or number of an output column. Another difference is that these expressions can contain aggregate function calls, which are not allowed in a regular GROUP BY clause. They are allowed here because windowing occurs after grouping and aggregation.

Similarly, the elements of the ORDER BY list are interpreted in much the same fashion as elements of a statement-level ORDER BY clause, except that the expressions are always taken as simple expressions and never the name or number of an output column.

The optional frame_clause defines the window frame for window functions that depend on the frame (not all do). The window frame is a set of related rows for each row of the query (called the current row). The frame_clause can be one of

{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ]
{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ]

where frame_start and frame_end can be one of

UNBOUNDED PRECEDING
offset PRECEDING
CURRENT ROW
offset FOLLOWING
UNBOUNDED FOLLOWING

and frame_exclusion can be one of

EXCLUDE CURRENT ROW
EXCLUDE GROUP
EXCLUDE TIES
EXCLUDE NO OTHERS

If frame_end is omitted it defaults to CURRENT ROW. Restrictions are that frame_start cannot be UNBOUNDED FOLLOWING, frame_end cannot be UNBOUNDED PRECEDING, and the frame_end choice cannot appear earlier in the above list of frame_start and frame_end options than the frame_start choice does — for example RANGE BETWEEN CURRENT ROW AND offset PRECEDING is not allowed.

The default framing option is RANGE UNBOUNDED PRECEDING, which is the same as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; it sets the frame to be all rows from the partition start up through the current row’s last peer (a row that the window’s ORDER BY clause considers equivalent to the current row; all rows are peers if there is no ORDER BY). In general, UNBOUNDED PRECEDING means that the frame starts with the first row of the partition, and similarly UNBOUNDED FOLLOWING means that the frame ends with the last row of the partition, regardless of RANGE, ROWS or GROUPS mode. In ROWS mode, CURRENT ROW means that the frame starts or ends with the current row; but in RANGE or GROUPS mode it means that the frame starts or ends with the current row’s first or last peer in the ORDER BY ordering. The offset PRECEDING and offset FOLLOWING options vary in meaning depending on the frame mode. In ROWS mode, the offset is an integer indicating that the frame starts or ends that many rows before or after the current row. In GROUPS mode, the offset is an integer indicating that the frame starts or ends that many peer groups before or after the current row’s peer group, where a peer group is a group of rows that are equivalent according to the window’s ORDER BY clause. In RANGE mode, use of an offset option requires that there be exactly one ORDER BY column in the window definition. Then the frame contains those rows whose ordering column value is no more than offset less than (for PRECEDING) or more than (for FOLLOWING) the current row’s ordering column value. In these cases the data type of the offset expression depends on the data type of the ordering column. For numeric ordering columns it is typically of the same type as the ordering column, but for datetime ordering columns it is an interval. In all these cases, the value of the offset must be non-null and non-negative. Also, while the offset does not have to be a simple constant, it cannot contain variables, aggregate functions, or window functions.

The frame_exclusion option allows rows around the current row to be excluded from the frame, even if they would be included according to the frame start and frame end options. EXCLUDE CURRENT ROW excludes the current row from the frame. EXCLUDE GROUP excludes the current row and its ordering peers from the frame. EXCLUDE TIES excludes any peers of the current row from the frame, but not the current row itself. EXCLUDE NO OTHERS simply specifies explicitly the default behavior of not excluding the current row or its peers.

Beware that the ROWS mode can produce unpredictable results if the ORDER BY ordering does not order the rows uniquely. The RANGE and GROUPS modes are designed to ensure that rows that are peers in the ORDER BY ordering are treated alike: all rows of a given peer group will be in the frame or excluded from it.

The purpose of a WINDOW clause is to specify the behavior of window functions appearing in the query’s SELECT list or ORDER BY clause. These functions can reference the WINDOW clause entries by name in their OVER clauses. A WINDOW clause entry does not have to be referenced anywhere, however; if it is not used in the query it is simply ignored. It is possible to use window functions without any WINDOW clause at all, since a window function call can specify its window definition directly in its OVER clause. However, the WINDOW clause saves typing when the same window definition is needed for more than one window function.

Currently, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE and FOR KEY SHARE cannot be specified with WINDOW.

Window functions are described in detail in Section 3.5, Section 4.2.8, and Section 7.2.5.

SELECT List

The SELECT list (between the key words SELECT and FROM) specifies expressions that form the output rows of the SELECT statement. The expressions can (and usually do) refer to columns computed in the FROM clause.

Just as in a table, every output column of a SELECT has a name. In a simple SELECT this name is just used to label the column for display, but when the SELECT is a sub-query of a larger query, the name is seen by the larger query as the column name of the virtual table produced by the sub-query. To specify the name to use for an output column, write AS output_name after the column’s expression. (You can omit AS, but only if the desired output name does not match any PostgreSQL keyword (see Appendix C). For protection against possible future keyword additions, it is recommended that you always either write AS or double-quote the output name.) If you do not specify a column name, a name is chosen automatically by PostgreSQL. If the column’s expression is a simple column reference then the chosen name is the same as that column’s name. In more complex cases a function or type name may be used, or the system may fall back on a generated name such as ?column?.

An output column’s name can be used to refer to the column’s value in ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must write out the expression instead.

Instead of an expression, * can be written in the output list as a shorthand for all the columns of the selected rows. Also, you can write table_name.* as a shorthand for the columns coming from just that table. In these cases it is not possible to specify new names with AS; the output column names will be the same as the table columns’ names.

According to the SQL standard, the expressions in the output list should be computed before applying DISTINCT, ORDER BY, or LIMIT. This is obviously necessary when using DISTINCT, since otherwise it’s not clear what values are being made distinct. However, in many cases it is convenient if output expressions are computed after ORDER BY and LIMIT; particularly if the output list contains any volatile or expensive functions. With that behavior, the order of function evaluations is more intuitive and there will not be evaluations corresponding to rows that never appear in the output. PostgreSQL will effectively evaluate output expressions after sorting and limiting, so long as those expressions are not referenced in DISTINCT, ORDER BY or GROUP BY. (As a counterexample, SELECT f(x) FROM tab ORDER BY 1 clearly must evaluate f(x) before sorting.) Output expressions that contain set-returning functions are effectively evaluated after sorting and before limiting, so that LIMIT will act to cut off the output from a set-returning function.

Note

PostgreSQL versions before 9.6 did not provide any guarantees about the timing of evaluation of output expressions versus sorting and limiting; it depended on the form of the chosen query plan.

DISTINCT Clause

If SELECT DISTINCT is specified, all duplicate rows are removed from the result set (one row is kept from each group of duplicates). SELECT ALL specifies the opposite: all rows are kept; that is the default.

SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the “first row” of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example:

SELECT DISTINCT ON (location) location, time, report
    FROM weather_reports
    ORDER BY location, time DESC;

retrieves the most recent weather report for each location. But if we had not used ORDER BY to force descending order of time values for each location, we’d have gotten a report from an unpredictable time for each location.

The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s). The ORDER BY clause will normally contain additional expression(s) that determine the desired precedence of rows within each DISTINCT ON group.

Currently, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE and FOR KEY SHARE cannot be specified with DISTINCT.

UNION Clause

The UNION clause has this general form:

select_statement UNION [ ALL | DISTINCT ] select_statement

select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, or FOR KEY SHARE clause. (ORDER BY and LIMIT can be attached to a subexpression if it is enclosed in parentheses. Without parentheses, these clauses will be taken to apply to the result of the UNION, not to its right-hand input expression.)

The UNION operator computes the set union of the rows returned by the involved SELECT statements. A row is in the set union of two result sets if it appears in at least one of the result sets. The two SELECT statements that represent the direct operands of the UNION must produce the same number of columns, and corresponding columns must be of compatible data types.

The result of UNION does not contain any duplicate rows unless the ALL option is specified. ALL prevents elimination of duplicates. (Therefore, UNION ALL is usually significantly quicker than UNION; use ALL when you can.) DISTINCT can be written to explicitly specify the default behavior of eliminating duplicate rows.

Multiple UNION operators in the same SELECT statement are evaluated left to right, unless otherwise indicated by parentheses.

Currently, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE and FOR KEY SHARE cannot be specified either for a UNION result or for any input of a UNION.

INTERSECT Clause

The INTERSECT clause has this general form:

select_statement INTERSECT [ ALL | DISTINCT ] select_statement

select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, or FOR KEY SHARE clause.

The INTERSECT operator computes the set intersection of the rows returned by the involved SELECT statements. A row is in the intersection of two result sets if it appears in both result sets.

The result of INTERSECT does not contain any duplicate rows unless the ALL option is specified. With ALL, a row that has m duplicates in the left table and n duplicates in the right table will appear min(m,n) times in the result set. DISTINCT can be written to explicitly specify the default behavior of eliminating duplicate rows.

Multiple INTERSECT operators in the same SELECT statement are evaluated left to right, unless parentheses dictate otherwise. INTERSECT binds more tightly than UNION. That is, A UNION B INTERSECT C will be read as A UNION (B INTERSECT C).

Currently, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE and FOR KEY SHARE cannot be specified either for an INTERSECT result or for any input of an INTERSECT.

EXCEPT Clause

The EXCEPT clause has this general form:

select_statement EXCEPT [ ALL | DISTINCT ] select_statement

select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, or FOR KEY SHARE clause.

The EXCEPT operator computes the set of rows that are in the result of the left SELECT statement but not in the result of the right one.

The result of EXCEPT does not contain any duplicate rows unless the ALL option is specified. With ALL, a row that has m duplicates in the left table and n duplicates in the right table will appear max(m—n,0) times in the result set. DISTINCT can be written to explicitly specify the default behavior of eliminating duplicate rows.

Multiple EXCEPT operators in the same SELECT statement are evaluated left to right, unless parentheses dictate otherwise. EXCEPT binds at the same level as UNION.

Currently, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE and FOR KEY SHARE cannot be specified either for an EXCEPT result or for any input of an EXCEPT.

ORDER BY Clause

The optional ORDER BY clause has this general form:

ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...]

The ORDER BY clause causes the result rows to be sorted according to the specified expression(s). If two rows are equal according to the leftmost expression, they are compared according to the next expression and so on. If they are equal according to all specified expressions, they are returned in an implementation-dependent order.

Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values.

The ordinal number refers to the ordinal (left-to-right) position of the output column. This feature makes it possible to define an ordering on the basis of a column that does not have a unique name. This is never absolutely necessary because it is always possible to assign a name to an output column using the AS clause.

It is also possible to use arbitrary expressions in the ORDER BY clause, including columns that do not appear in the SELECT output list. Thus the following statement is valid:

SELECT name FROM distributors ORDER BY code;

A limitation of this feature is that an ORDER BY clause applying to the result of a UNION, INTERSECT, or EXCEPT clause can only specify an output column name or number, not an expression.

If an ORDER BY expression is a simple name that matches both an output column name and an input column name, ORDER BY will interpret it as the output column name. This is the opposite of the choice that GROUP BY will make in the same situation. This inconsistency is made to be compatible with the SQL standard.

Optionally one can add the key word ASC (ascending) or DESC (descending) after any expression in the ORDER BY clause. If not specified, ASC is assumed by default. Alternatively, a specific ordering operator name can be specified in the USING clause. An ordering operator must be a less-than or greater-than member of some B-tree operator family. ASC is usually equivalent to USING < and DESC is usually equivalent to USING >. (But the creator of a user-defined data type can define exactly what the default sort ordering is, and it might correspond to operators with other names.)

If NULLS LAST is specified, null values sort after all non-null values; if NULLS FIRST is specified, null values sort before all non-null values. If neither is specified, the default behavior is NULLS LAST when ASC is specified or implied, and NULLS FIRST when DESC is specified (thus, the default is to act as though nulls are larger than non-nulls). When USING is specified, the default nulls ordering depends on whether the operator is a less-than or greater-than operator.

Note that ordering options apply only to the expression they follow; for example ORDER BY x, y DESC does not mean the same thing as ORDER BY x DESC, y DESC.

Character-string data is sorted according to the collation that applies to the column being sorted. That can be overridden at need by including a COLLATE clause in the expression, for example ORDER BY mycolumn COLLATE "en_US". For more information see Section 4.2.10 and Section 24.2.

LIMIT Clause

The LIMIT clause consists of two independent sub-clauses:

LIMIT { count | ALL }
OFFSET start

The parameter count specifies the maximum number of rows to return, while start specifies the number of rows to skip before starting to return rows. When both are specified, start rows are skipped before starting to count the count rows to be returned.

If the count expression evaluates to NULL, it is treated as LIMIT ALL, i.e., no limit. If start evaluates to NULL, it is treated the same as OFFSET 0.

SQL:2008 introduced a different syntax to achieve the same result, which PostgreSQL also supports. It is:

OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }

In this syntax, the start or count value is required by the standard to be a literal constant, a parameter, or a variable name; as a PostgreSQL extension, other expressions are allowed, but will generally need to be enclosed in parentheses to avoid ambiguity. If count is omitted in a FETCH clause, it defaults to 1. The WITH TIES option is used to return any additional rows that tie for the last place in the result set according to the ORDER BY clause; ORDER BY is mandatory in this case, and SKIP LOCKED is not allowed. ROW and ROWS as well as FIRST and NEXT are noise words that don’t influence the effects of these clauses. According to the standard, the OFFSET clause must come before the FETCH clause if both are present; but PostgreSQL is laxer and allows either order.

When using LIMIT, it is a good idea to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query’s rows — you might be asking for the tenth through twentieth rows, but tenth through twentieth in what ordering? You don’t know what ordering unless you specify ORDER BY.

The query planner takes LIMIT into account when generating a query plan, so you are very likely to get different plans (yielding different row orders) depending on what you use for LIMIT and OFFSET. Thus, using different LIMIT/OFFSET values to select different subsets of a query result will give inconsistent results unless you enforce a predictable result ordering with ORDER BY. This is not a bug; it is an inherent consequence of the fact that SQL does not promise to deliver the results of a query in any particular order unless ORDER BY is used to constrain the order.

It is even possible for repeated executions of the same LIMIT query to return different subsets of the rows of a table, if there is not an ORDER BY to enforce selection of a deterministic subset. Again, this is not a bug; determinism of the results is simply not guaranteed in such a case.

TABLE Command

The command

TABLE name

is equivalent to

SELECT * FROM name

It can be used as a top-level command or as a space-saving syntax variant in parts of complex queries. Only the WITH, UNION, INTERSECT, EXCEPT, ORDER BY, LIMIT, OFFSET, FETCH and FOR locking clauses can be used with TABLE; the WHERE clause and any form of aggregation cannot be used.

Examples

To join the table films with the table distributors:

SELECT f.title, f.did, d.name, f.date_prod, f.kind
    FROM distributors d JOIN films f USING (did);

       title       | did |     name     | date_prod  |   kind
-------------------+-----+--------------+------------+----------
 The Third Man     | 101 | British Lion | 1949-12-23 | Drama
 The African Queen | 101 | British Lion | 1951-08-11 | Romantic
 ...

To sum the column len of all films and group the results by kind:

SELECT kind, sum(len) AS total FROM films GROUP BY kind;

   kind   | total
----------+-------
 Action   | 07:34
 Comedy   | 02:58
 Drama    | 14:28
 Musical  | 06:42
 Romantic | 04:38

To sum the column len of all films, group the results by kind and show those group totals that are less than 5 hours:

SELECT kind, sum(len) AS total
    FROM films
    GROUP BY kind
    HAVING sum(len) < interval '5 hours';

   kind   | total
----------+-------
 Comedy   | 02:58
 Romantic | 04:38

The following two examples are identical ways of sorting the individual results according to the contents of the second column (name):

SELECT * FROM distributors ORDER BY name;
SELECT * FROM distributors ORDER BY 2;

 did |       name
-----+------------------
 109 | 20th Century Fox
 110 | Bavaria Atelier
 101 | British Lion
 107 | Columbia
 102 | Jean Luc Godard
 113 | Luso films
 104 | Mosfilm
 103 | Paramount
 106 | Toho
 105 | United Artists
 111 | Walt Disney
 112 | Warner Bros.
 108 | Westward

The next example shows how to obtain the union of the tables distributors and actors, restricting the results to those that begin with the letter W in each table. Only distinct rows are wanted, so the key word ALL is omitted.

distributors:               actors:
 did |     name              id |     name
-----+--------------        ----+----------------
 108 | Westward               1 | Woody Allen
 111 | Walt Disney            2 | Warren Beatty
 112 | Warner Bros.           3 | Walter Matthau
 ...                         ...

SELECT distributors.name
    FROM distributors
    WHERE distributors.name LIKE 'W%'
UNION
SELECT actors.name
    FROM actors
    WHERE actors.name LIKE 'W%';

      name
----------------
 Walt Disney
 Walter Matthau
 Warner Bros.
 Warren Beatty
 Westward
 Woody Allen

This example shows how to use a function in the FROM clause, both with and without a column definition list:

CREATE FUNCTION distributors(int) RETURNS SETOF distributors AS $$
    SELECT * FROM distributors WHERE did = $1;
$$ LANGUAGE SQL;

SELECT * FROM distributors(111);
 did |    name
-----+-------------
 111 | Walt Disney

CREATE FUNCTION distributors_2(int) RETURNS SETOF record AS $$
    SELECT * FROM distributors WHERE did = $1;
$$ LANGUAGE SQL;

SELECT * FROM distributors_2(111) AS (f1 int, f2 text);
 f1  |     f2
-----+-------------
 111 | Walt Disney

Here is an example of a function with an ordinality column added:

SELECT * FROM unnest(ARRAY['a','b','c','d','e','f']) WITH ORDINALITY;
 unnest | ordinality
--------+----------
 a      |        1
 b      |        2
 c      |        3
 d      |        4
 e      |        5
 f      |        6
(6 rows)

This example shows how to use a simple WITH clause:

WITH t AS (
    SELECT random() as x FROM generate_series(1, 3)
  )
SELECT * FROM t
UNION ALL
SELECT * FROM t

         x
--------------------
  0.534150459803641
  0.520092216785997
 0.0735620250925422
  0.534150459803641
  0.520092216785997
 0.0735620250925422

Notice that the WITH query was evaluated only once, so that we got two sets of the same three random values.

This example uses WITH RECURSIVE to find all subordinates (direct or indirect) of the employee Mary, and their level of indirectness, from a table that shows only direct subordinates:

WITH RECURSIVE employee_recursive(distance, employee_name, manager_name) AS (
    SELECT 1, employee_name, manager_name
    FROM employee
    WHERE manager_name = 'Mary'
  UNION ALL
    SELECT er.distance + 1, e.employee_name, e.manager_name
    FROM employee_recursive er, employee e
    WHERE er.employee_name = e.manager_name
  )
SELECT distance, employee_name FROM employee_recursive;

Notice the typical form of recursive queries: an initial condition, followed by UNION, followed by the recursive part of the query. Be sure that the recursive part of the query will eventually return no tuples, or else the query will loop indefinitely. (See Section 7.8 for more examples.)

This example uses LATERAL to apply a set-returning function get_product_names() for each row of the manufacturers table:

SELECT m.name AS mname, pname
FROM manufacturers m, LATERAL get_product_names(m.id) pname;

Manufacturers not currently having any products would not appear in the result, since it is an inner join. If we wished to include the names of such manufacturers in the result, we could do:

SELECT m.name AS mname, pname
FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true;

Compatibility

Of course, the SELECT statement is compatible with the SQL standard. But there are some extensions and some missing features.

Omitted FROM Clauses

PostgreSQL allows one to omit the FROM clause. It has a straightforward use to compute the results of simple expressions:

SELECT 2+2;

 ?column?
----------
        4

Some other SQL databases cannot do this except by introducing a dummy one-row table from which to do the SELECT.

Empty SELECT Lists

The list of output expressions after SELECT can be empty, producing a zero-column result table. This is not valid syntax according to the SQL standard. PostgreSQL allows it to be consistent with allowing zero-column tables. However, an empty list is not allowed when DISTINCT is used.

Omitting the AS Key Word

In the SQL standard, the optional key word AS can be omitted before an output column name whenever the new column name is a valid column name (that is, not the same as any reserved keyword). PostgreSQL is slightly more restrictive: AS is required if the new column name matches any keyword at all, reserved or not. Recommended practice is to use AS or double-quote output column names, to prevent any possible conflict against future keyword additions.

In FROM items, both the standard and PostgreSQL allow AS to be omitted before an alias that is an unreserved keyword. But this is impractical for output column names, because of syntactic ambiguities.

ONLY and Inheritance

The SQL standard requires parentheses around the table name when writing ONLY, for example SELECT * FROM ONLY (tab1), ONLY (tab2) WHERE .... PostgreSQL considers these parentheses to be optional.

PostgreSQL allows a trailing * to be written to explicitly specify the non-ONLY behavior of including child tables. The standard does not allow this.

(These points apply equally to all SQL commands supporting the ONLY option.)

TABLESAMPLE Clause Restrictions

The TABLESAMPLE clause is currently accepted only on regular tables and materialized views. According to the SQL standard it should be possible to apply it to any FROM item.

Function Calls in FROM

PostgreSQL allows a function call to be written directly as a member of the FROM list. In the SQL standard it would be necessary to wrap such a function call in a sub-SELECT; that is, the syntax FROM func(...) alias is approximately equivalent to FROM LATERAL (SELECT func(...)) alias. Note that LATERAL is considered to be implicit; this is because the standard requires LATERAL semantics for an UNNEST() item in FROM. PostgreSQL treats UNNEST() the same as other set-returning functions.

Namespace Available to GROUP BY and ORDER BY

In the SQL-92 standard, an ORDER BY clause can only use output column names or numbers, while a GROUP BY clause can only use expressions based on input column names. PostgreSQL extends each of these clauses to allow the other choice as well (but it uses the standard’s interpretation if there is ambiguity). PostgreSQL also allows both clauses to specify arbitrary expressions. Note that names appearing in an expression will always be taken as input-column names, not as output-column names.

SQL:1999 and later use a slightly different definition which is not entirely upward compatible with SQL-92. In most cases, however, PostgreSQL will interpret an ORDER BY or GROUP BY expression the same way SQL:1999 does.

Functional Dependencies

PostgreSQL recognizes functional dependency (allowing columns to be omitted from GROUP BY) only when a table’s primary key is included in the GROUP BY list. The SQL standard specifies additional conditions that should be recognized.

LIMIT and OFFSET

The clauses LIMIT and OFFSET are PostgreSQL-specific syntax, also used by MySQL. The SQL:2008 standard has introduced the clauses OFFSET ... FETCH {FIRST|NEXT} ... for the same functionality, as shown above in LIMIT Clause. This syntax is also used by IBM DB2. (Applications written for Oracle frequently use a workaround involving the automatically generated rownum column, which is not available in PostgreSQL, to implement the effects of these clauses.)

FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, FOR KEY SHARE

Although FOR UPDATE appears in the SQL standard, the standard allows it only as an option of DECLARE CURSOR. PostgreSQL allows it in any SELECT query as well as in sub-SELECTs, but this is an extension. The FOR NO KEY UPDATE, FOR SHARE and FOR KEY SHARE variants, as well as the NOWAIT and SKIP LOCKED options, do not appear in the standard.

Data-Modifying Statements in WITH

PostgreSQL allows INSERT, UPDATE, and DELETE to be used as WITH queries. This is not found in the SQL standard.

Nonstandard Clauses

DISTINCT ON ( ... ) is an extension of the SQL standard.

ROWS FROM( ... ) is an extension of the SQL standard.

The MATERIALIZED and NOT MATERIALIZED options of WITH are extensions of the SQL standard.

Источник

Понравилась статья? Поделить с друзьями:
  • Какие инструкции должны быть разработаны в производственных подразделениях
  • Какие вопросы должны быть отражены в инструкции о мерах пожарной безопасности
  • Какие вопросы должны быть обязательно отражены в инструкции о мерах пожарной
  • Какие виды работ проводят без оформления наряда допуска по производственной инструкции
  • Какие виды инструкций по охране труда бывают