Разница в выводах OR и UNION в sql. Union отличие от union all

sql - Разница в выводах OR и UNION в sql

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

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

--Call this Query 1 (or Q1) Select c, d from T Where --The following line is the "first disjunct" c = 'c1' OR --While this is the "second disjunct" d < 'd1';

а также

--Call this Query 2 (or Q2) --Call this Query 2 part A (Q2a) select c, d from T Where c = 'c1' UNION --Call this Query 2 part B (Q2b) Select c, d from T Where d < 'd1';

Почему UNION соответствует тем же самым записям, что и OR

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

| Row | Disjunct / Where clause | Returned By | | # | c = 'c1' | d < 'd1' | Q1 | Q2a | Q2b | Q2 | |-------------------------------------------------------------| | | A | B | W | X | Y | Z | |-------------------------------------------------------------| | R1 | TRUE | TRUE | Yes | Yes | Yes | Yes | | R2 | TRUE | FALSE | Yes | Yes | No | Yes | | R3 | FALSE | TRUE | Yes | No | Yes | Yes | | R4 | FALSE | FALSE | No | No | No | No |

(Если поля c или d равны NULL тогда сравнения будут считаться ложными, поэтому нет необходимости рассматривать NULL отдельно.)

Q1 находит все и только те строки, где либо столбец A, либо столбец B является истинным.
Q2a находит все и только те строки, где столбец A истинен.
Q2b находит все и только те строки, где столбец B истинен.
Q3 находит все и только те строки, найденные одним или обоими Q2a или Q2b.

Как вы можете видеть, столбец W читает то же, что и столбец Z, то есть Q1 находит строку тогда и только тогда, когда она также найдена Q2.

Как UNION и UNION ALL отличаются от OR

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

Во-вторых, версии OR, UNION и UNION ALL отличаются своим обращением с дубликатами:

OR: возвращает все записи, соответствующие критериям, ни добавление, ни удаление дубликатов
UNION: возвращает все записи, соответствующие критериям, но удаляя любые дубликаты
UNION ALL: возвращает все записи, соответствующие критериям, с возможностью возврата некоторых записей по обе стороны UNION и, следовательно, создания дубликатов.

Это можно проиллюстрировать, используя следующие данные (также доступно здесь - rextester.com/BLNTJT55740):

create table UnionVsOr ( id int primary key, c varchar(5), d varchar(5), Notes varchar(100) ) insert into UnionVsOr values (1, 'c1', 'd0', 'Meets both criteria, possible duplicate (1)' ), (2, 'c1', 'd0', 'Meets both criteria, possible duplicate (2)' ), (3, 'c1', 'dX', 'Meets first criteria'), (4, 'cX', 'd0', 'Meets second criteria'), (5, 'cX', 'dX', 'Meets neither criteria')

Запросы OR, UNION и UNION ALL возвращают следующие наборы данных соответственно...

| c | d ------------ 1 | c1 | d0 2 | c1 | d0 --duplicate of row above retained 3 | c1 | dX 4 | cX | d0 | c | d ------------ 1 | c1 | d0 --duplicate removed 2 | c1 | dX 3 | cX | d0 | c | d ------------ 1 | c1 | d0 2 | c1 | d0 --duplicate of row above retained 3 | c1 | dX 4 | c1 | d0 5 | c1 | d0 --duplcates added as returned by both Q2a and Q2b 6 | cX | d0

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

qaru.site

sql - В чем разница между JOIN и UNION?

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

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

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

Вот визуальное изображение соединения. Столбцы A и Bs объединены в один результат.

Каждая строка в результате содержит столбцы из BOTH таблицы A и B. Строки создаются, когда столбцы из одной таблицы соответствуют столбцам из другого. Это совпадение называется условием объединения.

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

Теперь сравните приведенное выше изображение с изображением объединения. В объединении каждая строка в результате получается из одной таблицы ИЛИ другой. В объединении столбцы arent объединены для создания результатов, строки объединяются.

