How I Learned to Stop Worrying and Love NULL in SQL
You should not try to prevent NULL values — instead, write your query in a way to overcome its limitations.
2 years ago • 6 min read
The NULL value is a data type that represents an unknown value. It is not equivalent to empty string or zero. Suppose you have an employee table containing columns such as EmployeeId , Name , ContactNumber and an alternate contact number. This table has a few mandatory value columns like EmployeeId , Name , and ContactNumber . However, an alternate contact number is not required and therefore has an unknown value. Therefore a NULL value in this table represents missing or inadequate information. Here are other meanings NULL can have:
- Value Unknown
- Value not available
- Attribute not applicable
In this post we will consider how NULL is used in creating tables, querying, string operations, and functions. Screenshots in this post come from the Arctype SQL Client.
Allowing NULL in CREATE TABLE
To a table structure, we need to define whether the respective column allows NULL or not. For example, look at the following customer's table. The columns such as CustomerID , FirstName , LastName do not allow NULL values, whereas the Suffix , CompanyName , and SalesPerson columns can store NULL values.
Let’s insert a few records into this table using the following script.
Using NULL in the WHERE Clause
Now, suppose you want to fetch records for those customers who do not have an email address. The following query works fine, but it will not give us a row:
Values that are NULL cannot be queried using =
In the above select statement expression defines “Where the email address equals an UNKNOWN value”. In the SQL standard, we cannot compare a value to NULL. Instead, you refer to the value as IS NULL for this purpose. Note: There is a space between IS and NULL. If you remove space, it becomes a function ISNULL().
By using IS NULL instead of equals you can query for NULL values.
Integer, Decimal, and String Operations with NULL
Similarly, suppose you declared a variable but did not initialize its value. If you try to perform an arithmetic operation, it also returns NULL because SQL cannot determine the correct value for the variable, and it considers an UNKNOWN value.
Multiplying an integer by NULL returns NULL 
Multiplying a decimal by NULL returns NULL
NULL also plays an important role in string concatenation. Suppose you required the customer's full name in a single column, and you concatenate them using the pipe sign(||) .
Setting a string to NULL and then concatenating it returns NULL
Look at the result set — the query returns NULL in the concatenated string if any part of the string has NULL. For example, the person in Row 1 does not have a middle name. Its concatenated string is NULL as well, because SQL cannot validate the string value contains NULL.
There are many SQL functions available to overcome these NULL value issues in string concatenations. We’ll look at them later in this article.
The NULL value in SQL Aggregates
Suppose you use aggregate functions such as SUM, AVG, or MIN, MAX for NULL values. What do you think the expected outcome would be?
In aggregate functions NULL is ignored.
Look at the above figure: it calculated values for all aggregated functions. SQL ignores the NULLs in aggregate functions except for COUNT() and GROUP BY(). You get an error message if we try to use the aggregate function on all NULL values.
ORDER BY and GROUP BY with NULL
SQL considers the NULL values as the UNKNOWN values. Therefore, if we use ORDER By and GROUP by clause with NULL value columns, it treats them equally and sorts, group them. For example, in our customer table, we have NULLs in the MilddleName column. If we sort data using this column, it lists the NULL values at the end, as shown below.
NULL values appear last in ORDER BY
Before we use GROUP BY, let's insert one more record in the table. It has NULL values in most of the columns, as shown below.
Now, use the GROUP BY clause to group records based on their suffix.
GROUP BY does treat all NULL values equally.
As shown above, SQL treats these NULL values equally and groups them. You get two customer counts for records that do not have any suffix specified in the customers table.
Useful Functions for Working with NULL
We explored how SQL treats NULL values in different operations. In this section, we will explore a few valuable functions to avoid getting undesirable values due to NULL.
Using NULLIF in Postgres and MySQL
The NULLIF() function compares two input values.
● If both values are equal, it returns NULL.
● In case of mismatch, it returns the first value as an output.
For example, look at the output of the following NULLIF() functions.
NULLIF returns NULL if two values are equal 
NULLIF returns the first value if the values are not equal. 
NULLIF returns the first string in a string compare.
COALESCE function
The COALESCE() function accepts multiple input values and returns the first non-NULL value. We can specify the various data types in a single COALESCE() function and return the high precedence data type.
COALESCE returns the first non NULL data type in a list. 
Summary
The NULL value type is required in a relational database to represent an unknown or missing value. You need to use the appropriate SQL function to avoid getting undesired output for operations such as data concatenation, comparison, ORDER BY, or GROUP BY. You should not try to prevent NULL values — instead, write your query in a way to overcome its limitations. This way you will learn to love NULL.
How does the GROUP BY clause manage the NULL values?
How does the GROUP BY clause manage the NULL values? Does it correspond to the general treatment of these values?
4 Answers 4
You mean, when you GROUP BY a nullable column? All rows with a NULL in the column are treated as if NULL was another value.
If a grouping column contains null values, all null values are considered equal, and they are put into a single group.
Null values of a column are grouped as a separate group.
See SQL Fiddle demonstrating Group By and aggregate functions on nullable column
Group By groups all the records with NULL values.
According to the sql:2003 specification (the late draft can be found here for free) — 4.4.2 The null value:
. — Although the null value is neither equal to any other value nor not equal to any other value — it is unknown whether or not it is equal to any given value — in some contexts, multiple null values are treated together; for example, the <group by clause> treats all null values together
But above it is just a common informal notice about GROUP BY. As far as we dive deeper: to remain the GROUP BY technically consistent with the idea that it’s unknown whether two NULLs are equal or not to each other, they just use the definition of groups which is based not on the equality but on the "distinct values" (7.9 <group by clause>, General rules):
b) Otherwise, the result of the is a partitioning of the rows of T into the minimum number of groups such that, for each grouping column of each group, no two values of that grouping column are distinct.
And distinct values are defined in the following way:
3.1.6.8 distinct (of a pair of comparable values): Capable of being distinguished within a given context. Informally, not equal, not both null. A null value and a non-null value are distinct.
So, formally answering your question — NULLs are not treated as equal values but treated as not distinct values. In some cases, the equality treatment is used (obviously, you call that "general treatment"), but in other cases, the distinct treatment is used. That way, the spec remains logically consistent.
5 заданий по SQL с реальных собеседований
SQL — один из самых востребованных навыков в современной IT индустрии (на 3 месте по популярности согласно StackOverflow Developer Survey 2020, даже Python идет на 4 месте).
Само собой, конкуренция в этой сфере огромна и собеседования порой превращаются в сущую пытку — кандидатам дают огромные задачи, задают десятки каверзных вопросов, устраивают дизайн-интервью и все это на позицию Junior аналитика…
Сегодня Елизавета, аналитик и эксперт IT Resume, делится 5 задачами и вопросами, которые входят в программу подготовки к собеседованию SQL Interview. Это задачи с реальных собеседований в крупные IT-компании, и они разбиты по уровням — Junior, Middle и Senior. Попробуйте и вы свои силы — сможете их решить без подсказки ��
Вводные данные
Есть таблица анализов Analysis:
- an_id — ID анализа;
- an_name — название анализа;
- an_cost — себестоимость анализа;
- an_price — розничная цена анализа;
- an_group — группа анализов.
Есть таблица групп анализов Groups:
- gr_id — ID группы;
- gr_name — название группы;
- gr_temp — температурный режим хранения.
Есть таблица заказов Orders:
- ord_id — ID заказа;
- ord_datetime — дата и время заказа;
- ord_an — ID анализа.
Далее мы будем работать с этими таблицами.
Задача 1. Уровень: Junior
Формулировка: вывести название и цену для всех анализов, которые продавались 5 февраля 2020 и всю следующую неделю.
Это задача для начинающих специалистов. В ней проверяется базовое знание SELECT-запросов и умение работать с датой-временем.
Примечание На собеседованиях редко придираются к специфике диалекта — если Вы привыкли работать в PostgreSQL, то используйте привычные функции. Главное — правильно решить задачу.
Задача 2. Уровень: Middle
Формулировка: нарастающим итогом рассчитать, как увеличивалось количество проданных тестов каждый месяц каждого года с разбивкой по группе.
Эта задача уже более высокого уровня: ее можно давать как Middle, так и Junior специалистам. Здесь проверяется базовое понимание оконных функций, джоинов и группировок.
Примечание После того, как вы написали первую версию своего запроса, попробуйте его оптимизировать. Например, в данном примере мы используем CTE — обобщенные табличные выражения.
Задача 3: Уровень Senior
В этой задаче мы будем работать с другой таблицей (да, она будет всего одна). Сам запрос в этой задаче не сложный, но для его написания необходимо как бы уметь «мыслить на SQL».
Рассмотрим таблицу балансов клиентов:
ClientBalance(client_id, client_name, client_balance_date, client_balance_value)
- client_id — идентификатор клиента;
- client_name — ФИО клиента;
- client_balance_date — дата баланса клиента;
- client_balance_value — значение баланса клиента.
Формулировка: в данной таблице в какой-то момент времени появились полные дубли. Предложите способ для избавления от них без создания новой таблицы.
Вопрос 1. Уровень: Junior
Есть категория «хитрых» вопросов, которые особенно любят задавать Junior-специалистам. Хотя чего уж там, любым специалистам.
Один из таких видов — вопросы по темам, на которые в повседневной жизни не обращаешь внимания и о которых не задумываешься. Просто делаешь на автомате, а на собеседовании это стреляет. Ну или на проекте, когда код начинает работать неправильно. Но это другая история…
Вопрос: Как оператор GROUP BY обрабатывает поля с NULL?
Если Вы не знаете ответ на этот вопрос, то после прочтения ответа обязательно проверьте свои проекты — может у вас где-то закралась ошибка? ��
Учитывая, что NULL в SQL — просто отсутствие значения, то все значения NULL при группировке попадают в одну группу. Например, пусть есть таблица:
Тогда запрос select sum(score) from table group by name даст:
Вопрос 2. Уровень Middle
Этот вопрос не такой хитрый, как предыдущий, а вполне себе конкретный. Однако, он требует знания оконных функций и их тонкостей, а это исконное Middle требование.
Вопрос: В чем отличие функции RANK() от DENSE_RANK() ?
Примечание Кстати говоря, по мотивам этого вопроса очень любят давать задачи на собеседованиях в разных вариациях: пронумеровать строки с одинаковыми значениями без разрывов, с разрывами и так далее. Так что потренируйтесь на досуге ��
По аналогии с функцией ROW_NUMBER , оконные функции RANK и DENSE_RANK служат для нумерации строк. Однако, делают они это немного иначе: строки с одинаковым значениям получают одинаковый ранг. Для ряда задач это логично: если у двух сотрудников одинаковая зарплата, мы не можем сказать, что кто-то из них первый, а кто-то второй. Они одинаковы. Но при таком подходе возникает проблема: а какой ранг должен получить следующий сотрудник? Например, если первые два были одинаковые и у них ранги 1, то сотрудник со второй зарплатой в компании должен иметь ранг 2 или 3?
Эпилог
Мы разобрали с вами всего 5 возможных заданий, которые судьба и рекрутеры могут подкинуть вам на собеседовании. Однако, вариантов, на самом деле, масса. И чтобы реально подготовиться к собеседованию, нужно нарешать как можно больше задач, понять и прочувствовать теоретические аспекты и буквально научиться «мыслить на SQL». Именно на это и нацелена программа SQL Interview — так что будем рады помочь вам получить работу мечты!
И, помимо этого, не забывайте: SQL — чисто прикладной инструмент. Чтобы его освоить, нужно практиковаться, практиковаться и практиковаться. Выучить его можно буквально за несколько недель интенсивных занятий, а вот освоить на уровне Senior… порой на это уходят годы.
Группировка записей. Предложение group by
Объединяет записи с одинаковыми значениями в указанном списке полей в одну запись. Если инструкция SELECT содержит статистическую функцию языка SQL (например, Sum или Count), то для каждой записи будет вычислено итоговое значение.
Синтаксис
SELECT список_полей
FROM таблица
Where условие_отбора
GROUP BY группируемые_поля
где группируемые_поля — имена полей (до 10), которые используются для группирования записей.
Порядок имен полей в аргументе группируемые поля определяет уровень группирования для каждого из этих полей.
Предложение GROUP BY не является обязательным.
Итоговые значения опускаются, если инструкция SELECT не содержит статистической функции SQL.
Значения Null, которые находятся в полях, заданных в предложении GROUP BY, группируются и не опускаются. Однако статистические функции SQL не обрабатывают значения Null.
Предложение WHERE используется для исключения записей из группирования, а предложение HAVING — для применения фильтра к записям после группирования.
Если поле, включенное в предложение GROUP BY, не является полем типа МЕМО или объекта OLE, оно может ссылаться на любое поле, перечисленное в предложении FROM, даже если это поле не включено в инструкцию SELECT, при условии, что инструкция SELECT содержит по крайней мере одну статистическую функцию SQL. Ядро базы данных Jet нельзя использовать для группирования полей МЕМО или объекта OLE.
При использовании предложения GROUP BY все поля в списке полей инструкции SELECT должны быть либо включены в предложение GROUP BY, либо использоваться в качестве аргументов статистической функции SQL.
Предложение having
Определяет, какие сгруппированные записи отображаются при использовании инструкции SELECT с предложением GROUP BY. После того как записи будут сгруппированы с помощью предложения GROUP BY, предложение HAVING отберет те из полученных записей, которые удовлетворяют условиям отбора, указанным в предложении HAVING.
Синтаксис
SELECT список_полей
From таблица
WHERE условие_отбора
GROUP BY группируемые_поля
HAVING условие_отбора_групп
где условие_отбора_групп — выражение, определяющее, какие сгруппированные записи отображать.
Предложение HAVING не является обязательным.
Предложение HAVING похоже на предложение WHERE, которое определяет, какие записи должны быть отобраны. После того как записи будут сгруппированы с помощью предложения GROUP BY, предложение HAVING указывает, какие из полученных записей должны быть отобраны.
Предложение HAVING может содержать до 40 выражений, связанных логическими операторами, такими как And и Or.
Определить количество товаров каждого типа:
SELECT Тип, Count([Код товара]) AS [Колич]
Определить суммарное количество товаров, поставляемых каждым поставщиком в количествые менее 100:
SELECT [Код поставщика], Sum(Количество) AS Сумма
GROUP BY [Код поставщика]
Запросы с соединением таблиц. Операция inner join
Объединяет записи из двух таблиц, если связующие поля этих таблиц содержат одинаковые значения.
FROM таблица1 INNER JOIN таблица2 ON таблица1.поле1 оператор_сравнения таблица2.поле2
таблица1, таблица2 — имена таблиц, записи которых подлежат объединению;
поле1, поле2 — имена объединяемых полей. Если эти поля не являются числовыми, то должны иметь одинаковый тип данных и содержать данные одного рода, однако они могут иметь разные имена;
оператор_сравнения — любой оператор сравнения: “=,” “<,” “>,” “<=,” “>=,” or “<>.”
Операцию INNER JOIN можно использовать в любом предложении FROM. Это самые обычные типы связывания. Они объединяют записи двух таблиц, если связующие поля обеих таблиц содержат значения, удовлетворяющие условию.
Попытка объединить поля МЕМО или объекта OLE приведет к возникновению ошибки.
Можно объединить два числовых поля подобных типов, например, поле-счетчик с типом «Длинное целое», потому что эти типы подобны. Однако нельзя объединить типы полей «С плавающей точкой (4 байт)» и «С плавающей точкой (* байт)».
Чтобы связать несколько предложений ON в инструкции JOIN, используется следующий синтаксис: