Нерекурсивний_запит_SELECT_2

{EXCEPT | INTERSECT | UNION [ALL]}

…

Нерекурсивний_запит_SELECT_N

]

UNION ALL

Рекурсивний_запит_SELECT_1

[

Рекурсивний_запит_SELECT_2

UNION ALL

…

Рекурсивний_запит_SELECT_N

]

Слід зазначити, що кількість стовпців поєднуваних запитів повинна бути однаковою, а їхні типи даних - сумісні.

Створення CTE підкоряється наступним правилам:

1. CTE може посилатися сама на себе або на раніше описані CTE, але не може посилатися на CTE, які ще не описані.

2. Не можна використовувати секцію WITH в CTE.

3. У запитах SELECT, які утворюють CTE, не можуть бути використані наступні секції:

· COMPUTE [BY].

· ORDER BY (крім випадку, коли використовується опція TOP).

· INTO.

· OPTION із вказівкою настроювань поводження запиту.

· FOR XML.

· FOR BROWSE.

4. Якщо команда, що містить секцію WITH іде не 1-ою у пакеті команд, то попередня їй команда повинна завершуватися символом ;.

Для рекурсивних CTE існує ряд додаткових правил і обмежень:

1. Секція FROM рекурсивних запитів повинна посилатися на ім'я CTE, до якого вони належать, тільки 1 раз.

2. Наступні секції, оператори і конструкції не можуть використовуватися в запитах SELECT, які утворюють CTE:

· Опції DISTINCT і TOP.

· GROUP BY.

· HAVING.

· Оператори {LEFT | RIGHT | FULL} [OUTER] JOIN (INNER JOIN і CROSS JOIN- припустимі).

· Вкладені запити SELECT.

· Скалярні агрегаційнні функції.

3. Всі стовпці, що повертаються рекурсивним CTE, мають властивість NULL.

4. Подання, що містить рекурсивні CTE, не може бути використане для зміни даних у пов'язаних з ним таблицях.

5. Якщо рекурсивний CTE міститься в команді SELECT, що описує курсор, то тип курсору може бути тільки STATIC або FAST_FORWARD. У противному випадку - тип курсору буде приведений до типу STATIC.

6. У рекурсивному запиті повинна міститися умова зупинки, що перериває його виконання. У противному випадку виходить нескінченний цикл виконання рекурсивного запиту SELECT, що приводить до помилки.

Секція SELECT.

У загальному виді секція SELECT, відповідальна за перерахування одержуваних у результаті виконання команди SELECT стовпців або виразів, виглядає так:

SELECT [ {ALL | DISTINCT} ]

[TOP вираження [PERCENT] [WITH TIES] ]