Оба объединения и объединения могут использоваться для объединения данных из одной или нескольких таблиц в один результат. Они оба обходятся по-разному. В то время как объединение используется для объединения столбцов из разных таблиц, объединение используется для объединения строк.

Источник

qaru.site

Некоторые примеры нестандартных возможностей синтаксиса Oracle SQL / Хабр

Введение

Примерно полтора года назад я сдал экзамены на OCP Advanced PL/SQL Developer, далее специфика работы несколько изменилась, и после стандартного производственного использования Oracle я занимался разработкой архитектуры двухуровневой клиент-серверной системы на основе Oracle для нужд компьютерной лингвистики. Далее был этап развития системы и решения наукоемких задач на ее основе, пришлось заниматься использованием иерархических запросов в решении нестандартных задач и другими специфическими вещами. Результатом углубления в специфику стало некоторое «проседание» базы, а значит, наступило время снова просмотреть материалы, использовавшиеся для подготовки к экзаменам.

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

Несколько слов о специфике оператора order by

Как вы думаете, будет ли ошибка в результате выполнения данного запроса?--Запрос №1 select * from dual order by 1+2||dummy Ответ: ошибки не будет, т.к. спецификация order by определена следующим образом:ORDER BY {col(s)|expression|numeric_pos} [ASC|DESC] [NULLS FIRST|LAST]; Где expression — A sort key expression, such as numeric, string, and datetime expressions. Expression can also be a row value expression such as a scalar subquery or case expression. Подробнее, например, здесь download.oracle.com/javadb/10.6.2.1/ref/rrefsqlj13658.html

Таким образом, мы отсортировали таблицу dual по выражению «3X», что бессмысленно, однако в качестве выражения для сортировки можно, например, использовать выражение с функцией substr. Важна сама возможность использования выражений.

Как мы знаем, таблица dual содержит один столбец, вернет ли ошибку такой запрос:

--Запрос №2 select * from dual order by 2 А такой?--Запрос №3 select * from dual order by 2+0 В первом случае мы имеем дело с позиционным указанием колонок – т.е. ссылкой на колонку №2, которой не существует, соответственно, получим ошибку.

Во втором случае мы имеем дело с выражением, т.е. это уже не номер колонки, а сортировка по числу 2, аналогичная сортировке по строке «3X» в запросе №1. Поэтому ошибки не будет.

Посмотрите на следующий пример. Какой порядок строк мы получим в результате выполнения этого запроса? Будет ли ошибка?

--Запрос №4 select 3, dummy from dual union all select 2, dummy from dual order by "3" Union all не сортирует строки объединяемых множеств (в отличии от union), т.е. без order by мы получим строки в указанном в запросе порядке (union all гарантирует сохранение исходного порядка строк). Теперь настала очередь order by, главный вопрос, что такое «3» в этом случае? Поскольку у нас использованы двойные кавычки ", а не одинарные ', то «3» – это алиас колонки. Как известно, операции с множествами требуют использования сходных типов данных, а имена колонок берутся из первого запроса, поскольку мы явно не указали имя первой колонки, то, по умолчанию, она получила имя выражения, т.е. «3». Работа с такими алиасами показана, например, в запросе №5 (главное не забывать про аппер-кейс).

Сортировка по умолчанию – всегда asc, т.е. результаты запроса №4 сортируются по первой колонке по возрастанию. Результат: строка «2, Х», потом «3, Х».

