Вопросы по SQL на собеседовании. Часть 1

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

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

Поэтому в случаях, когда точно известно, что дубликатов не будет, или когда их наличие не является проблемой, рекомендуется использовать UNION ALL из соображений производительности.

ANSI-стандарт SQL определяет пять типов операторов JOIN:

• INNER JOIN: Возвращает все строки, для которых есть хотя бы одно совпадение в ОБЕИХ таблицах. Это тип соединения по умолчанию, если не указан конкретный тип JOIN.
• LEFT JOIN (или LEFT OUTER JOIN): Возвращает все строки из левой таблицы и соответствующие строки из правой таблицы; т.е. результаты будут содержать все записи из левой таблицы, даже если условие JOIN не найдёт соответствующих записей в правой таблице.
• RIGHT JOIN (или RIGHT OUTER JOIN): Возвращает все строки из правой таблицы и соответствующие строки из левой таблицы. Это полная противоположность LEFT JOIN; т.е. результаты будут содержать все записи из правой таблицы, даже если условие JOIN не найдет соответствующих записей в левой таблице.
• FULL JOIN (или FULL OUTER JOIN): Возвращает все строки, для которых есть совпадения в ЛЮБОЙ из таблиц. Концептуально FULL JOIN сочетает в себе эффект применения как LEFT JOIN, так и RIGHT JOIN; т.е. его набор результатов эквивалентен выполнению UNION результатов левого и правого внешних запросов.
• CROSS JOIN: Возвращает все записи, в которых каждая строка из первой таблицы объединяется с каждой строкой из второй таблицы (т.е. возвращает декартово произведение наборов строк из соединяемых таблиц).

Учитывая предоставленную выборку данных, результат этого запроса будет пустым. Причина этого заключается в следующем: если условие SQL NOT IN содержит какие-либо значения null, то внешний запрос в данном случае вернет пустой результат, даже если существует много id в таблице runners, которые соответствуют winner_ids в таблице races.

Зная это, запрос, позволяющий избежать этой проблемы, будет выглядеть следующим образом:

SELECT * FROM runners WHERE id NOT IN (SELECT winner_id FROM races WHERE winner_id IS NOT null)

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

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

idnum  pageseq  doctext
1      5        5
2      6        6
NULL   0        NULL

Условие EXISTS в приведенном выше запросе всегда будет истинным, поскольку ID не является полем таблицы  dbo.docs. Таким образом, оно будет ссылаться на таблицу envelope, сравнивая её саму с собой!

Значение idnum, равное NULL, не будет установлено, поскольку соединение со значением NULL не даст результата при попытке сопоставления с любым значением из таблицы envelope.

Запрос вернет 50 строк, формируя «декартово произведение» или «перекрёстное соединение», которое является результатом по умолчанию при отсутствии условия WHERE.

В SQL Server, PostgreSQL и SQLite это можно сделать с помощью ключевого слова except следующим образом:

select * from test_a
except
select * from test_b;

В Oracle вместо этого используется ключевое слово minus. Обратите внимание, что при наличии нескольких столбцов (например ID и Name) в запросах Oracle следует явно указывать столбец: Select ID from test_a minus select ID from test_b

MySQL не поддерживает функцию except. Однако существует стандартное SQL-решение, которое работает во всех вышеперечисленных СУБД, включая MySQL:

select a.id
from test_a a
left join test_b b on a.id = b.id
where b.id is null;

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

SELECT TOP (1) Salary FROM
(
    SELECT DISTINCT TOP (10) Salary FROM Employee ORDER BY Salary DESC
) AS Emp ORDER BY Salary

Принцип работы:

Сначала запрос SELECT DISTINCT TOP (10) Salary FROM Employee ORDER BY Salary DESC выберет 10 самых высоких зарплат в таблице. Однако эти зарплаты будут отсортированы по убыванию. Это было необходимо для работы первого запроса, но если теперь выбрать верхнюю строку из этого списка, вы получите самую высокую зарплату, а не 10-ю.

Поэтому второй запрос переупорядочивает эти 10 записей по возрастанию (используя порядок сортировки по умолчанию), а затем выбирает верхнюю запись (которая теперь будет наименьшей из этих 10 зарплат).

Не все базы данных поддерживают ключевое слово TOP. Например, в MySQL и PostgreSQL используется ключевое слово LIMIT, как показано ниже:

SELECT Salary FROM
(
    SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 10
) AS Emp ORDER BY Salary LIMIT 1;

Или в более краткой форме для MySQL:

SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 9,1;

А в PostgreSQL это можно сделать так:

SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 9;

Вы можете избежать дубликатов с помощью UNION ALL и при этом работать гораздо быстрее, чем с UNION DISTINCT (который фактически идентичен UNION), выполнив запрос следующим образом:

SELECT * FROM mytable WHERE a=X UNION ALL SELECT * FROM mytable WHERE b=Y AND a!=X

Ключевым моментом является часть AND a!=X. Это дает вам преимущества команды UNION, избегая при этом значительного снижения производительности.

SELECT
      u.user_id,
      username,
      training_id,
      training_date,
      count( user_training_id ) AS count
  FROM users u JOIN training_details t ON t.user_id = u.user_id
  GROUP BY u.user_id,
           username,
           training_id,
           training_date
  HAVING count( user_training_id ) > 1
  ORDER BY training_date DESC;

user_id  username      training_id  training_date             count
4        Lisa Romero   2            August, 04 2015 00:00:00  2
4        Lisa Romero   3            August, 03 2015 00:00:00  2
1        John Doe      1            August, 02 2015 00:00:00  3
3        Alice Jones   2            August, 02 2015 00:00:00  2

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

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

Во многих SQL-системах просмотреть план выполнения запроса можно с помощью ключевого слова EXPLAIN. В Microsoft SQL Server анализатор запросов имеет опцию “Show Execution Plan” (находится в выпадающем меню Query). Если эта опция включена, то при запуске запроса в отдельном окне будут отображаться планы выполнения запроса.