{

* |

[ {ім'я_таблиці | ім'я_подання | псевдонім_таблиці}.]* |

{

[ {ім'я_таблиці | ім'я_подання | псевдонім_таблиці}.]

{ім'я_стовпця | $IDENTITY | $ROWGUID} |

ім'я_стовпця_користув_типу [ {.|::} { {ім'я_властивості | ім'я_поля } |

ім'я_методу(аргументи) }] |

Вираження

[ [AS] псевдонім_стовпця]

} |

псевдонім_стовпця = вираз

}

де:

· ALL | DISTINCT - описує можливість (ALL) появи рядків, що дублюються, у результаті виконання запиту або їх відсутність (DISTINCT);

· TOP вираз [PERCENT] [WITH TIES] - показує яка кількість рядків з результуючого набору буде отримана, причому, якщо зазначено ключове слово PERCENT, те вираз задає кількість відсотків рядків. WITH TIES показує, що будуть отримані всі рядки, значення стовпців, використовуваних у секції ORDER BY, у яких збігаються зі значеннями стовпців останнього одержуваного рядка. WITH TIES не може бути використане без ORDER BY. Слід зазначити, що впорядкування набору за допомогою ORDER BY впливає на те, які рядки будуть отримані за допомогою TOP;

· Далі йде перелік обираних стовпців (виразів) - select_list, що може задаватися або за допомогою символу *, що означає всі стовпці, причому цьому символу може передувати ім'я таблиці або подання, якщо використовується кілька джерел вибірки даних, або за допомогою завдання переліку імен стовпців, що витягаються, перед якими може вказуватися ім'я таблиці або подання. Як ім'я стовпця може бути використані спеціальні ключові слова: $IDENTITY або $ROWGUID, які означають вибір значень стовпця з відповідними властивостями (Властивість IDENTITY може мати тільки один стовпець у таблиці). Для обираного стовпця або виразу може задаватися псевдонім, що буде використовуватися як ім'я для одержуваного стовпця значень у результуючій множині рядків. Також можна використовувати псевдоніми для імен таблиць, які задаються в секції FROM. Можуть бути використані типи даних common language runtime (CLR), а також їхні методи властивості й поля. Максимальна кількість виразів в select_list - 4096.

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

Секція INTO.

Використовуючи цю секцію можна скопіювати одержувані значення стовпців зі списку обираних стовпців секції SELECT у нову таблицю, задану своїм ім'ям (такої таблиці або подання існувати не повинно). Слід зазначити, що при такому копіюванні стовпці, що обчислюються (computed) в новій таблиці стануть звичайними. Команда SELECT із секцією INTO не може використовувати секцію COMPUTE.

Секція FROM.

Секція FROM показує з якого джерела(ел) даних будуть витягати рядки, що утворять результуючу множин. У загальному виді ця секція виглядає так:

FROM table_source, де table_source, максимальна кількість яких 256, може бути описаний так:

{

ім'я_табл_або_подання [ [as] псевдонім ]

[TABLESAMPLE [SYSTEM] (розмір_вибірки [ {PERCENT | ROWS }])]

[REPEATTABLE (ідентифікатор_вибірки)]

[WITH (список_опцій)] |

функція_що_повертає_набір_рядків [ [as] псевдонім ]

[(псевдоніми_стовпців)] |

функція_визначена_користувачем [ [as] псевдонім ]

[(псевдоніми_стовпців)] |

OPENXML (doc_handle int [in], шаблон_ідентиф_вузла nvarchar [in],

[прапори byte [in] ])

[WITH ({опис_схеми | ім'я_таблиці})] |

(вкладений_запит) [ [as] псевдонім ]

[(псевдоніми_стовпців)] |

{

table_source [{INNER | {LEFT | RIGHT | FULL} [OUTER] }]

[опції_об'єднання]

JOIN table_source ON умова_об'єднання |

table_source CROSS JOIN table_source |

table_source_ліва_частина {CROSS | OUTER} APPLY

Table_source_права_частина

} |

table_source PIVOT агрегаційна_функція(ім'я_стовпця_обчисл)

FOR ім'я_стовпця_угруповання

IN (список_імен_стовпців_значень_груп)

псевдонім_таблиці |

table_source UNPIVOT (ім'я_стовпця_обчисл FOR ім'я_стовпця_угруповання

IN (список_імен_стовпців))

псевдонім_таблиці |

@ім'я_змінної [ [as] псевдонім ] |

@ім'я_змінної.виклик_функції(аргументи) [ [as] псевдонім ]

[(псевдоніми_стовпців)]

}

Розглянемо деякі можливі значення table_source:

1. Ім'я таблиці або подання. Можливе завдання псевдоніма. При цьому ім'я може описуватися як завданням всіх 4-ьох його компонентів, так і частковим їх використанням. Слід зазначити появу в MS SQL Server 2005 конструкції TABLESAMPLE, таким чином, використання цієї конструкції припустимо, якщо рівень сумісності для БД виставлений у значення 90. Вона призначена для «випадкової» вибірки зазначеного числа (приблизно) відсотків рядків (ключове слово PERCENT - значення за замовчуванням) або рядків (ключове слово ROWS). Ключове слово SYSTEM описує єдино доступний на даний момент спосіб «випадкової» вибірки, причому є значенням за замовчуванням. Слід зазначити, що у випадку маленьких таблиць (десятки або сотні рядків) і малого розміру вибірки можлива ситуація, коли при виконанні команди вертаються або всі рядки таблиці або порожня множина, причому ймовірність одержання всіх рядків пропорційна розміру «випадкової» вибірки. Ключове слово REPEATABLE, за яким іде ідентифікатор «випадкової» вибірки, призначене для повторного одержання заданої випадкової вибірки. Якщо його не використовувати, то при повторному виконанні команди SELECT з конструкцією TABLESAMPLE може бути отримана інша «випадкова» вибірка.

2. Системна або користувальницька функція, що повертає набір рядків.

3. Вкладений SQL запит (команда SELECT), що повертає деяку множину рядків.

4. Вертикальне об'єднання 2-ух і більше таблиць (JOIN). По суті - вертикальне об'єднання є декартовим добутком, тобто множиною різних об'єднань пара рядків для зазначених таблиць. Воно часто застосовується для об'єднання двох і більше таблиць, зв'язаних обмеженням FOREIGN KEY. В MS SQL Server 2005 (як і в MS SQL Server 2000) існує 5 різних типів таких об'єднань всіх можливих пар рядків (слід зазначити, що поєднуються тільки ті стовпці таблиць, які перебувають в select_list):

a) об'єднання по певній умові, які виконуються в такий спосіб (якщо не зазначений тип об'єднання, то за замовчуванням мається на увазі INNER JOIN):

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

· LEFT OUTER JOIN - для кожної строки таблиці з лівої частини оператора об'єднання по черзі вибираються рядок з таблиці правої частини оператора об'єднання, що задовольняє умові. Якщо такого рядка нема, то беруться значення NULL для всіх обираних стовпців «правої» таблиці.

· RIGHT OUTER JOIN - для кожної строки таблиці із правої частини оператора об'єднання по черзі вибирається рядок з таблиці лівої частини оператора об'єднання, що задовольняє умові. Якщо такого рядка нема, то беруться значення NULL для всіх обираних стовпців «лівої» таблиці.

· FULL OUTER JOIN - для кожної строки таблиці з лівої частини оператора об'єднання по черзі вибирається рядок з таблиці правої частини оператора об'єднання, що задовольняє умові. Якщо такого рядка нема, то беруться значення NULL для всіх обираних стовпців «правої» таблиці. Далі, беруться рядки «правої» таблиці, не задовольняючі умові, і доповнюються значеннями NULL для обираних стовпців «лівої» таблиці.

b) CROSS JOIN - виконується так: для кожної строки таблиці із правої частини оператора об'єднання по черзі вибирається рядок з таблиці лівої частини оператора об'єднання. Еквівалентно перерахуванню джерел даних через кому.

c) об'єднання за допомогою оператора {CROSS | OUTER} APPLY. Цей оператор об'єднання уведений в MS SQL Server 2005, таким чином, його використання припустиме, якщо рівень сумісності для БД виставлений у значення 90. Особливістю використання цього оператора є те, що в якості «правої» таблиці може виступати функція, що повертає множини рядкків, що як аргумент одержує значення стовпця «лівої» таблиці. Слід зазначити, що «ліве» джерело даних може також включати функцію, що повертає множин рядків, однак ця функція не може одержувати значення стовпця з «правої» таблиці як свій аргумент. Розглянемо докладніше два можливих види оператора APPLY:

· CROSS - результатом є всі пари рядків отриманих у результаті об'єднання множині рядків, отриманих функцією, з множиною рядків «лівої» таблиці. Причому, якщо для якого або значення аргументу, що повертається функцією множина рядків є порожньою, то в цьому випадку нові рядки не генеруються.

· OUTER - результатом є все пари рядків отриманих у результаті об'єднання множини рядків, отриманих функцією, з множиною рядків «лівої» таблиці. Причому, якщо для якого або значення аргументу, що повертається функцією множина рядків є порожньою, то в цьому випадку нові рядки виходять шляхом об'єднання рядка з «лівої» таблиці зі значеннями NULL для обираних стовпців з множини рядків, що повертаються «правої» функцією.

5. Угруповання (з «розворотом») рядків таблиці (оператор PIVOT). Його використання припустиме, якщо рівень сумісності для БД виставлений у значення 90. Цей оператор здійснює угруповання по стовпці, ім'я якого вказується після ключового слова FOR. Потім, для кожної групи, значення яких повинні бути зазначені в select_list і в переліку імен стовпців після ключового слова IN генерується значення, які повертає агрегаційна функція. Ці значення формують рядки, які вертаються подібною командою SELECT. Причому, заголовки стовпців цих рядків збігаються з іменами стовпців після ключового слова IN. Якщо яка-небудь група відсутня (тобто серед значень стовпця угруповання немає такого значення, що входить у множини значень після ключового слова IN), то у відповідному стовпці виводиться значення NULL (якщо агрегаційна функція - count, то виводиться 0). Слід зазначити, що при використанні оператора PIVOT над аргументом агрегаційної функції, що є ім'ям стовпця, не можна виконувати перетворення типу за допомогою функцій CAST і CONVERT.

6. Зворотний «розворот» стовпців таблиці (оператор UNPIVOT). Виконує, по суті, дія зворотню операторові PIVOT (за винятком одержання наборів рядків, які утворювали кожну групу). Його використання припустиме, якщо рівень сумісності для БД виставлений у значення 90. Він виконує «розворот» таблиці, так що стовпці стають рядками, а рядки - стовпцями (подібно транспонуванню матриці). Таким чином, значення стовпця, ім'я якого вказується після оператора UNPIVOT (він був аргументом агрегаційної функції в операторі PIVOT) формують різні значення для груп. Їх назви формуються значеннями стовпця, ім'я якого задане після ключового слова FOR, а перелік можливих значень заданий після ключового слова IN. Одержувана таблиця не містить «порожніх» груп (для яких оператор PIVOT генерував значення NULL(0)).

Секція WHERE.

Після ключового слова WHERE іде умова пошуку, відповідно до виконання (істинністю) якого приймається рішення про добування чергового рядка із джерела даних (таблиць(і)) командою SELECT. Слід зазначити, що умова може включати логічні операції NOT, AND, OR, LIKE, BETWEEN, IS [NOT] NULL, ALL, SOME, ANY, EXISTS, операції порівняння, FREETEXT, вкладені запити SELECT.

Секція GROUP BY.

Використання цієї секції приводить до угруповання результуючої множини рядків по обраних стовпцях. У загальному виді виглядає в такий спосіб:

GROUP BY [ALL] список_стовпців_по_яких_відбувається_угруповання

[WITH {CUBE | ROLLUP}]

Опція ALL примушує використовувати при формуванні груп навіть ті рядки, які були відкинуті в секції WHERE, тому що не задовольняли умові пошуку. Однак ALL не можна використовувати з опціями WITH CUBE або WITH ROLLUP, а також при звертанні до вилучених таблиць, якщо використовується секція WHERE.

Стовпці, по яких здійснюється угруповання, не можуть мати типів даних text, ntext і image. Якщо опції WITH CUBE або WITH ROLLUP не використовуються, то кількість стовпців обмежується тільки їхньою сумарною довжиною, що не повинна перевищувати 8060 байт. Якщо ж опції WITH CUBE або WITH ROLLUP використовуються, то максимальна кількість стовпців, по яких здійснюється угруповання, не може перевищувати 10. Слід зазначити, що перелік стовпців, по яких відбувається угруповання, повинен бути повністю включений в select_list секції SELECT, причому використовувати символ * стає неможливим.

Опція WITH CUBE для кожної групи генерує рядки, що мають усілякі комбінації значень NULL для групоутворюючих стовпців.

Опція WITH ROLLUP для кожної одержуваної групи генерує значення NULL, таким чином, щоб були отримані набори рядків виду: (c1, c2, ..., cn-1, NULL), (c1, c2, ..., NULL, NULL), (c1, c2, ..., cn-3, NULL, NULL, NULL), ..., (NULL, ... NULL), де c1, c2, ..., cn - імена групоутворюючих стовпців.

Секція HAVING.

Аналогічно секції WHERE, але застосовується для груп, отриманих у результаті використання секції GROUP BY. Якщо GROUP BY не використовується, то повністю еквівалентно WHERE, але спрацьовує після нього. У секції HAVING не можна використовувати дані типів text, ntext і image. Якщо секція GROUP BY використовується з опцією ALL, то секція HAVING «заміщає» (має можливість обмежити кількість рядків) дію цієї опції.

Оператори UNION, EXCEPT і INTERSECT.

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

Оператор UNION [ALL] виконує просте об'єднання двох множин рядків, причому використання опції ALL приводить до того, що рядки, що дублюються, зберігаються, у противному випадку - з кожного набору рядків, що дублюються, залишається тільки один.

Оператори EXCEPT і INTERSECT не можуть використовуватися зі стовпцями (виразами), які мають типи даних xml, text, ntext, image, а також користувальницькі типи даних, які не приводяться до типу varbinary. Використання цих операторів припустимо, якщо рівень сумісності для БД виставлений у значення 90. Якщо використовується секція ORDER BY, то в ній повинні використовуватися імена стовпців із запиту SELECT лівої частини оператора EXCEPT або INTERSECT.

За допомогою оператора EXCEPT вибираються неповторювані рядки з результуючої множини рядків «лівого» запиту, які відсутні в результуючій множині рядків «правого» запиту.

За допомогою оператора INTERSECT вибираються неповторювані рядки з результуючої множини рядків «лівого» запиту, які входять і в результуючу множину рядків «правого» запиту.

Секція ORDER BY.

У цій секції здійснюється впорядкування одержуваної множині рядків, і вона має такий загальний вигляд:

ORDER BY назва_стовпця__або__синонім_назви_стовпця

[COLLATE назва]

[ {ASC | DESC} ]

[, і т.п.]

Варто врахувати, що стовпець, по якому відбувається впорядкування результуючої множини рядків, може бути виразом, але цей вираз не повинен давати константне значення в результаті свого обчислення, а також не повинен містити функцій ад'єктивування (наприклад, avg, sum, min, max і т.п.). У переліку імен стовпців секції ORDER BY можуть бути стовпці, які відсутні в select_list, однак це можливо, якщо не використовується опція DISTINCT, і якщо відсутні секції GROUP BY і UNION. Крім того, якщо використовується оператор UNION, то в переліку імен стовпців ORDER BY можуть вказуватися тільки імена або псевдоніми стовпців з select_list 1-ої з поєднуваних команд SELECT. Слід також зазначити, що стовпці, використовувані в переліку ORDER BY, не можуть мати типи даних text, ntext і image.

Опція COLLATE задає назву (collation name) кодової сторінки і, відповідно, правила упорядкування символів і застосовна тільки для стовпців, що мають типи даних char, nchar, varchar, nvarchar.

Опції ASC(за замовчуванням) або DESC задають тип сортування: по зростанню або по убуванню.

Слід зазначити, що значення NULL інтерпретуються як мінімально можливі. Кількість імен або псевдонімів стовпців у переліку ORDER BY не обмежено, але існує обмеження на сумарну довжину одного рядка тимчасової таблиці, використовуваної для сортування. Воно становить 8060 байт.

Секція COMPUTE.

Ця секція призначена для одержання і виводу деякої статистичної інформації про отриману у результаті виконання команди SELECT множину рядків і в загальному випадку виглядає так:

COMPUTE

{avg | count | max | min | stdev | stdevp | var | varp | sum}

(вираз) [, і т.п.]

[BY вираз [, і т.п.] ]

Таким чином, COMPUTE здійснює вивід рядка, що містить статистичну інформацію, отриману за допомогою якоїсь(якихось) із припустимих агрегаційних функцій, як аргумент якої(яких) виступає вираз, що містить стовпці таблиці. Ця статистична інформація генерується або для всієї множини одержуваних рядків, або для груп рядків (угруповання відбувається у випадку використання опції BY, після якої йде список імен стовпців, по яких формуються групи).

Слід зазначити, що стовпці, використовувані в секції COMPUTE, не можуть мати типи даних text, ntext, image. Також, у випадку використання COMPUTE, необхідно застосовувати впорядкування, виконане в секції ORDER BY. Причому список імен стовпців секції COMPUTE повинен містити тільки стовпці, використовувані в секції ORDER BY, хоча не всі стовпці, використовувані в секції ORDER BY, повинні міститися в секції COMPUTE.