--Запрос №5 select substr(dummy,1,1) from dual order by "SUBSTR(DUMMY,1,1)" Повторим эксперимент из запроса №3 на множествах. Каким будет результат запроса?--Запрос №6 select 3, dummy from dual union all select 2, dummy from dual order by 2+0 Может показаться, что результат запроса будет следующим: строка «3, Х» потом «2, Х». Предпосылки для этого следующие: данный порядок гарантирует union all, а выражение 2+0 не повлияет на сортировку записей (что показано в запросе №7). Так бы оно и было, если бы не одно «но»: в операциях с множествами order by можно использовать только в конце составного запроса с именами или номерами столбцов первого запроса, выражения не допускаются: For compound queries containing set operators UNION, INTERSECT, MINUS, or UNION ALL, the ORDER BY clause must specify positions or aliases rather than explicit expressions. Also, the ORDER BY clause can appear only in the last component query. The ORDER BY clause orders all rows returned by the entire compound query. download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries005.htm#i2053998

Поэтому запрос №6 вернет ошибку.

--Запрос №7 select * from (select 3, dummy from dual union all select 2, dummy from dual) order by 2+0

Использование not in

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

Определите результат выполнения следующего запроса:

--Запрос №1 select * from dual where 1 not in (select 2 from dual union select null from dual) Для начала рассмотрим такой запрос: --Запрос №2 select * from dual where 2 in (select 2 from dual union select null from dual) Ничего необычного в запросе №2 нет: подзапрос возвращает множество из двух строк со значениями «2» и «null», условие where принимает значение true, весь запрос №2 возвращает 1 строку – стандартное поведение.

Теперь рассмотрим разницу в поведении in и not in, здесь нам понадобятся знания о null-арифметике: 1) Любые арифметические операции с null возвращают null 2) Булевые операторы с null работают следующим образом:

null AND false = false
false AND null = false
null AND true = null
true AND null = null
null OR true = true
true OR null = true
null OR false = null
false OR null = null

Пока все логично. Теперь рассмотрим, как работает IN в запросе №2: 2 IN (2,null) => (2=2) OR (2=null) => true OR null => true Рассмотрим работу NOT IN в запросе №1: 1 NOT IN (2, null) => (1!=2) AND (1!=null) => true AND null => null

Таким образом, условие where в запросе №1 преобразуется в null, если хотя бы один операнд null, поэтому весь запрос №1 приблизительно эквивалентен следующему запросу:

--Запрос №3 select * from dual where null=null Очевидно, что запрос №3 не вернет ни одной строки, соответственно, запрос №1 также не вернет ни одной строки.

Неявное преобразование типов

Тема явного и неявного преобразования типов очень обширна, поэтому, не пытаясь охватить ее в целом, я хотел бы рассмотреть лишь один пример. Пускай сегодня 10.09.11 10:00:00 и Оракл сконфигурирован так, что формат DD.MM.RR распознается по умолчанию, какой из запросов вернет одну строку?--Запрос №1 select * from dual where sysdate>'10.09.11'; --Запрос №2 select * from dual where sysdate||''='10.09.11'; Ответ – оба. Почему так и как это, вообще, возможно? Ответ лежит в механизме неявного преобразования типов.

Рассмотрим запрос №1: в where мы сравниваем дату со строкой, в этом случае Оракл пытается преобразовать строку в дату, если формат строки соответствует одному из форматов даты по умолчанию (формат даты по умолчанию для сессии можно посмотреть в параметре NLS_DATE_FORMAT, выполнив запрос select * from nls_session_parameters). Если формат строки не отвечает формату даты по умолчанию, то получим ошибку. В нашем случае форматы соответствуют и строка '10.09.11' преобразуется в дату 10.09.11 00:00:00, поскольку sysdate = 10.09.11 10:00:00, то запрос №1 вернет 1 строку.

Рассмотрим запрос №2: в where происходит конкатенация даты со строкой, результатом такой операции будет строка, причем при неявном преобразовании даты в строку происходит урезание данных до формата DD.MM.RR, т.е. мы сравниваем две строки '10.09.11' и '10.09.11'. В результате условие where выполняется, и запрос возвращает одну строку.

Заключение

Приведенный обзор не претендует на полноту освещения какой-либо функциональности Oracle, создан «for fun» и призван продемонстрировать несколько случаев нестандартного использования возможностей синтаксиса sql.

habr.com