SQL условие IS NOT NULL
В этом учебном материале вы узнаете, как использовать SQL условие IS NOT NULL с синтаксисом и примерами.
Описание
Условие IS NOT NULL используется в SQL для проверки значения, отличного от NULL. Оно возвращает TRUE, если найдено ненулевое значение, в противном случае оно возвращает FALSE. Его можно использовать в операторе SELECT, INSERT, UPDATE или DELETE.
Синтаксис
Синтаксис для условия IS NOT NULL в SQL:
Параметры или аргументы
Пример — использование IS NOT NULL с оператором SELECT
При проверке значения, отличного от NULL, IS NOT NULL является рекомендуемым оператором сравнения для использования в SQL. Давайте начнем с примера, который показывает, как использовать условие IS NOT NULL в SELECT предложении.
В этом примере у нас есть таблица products со следующими данными:
| product_id | product_name | category_id |
|---|---|---|
| 1 | Pear | 50 |
| 2 | Banana | 50 |
| 3 | Orange | 50 |
| 4 | Apple | 50 |
| 5 | Bread | 75 |
| 6 | Sliced Ham | 25 |
| 7 | Kleenex | NULL |
Введите следующий SQL оператор:
Будет выбрано 6 записей. Вот результаты, которые вы должны получить:
| product_id | product_name | category_id |
|---|---|---|
| 1 | Pear | 50 |
| 2 | Banana | 50 |
| 3 | Orange | 50 |
| 4 | Apple | 50 |
| 5 | Bread | 75 |
| 6 | Sliced Ham | 25 |
В этом примере будут возвращены все записи из таблицы products , где customer_id не содержит значения NULL.
Пример — использование IS NOT NULL с оператором UPDATE
Далее давайте рассмотрим пример использования условия IS NOT NULL в запросе UPDATE.
В этом примере у нас есть таблица customer со следующими данными:
| customer_id | first_name | last_name | favorite_website |
|---|---|---|---|
| 4000 | Justin | Bieber | google.com |
| 5000 | Selena | Gomez | bing.com |
| 6000 | Mila | Kunis | yahoo.com |
| 7000 | Tom | Cruise | oracle.com |
| 8000 | Johnny | Depp | NULL |
| 9000 | Russell | Crowe | google.com |
Введите следующий запрос UPDATE:
Будет обновлено 5 записей. Выберите данные из таблицы customer еще раз:
Вот результаты, которые вы должны получить:
| customer_id | first_name | last_name | favorite_website |
|---|---|---|---|
| 4000 | Justin | Bieber | google.com |
| 5000 | Selena | Gomez | google.com |
| 6000 | Mila | Kunis | google.com |
| 7000 | Tom | Cruise | google.com |
| 8000 | Johnny | Depp | NULL |
| 9000 | Russell | Crowe | google.com |
В этом примере будут обновлены все значения fav_website в таблице customer до google.com, где favourite_website содержит значение NULL. Как вы видите, значения поля favorite_website обновлены все строки кроме одной.
Пример — использование IS NOT NULL с оператором DELETE
Далее давайте рассмотрим пример использования условия IS NULL в запросе DELETE.
# NULLs
In SQL Server, NULL represents data that is missing, or unknown. This means that NULL is not really a value; it’s better described as a placeholder for a value. This is also the reason why you can’t compare NULL with any value, and not even with another NULL .
# COALESCE ()
COALESCE () Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL .
Although ISNULL() operates similarly to COALESCE(), the ISNULL() function only accepts two parameters — one to check, and one to use if the first parameter is NULL. See also ISNULL , below
Ограничения в SQL
Ограничение (constraints) — это ограничение типа значений, которое накладывается на один или несколько столбцов таблицы. Это позволяет поддерживать точность и целостность данных в таблице БД.
В SQL существует несколько различных типов ограничений, в том числе:
- NOT NULL
- PRIMARY KEY
- UNIQUE
- DEFAULT
- FOREIGN KEY
- CHECK
Давайте обсудим каждое из этих ограничений попродробнее.
Ограничение NOT NULL
Ограничение NOT NULL указывает, что столбец не может принимать значения NULL .
Если к столбцу применено ограничение NOT NULL , вы не сможете вставить новую строку в таблицу без добавления не-NULL-значения в этот столбец.
Пример
Следующая SQL-инструкция создает таблицу persons с четырьмя столбцами, из которых три столбца — id , name и phone — не могут иметь значение NULL .
Примечание. Нулевое значение ( NULL ) — это не ноль(0) и не строка символов нулевой длины (»). NULL означает, что записи нет.
Ограниение PRIMARY KEY
Ограничение PRIMARY KEY определяет столбец или набор столбцов, значения которых однозначно идентифицируют строку в таблице. То есть никакие две строки в таблице не могут иметь одинаковое значение первичного ключа. Также нельзя вводить значение NULL в столбец первичного ключа.
Пример
Следующая SQL-инструкция создает таблицу persons и указывает столбец id в качестве первичного ключа. Это означает, что в этом поле не допускаются значения NULL или дубликаты.
Примечание. Первичный ключ обычно состоит из одного столбца в таблице, однако несколько столбцов могут составлять первичный ключ. Например, адрес электронной почты сотрудника или присвоенный ID является логическим первичным ключом для таблицы сотрудников.
Ограничение UNIQUE
Ограничение UNIQUE означает, что в указанных столбцах обязательно должны быть уникальные значения.
Хотя и ограничение UNIQUE, и ограничение PRIMARY KEY обеспечивают уникальность значений, есть различия.
UNIQUE лучше PRIMARY KEY, когда вы хотите обеспечить уникальность столбца или комбинации столбцов, которые не являются первичным ключом.
Пример
Следующая SQL-инструкция создает таблицу persons и определяет столбец phone как уникальный. Это означает, что это поле не допускает дублирования значений.
Примечание. В одной таблице может быть задано ограничений UNIQUE , но только одно ограничение PRIMARY KEY . Кроме того, в отличие от ограничений PRIMARY KEY , ограничения UNIQUE допускают значения NULL .
Ограничение DEFAULT
Ограничение DEFAULT определяет значение по умолчанию для столбцов.
Значение столбца по умолчанию — это некоторое значение, которое будет вставлено в столбец базой данных, если оператор INSERT явно не назначит конкретное значение.
Пример
В следующей SQL-инструкции мы задаем значение по умолчанию для столбца country .
Примечание. Если вы определили столбец таблицы как NOT NULL , но присвоили ему значение по умолчанию, то в операторе INSERT вам не нужно явно присваивать значение этому столбцу, чтобы вставить новую строку в таблицу.
Ограничение FOREIGN KEY
Внешний ключ (foreign key) — это столбец или комбинация столбцов, которые используются для установления и обеспечения взаимосвязи между данными в двух таблицах.
Ниже — диаграмма, которая показывает связь между таблицами сотрудников ( employees ) и отделов ( departments ). Если вы внимательно посмотрите на нее, то заметите, что столбец dept_id таблицы сотрудников совпадает со столбцом первичного ключа таблицы отделов. Поэтому столбец dept_id таблицы сотрудников является внешним ключом для таблицы отделов.
В MySQL можно создать внешний ключ, задав ограничение FOREIGN KEY при создании таблицы следующим образом.
Пример
В следующей SQL-инструкции мы определяем столбец dept_id таблицы employees как внешний ключ, который ссылается на столбец dept_id таблицы departments .
Ограничение CHECK
Ограничение CHECK используется для ограничения значений, которые могут быть помещены в столбец.
Например, диапазон значений для столбца зарплаты salary можно ограничить, создав ограничение CHECK , которое допускает значения только от 30 000 до 100 000. Это предотвратит ввод зарплат за пределами обычного (условного) диапазона.
Пример
Примечание. MySQL не поддерживает ограничение CHECK.
MySQL NULL or NOT NULL That is The Question?
What is the difference between NULL and NOT NULL? And when should they be used?
9 Answers 9
NULL means you do not have to provide a value for the field.
NOT NULL means you must provide a value for the fields.
For example, if you are building a table of registered users for a system, you might want to make sure the user-id is always populated with a value (i.e. NOT NULL), but the optional spouses name field, can be left empty (NULL)
I would suggest
- Use NOT NULL on every field if you can
- Use NULL if there is a sensible reason it can be null
Having fields which don’t have a meaningful meaning for NULL nullable is likely to introduce bugs, when nulls enter them by accident. Using NOT NULL prevents this.
The commonest reason for NULL fields is that you have a foreign key field which is options, i.e. not always linked, for a «zero or one» relationship.
If you find you have a table with lots of columns many of which can be NULL, that starts sounding like an antipattern, consider whether vertical partitioning makes more sense in your application context 🙂
There is another useful use for NULL — making all the columns in an index NULL will stop an index record being created for that row, which optimises indexes; you may want to index only a very small subset of rows (e.g. for an «active» flag set on only 1% or something) — making an index which starts with a column which is usually NULL saves space and optimises that index.
What is the difference between NULL and NOT NULL?
When creating a table or adding a column to a table, you need to specify the column value optionality using either NULL or NOT NULL . NOT NULL means that the column can not have a NULL value for any record; NULL means NULL is an allowable value (even when the column has a foreign key constraint). Because NULL isn’t a value, you can see why some call it optionality — because database table requires that in order to have a column, there must be an instance of the column for every record within the table.
And when should they be used?
That is determined by your business rules.
Generally you want as many columns as possible to be NOT NULL because you want to be sure data is always there.
NOT NULL means that a column cannot have the NULL value in it — instead, if nothing is specified when inserting a row for that column, it will use whatever default is specified (or if no default is specified, whatever MySQL’s default is for that type).
Fields that aren’t NOT NULL can potentially have their value as NULL (which essentially means a missing/unknown/unspecified value). NULL behaves differently than normal values, see here for more info.
As others have answered, NOT NULL simply means that NULL is not a permitted value. However, you will always have the option of empty string » (for varchar ) or 0 (for int ), etc.
One nice feature when using NOT NULL is that you can get an error or warning should you forget set the column’s value during INSERT . (assuming the NOT NULL column has no DEFAULT )
The main hiccup with allowing NULL columns is that they will never be found with the <> (not equal) operator. For example, with the following category s
The = operator works as expected
However, the <> operator will exclude any NULL entries.
This can be the cause of subtle bugs, especially if the column has no NULL data during testing initial, but later some NULL values are added due to subsequent development. For this reason I set all the columns to NOT NULL .
However, it can be helpful to allow NULL values when using a UNIQUE KEY / INDEX . Normally a unique key requires the column (or combination of columns) to be unique for the whole table. Unique keys are a great safeguard that the database will enforce for you.
In some cases, you may want the safeguard for most of the rows, but there are exceptions.
If any column referenced by that particular UNIQUE KEY is NULL , then the uniqueness will no longer be enforced for that row. Obviously this would only work if you permit NULL s on that column, understanding the hiccup I explained above.
If you decide to allow NULL values, consider writing your <> statements with an additional condition to detect NULL s.