Изменения

Представим себе таблицы студентов в БД университета ИТМО * ''Students(StudentId, StudentName, GroupId)'' и * ''Groups(GroupId, GroupName)''. Что может означать тот факт, что у студента '''null''' в столбце GroupId?

* Значение еще/уже не существует (например, студент был зачислен, но еще не распределен в группу или уже отчислен)

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

Как представить кортеж с неопределенными частями в нашем случае?* Разбить на 2 группы и сделать необязательную связь 1:1. В таком случае, в дополнительной таблице будет запись ''(StudentId, GroupId)'' тогда и только тогда, когда у студента определена группа=== Где еще появляется null ===* Результаты внешних соединений* Результаты множественных операцийОказывается, что в некоторых случаях без null не обойтись и надо уметь с ним работать.== Тернарная логика с использованием null ==С точки зрения SQL, результат логического выражения может быть ''true'', ''false'' или ''unknown''.<br>С другой стороны есть тип ''boolean'', и у него есть 3 значения: ''true'', ''false'' и '''''null''''' <br>То есть формально ''unknown'' - это результат вычисления, а '''''null''''' - это конкретное значение, которое может быть записано в БД. На практике ''unknown'' представляется значением '''''null''''', и это различие не будет иметь большого значения.=== Конъюнкция ==={| style="background-color:#CCC;margin:0.5px;text-align:center"!style="background-color:#EEE;color:#00F"| <tex>\bf{and}</tex>!style="background-color:#EEE"| <tex>\bf{true}</tex>!style="background-color:#EEE"| <tex>\bf{unknown}</tex>!style="background-color:#EEE"| <tex>\bf{false}</tex>|-|style="background-color:#EEE;padding:2px 30px"| <tex>\bf{true}</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>true</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>unknown</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>false</tex>|-|style="background-color:#EEE;padding:2px 30px"| <tex>\bf{unknown}</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>unknown</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>unknown</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>false</tex>|-|style="background-color:#EEE;padding:2px 30px"| <tex>\bf{false}</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>false</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>false</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>false</tex>|} === Дизъюнкция ==={| style="background-color:#CCC;margin:0.5px;text-align:center"!style="background-color:#EEE;color:#00F"| <tex>\bf{or}</tex>!style="background-color:#EEE"| <tex>\bf{true}</tex>!style="background-color:#EEE"| <tex>\bf{unknown}</tex>!style="background-color:#EEE"| <tex>\bf{false}</tex>|-|style="background-color:#EEE;padding:2px 30px"| <tex>\bf{true}</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>true</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>true</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>true</tex>|-|style="background-color:#EEE;padding:2px 30px"| <tex>\bf{unknown}</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>true</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>unknown</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>unknown</tex>|-|style="background-color:#EEE;padding:2px 30px"| <tex>\bf{false}</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>true</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>unknown</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>false</tex>|} === Отрицание ==={| style="background-color:#CCC;margin:0.5px;text-align:center"!style="background-color:#EEE;color:#00F"| <tex></tex>!style="background-color:#EEE"| <tex>\bf{true}</tex>!style="background-color:#EEE"| <tex>\bf{unknown}</tex>!style="background-color:#EEE"| <tex>\bf{false}</tex>|-|style="background-color:#EEE;color:#00F;;padding:2px 30px"| <tex>\bf{not}</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>false</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>unknown</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>true</tex>|} === Сравнение ======= Равенство ===={| style="background-color:#CCC;margin:0.5px;text-align:center"!style="background-color:#EEE;color:#00F"| <tex>\bf{= Операции }</tex>!style="background-color:#EEE"| <tex>\bf{true}</tex>!style="background-color:#EEE"| <tex>\bf{unknown}</tex>!style="background-color:#EEE"| <tex>\bf{false}</tex>|-|style="background-color:#EEE;padding:2px 30px"| <tex>\bf{true}</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>true</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>unknown</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>false</tex>|-|style="background-color:#EEE;padding:2px 30px"| <tex>\bf{unknown}</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>unknown</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>unknown</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>unknown</tex>|-|style="background-color:#EEE;padding:2px 30px"| <tex>\bf{false}</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>false</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>unknown</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>true</tex>|} ==== is ===={| style="background-color:#CCC;margin:0.5px;text-align:center"!style="background-color:#EEE;color:#00F"| <tex></tex>!style="background-color:#EEE"| <tex>\bf{true}</tex>!style="background-color:#EEE"| <tex>\bf{unknown}</tex>!style="background-color:#EEE"| <tex>\bf{false}</tex>|-|style="background-color:#EEE;color:#00F;;padding:2px 30px"| <tex>\bf{is\ true}</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>true</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>false</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>false</tex>|-|style="background-color:#EEE;color:#00F;;padding:2px 30px"| <tex>\bf{is\ unknown}</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>false</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>true</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>false</tex>|-|style="background-color:#EEE;color:#00F;;padding:2px 30px"| <tex>\bf{is\ false}</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>false</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>false</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>true</tex>|-|style="background-color:#EEE;color:#00F;;padding:2px 30px"| <tex>\bf{is\ not\ true}</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>false</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>true</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>true</tex>|-|style="background-color:#EEE;color:#00F;;padding:2px 30px"| <tex>\bf{is\ not\ unknown}</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>true</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>false</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>true</tex>|-|style="background-color:#EEE;color:#00F;;padding:2px 30px"| <tex>\bf{is\ not\ false}</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>true</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>true</tex>|style="background-color:#FFF;padding:2px 30px"| <tex>false</tex>|} == Проблемы при работе с null ==todoПри работе с '''null''' в процессе разработки БД, во избежание непредвиденных ошибок, необъодимо заранее ознакомиться с тем, какие проблемы могут возникнуть.== Null = Вывод логических выражений ===В новой тернарной логике работают не все правила преобразований, присущие двоичной.Например, нельзя полагать, что <tex>(A\ \vee\ \neg\ A)</tex> всегда истинно, потому что теперь может получиться ''unknown''. <br>Поэтому при каждом преобразовании троичного логического выражения, лучше сверяться с таблицами истинности. === Скалярные операции, порождающие null ===Следующие операции с '''null''' порождают '''null''', и иногда это может сбивать с толку начинающих разработчиков.* <tex>=</tex>, <tex><></tex>, <tex><</tex>, <tex><=</tex>, <tex>></tex>, <tex>>=</tex>* <tex>+</tex>, <tex>−</tex>, <tex>*</tex>,<tex>/</tex>* <tex>\|</tex>* <tex>in</tex>Рассмотрим несколько примеров. <font color = blue>select</font> (1 + <font color = blue>null</font>) <font color = blue>from</font> Students;Не смотря на то, что этот запрос не несет большого смысла, на его примере можно убедиться, что в арифметических операциях '''null''' "заразен".<br>  <font color = blue>select</font> StudentId <font color = blue>from</font> Students where GroupId = null;Это частая ошибка, сравнение с '''''null''''' дает ''unknown'', а значит запрос вернет пустую таблицу.<br> Говоря об операции сравнения, стоит отметить, что она не транзитивна и не рефлексивна.* <tex>x\ =\ x</tex> - ''true'' или '''''null'''''* <tex>x\ <>\ x</tex> - ''true'' или '''''null'''''* <tex>x\ or\ x</tex> - ''true'' или '''''null'''''* <tex>x\ or\ not\ x</tex> - ''true'' или '''''null'''''* <tex>x\ and\ not\ x</tex> - ''false'' или '''''null''''' === Дубликаты и null ===Так как null ≠ null, сравнения кортежей, содержащих null не обладают интуитивными свойствами, например:* <tex>R \cup R</tex> - не всегда <tex>R</tex> * <tex>R \cap R</tex> - не всегда <tex>R</tex> * <tex>R \bowtie R</tex> - не всегда <tex>R</tex>  === Неинтуитивность null ===Рассмотрим запрос, для нахождения студентов не из группы 'M34391'. <font color = blue>select</font> * <font color = blue>from</font> Students <font color = blue>where</font> GroupId <> <font color = green>'M34391'</font>Корректность запроса зависит от смысла '''null'''. Неясно, надо ли возвращать в этом запросе студента, о котором нет информации, в какой группе он учится. Следующий запрос, хоть и выглядит странно, предполагает просто поиск всевозможных студентов <font color = blue>select</font> * <font color = blue>from</font> Students <font color = blue>where</font> GroupId <> <font color = green>'M34391'</font> <font color = blue>union</font> <font color = blue>select</font> * <font color = blue>from</font> Students <font color = blue>where</font> GroupId = <font color = green>'M34391'</font>Но из-за наличия '''null''', этот запрос не отработает так, как предполагалось. Если ''GroupId'' студента '''''null''''', то сравнение не вернет ''true'', а значит в результате это учтено не будет. <br>Подробнее у работе функции <font color = blue>where</font> будет рассказано в следующем разделе. == Работа с null в SQL ==todoНесмотря на множество проблем, описанных выше, в SQL существуют механизмы, позволяющие корректно обработать '''null'''.=== Проверки значений ===Для сравнения с '''null''' используется <font color = blue>is null</font> (или <font color = blue>is not null</font>).Получить всех студентов с '''''null''''' в поле ''GroupId'' можно следующим образом: <font color = blue>select</font> StudentId <font color = blue>from</font> Students <font color = blue>where</font> GroupId <font color = blue>is null</font>;В общем виде синтаксис проверки значений выглядит следующим образом:<font color = red>значение</font> <font color = blue>is</font> <font color = red>[</font><font color = blue>not</font><font color = red>]</font> <font color = red>{</font><font color = blue>null</font>|true|false|unknown<font color = red>}</font>, например:* x <font color = blue>is not</font> true* x <font color = blue>or</font> x <font color = blue>is not null</font> Так же в SQL существует функция '''''coalesce(v1, v2, ...)''''', которая принимает произвольное число аргументов и возвращает первый не '''не null'''. Если все аргументы '''null''', то возвращает '''null'''. === Ключи и null ===Можно использовать null:* Альтернативные ключи* Внешние ключи** Простые** Составные, отсутствующие целиком'''Первичные ключи не могут содержать null.''' === Предикаты ======= DML ====<font color = blue>where</font> и <font color = blue>having</font> считают истинным предикат '''только если он вернул ''true'''''Зная этот факт можно, например убедиться, что ''false'' <font color = blue>and</font> ''unknown'' дает ''false''. Следующий запрос вернет 1: <font color = blue>select</font> 1 <font color = blue>where not</font> (0 = 1 <font color = blue>and</font> 0 = <font color = blue>null</font>) ==== DDL ====C точки зрения <font color = blue>check</font> constraint-ов не подходит только ''false''. ''Unknown'' превращается в ''true'' === Различимость ===Два '''null''' не равны и не различимы. Это важно для <font color = blue>distinct</font> и <font color = blue>group by</font> <br>Например, кортежи ''(1, '''null''')'' и ''(1, '''null''')'' склеятся в случае <font color = blue>distinct</font> и не породят разные группы в случае <font color = blue>group by</font>, т.к. не различимы === Типы столбцов ===В SQL столбцы могут быть <font color = blue>nullable</font> (по умолчанию) и не <font color = blue>nullable</font> <font color>birthday date</font> <font color>birthday date</font> <font color = blue>not null</font> Перед созданием <font color = blue>nullable</font> столбца, рекомендуется дополнительно обдумать, какой конкретно смысл вкладывается в '''null''' в данном случае, не скажется ли это негативно на остальных запросах, в случае, если начать его использовать. Если есть возможность, во избежание дополнительных проблем, описанных выше, лучше объявлять столбцы '''not null'''. === Прочее ===* <font color = blue>exists</font>** возвращает ''true'' или 'false'** если внутри получились только строки, состоящие из '''null''', то вернет так же ''false''* агрегирующие функции (<font color = blue>count</font>, <font color = blue>sum</font>, <font color = blue>avg</font> и т.д.)** пропускают '''''null''''', т.е. не учитывают его при подсчете** при отсутствии аргументов, отличных от '''''null''''', возвращают '''''null'''''** исключением является <font color = blue>count</font>(*), что просто считает количество строк* <font color = blue>order by</font>** Помимо указаний порядка сортировки (asc или desc), можно указывать, куда ставить '''''null'''-ы'' - в начало или в конец. Например <font color = blue>order by</font> year nulls first. По умолчанию '''''null'''-ы'' складываются либо в начало, либо в конец, это нужно уточнять в документации к конкретной СУБД.