SQL: Working with NULL Values

Photo by Philipp Berndt on Unsplash

This blog covers the handling of NULL values in SQL.

A NULL value is used in SQL when information for a column is either unknown or applicable. Note that a NULL value is different from the value zero.

Any arithmetic expression (unary, binary or relational) results in a NULL if any operand of that expression is itself a NULL value. In Boolean AND, OR, NOT, the behavior of NULL values is as follows [T — True, F — False, U — Unknown(NULL)]:

  1. AND: If any one argument of AND is False then the result is False. Therefore, if U comes with T, it will always result into U because the output will depend on U. However, if U comes with F, it is sure that the result is F.
    - T AND U = U
    - U AND U = U
    - F AND U = F
    Note: AND is a symmetric function, i.e., A AND B is same as B AND A.
  2. OR: If any one argument of OR is T, the result is T. Therefore, U OR’d with T will have a result T. However, F OR’d with U will be U because the result will totally depend on the U.
    - T OR U = T
    - U OR U = U
    - F OR U = U
  3. NOT: NOT(U) = U

NOTE:

  • Any NULL value in the argument of aggregate functions (AVG, SUM, MAX, MIN, COUNT) is eliminated before the respective Function is calculated. However, the aggregate function COUNT(*) handles all NULL values the same as non-NULL values.
  • If the column contains only NULL values, the result of the function COUNT(DISTINCT column_name) is 0.

--

--

--

Collecting data nuts, one at a time!

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

BDD with Cucumber: Some useful info

Queuing Mailables with Custom Headers in Laravel 5.4

How To Deliver Personalized Customer Experiences With Microservices

Single-Line Controller: Advanced case

Automated pipeline using jenkinsfile approach

How to Optimize your Programming with the Perfect Music

COVID-19 emergency: A Community Manager’s Diary — Chapter 8

New ISO 27001 case for a Norwegian company

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
The Data Squirrel

The Data Squirrel

Collecting data nuts, one at a time!

More from Medium

Anonymous Block, Nested Anonymous Block, Branching and looping in ANSI SQL

SQL Cheatsheet

SELECT DISTINCT

Window Functions