T-SQL. Почему пишут WHERE 1=1?
T-SQL. Почему пишут WHERE 1=1?
Новичков часто ставит в тупик конструкция типа:
SELECT *
FROM [dbo].[bkp_product]
WHERE 1=1
AND [model] = ‘Product 1’
AND [quantity] = 939
Зачем пишут в коде 1=1?
Ответ — для удобства при отладке кода.
Покажу на примере. Напишем простой селект
SELECT *
FROM [Table_1] c
[id] = 1AND [c].[v_sm] <> ‘d’
AND [c].[qty] >= 1000
Допустим при отладке кода, нам нужно из условия WHERE убрать [c].[id] = 1
Что мы делаем, комментируем условие [c].[id] = 1 и AND
SELECT *
FROM [Table_1] c
—[c].[id] = 1
[c].[v_sm] <> ‘d’
AND [c].[qty] >= 1000
А если у нас изначально было написано
SELECT *
FROM [Table_1] c
WHERE 1=1
AND [c].[id] = 1
AND [c].[v_sm] <> ‘d’
AND [c].
[qty] >= 1000
То нам нужно будет закомментировать всего одну строку
SELECT *
FROM [Table_1] c
WHERE 1=1
—AND [c].[id] = 1
AND [c].[v_sm] <> ‘d’
AND [c].[qty] >= 1000
А теперь представьте, что нам нужно вывести 0 строк (pl/sql: rownum<=0)
SELECT TOP 0 *
FROM [Table_1] c
[c].[id] = 1
AND [c].[v_sm] <> ‘d’
AND [c].[qty] >= 1000
SELECT *
FROM [Table_1] c
WHERE 1=0
AND
[c].
[id] = 1
AND [c].[v_sm] <> ‘d’
AND [c].[qty] >= 1000
Поэтому не пугайтесь такой конструкции, а берите ее на вооружение 🙂
Популярные сообщения из этого блога
PL/SQL. Oracle shrink tablespace — сжатие табличного пространства
Привожу простой пример заполнения и последующего сжатия tablespace . Пример привожу на системе, которую настроили в этом посте О том, как хранятся данные хорошо показано здесь И так в чем нюансы при сжатии табличного пространства. Создадим табличное пространство – MGMT ( CREATE TABLESPACE MGMT DATAFILE ‘ C :\ ORACLEXE \ APP \ ORACLE \ ORADATA \ XE \ MGMT . DBF ‘ .
Далее…
PL/SQL. Установка Oracle на Windows
Минимально необходимые действия, что бы установить Oracle на windows. Пошаговая инструкция. Пример привожу на Windows 7 32bit 1. Заходим на сайт www.oracle.com 2. Регистрируемся 3. В разделе Downloads скачиваем Oracle Database Express Edition 4. Распаковываем архив 5. Запускаем установку. Задаем пароль 6.
Базу данных мы установили. Теперь запустим SQL Developer. Скачиваем 7. Распаковываем 8. Запускаем 9. Для работы SQL Developer необходима Java JDK. Если данное поле автоматически не заполнилось, устанавливаем Java 10. В разделе Downloads находим Java. Версий несколько нам Java SE хватит с лихвой Скачиваем 11. Запускаем установку 12. Открываем папку куда установлена Java JDK, копируем путь 13. Возвращаемся к установке SQL Developer. Вставляем путь Java JDK 14. SQL Developer установлена, проверим работоспособность 15. Создаем подключение с базе данных Oracle Заполняем поля
Далее…
7 распространенных ошибок в SQL-запросах, которые делал каждый (почти) / Хабр
Сегодня SQL используют уже буквально все на свете: и аналитики, и программисты, и тестировщики, и т.д. Отчасти это связано с тем, что базовые возможности этого языка легко освоить.
Однако работая с большим количеством junior-ов, мы раз от раза находим в их решениях одни и те же ошибки.
Реально — иногда просто создается ощущение, что они копируют друг у друга код.
Кстати, иногда такая же участь постигает и специалистов более высокого полета.
Сегодня мы решили собрать 7 таких ошибок в одном месте, чтобы как можно меньше людей их совершали.
Примечание: Ошибки будут 2 видов — реальные ошибки и своего рода best practices, которым часто не следуют.
Но, обо всем по порядку 🙂
Кстати, будем рады видеть вас в своих социальных сетях — ВКонтакте Телеграм Инстаграм
1. Преобразование типов
Мы привыкли, что в математике мы всегда можем разделить одно число на другое и получить ответ. Если нацело не получается, то в виде дроби.
В SQL это не всегда так работает. Например, в PostgreSQL деление двух целых чисел друг на друга даст целочисленный ответ. Это можно проверить как для целочисленных столбцов, так и для чисел.
SELECT a/b FROM demo # столбец целых чисел SELECT 1 / 2 # 0
Аналогичные запросы, например, в MySQL дадут дробное число, как и положено.
Если Вы точно не уверены или хотите подстраховаться, то лучше всегда явно делать преобразование типов. Например:
SELECT a::NUMERIC/b FROM demo SELECT a*1.0/b FROM demo SELECT CAST(1 AS FLOAT)/2 FROM demo
Все перечисленные примеры дадут нужный ответ.
2. HAVING вместо WHERE
Часто встречается ошибка — оператор HAVING используется вместо WHERE в запросах с агрегацией. Это неверно!
WHERE производит фильтрацию строк в исходном наборе данных, отсеивая неподходящие. После этого GROUP BY формирует группы и оператор HAVING производит фильтрацию уже целых групп (будто группа — одно запись).
Например:
SELECT date, COUNT(*) FROM transactions t WHERE date >= '2019-01-01' GROUP BY date HAVING COUNT(*) = 2
Здесь мы сначала отсеиваем строки, в которых хранятся записи до 2019 года. После этого формируем группы и оставляем только те, в которых ровно две записи.
Некоторые же пишут так:
SELECT date, COUNT(*) FROM transactions t GROUP BY date HAVING COUNT(*) = 2 AND date >= '2019-01-01'
Так делать не нужно 🙂
Кстати, для закрепления этой темы мы специально делали задачку «Отфильтрованные продажи» у себя на платформе.
3. Алиасы и план запроса
Если «проговаривать SQL-запрос» словами, то получится что-то такое:
В таблице есть старая цена, а есть новая цена. Их разность я назову diff. Я хочу отобрать только те строки, где значение diff больше 100.
Звучит вполне логично. Но в SQL прям так реализовать не получится — и многие попадаются в эту ловушку.
Вот неправильный запрос:
SELECT old_price - new_price AS diff FROM goods WHERE diff > 100
Ошибка его заключается в том, что мы используем алиас столбца diff внутри оператора WHERE.
Да, это выглядит вполне логичным, но мы не можем так сделать из-за порядка выполнения операторов в SQL-запросе. Дело в том, что фильтр WHERE выполняется сильно раньше оператора SELECT (а значит и AS). Соответственно, в момент выполнения столбца diff просто не существует. Об этом, кстати, и говорит ошибка:
ERROR: column "diff" does not exist
Правильно будет использовать подзапрос или переписать запрос следующим образом:
SELECT old_price - new_price AS diff FROM goods WHERE old_price - new_price > 100
Важно: Внутри ORDER BY вы можете указывать алиас — этот оператор выполняется уже после SELECT.
Кстати, мы тут делали карточку, где наглядно показывается последовательность выполнения операторов. Возможно, это вам пригодится.
4. Не использовать COALESCE
Пришло время неочевидных пунктов. Но сейчас мы поясним свои чаяния.
COALESCE — это оператор, который принимает N значений и возвращает первое, которое не NULL. Если все NULL, то вернется NULL.
Нужен этот оператор для того, чтобы в расчеты случайно не попадали пропуски. Такие пропуски всегда сложно заметить, потому что при расчете среднего на основании ста тысяч строк вы вряд ли заметите подвох, даже если 1000 просто будет отсутствовать. Обычно такие численные пропуски заполняют средними значениями/минимальными/максимальными/медианными/средними или с помощью какой-то интерполяции — зависит от задачи.
Мы же рассмотрим нечисловой пример, а вполне себе бизнесовый. Например, есть таблица клиентов Clients. В поле name заносится имя пользователя.
Отдел маркетинга решил сделать email-рассылку, которая начинается с фразы:
Приветствуем, имя_пользователя!
Очевидно, что если name is NULL, то это превратится в тыкву:
Приветствуем, !
Вот в таких случаях и помогает COALESCE:
SELECT COALESCE(name, 'Дорогой друг') FROM Clients
Совет: Лучше всегда перестраховываться.
Особенно это касается вычислений и агрегирований — там вы не найдете ошибку примерно никогда, так что лучше подложить соломку.
5. Игнорирование CASE
Если вы используете CASE, то иногда вы можете сократить свои запросы в несколько раз.
Вот, например, была задача — вывести поле sum со знаком «-», если type=1 и со знаком «+», если type=0.
Пользователь предложил такое решение:
SELECT id, sum FROM transactions t WHERE type = 0 UNION ALL SELECT id, -sum FROM transactions t WHERE type = 1
В целом, не так плохо. Но это всего лишь промежуточный запрос, задача была намного масштабней и таких конструкций в итоге было наворочено очень много.
А вот то же самое с CASE:
SELECT id, CASE WHEN type = 0 THEN sum ELSE -sum END FROM transactions t
Согласитесь, получше?
Так более того, CASE можно использовать еще много для чего. Например, чтобы сделать из «длинной» таблицы «широкую».
А еще, кстати, COALESCE, который мы обсуждали выше — это просто «синтаксический сахар» и обертка вокруг CASE.
Если интересно — мы подробно это описали в статье.
6. Лишние подзапросы
Из-за того, что многие пишут SQL-запросы также, как это «звучит» в их голове, получается нагромождение подзапросов.
Это проходит с опытом — начинаешь буквально «мыслить на SQL» и все становится ок. Но первое время появляются такие штуки:
SELECT id, LAG(neg) OVER(ORDER BY id) AS lg FROM ( SELECT id, sm, -sm AS neg FROM ( SELECT id, sum AS sm FROM transactions t ) t ) t1
И это еще не все — можно и побольше накрутить. Но зачем так, если можно так:
SELECT id, LAG(-sum) OVER(ORDER BY id) FROM transactions t
Совет: Если пока сложно, не надо сразу бросаться писать оптимизированными конструкциями. Напишите сначала, как сможете, а потом пытайтесь сократить.
Как говорил дядюшка Кнут:
Преждевременная оптимизация — корень всех зол
7. Неправильное использование оконных функций
Вообще говоря, оконные функции — довольно продвинутый инструмент.
Считается, что им владеют специалисты уровня Middle и выше. Но по факту, их нужно знать всем — сейчас без них уже сложно жить (это чистое имхо).
И если базовые вещи по оконным функциям можно освоить довольно быстро, то всякая экзотика и нестандартное поведение осваивается, как правило, только на собственных шишках.
Одна из таких вещей — поведение оконной функции LAST_VALUE и прочих.
Например, когда мы пишем запрос:
WITH cte AS (
SELECT 'Marketing' AS department, 50 AS employees, 2018 AS year
UNION
SELECT 'Marketing' AS department, 10 AS employees, 2019 AS year
union
SELECT 'Sales' AS department, 35 AS employees, 2018 AS year
UNION
SELECT 'Sales' AS department, 25 AS employees, 2019 AS year
)
SELECT c.*,
LAST_VALUE(employees) OVER (PARTITION BY department ORDER BY year) AS emp
FROM cte cМы ожидаем увидеть 2 раза по 10 для департамента Маркетинг и 2 раза по 25 для Продаж. Однако такой запрос дает иную картину:
Получается, что запрос тупо продублировал значения из столбца employees.
Как так?
Лезем в документацию PostgreSQL и видим:
Заметьте, что функции first_value, last_value и nth_value рассматривают только строки в «рамке окна», которая по умолчанию содержит строки от начала раздела до последней родственной строки для текущей.
Ага, вот и ответ. То есть каждый раз у нас окно — это не весь набор строк, а только до текущей строки.
Получается, есть два способа вылечить такое поведение:
Вот, например, второй вариант:
WITH cte AS (
SELECT 'Marketing' AS department, 50 AS employees, 2018 AS year
UNION
SELECT 'Marketing' AS department, 10 AS employees, 2019 AS year
union
SELECT 'Sales' AS department, 35 AS employees, 2018 AS year
UNION
SELECT 'Sales' AS department, 25 AS employees, 2019 AS year
)
SELECT c.*,
LAST_VALUE(employees) OVER (
PARTITION BY department
ORDER BY year ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS emp
FROM cte cКстати, такую тему подняла наша подписчица в Телеграме под постом «7 самых важных оконных функций».
Спасибо ей!
А вас рады будем видеть в числе подписчиков 🙂
Эпилог
Эти 7 ошибок — не единственные, которые часто встречаются среди новичков и даже профессионалов. У нас есть еще одна пачка тезисов по этому поводу — но это уже тема другой статьи.
Если вам есть что добавить — будем рады продолжить обсуждение в комментариях. Возможно, чей-то код станет лучше и чище в результате нашей беседы 🙂
Что означает «ГДЕ 1» в SQL?
спросил
Изменено 12 лет, 1 месяц назад
Просмотрено 35 тысяч раз
Иногда phpMyAdmin генерирует запросы типа:
SELECT * ИЗ `предметов` ГДЕ 1 ПРЕДЕЛ 0 , 30
Интересно, если ГДЕ 1 имеет какое-либо значение в подобном запросе.
3
Нет.
Это означает, что ВСЕГДА ИСТИНА , поэтому это не повлияет на фильтрацию вашего запроса. Планировщик запросов, вероятно, проигнорирует это предложение.
Обычно используется при построении клиентского запроса путем объединения условий фильтрации.
Итак, если ваш базовый запрос хранится в такой строке (пример на PHP, но он, безусловно, применим ко многим другим языкам):
$sql = "выберите * из foo, где 1";
Затем вы можете просто объединить множество условий фильтрации с предложением AND независимо от того, используете ли вы первое условие или нет:
// следует псевдо-php...
если ($filter_by_name) {
$sql = $sql. "и имя =?";
}
если ($filter_by_number) {
$sql = $sql. "и число = ?";
}
// так далее и так далее.
3
ГДЕ 1 — это синоним «истинного» или «всего».
Это ярлык, поэтому им не нужно удалять предложение where из сгенерированного SQL.
В противном случае вы бы написали что-то вроде этого:
$sql = "SELECT * FROM `errors`";
если ($hasWhereClause == true) {
$sql .= "ГДЕ $whereClause";
}
$sql .= "ОГРАНИЧЕНИЕ 0, 30";
Я предполагаю, что это артефакт конкатенации строк: если условия не указаны, выводится «1». Таким образом, не нужно решать, следует ли выводить ключевое слово WHERE или нет.
Также всегда можно вывести и просто соединить условия с «И» и «ИЛИ». Вам не нужно решать, что первое условие не должно начинаться с ключевых слов AND, OR.
Зарегистрируйтесь или войдите в систему
Зарегистрируйтесь с помощью Google
Зарегистрироваться через Facebook
Зарегистрируйтесь, используя электронную почту и пароль
Опубликовать как гость
Электронная почта
Требуется, но не отображается
Опубликовать как гость
Электронная почта
Требуется, но не отображается
mysql — Почему мы используем Group by 1 и Group by 1,2,3 в SQL-запросе?
Рассмотрим следующий случай:
+------------+--------------+-----------+ | дата | услуги | загрузки | +------------+---------------+-----------+ | 2016-05-31 | Приложения | 1 | | 2016-05-31 | Приложения | 1 | | 2016-05-31 | Приложения | 1 | | 2016-05-31 | Приложения | 1 | | 2016-05-31 | Видео | 1 | | 2016-05-31 | Видео | 1 | | 2016-06-01 | Приложения | 3 | | 2016-06-01 | Приложения | 4 | | 2016-06-01 | Видео | 2 | | 2016-06-01 | Приложения | 2 | +------------+---------------+-----------+
Вам необходимо узнать количество загрузок на службу в день, считая приложения и приложения одной и той же службой.
Если сгруппировать по дате службы , то приложений и приложений будут считаться отдельными службами.
В этом случае запрос будет таким:
выберите дату, услуги, сумму (загрузок) в качестве загрузок из test.zvijay_test группировать по дате,услуги
И вывод:
+-------------+--------------+-----------+ | дата | услуги | загрузки | +------------+---------------+-----------+ | 2016-05-31 | Приложения | 2 | | 2016-05-31 | Приложения | 2 | | 2016-05-31 | Видео | 2 | | 2016-06-01 | Приложения | 4 | | 2016-06-01 | Приложения | 5 | | 2016-06-01 | Видео | 2 | +------------+---------------+-----------+
Но это не то, что вам нужно, поскольку приложения и приложения должны быть сгруппированы. Так что мы можем сделать?
Один из способов — заменить Apps на Applications , используя выражение CASE или функцию IF , а затем сгруппировать их по службам следующим образом:
select свидание, if(services='Apps','Applications',services) как сервисы, сумма(загрузки) как загрузки из test.
