{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "## Handling NULL Values\n", "\n", "Let us understand how to handle nulls." ] }, { "cell_type": "code", "execution_count": 188, "metadata": { "tags": [ "remove-cell" ] }, "outputs": [ { "data": { "text/html": [ "\n" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%%HTML\n", "" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "* By default if we try to add or concatenate null to another column or expression or literal, it will return null.\n", "* If we want to replace null with some default value, we can use `coalesce`.\n", " * Replace commission_pct with 0 if it is null.\n", "* `coalesce` returns first not null value if we pass multiple arguments to it.\n", "* We have a function called as `nullif`. If the first argument is equal to second argument, it returns null. It is typically used when we compare against 2 columns where nulls are also involved.\n", "* You might have seen functions like `nvl`, `nvl2` etc with respect to databases like Oracle. Postgres does not support them." ] }, { "cell_type": "code", "execution_count": 189, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "The sql extension is already loaded. To reload it, use:\n", " %reload_ext sql\n" ] } ], "source": [ "%load_ext sql" ] }, { "cell_type": "code", "execution_count": 190, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db\n" ] } ], "source": [ "%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db" ] }, { "cell_type": "code", "execution_count": 191, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
result
None
" ], "text/plain": [ "[(None,)]" ] }, "execution_count": 191, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT 1 + NULL AS result" ] }, { "cell_type": "code", "execution_count": 192, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
result
1
" ], "text/plain": [ "[(1,)]" ] }, "execution_count": 192, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT coalesce(1, 0) AS result" ] }, { "cell_type": "code", "execution_count": 193, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
result
2
" ], "text/plain": [ "[(2,)]" ] }, "execution_count": 193, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT coalesce(NULL, NULL, 2, NULL, 3) AS result" ] }, { "cell_type": "code", "execution_count": 194, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "Done.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 194, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql DROP TABLE IF EXISTS sales" ] }, { "cell_type": "code", "execution_count": 195, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "Done.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 195, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "CREATE TABLE IF NOT EXISTS sales(\n", " sales_person_id INT,\n", " sales_amount FLOAT,\n", " commission_pct INT\n", ")" ] }, { "cell_type": "code", "execution_count": 196, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "5 rows affected.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 196, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "INSERT INTO sales VALUES\n", " (1, 1000, 10),\n", " (2, 1500, 8),\n", " (3, 500, NULL),\n", " (4, 800, 5),\n", " (5, 250, NULL)" ] }, { "cell_type": "code", "execution_count": 197, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "5 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
sales_person_idsales_amountcommission_pct
11000.010
21500.08
3500.0None
4800.05
5250.0None
" ], "text/plain": [ "[(1, 1000.0, 10),\n", " (2, 1500.0, 8),\n", " (3, 500.0, None),\n", " (4, 800.0, 5),\n", " (5, 250.0, None)]" ] }, "execution_count": 197, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT * FROM sales" ] }, { "cell_type": "code", "execution_count": 198, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "5 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
sales_person_idsales_amountcommission_pctincorrect_commission_amount
11000.010100.00
21500.08120.00
3500.0NoneNone
4800.0540.00
5250.0NoneNone
" ], "text/plain": [ "[(1, 1000.0, 10, Decimal('100.00')),\n", " (2, 1500.0, 8, Decimal('120.00')),\n", " (3, 500.0, None, None),\n", " (4, 800.0, 5, Decimal('40.00')),\n", " (5, 250.0, None, None)]" ] }, "execution_count": 198, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT s.*, \n", " round((sales_amount * commission_pct / 100)::numeric, 2) AS incorrect_commission_amount\n", "FROM sales AS s" ] }, { "cell_type": "code", "execution_count": 199, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "5 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
sales_person_idsales_amountcommission_pctcommission_pct_1
11000.01010
21500.088
3500.0None0
4800.055
5250.0None0
" ], "text/plain": [ "[(1, 1000.0, 10, 10),\n", " (2, 1500.0, 8, 8),\n", " (3, 500.0, None, 0),\n", " (4, 800.0, 5, 5),\n", " (5, 250.0, None, 0)]" ] }, "execution_count": 199, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT s.*, \n", " coalesce(commission_pct, 0) AS commission_pct\n", "FROM sales AS s" ] }, { "cell_type": "code", "execution_count": 200, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "5 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
sales_person_idsales_amountcommission_pctcommission_amount
11000.010100.00
21500.08120.00
3500.0None0.00
4800.0540.00
5250.0None0.00
" ], "text/plain": [ "[(1, 1000.0, 10, Decimal('100.00')),\n", " (2, 1500.0, 8, Decimal('120.00')),\n", " (3, 500.0, None, Decimal('0.00')),\n", " (4, 800.0, 5, Decimal('40.00')),\n", " (5, 250.0, None, Decimal('0.00'))]" ] }, "execution_count": 200, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT s.*, \n", " round((sales_amount * coalesce(commission_pct, 0) / 100)::numeric, 2) AS commission_amount\n", "FROM sales AS s" ] }, { "cell_type": "code", "execution_count": 201, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
nullif
1
" ], "text/plain": [ "[(1,)]" ] }, "execution_count": 201, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT nullif(1, 0)" ] }, { "cell_type": "code", "execution_count": 202, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
nullif
None
" ], "text/plain": [ "[(None,)]" ] }, "execution_count": 202, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT nullif(1, 1)" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.6.12" } }, "nbformat": 4, "nbformat_minor": 4 }