CINXE.COM

<!DOCTYPE html> <html class="client-nojs vector-feature-language-in-header-enabled vector-feature-language-in-main-page-header-disabled vector-feature-page-tools-pinned-disabled vector-feature-toc-pinned-clientpref-1 vector-feature-main-menu-pinned-disabled vector-feature-limited-width-clientpref-1 vector-feature-limited-width-content-enabled vector-feature-custom-font-size-clientpref-1 vector-feature-appearance-pinned-clientpref-1 vector-feature-night-mode-enabled skin-theme-clientpref-day vector-sticky-header-enabled vector-toc-available" lang="en" dir="ltr"> <head> <meta charset="UTF-8"> <title>Null (SQL) - Wikipedia</title> <script>(function(){var className="client-js vector-feature-language-in-header-enabled vector-feature-language-in-main-page-header-disabled vector-feature-page-tools-pinned-disabled vector-feature-toc-pinned-clientpref-1 vector-feature-main-menu-pinned-disabled vector-feature-limited-width-clientpref-1 vector-feature-limited-width-content-enabled vector-feature-custom-font-size-clientpref-1 vector-feature-appearance-pinned-clientpref-1 vector-feature-night-mode-enabled skin-theme-clientpref-day vector-sticky-header-enabled vector-toc-available";var cookie=document.cookie.match(/(?:^|; )enwikimwclientpreferences=([^;]+)/);if(cookie){cookie[1].split('%2C').forEach(function(pref){className=className.replace(new RegExp('(^| )'+pref.replace(/-clientpref-\w+$|[^\w-]+/g,'')+'-clientpref-\\w+( |$)'),'$1'+pref+'$2');});}document.documentElement.className=className;}());RLCONF={"wgBreakFrames":false,"wgSeparatorTransformTable":["",""],"wgDigitTransformTable":["",""],"wgDefaultDateFormat":"dmy","wgMonthNames":["","January","February","March","April","May","June","July","August","September","October","November","December"],"wgRequestId":"46a2f226-7633-4739-896c-11b9aa5daee7","wgCanonicalNamespace":"","wgCanonicalSpecialPageName":false,"wgNamespaceNumber":0,"wgPageName":"Null_(SQL)","wgTitle":"Null (SQL)","wgCurRevisionId":1271809523,"wgRevisionId":1271809523,"wgArticleId":2002540,"wgIsArticle":true,"wgIsRedirect":false,"wgAction":"view","wgUserName":null,"wgUserGroups":["*"],"wgCategories":["CS1 errors: requires URL","Articles with short description","Short description is different from Wikidata","All articles with unsourced statements","Articles with unsourced statements from November 2012","Webarchive template wayback links","Articles with example SQL code","SQL keywords","Unknown content"],"wgPageViewLanguage":"en","wgPageContentLanguage":"en","wgPageContentModel":"wikitext","wgRelevantPageName":"Null_(SQL)","wgRelevantArticleId":2002540,"wgIsProbablyEditable":true,"wgRelevantPageIsProbablyEditable":true,"wgRestrictionEdit":[],"wgRestrictionMove":[],"wgNoticeProject":"wikipedia","wgCiteReferencePreviewsActive":false,"wgFlaggedRevsParams":{"tags":{"status":{"levels":1}}},"wgMediaViewerOnClick":true,"wgMediaViewerEnabledByDefault":true,"wgPopupsFlags":0,"wgVisualEditor":{"pageLanguageCode":"en","pageLanguageDir":"ltr","pageVariantFallbacks":"en"},"wgMFDisplayWikibaseDescriptions":{"search":true,"watchlist":true,"tagline":false,"nearby":true},"wgWMESchemaEditAttemptStepOversample":false,"wgWMEPageLength":60000,"wgEditSubmitButtonLabelPublish":true,"wgULSPosition":"interlanguage","wgULSisCompactLinksEnabled":false,"wgVector2022LanguageInHeader":true,"wgULSisLanguageSelectorEmpty":false,"wgWikibaseItemId":"Q371029","wgCheckUserClientHintsHeadersJsApi":["brands","architecture","bitness","fullVersionList","mobile","model","platform","platformVersion"],"GEHomepageSuggestedEditsEnableTopics":true,"wgGETopicsMatchModeEnabled":false,"wgGEStructuredTaskRejectionReasonTextInputEnabled":false,"wgGELevelingUpEnabledForUser":false}; RLSTATE={"ext.globalCssJs.user.styles":"ready","site.styles":"ready","user.styles":"ready","ext.globalCssJs.user":"ready","user":"ready","user.options":"loading","ext.cite.styles":"ready","ext.pygments":"ready","ext.math.styles":"ready","skins.vector.search.codex.styles":"ready","skins.vector.styles":"ready","skins.vector.icons":"ready","jquery.makeCollapsible.styles":"ready","ext.wikimediamessages.styles":"ready","ext.visualEditor.desktopArticleTarget.noscript":"ready","ext.uls.interlanguage":"ready","wikibase.client.init":"ready","ext.wikimediaBadges":"ready"};RLPAGEMODULES=["ext.cite.ux-enhancements","ext.pygments.view","mediawiki.page.media","site","mediawiki.page.ready","jquery.makeCollapsible","mediawiki.toc","skins.vector.js","ext.centralNotice.geoIP","ext.centralNotice.startUp","ext.gadget.ReferenceTooltips","ext.gadget.switcher","ext.urlShortener.toolbar","ext.centralauth.centralautologin","mmv.bootstrap","ext.popups","ext.visualEditor.desktopArticleTarget.init","ext.visualEditor.targetLoader","ext.echo.centralauth","ext.eventLogging","ext.wikimediaEvents","ext.navigationTiming","ext.uls.interface","ext.cx.eventlogging.campaigns","ext.cx.uls.quick.actions","wikibase.client.vector-2022","ext.checkUser.clientHints","ext.growthExperiments.SuggestedEditSession"];</script> <script>(RLQ=window.RLQ||[]).push(function(){mw.loader.impl(function(){return["user.options@12s5i",function($,jQuery,require,module){mw.user.tokens.set({"patrolToken":"+\\","watchToken":"+\\","csrfToken":"+\\"}); }];});});</script> <link rel="stylesheet" href="/w/load.php?lang=en&modules=ext.cite.styles%7Cext.math.styles%7Cext.pygments%2CwikimediaBadges%7Cext.uls.interlanguage%7Cext.visualEditor.desktopArticleTarget.noscript%7Cext.wikimediamessages.styles%7Cjquery.makeCollapsible.styles%7Cskins.vector.icons%2Cstyles%7Cskins.vector.search.codex.styles%7Cwikibase.client.init&only=styles&skin=vector-2022"> <script async="" src="/w/load.php?lang=en&modules=startup&only=scripts&raw=1&skin=vector-2022"></script> <meta name="ResourceLoaderDynamicStyles" content=""> <link rel="stylesheet" href="/w/load.php?lang=en&modules=site.styles&only=styles&skin=vector-2022"> <meta name="generator" content="MediaWiki 1.44.0-wmf.20"> <meta name="referrer" content="origin"> <meta name="referrer" content="origin-when-cross-origin"> <meta name="robots" content="max-image-preview:standard"> <meta name="format-detection" content="telephone=no"> <meta property="og:image" content="https://upload.wikimedia.org/wikipedia/commons/3/3a/Db_null.png"> <meta property="og:image:width" content="1200"> <meta property="og:image:height" content="1200"> <meta property="og:image" content="https://upload.wikimedia.org/wikipedia/commons/3/3a/Db_null.png"> <meta property="og:image:width" content="800"> <meta property="og:image:height" content="800"> <meta property="og:image:width" content="640"> <meta property="og:image:height" content="640"> <meta name="viewport" content="width=1120"> <meta property="og:title" content="Null (SQL) - Wikipedia"> <meta property="og:type" content="website"> <link rel="preconnect" href="//upload.wikimedia.org"> <link rel="alternate" media="only screen and (max-width: 640px)" href="//en.m.wikipedia.org/wiki/Null_(SQL)"> <link rel="alternate" type="application/x-wiki" title="Edit this page" href="/w/index.php?title=Null_(SQL)&action=edit"> <link rel="apple-touch-icon" href="/static/apple-touch/wikipedia.png"> <link rel="icon" href="/static/favicon/wikipedia.ico"> <link rel="search" type="application/opensearchdescription+xml" href="/w/rest.php/v1/search" title="Wikipedia (en)"> <link rel="EditURI" type="application/rsd+xml" href="//en.wikipedia.org/w/api.php?action=rsd"> <link rel="canonical" href="https://en.wikipedia.org/wiki/Null_(SQL)"> <link rel="license" href="https://creativecommons.org/licenses/by-sa/4.0/deed.en"> <link rel="alternate" type="application/atom+xml" title="Wikipedia Atom feed" href="/w/index.php?title=Special:RecentChanges&feed=atom"> <link rel="dns-prefetch" href="//meta.wikimedia.org" /> <link rel="dns-prefetch" href="login.wikimedia.org"> </head> <body class="skin--responsive skin-vector skin-vector-search-vue mediawiki ltr sitedir-ltr mw-hide-empty-elt ns-0 ns-subject mw-editable page-Null_SQL rootpage-Null_SQL skin-vector-2022 action-view"><a class="mw-jump-link" href="#bodyContent">Jump to content</a> <div class="vector-header-container"> <header class="vector-header mw-header"> <div class="vector-header-start"> <nav class="vector-main-menu-landmark" aria-label="Site"> <div id="vector-main-menu-dropdown" class="vector-dropdown vector-main-menu-dropdown vector-button-flush-left vector-button-flush-right" title="Main menu" > <input type="checkbox" id="vector-main-menu-dropdown-checkbox" role="button" aria-haspopup="true" data-event-name="ui.dropdown-vector-main-menu-dropdown" class="vector-dropdown-checkbox " aria-label="Main menu" > <label id="vector-main-menu-dropdown-label" for="vector-main-menu-dropdown-checkbox" class="vector-dropdown-label cdx-button cdx-button--fake-button cdx-button--fake-button--enabled cdx-button--weight-quiet cdx-button--icon-only " aria-hidden="true" ><span class="vector-icon mw-ui-icon-menu mw-ui-icon-wikimedia-menu"></span> <span class="vector-dropdown-label-text">Main menu</span> </label> <div class="vector-dropdown-content"> <div id="vector-main-menu-unpinned-container" class="vector-unpinned-container"> <div id="vector-main-menu" class="vector-main-menu vector-pinnable-element"> <div class="vector-pinnable-header vector-main-menu-pinnable-header vector-pinnable-header-unpinned" data-feature-name="main-menu-pinned" data-pinnable-element-id="vector-main-menu" data-pinned-container-id="vector-main-menu-pinned-container" data-unpinned-container-id="vector-main-menu-unpinned-container" > <div class="vector-pinnable-header-label">Main menu</div> <button class="vector-pinnable-header-toggle-button vector-pinnable-header-pin-button" data-event-name="pinnable-header.vector-main-menu.pin">move to sidebar</button> <button class="vector-pinnable-header-toggle-button vector-pinnable-header-unpin-button" data-event-name="pinnable-header.vector-main-menu.unpin">hide</button> </div> <div id="p-navigation" class="vector-menu mw-portlet mw-portlet-navigation" > <div class="vector-menu-heading"> Navigation </div> <div class="vector-menu-content"> <ul class="vector-menu-content-list"> <li id="n-mainpage-description" class="mw-list-item"><a href="/wiki/Main_Page" title="Visit the main page [z]" accesskey="z"><span>Main page</span></a></li><li id="n-contents" class="mw-list-item"><a href="/wiki/Wikipedia:Contents" title="Guides to browsing Wikipedia"><span>Contents</span></a></li><li id="n-currentevents" class="mw-list-item"><a href="/wiki/Portal:Current_events" title="Articles related to current events"><span>Current events</span></a></li><li id="n-randompage" class="mw-list-item"><a href="/wiki/Special:Random" title="Visit a randomly selected article [x]" accesskey="x"><span>Random article</span></a></li><li id="n-aboutsite" class="mw-list-item"><a href="/wiki/Wikipedia:About" title="Learn about Wikipedia and how it works"><span>About Wikipedia</span></a></li><li id="n-contactpage" class="mw-list-item"><a href="//en.wikipedia.org/wiki/Wikipedia:Contact_us" title="How to contact Wikipedia"><span>Contact us</span></a></li> </ul> </div> </div> <div id="p-interaction" class="vector-menu mw-portlet mw-portlet-interaction" > <div class="vector-menu-heading"> Contribute </div> <div class="vector-menu-content"> <ul class="vector-menu-content-list"> <li id="n-help" class="mw-list-item"><a href="/wiki/Help:Contents" title="Guidance on how to use and edit Wikipedia"><span>Help</span></a></li><li id="n-introduction" class="mw-list-item"><a href="/wiki/Help:Introduction" title="Learn how to edit Wikipedia"><span>Learn to edit</span></a></li><li id="n-portal" class="mw-list-item"><a href="/wiki/Wikipedia:Community_portal" title="The hub for editors"><span>Community portal</span></a></li><li id="n-recentchanges" class="mw-list-item"><a href="/wiki/Special:RecentChanges" title="A list of recent changes to Wikipedia [r]" accesskey="r"><span>Recent changes</span></a></li><li id="n-upload" class="mw-list-item"><a href="/wiki/Wikipedia:File_upload_wizard" title="Add images or other media for use on Wikipedia"><span>Upload file</span></a></li><li id="n-specialpages" class="mw-list-item"><a href="/wiki/Special:SpecialPages"><span>Special pages</span></a></li> </ul> </div> </div> </div> </div> </div> </div> </nav> <a href="/wiki/Main_Page" class="mw-logo"> <img class="mw-logo-icon" src="/static/images/icons/wikipedia.png" alt="" aria-hidden="true" height="50" width="50"> <span class="mw-logo-container skin-invert"> <img class="mw-logo-wordmark" alt="Wikipedia" src="/static/images/mobile/copyright/wikipedia-wordmark-en.svg" style="width: 7.5em; height: 1.125em;"> <img class="mw-logo-tagline" alt="The Free Encyclopedia" src="/static/images/mobile/copyright/wikipedia-tagline-en.svg" width="117" height="13" style="width: 7.3125em; height: 0.8125em;"> </span> </a> </div> <div class="vector-header-end"> <div id="p-search" role="search" class="vector-search-box-vue vector-search-box-collapses vector-search-box-show-thumbnail vector-search-box-auto-expand-width vector-search-box"> <a href="/wiki/Special:Search" class="cdx-button cdx-button--fake-button cdx-button--fake-button--enabled cdx-button--weight-quiet cdx-button--icon-only search-toggle" title="Search Wikipedia [f]" accesskey="f"><span class="vector-icon mw-ui-icon-search mw-ui-icon-wikimedia-search"></span> <span>Search</span> </a> <div class="vector-typeahead-search-container"> <div class="cdx-typeahead-search cdx-typeahead-search--show-thumbnail cdx-typeahead-search--auto-expand-width"> <form action="/w/index.php" id="searchform" class="cdx-search-input cdx-search-input--has-end-button"> <div id="simpleSearch" class="cdx-search-input__input-wrapper" data-search-loc="header-moved"> <div class="cdx-text-input cdx-text-input--has-start-icon"> <input class="cdx-text-input__input" type="search" name="search" placeholder="Search Wikipedia" aria-label="Search Wikipedia" autocapitalize="sentences" title="Search Wikipedia [f]" accesskey="f" id="searchInput" > <span class="cdx-text-input__icon cdx-text-input__start-icon"></span> </div> <input type="hidden" name="title" value="Special:Search"> </div> <button class="cdx-button cdx-search-input__end-button">Search</button> </form> </div> </div> </div> <nav class="vector-user-links vector-user-links-wide" aria-label="Personal tools"> <div class="vector-user-links-main"> <div id="p-vector-user-menu-preferences" class="vector-menu mw-portlet emptyPortlet" > <div class="vector-menu-content"> <ul class="vector-menu-content-list"> </ul> </div> </div> <div id="p-vector-user-menu-userpage" class="vector-menu mw-portlet emptyPortlet" > <div class="vector-menu-content"> <ul class="vector-menu-content-list"> </ul> </div> </div> <nav class="vector-appearance-landmark" aria-label="Appearance"> <div id="vector-appearance-dropdown" class="vector-dropdown " title="Change the appearance of the page's font size, width, and color" > <input type="checkbox" id="vector-appearance-dropdown-checkbox" role="button" aria-haspopup="true" data-event-name="ui.dropdown-vector-appearance-dropdown" class="vector-dropdown-checkbox " aria-label="Appearance" > <label id="vector-appearance-dropdown-label" for="vector-appearance-dropdown-checkbox" class="vector-dropdown-label cdx-button cdx-button--fake-button cdx-button--fake-button--enabled cdx-button--weight-quiet cdx-button--icon-only " aria-hidden="true" ><span class="vector-icon mw-ui-icon-appearance mw-ui-icon-wikimedia-appearance"></span> <span class="vector-dropdown-label-text">Appearance</span> </label> <div class="vector-dropdown-content"> <div id="vector-appearance-unpinned-container" class="vector-unpinned-container"> </div> </div> </div> </nav> <div id="p-vector-user-menu-notifications" class="vector-menu mw-portlet emptyPortlet" > <div class="vector-menu-content"> <ul class="vector-menu-content-list"> </ul> </div> </div> <div id="p-vector-user-menu-overflow" class="vector-menu mw-portlet" > <div class="vector-menu-content"> <ul class="vector-menu-content-list"> <li id="pt-sitesupport-2" class="user-links-collapsible-item mw-list-item user-links-collapsible-item"><a data-mw="interface" href="https://donate.wikimedia.org/?wmf_source=donate&wmf_medium=sidebar&wmf_campaign=en.wikipedia.org&uselang=en" class=""><span>Donate</span></a> </li> <li id="pt-createaccount-2" class="user-links-collapsible-item mw-list-item user-links-collapsible-item"><a data-mw="interface" href="/w/index.php?title=Special:CreateAccount&returnto=Null+%28SQL%29" title="You are encouraged to create an account and log in; however, it is not mandatory" class=""><span>Create account</span></a> </li> <li id="pt-login-2" class="user-links-collapsible-item mw-list-item user-links-collapsible-item"><a data-mw="interface" href="/w/index.php?title=Special:UserLogin&returnto=Null+%28SQL%29" title="You're encouraged to log in; however, it's not mandatory. [o]" accesskey="o" class=""><span>Log in</span></a> </li> </ul> </div> </div> </div> <div id="vector-user-links-dropdown" class="vector-dropdown vector-user-menu vector-button-flush-right vector-user-menu-logged-out" title="Log in and more options" > <input type="checkbox" id="vector-user-links-dropdown-checkbox" role="button" aria-haspopup="true" data-event-name="ui.dropdown-vector-user-links-dropdown" class="vector-dropdown-checkbox " aria-label="Personal tools" > <label id="vector-user-links-dropdown-label" for="vector-user-links-dropdown-checkbox" class="vector-dropdown-label cdx-button cdx-button--fake-button cdx-button--fake-button--enabled cdx-button--weight-quiet cdx-button--icon-only " aria-hidden="true" ><span class="vector-icon mw-ui-icon-ellipsis mw-ui-icon-wikimedia-ellipsis"></span> <span class="vector-dropdown-label-text">Personal tools</span> </label> <div class="vector-dropdown-content"> <div id="p-personal" class="vector-menu mw-portlet mw-portlet-personal user-links-collapsible-item" title="User menu" > <div class="vector-menu-content"> <ul class="vector-menu-content-list"> <li id="pt-sitesupport" class="user-links-collapsible-item mw-list-item"><a href="https://donate.wikimedia.org/?wmf_source=donate&wmf_medium=sidebar&wmf_campaign=en.wikipedia.org&uselang=en"><span>Donate</span></a></li><li id="pt-createaccount" class="user-links-collapsible-item mw-list-item"><a href="/w/index.php?title=Special:CreateAccount&returnto=Null+%28SQL%29" title="You are encouraged to create an account and log in; however, it is not mandatory"><span class="vector-icon mw-ui-icon-userAdd mw-ui-icon-wikimedia-userAdd"></span> <span>Create account</span></a></li><li id="pt-login" class="user-links-collapsible-item mw-list-item"><a href="/w/index.php?title=Special:UserLogin&returnto=Null+%28SQL%29" title="You're encouraged to log in; however, it's not mandatory. [o]" accesskey="o"><span class="vector-icon mw-ui-icon-logIn mw-ui-icon-wikimedia-logIn"></span> <span>Log in</span></a></li> </ul> </div> </div> <div id="p-user-menu-anon-editor" class="vector-menu mw-portlet mw-portlet-user-menu-anon-editor" > <div class="vector-menu-heading"> Pages for logged out editors <a href="/wiki/Help:Introduction" aria-label="Learn more about editing"><span>learn more</span></a> </div> <div class="vector-menu-content"> <ul class="vector-menu-content-list"> <li id="pt-anoncontribs" class="mw-list-item"><a href="/wiki/Special:MyContributions" title="A list of edits made from this IP address [y]" accesskey="y"><span>Contributions</span></a></li><li id="pt-anontalk" class="mw-list-item"><a href="/wiki/Special:MyTalk" title="Discussion about edits from this IP address [n]" accesskey="n"><span>Talk</span></a></li> </ul> </div> </div> </div> </div> </nav> </div> </header> </div> <div class="mw-page-container"> <div class="mw-page-container-inner"> <div class="vector-sitenotice-container"> <div id="siteNotice"></div> </div> <div class="vector-column-start"> <div class="vector-main-menu-container"> <div id="mw-navigation"> <nav id="mw-panel" class="vector-main-menu-landmark" aria-label="Site"> <div id="vector-main-menu-pinned-container" class="vector-pinned-container"> </div> </nav> </div> </div> <div class="vector-sticky-pinned-container"> <nav id="mw-panel-toc" aria-label="Contents" data-event-name="ui.sidebar-toc" class="mw-table-of-contents-container vector-toc-landmark"> <div id="vector-toc-pinned-container" class="vector-pinned-container"> <div id="vector-toc" class="vector-toc vector-pinnable-element"> <div class="vector-pinnable-header vector-toc-pinnable-header vector-pinnable-header-pinned" data-feature-name="toc-pinned" data-pinnable-element-id="vector-toc" > <h2 class="vector-pinnable-header-label">Contents</h2> <button class="vector-pinnable-header-toggle-button vector-pinnable-header-pin-button" data-event-name="pinnable-header.vector-toc.pin">move to sidebar</button> <button class="vector-pinnable-header-toggle-button vector-pinnable-header-unpin-button" data-event-name="pinnable-header.vector-toc.unpin">hide</button> </div> <ul class="vector-toc-contents" id="mw-panel-toc-list"> <li id="toc-mw-content-text" class="vector-toc-list-item vector-toc-level-1"> <a href="#" class="vector-toc-link"> <div class="vector-toc-text">(Top)</div> </a> </li> <li id="toc-History" class="vector-toc-list-item vector-toc-level-1"> <a class="vector-toc-link" href="#History"> <div class="vector-toc-text"> <span class="vector-toc-numb">1</span> <span>History</span> </div> </a> <button aria-controls="toc-History-sublist" class="cdx-button cdx-button--weight-quiet cdx-button--icon-only vector-toc-toggle"> <span class="vector-icon mw-ui-icon-wikimedia-expand"></span> <span>Toggle History subsection</span> </button> <ul id="toc-History-sublist" class="vector-toc-list"> <li id="toc-Challenges" class="vector-toc-list-item vector-toc-level-2"> <a class="vector-toc-link" href="#Challenges"> <div class="vector-toc-text"> <span class="vector-toc-numb">1.1</span> <span>Challenges</span> </div> </a> <ul id="toc-Challenges-sublist" class="vector-toc-list"> </ul> </li> </ul> </li> <li id="toc-Null_propagation" class="vector-toc-list-item vector-toc-level-1"> <a class="vector-toc-link" href="#Null_propagation"> <div class="vector-toc-text"> <span class="vector-toc-numb">2</span> <span>Null propagation</span> </div> </a> <button aria-controls="toc-Null_propagation-sublist" class="cdx-button cdx-button--weight-quiet cdx-button--icon-only vector-toc-toggle"> <span class="vector-icon mw-ui-icon-wikimedia-expand"></span> <span>Toggle Null propagation subsection</span> </button> <ul id="toc-Null_propagation-sublist" class="vector-toc-list"> <li id="toc-Arithmetic_operations" class="vector-toc-list-item vector-toc-level-2"> <a class="vector-toc-link" href="#Arithmetic_operations"> <div class="vector-toc-text"> <span class="vector-toc-numb">2.1</span> <span>Arithmetic operations</span> </div> </a> <ul id="toc-Arithmetic_operations-sublist" class="vector-toc-list"> </ul> </li> <li id="toc-String_concatenation" class="vector-toc-list-item vector-toc-level-2"> <a class="vector-toc-link" href="#String_concatenation"> <div class="vector-toc-text"> <span class="vector-toc-numb">2.2</span> <span>String concatenation</span> </div> </a> <ul id="toc-String_concatenation-sublist" class="vector-toc-list"> </ul> </li> </ul> </li> <li id="toc-Comparisons_with_NULL_and_the_three-valued_logic_(3VL)" class="vector-toc-list-item vector-toc-level-1"> <a class="vector-toc-link" href="#Comparisons_with_NULL_and_the_three-valued_logic_(3VL)"> <div class="vector-toc-text"> <span class="vector-toc-numb">3</span> <span>Comparisons with NULL and the three-valued logic (3VL)</span> </div> </a> <button aria-controls="toc-Comparisons_with_NULL_and_the_three-valued_logic_(3VL)-sublist" class="cdx-button cdx-button--weight-quiet cdx-button--icon-only vector-toc-toggle"> <span class="vector-icon mw-ui-icon-wikimedia-expand"></span> <span>Toggle Comparisons with NULL and the three-valued logic (3VL) subsection</span> </button> <ul id="toc-Comparisons_with_NULL_and_the_three-valued_logic_(3VL)-sublist" class="vector-toc-list"> <li id="toc-Effect_of_Unknown_in_WHERE_clauses" class="vector-toc-list-item vector-toc-level-2"> <a class="vector-toc-link" href="#Effect_of_Unknown_in_WHERE_clauses"> <div class="vector-toc-text"> <span class="vector-toc-numb">3.1</span> <span>Effect of Unknown in WHERE clauses</span> </div> </a> <ul id="toc-Effect_of_Unknown_in_WHERE_clauses-sublist" class="vector-toc-list"> </ul> </li> <li id="toc-Null-specific_and_3VL-specific_comparison_predicates" class="vector-toc-list-item vector-toc-level-2"> <a class="vector-toc-link" href="#Null-specific_and_3VL-specific_comparison_predicates"> <div class="vector-toc-text"> <span class="vector-toc-numb">3.2</span> <span>Null-specific and 3VL-specific comparison predicates</span> </div> </a> <ul id="toc-Null-specific_and_3VL-specific_comparison_predicates-sublist" class="vector-toc-list"> </ul> </li> <li id="toc-Law_of_the_excluded_fourth_(in_WHERE_clauses)" class="vector-toc-list-item vector-toc-level-2"> <a class="vector-toc-link" href="#Law_of_the_excluded_fourth_(in_WHERE_clauses)"> <div class="vector-toc-text"> <span class="vector-toc-numb">3.3</span> <span>Law of the excluded fourth (in WHERE clauses)</span> </div> </a> <ul id="toc-Law_of_the_excluded_fourth_(in_WHERE_clauses)-sublist" class="vector-toc-list"> </ul> </li> <li id="toc-Effect_of_Null_and_Unknown_in_other_constructs" class="vector-toc-list-item vector-toc-level-2"> <a class="vector-toc-link" href="#Effect_of_Null_and_Unknown_in_other_constructs"> <div class="vector-toc-text"> <span class="vector-toc-numb">3.4</span> <span>Effect of Null and Unknown in other constructs</span> </div> </a> <ul id="toc-Effect_of_Null_and_Unknown_in_other_constructs-sublist" class="vector-toc-list"> <li id="toc-Joins" class="vector-toc-list-item vector-toc-level-3"> <a class="vector-toc-link" href="#Joins"> <div class="vector-toc-text"> <span class="vector-toc-numb">3.4.1</span> <span>Joins</span> </div> </a> <ul id="toc-Joins-sublist" class="vector-toc-list"> </ul> </li> <li id="toc-CASE_expressions" class="vector-toc-list-item vector-toc-level-3"> <a class="vector-toc-link" href="#CASE_expressions"> <div class="vector-toc-text"> <span class="vector-toc-numb">3.4.2</span> <span>CASE expressions</span> </div> </a> <ul id="toc-CASE_expressions-sublist" class="vector-toc-list"> </ul> </li> <li id="toc-IF_statements_in_procedural_extensions" class="vector-toc-list-item vector-toc-level-3"> <a class="vector-toc-link" href="#IF_statements_in_procedural_extensions"> <div class="vector-toc-text"> <span class="vector-toc-numb">3.4.3</span> <span>IF statements in procedural extensions</span> </div> </a> <ul id="toc-IF_statements_in_procedural_extensions-sublist" class="vector-toc-list"> </ul> </li> </ul> </li> </ul> </li> <li id="toc-Analysis_of_SQL_Null_missing-value_semantics" class="vector-toc-list-item vector-toc-level-1"> <a class="vector-toc-link" href="#Analysis_of_SQL_Null_missing-value_semantics"> <div class="vector-toc-text"> <span class="vector-toc-numb">4</span> <span>Analysis of SQL Null missing-value semantics</span> </div> </a> <button aria-controls="toc-Analysis_of_SQL_Null_missing-value_semantics-sublist" class="cdx-button cdx-button--weight-quiet cdx-button--icon-only vector-toc-toggle"> <span class="vector-icon mw-ui-icon-wikimedia-expand"></span> <span>Toggle Analysis of SQL Null missing-value semantics subsection</span> </button> <ul id="toc-Analysis_of_SQL_Null_missing-value_semantics-sublist" class="vector-toc-list"> <li id="toc-In_selections_and_projections:_weak_representation" class="vector-toc-list-item vector-toc-level-2"> <a class="vector-toc-link" href="#In_selections_and_projections:_weak_representation"> <div class="vector-toc-text"> <span class="vector-toc-numb">4.1</span> <span>In selections and projections: weak representation</span> </div> </a> <ul id="toc-In_selections_and_projections:_weak_representation-sublist" class="vector-toc-list"> </ul> </li> <li id="toc-If_joins_or_unions_are_considered:_not_even_weak_representation" class="vector-toc-list-item vector-toc-level-2"> <a class="vector-toc-link" href="#If_joins_or_unions_are_considered:_not_even_weak_representation"> <div class="vector-toc-text"> <span class="vector-toc-numb">4.2</span> <span>If joins or unions are considered: not even weak representation</span> </div> </a> <ul id="toc-If_joins_or_unions_are_considered:_not_even_weak_representation-sublist" class="vector-toc-list"> </ul> </li> </ul> </li> <li id="toc-Check_constraints_and_foreign_keys" class="vector-toc-list-item vector-toc-level-1"> <a class="vector-toc-link" href="#Check_constraints_and_foreign_keys"> <div class="vector-toc-text"> <span class="vector-toc-numb">5</span> <span>Check constraints and foreign keys</span> </div> </a> <ul id="toc-Check_constraints_and_foreign_keys-sublist" class="vector-toc-list"> </ul> </li> <li id="toc-Outer_joins" class="vector-toc-list-item vector-toc-level-1"> <a class="vector-toc-link" href="#Outer_joins"> <div class="vector-toc-text"> <span class="vector-toc-numb">6</span> <span>Outer joins</span> </div> </a> <ul id="toc-Outer_joins-sublist" class="vector-toc-list"> </ul> </li> <li id="toc-Aggregate_functions" class="vector-toc-list-item vector-toc-level-1"> <a class="vector-toc-link" href="#Aggregate_functions"> <div class="vector-toc-text"> <span class="vector-toc-numb">7</span> <span>Aggregate functions</span> </div> </a> <ul id="toc-Aggregate_functions-sublist" class="vector-toc-list"> </ul> </li> <li id="toc-When_two_nulls_are_equal:_grouping,_sorting,_and_some_set_operations" class="vector-toc-list-item vector-toc-level-1"> <a class="vector-toc-link" href="#When_two_nulls_are_equal:_grouping,_sorting,_and_some_set_operations"> <div class="vector-toc-text"> <span class="vector-toc-numb">8</span> <span>When two nulls are equal: grouping, sorting, and some set operations</span> </div> </a> <ul id="toc-When_two_nulls_are_equal:_grouping,_sorting,_and_some_set_operations-sublist" class="vector-toc-list"> </ul> </li> <li id="toc-Effect_on_index_operation" class="vector-toc-list-item vector-toc-level-1"> <a class="vector-toc-link" href="#Effect_on_index_operation"> <div class="vector-toc-text"> <span class="vector-toc-numb">9</span> <span>Effect on index operation</span> </div> </a> <ul id="toc-Effect_on_index_operation-sublist" class="vector-toc-list"> </ul> </li> <li id="toc-Null-handling_functions" class="vector-toc-list-item vector-toc-level-1"> <a class="vector-toc-link" href="#Null-handling_functions"> <div class="vector-toc-text"> <span class="vector-toc-numb">10</span> <span>Null-handling functions</span> </div> </a> <button aria-controls="toc-Null-handling_functions-sublist" class="cdx-button cdx-button--weight-quiet cdx-button--icon-only vector-toc-toggle"> <span class="vector-icon mw-ui-icon-wikimedia-expand"></span> <span>Toggle Null-handling functions subsection</span> </button> <ul id="toc-Null-handling_functions-sublist" class="vector-toc-list"> <li id="toc-NULLIF" class="vector-toc-list-item vector-toc-level-2"> <a class="vector-toc-link" href="#NULLIF"> <div class="vector-toc-text"> <span class="vector-toc-numb">10.1</span> <span>NULLIF</span> </div> </a> <ul id="toc-NULLIF-sublist" class="vector-toc-list"> </ul> </li> <li id="toc-COALESCE" class="vector-toc-list-item vector-toc-level-2"> <a class="vector-toc-link" href="#COALESCE"> <div class="vector-toc-text"> <span class="vector-toc-numb">10.2</span> <span>COALESCE</span> </div> </a> <ul id="toc-COALESCE-sublist" class="vector-toc-list"> </ul> </li> <li id="toc-NVL" class="vector-toc-list-item vector-toc-level-2"> <a class="vector-toc-link" href="#NVL"> <div class="vector-toc-text"> <span class="vector-toc-numb">10.3</span> <span>NVL</span> </div> </a> <ul id="toc-NVL-sublist" class="vector-toc-list"> </ul> </li> </ul> </li> <li id="toc-Data_typing_of_Null_and_Unknown" class="vector-toc-list-item vector-toc-level-1"> <a class="vector-toc-link" href="#Data_typing_of_Null_and_Unknown"> <div class="vector-toc-text"> <span class="vector-toc-numb">11</span> <span>Data typing of Null and Unknown</span> </div> </a> <ul id="toc-Data_typing_of_Null_and_Unknown-sublist" class="vector-toc-list"> </ul> </li> <li id="toc-BOOLEAN_data_type" class="vector-toc-list-item vector-toc-level-1"> <a class="vector-toc-link" href="#BOOLEAN_data_type"> <div class="vector-toc-text"> <span class="vector-toc-numb">12</span> <span>BOOLEAN data type</span> </div> </a> <ul id="toc-BOOLEAN_data_type-sublist" class="vector-toc-list"> </ul> </li> <li id="toc-Controversy" class="vector-toc-list-item vector-toc-level-1"> <a class="vector-toc-link" href="#Controversy"> <div class="vector-toc-text"> <span class="vector-toc-numb">13</span> <span>Controversy</span> </div> </a> <button aria-controls="toc-Controversy-sublist" class="cdx-button cdx-button--weight-quiet cdx-button--icon-only vector-toc-toggle"> <span class="vector-icon mw-ui-icon-wikimedia-expand"></span> <span>Toggle Controversy subsection</span> </button> <ul id="toc-Controversy-sublist" class="vector-toc-list"> <li id="toc-Common_mistakes" class="vector-toc-list-item vector-toc-level-2"> <a class="vector-toc-link" href="#Common_mistakes"> <div class="vector-toc-text"> <span class="vector-toc-numb">13.1</span> <span>Common mistakes</span> </div> </a> <ul id="toc-Common_mistakes-sublist" class="vector-toc-list"> </ul> </li> <li id="toc-Criticisms" class="vector-toc-list-item vector-toc-level-2"> <a class="vector-toc-link" href="#Criticisms"> <div class="vector-toc-text"> <span class="vector-toc-numb">13.2</span> <span>Criticisms</span> </div> </a> <ul id="toc-Criticisms-sublist" class="vector-toc-list"> </ul> </li> <li id="toc-Closed-world_assumption" class="vector-toc-list-item vector-toc-level-2"> <a class="vector-toc-link" href="#Closed-world_assumption"> <div class="vector-toc-text"> <span class="vector-toc-numb">13.3</span> <span>Closed-world assumption</span> </div> </a> <ul id="toc-Closed-world_assumption-sublist" class="vector-toc-list"> </ul> </li> </ul> </li> <li id="toc-See_also" class="vector-toc-list-item vector-toc-level-1"> <a class="vector-toc-link" href="#See_also"> <div class="vector-toc-text"> <span class="vector-toc-numb">14</span> <span>See also</span> </div> </a> <ul id="toc-See_also-sublist" class="vector-toc-list"> </ul> </li> <li id="toc-References" class="vector-toc-list-item vector-toc-level-1"> <a class="vector-toc-link" href="#References"> <div class="vector-toc-text"> <span class="vector-toc-numb">15</span> <span>References</span> </div> </a> <ul id="toc-References-sublist" class="vector-toc-list"> </ul> </li> <li id="toc-Further_reading" class="vector-toc-list-item vector-toc-level-1"> <a class="vector-toc-link" href="#Further_reading"> <div class="vector-toc-text"> <span class="vector-toc-numb">16</span> <span>Further reading</span> </div> </a> <ul id="toc-Further_reading-sublist" class="vector-toc-list"> </ul> </li> <li id="toc-External_links" class="vector-toc-list-item vector-toc-level-1"> <a class="vector-toc-link" href="#External_links"> <div class="vector-toc-text"> <span class="vector-toc-numb">17</span> <span>External links</span> </div> </a> <ul id="toc-External_links-sublist" class="vector-toc-list"> </ul> </li> </ul> </div> </div> </nav> </div> </div> <div class="mw-content-container"> <main id="content" class="mw-body"> <header class="mw-body-header vector-page-titlebar"> <nav aria-label="Contents" class="vector-toc-landmark"> <div id="vector-page-titlebar-toc" class="vector-dropdown vector-page-titlebar-toc vector-button-flush-left" title="Table of Contents" > <input type="checkbox" id="vector-page-titlebar-toc-checkbox" role="button" aria-haspopup="true" data-event-name="ui.dropdown-vector-page-titlebar-toc" class="vector-dropdown-checkbox " aria-label="Toggle the table of contents" > <label id="vector-page-titlebar-toc-label" for="vector-page-titlebar-toc-checkbox" class="vector-dropdown-label cdx-button cdx-button--fake-button cdx-button--fake-button--enabled cdx-button--weight-quiet cdx-button--icon-only " aria-hidden="true" ><span class="vector-icon mw-ui-icon-listBullet mw-ui-icon-wikimedia-listBullet"></span> <span class="vector-dropdown-label-text">Toggle the table of contents</span> </label> <div class="vector-dropdown-content"> <div id="vector-page-titlebar-toc-unpinned-container" class="vector-unpinned-container"> </div> </div> </div> </nav> <h1 id="firstHeading" class="firstHeading mw-first-heading"><span class="mw-page-title-main">Null (SQL)</span></h1> <div id="p-lang-btn" class="vector-dropdown mw-portlet mw-portlet-lang" > <input type="checkbox" id="p-lang-btn-checkbox" role="button" aria-haspopup="true" data-event-name="ui.dropdown-p-lang-btn" class="vector-dropdown-checkbox mw-interlanguage-selector" aria-label="Go to an article in another language. Available in 11 languages" > <label id="p-lang-btn-label" for="p-lang-btn-checkbox" class="vector-dropdown-label cdx-button cdx-button--fake-button cdx-button--fake-button--enabled cdx-button--weight-quiet cdx-button--action-progressive mw-portlet-lang-heading-11" aria-hidden="true" ><span class="vector-icon mw-ui-icon-language-progressive mw-ui-icon-wikimedia-language-progressive"></span> <span class="vector-dropdown-label-text">11 languages</span> </label> <div class="vector-dropdown-content"> <div class="vector-menu-content"> <ul class="vector-menu-content-list"> <li class="interlanguage-link interwiki-ar mw-list-item"><a href="https://ar.wikipedia.org/wiki/%D9%82%D9%8A%D9%85%D8%A9_%D9%81%D8%A7%D8%B1%D8%BA%D8%A9_(%D8%A5%D8%B3_%D9%83%D9%8A%D9%88_%D8%A5%D9%84)" title="قيمة فارغة (إس كيو إل) – Arabic" lang="ar" hreflang="ar" data-title="قيمة فارغة (إس كيو إل)" data-language-autonym="العربية" data-language-local-name="Arabic" class="interlanguage-link-target"><span>العربية</span></a></li><li class="interlanguage-link interwiki-az mw-list-item"><a href="https://az.wikipedia.org/wiki/Null_(SQL)" title="Null (SQL) – Azerbaijani" lang="az" hreflang="az" data-title="Null (SQL)" data-language-autonym="Azərbaycanca" data-language-local-name="Azerbaijani" class="interlanguage-link-target"><span>Azərbaycanca</span></a></li><li class="interlanguage-link interwiki-bn mw-list-item"><a href="https://bn.wikipedia.org/wiki/%E0%A6%A8%E0%A6%BE%E0%A6%B2_(%E0%A6%8F%E0%A6%B8%E0%A6%95%E0%A6%BF%E0%A6%89%E0%A6%8F%E0%A6%B2)" title="নাল (এসকিউএল) – Bangla" lang="bn" hreflang="bn" data-title="নাল (এসকিউএল)" data-language-autonym="বাংলা" data-language-local-name="Bangla" class="interlanguage-link-target"><span>বাংলা</span></a></li><li class="interlanguage-link interwiki-el mw-list-item"><a href="https://el.wikipedia.org/wiki/Null_(SQL)" title="Null (SQL) – Greek" lang="el" hreflang="el" data-title="Null (SQL)" data-language-autonym="Ελληνικά" data-language-local-name="Greek" class="interlanguage-link-target"><span>Ελληνικά</span></a></li><li class="interlanguage-link interwiki-es mw-list-item"><a href="https://es.wikipedia.org/wiki/Null_(SQL)" title="Null (SQL) – Spanish" lang="es" hreflang="es" data-title="Null (SQL)" data-language-autonym="Español" data-language-local-name="Spanish" class="interlanguage-link-target"><span>Español</span></a></li><li class="interlanguage-link interwiki-ko mw-list-item"><a href="https://ko.wikipedia.org/wiki/Null_(SQL)" title="Null (SQL) – Korean" lang="ko" hreflang="ko" data-title="Null (SQL)" data-language-autonym="한국어" data-language-local-name="Korean" class="interlanguage-link-target"><span>한국어</span></a></li><li class="interlanguage-link interwiki-pl badge-Q17437798 badge-goodarticle mw-list-item" title="good article badge"><a href="https://pl.wikipedia.org/wiki/Null_(SQL)" title="Null (SQL) – Polish" lang="pl" hreflang="pl" data-title="Null (SQL)" data-language-autonym="Polski" data-language-local-name="Polish" class="interlanguage-link-target"><span>Polski</span></a></li><li class="interlanguage-link interwiki-pt mw-list-item"><a href="https://pt.wikipedia.org/wiki/Null_(SQL)" title="Null (SQL) – Portuguese" lang="pt" hreflang="pt" data-title="Null (SQL)" data-language-autonym="Português" data-language-local-name="Portuguese" class="interlanguage-link-target"><span>Português</span></a></li><li class="interlanguage-link interwiki-ru mw-list-item"><a href="https://ru.wikipedia.org/wiki/NULL_(SQL)" title="NULL (SQL) – Russian" lang="ru" hreflang="ru" data-title="NULL (SQL)" data-language-autonym="Русский" data-language-local-name="Russian" class="interlanguage-link-target"><span>Русский</span></a></li><li class="interlanguage-link interwiki-uk mw-list-item"><a href="https://uk.wikipedia.org/wiki/Null_(SQL)" title="Null (SQL) – Ukrainian" lang="uk" hreflang="uk" data-title="Null (SQL)" data-language-autonym="Українська" data-language-local-name="Ukrainian" class="interlanguage-link-target"><span>Українська</span></a></li><li class="interlanguage-link interwiki-zh mw-list-item"><a href="https://zh.wikipedia.org/wiki/%E7%A9%BA%E5%80%BC_(SQL)" title="空值 (SQL) – Chinese" lang="zh" hreflang="zh" data-title="空值 (SQL)" data-language-autonym="中文" data-language-local-name="Chinese" class="interlanguage-link-target"><span>中文</span></a></li> </ul> <div class="after-portlet after-portlet-lang"><span class="wb-langlinks-edit wb-langlinks-link"><a href="https://www.wikidata.org/wiki/Special:EntityPage/Q371029#sitelinks-wikipedia" title="Edit interlanguage links" class="wbc-editpage">Edit links</a></span></div> </div> </div> </div> </header> <div class="vector-page-toolbar"> <div class="vector-page-toolbar-container"> <div id="left-navigation"> <nav aria-label="Namespaces"> <div id="p-associated-pages" class="vector-menu vector-menu-tabs mw-portlet mw-portlet-associated-pages" > <div class="vector-menu-content"> <ul class="vector-menu-content-list"> <li id="ca-nstab-main" class="selected vector-tab-noicon mw-list-item"><a href="/wiki/Null_(SQL)" title="View the content page [c]" accesskey="c"><span>Article</span></a></li><li id="ca-talk" class="vector-tab-noicon mw-list-item"><a href="/wiki/Talk:Null_(SQL)" rel="discussion" title="Discuss improvements to the content page [t]" accesskey="t"><span>Talk</span></a></li> </ul> </div> </div> <div id="vector-variants-dropdown" class="vector-dropdown emptyPortlet" > <input type="checkbox" id="vector-variants-dropdown-checkbox" role="button" aria-haspopup="true" data-event-name="ui.dropdown-vector-variants-dropdown" class="vector-dropdown-checkbox " aria-label="Change language variant" > <label id="vector-variants-dropdown-label" for="vector-variants-dropdown-checkbox" class="vector-dropdown-label cdx-button cdx-button--fake-button cdx-button--fake-button--enabled cdx-button--weight-quiet" aria-hidden="true" ><span class="vector-dropdown-label-text">English</span> </label> <div class="vector-dropdown-content"> <div id="p-variants" class="vector-menu mw-portlet mw-portlet-variants emptyPortlet" > <div class="vector-menu-content"> <ul class="vector-menu-content-list"> </ul> </div> </div> </div> </div> </nav> </div> <div id="right-navigation" class="vector-collapsible"> <nav aria-label="Views"> <div id="p-views" class="vector-menu vector-menu-tabs mw-portlet mw-portlet-views" > <div class="vector-menu-content"> <ul class="vector-menu-content-list"> <li id="ca-view" class="selected vector-tab-noicon mw-list-item"><a href="/wiki/Null_(SQL)"><span>Read</span></a></li><li id="ca-edit" class="vector-tab-noicon mw-list-item"><a href="/w/index.php?title=Null_(SQL)&action=edit" title="Edit this page [e]" accesskey="e"><span>Edit</span></a></li><li id="ca-history" class="vector-tab-noicon mw-list-item"><a href="/w/index.php?title=Null_(SQL)&action=history" title="Past revisions of this page [h]" accesskey="h"><span>View history</span></a></li> </ul> </div> </div> </nav> <nav class="vector-page-tools-landmark" aria-label="Page tools"> <div id="vector-page-tools-dropdown" class="vector-dropdown vector-page-tools-dropdown" > <input type="checkbox" id="vector-page-tools-dropdown-checkbox" role="button" aria-haspopup="true" data-event-name="ui.dropdown-vector-page-tools-dropdown" class="vector-dropdown-checkbox " aria-label="Tools" > <label id="vector-page-tools-dropdown-label" for="vector-page-tools-dropdown-checkbox" class="vector-dropdown-label cdx-button cdx-button--fake-button cdx-button--fake-button--enabled cdx-button--weight-quiet" aria-hidden="true" ><span class="vector-dropdown-label-text">Tools</span> </label> <div class="vector-dropdown-content"> <div id="vector-page-tools-unpinned-container" class="vector-unpinned-container"> <div id="vector-page-tools" class="vector-page-tools vector-pinnable-element"> <div class="vector-pinnable-header vector-page-tools-pinnable-header vector-pinnable-header-unpinned" data-feature-name="page-tools-pinned" data-pinnable-element-id="vector-page-tools" data-pinned-container-id="vector-page-tools-pinned-container" data-unpinned-container-id="vector-page-tools-unpinned-container" > <div class="vector-pinnable-header-label">Tools</div> <button class="vector-pinnable-header-toggle-button vector-pinnable-header-pin-button" data-event-name="pinnable-header.vector-page-tools.pin">move to sidebar</button> <button class="vector-pinnable-header-toggle-button vector-pinnable-header-unpin-button" data-event-name="pinnable-header.vector-page-tools.unpin">hide</button> </div> <div id="p-cactions" class="vector-menu mw-portlet mw-portlet-cactions emptyPortlet vector-has-collapsible-items" title="More options" > <div class="vector-menu-heading"> Actions </div> <div class="vector-menu-content"> <ul class="vector-menu-content-list"> <li id="ca-more-view" class="selected vector-more-collapsible-item mw-list-item"><a href="/wiki/Null_(SQL)"><span>Read</span></a></li><li id="ca-more-edit" class="vector-more-collapsible-item mw-list-item"><a href="/w/index.php?title=Null_(SQL)&action=edit" title="Edit this page [e]" accesskey="e"><span>Edit</span></a></li><li id="ca-more-history" class="vector-more-collapsible-item mw-list-item"><a href="/w/index.php?title=Null_(SQL)&action=history"><span>View history</span></a></li> </ul> </div> </div> <div id="p-tb" class="vector-menu mw-portlet mw-portlet-tb" > <div class="vector-menu-heading"> General </div> <div class="vector-menu-content"> <ul class="vector-menu-content-list"> <li id="t-whatlinkshere" class="mw-list-item"><a href="/wiki/Special:WhatLinksHere/Null_(SQL)" title="List of all English Wikipedia pages containing links to this page [j]" accesskey="j"><span>What links here</span></a></li><li id="t-recentchangeslinked" class="mw-list-item"><a href="/wiki/Special:RecentChangesLinked/Null_(SQL)" rel="nofollow" title="Recent changes in pages linked from this page [k]" accesskey="k"><span>Related changes</span></a></li><li id="t-upload" class="mw-list-item"><a href="//en.wikipedia.org/wiki/Wikipedia:File_Upload_Wizard" title="Upload files [u]" accesskey="u"><span>Upload file</span></a></li><li id="t-permalink" class="mw-list-item"><a href="/w/index.php?title=Null_(SQL)&oldid=1271809523" title="Permanent link to this revision of this page"><span>Permanent link</span></a></li><li id="t-info" class="mw-list-item"><a href="/w/index.php?title=Null_(SQL)&action=info" title="More information about this page"><span>Page information</span></a></li><li id="t-cite" class="mw-list-item"><a href="/w/index.php?title=Special:CiteThisPage&page=Null_%28SQL%29&id=1271809523&wpFormIdentifier=titleform" title="Information on how to cite this page"><span>Cite this page</span></a></li><li id="t-urlshortener" class="mw-list-item"><a href="/w/index.php?title=Special:UrlShortener&url=https%3A%2F%2Fen.wikipedia.org%2Fwiki%2FNull_%28SQL%29"><span>Get shortened URL</span></a></li><li id="t-urlshortener-qrcode" class="mw-list-item"><a href="/w/index.php?title=Special:QrCode&url=https%3A%2F%2Fen.wikipedia.org%2Fwiki%2FNull_%28SQL%29"><span>Download QR code</span></a></li> </ul> </div> </div> <div id="p-coll-print_export" class="vector-menu mw-portlet mw-portlet-coll-print_export" > <div class="vector-menu-heading"> Print/export </div> <div class="vector-menu-content"> <ul class="vector-menu-content-list"> <li id="coll-download-as-rl" class="mw-list-item"><a href="/w/index.php?title=Special:DownloadAsPdf&page=Null_%28SQL%29&action=show-download-screen" title="Download this page as a PDF file"><span>Download as PDF</span></a></li><li id="t-print" class="mw-list-item"><a href="/w/index.php?title=Null_(SQL)&printable=yes" title="Printable version of this page [p]" accesskey="p"><span>Printable version</span></a></li> </ul> </div> </div> <div id="p-wikibase-otherprojects" class="vector-menu mw-portlet mw-portlet-wikibase-otherprojects" > <div class="vector-menu-heading"> In other projects </div> <div class="vector-menu-content"> <ul class="vector-menu-content-list"> <li id="t-wikibase" class="wb-otherproject-link wb-otherproject-wikibase-dataitem mw-list-item"><a href="https://www.wikidata.org/wiki/Special:EntityPage/Q371029" title="Structured data on this page hosted by Wikidata [g]" accesskey="g"><span>Wikidata item</span></a></li> </ul> </div> </div> </div> </div> </div> </div> </nav> </div> </div> </div> <div class="vector-column-end"> <div class="vector-sticky-pinned-container"> <nav class="vector-page-tools-landmark" aria-label="Page tools"> <div id="vector-page-tools-pinned-container" class="vector-pinned-container"> </div> </nav> <nav class="vector-appearance-landmark" aria-label="Appearance"> <div id="vector-appearance-pinned-container" class="vector-pinned-container"> <div id="vector-appearance" class="vector-appearance vector-pinnable-element"> <div class="vector-pinnable-header vector-appearance-pinnable-header vector-pinnable-header-pinned" data-feature-name="appearance-pinned" data-pinnable-element-id="vector-appearance" data-pinned-container-id="vector-appearance-pinned-container" data-unpinned-container-id="vector-appearance-unpinned-container" > <div class="vector-pinnable-header-label">Appearance</div> <button class="vector-pinnable-header-toggle-button vector-pinnable-header-pin-button" data-event-name="pinnable-header.vector-appearance.pin">move to sidebar</button> <button class="vector-pinnable-header-toggle-button vector-pinnable-header-unpin-button" data-event-name="pinnable-header.vector-appearance.unpin">hide</button> </div> </div> </div> </nav> </div> </div> <div id="bodyContent" class="vector-body" aria-labelledby="firstHeading" data-mw-ve-target-container> <div class="vector-body-before-content"> <div class="mw-indicators"> </div> <div id="siteSub" class="noprint">From Wikipedia, the free encyclopedia</div> </div> <div id="contentSub"><div id="mw-content-subtitle"></div></div> <div id="mw-content-text" class="mw-body-content"><div class="mw-content-ltr mw-parser-output" lang="en" dir="ltr"><div class="shortdescription nomobile noexcerpt noprint searchaux" style="display:none">Marker used in SQL databases to indicate a value does not exist</div> <figure class="mw-halign-right" typeof="mw:File/Thumb"><a href="/wiki/File:Db_null.png" class="mw-file-description"><img src="//upload.wikimedia.org/wikipedia/commons/3/3a/Db_null.png" decoding="async" width="128" height="128" class="mw-file-element" data-file-width="128" data-file-height="128" /></a><figcaption>The Greek lowercase <a href="/wiki/Omega" title="Omega">omega (ω)</a> character is used to represent Null in <a href="/wiki/Database_theory" title="Database theory">database theory</a>.</figcaption></figure> <p>In <a href="/wiki/SQL" title="SQL">SQL</a>, <b>null</b> or <b>NULL</b> is a special marker used to indicate that a data value does not exist in the <a href="/wiki/Database" title="Database">database</a>. Introduced by the creator of the <a href="/wiki/Relational_model" title="Relational model">relational</a> database model, <a href="/wiki/E._F._Codd" class="mw-redirect" title="E. F. Codd">E. F. Codd</a>, SQL null serves to fulfill the requirement that all <i>true relational database management systems (<a href="/wiki/Relational_database#RDBMS" title="Relational database">RDBMS</a>)</i> support a representation of "missing information and inapplicable information". Codd also introduced the use of the lowercase Greek <a href="/wiki/Omega" title="Omega">omega</a> (ω) symbol to represent null in <a href="/wiki/Database_theory" title="Database theory">database theory</a>. In SQL, <code>NULL</code> is a <a href="/wiki/List_of_SQL_reserved_words" title="List of SQL reserved words">reserved word</a> used to identify this marker. </p><p>A null should not be confused with a value of <a href="/wiki/0" title="0">0</a>. A null indicates a lack of a value, which is not the same as a zero value. For example, consider the question "How many books does Adam own?" The answer may be "zero" (we <i>know</i> that he owns <i>none</i>) or "null" (we <i>do not know</i> how many he owns). In a database table, the <a href="/wiki/Column_(database)" title="Column (database)">column</a> reporting this answer would start with no value (marked by null), and it would not be updated with the value zero until it is ascertained that Adam owns no books. </p><p>In SQL, null is a marker, not a value. This usage is quite different from most programming languages, where a <a href="/wiki/Null_pointer" title="Null pointer">null value</a> of a reference means it is not pointing to any <a href="/wiki/Object_(computer_science)" title="Object (computer science)">object</a>. </p> <meta property="mw:PageProp/toc" /> <div class="mw-heading mw-heading2"><h2 id="History">History</h2><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=1" title="Edit section: History"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <p>E. F. Codd mentioned nulls as a method of representing missing data in the <a href="/wiki/Relational_model" title="Relational model">relational model</a> in a 1975 paper in the <i>FDT Bulletin of <a href="/wiki/Association_for_Computing_Machinery" title="Association for Computing Machinery">ACM</a>-<a href="/wiki/SIGMOD" title="SIGMOD">SIGMOD</a></i>. Codd's paper that is most commonly cited with the semantics of Null (as adopted in SQL) is his 1979 paper in the <i><a href="/wiki/ACM_Transactions_on_Database_Systems" title="ACM Transactions on Database Systems">ACM Transactions on Database Systems</a></i>, in which he also introduced his <a href="/wiki/Relational_Model/Tasmania" title="Relational Model/Tasmania">Relational Model/Tasmania</a>, although much of the other proposals from the latter paper have remained obscure. Section 2.3 of his 1979 paper details the semantics of Null propagation in arithmetic operations as well as comparisons employing a <a href="/wiki/Ternary_logic" class="mw-redirect" title="Ternary logic">ternary (three-valued)</a> logic when comparing to nulls; it also details the treatment of Nulls on other set operations (the latter issue still controversial today). In <a href="/wiki/Database_theory" title="Database theory">database theory</a> circles, the original proposal of Codd (1975, 1979) is now referred to as "Codd tables".<sup id="cite_ref-Meyden_1-0" class="reference"><a href="#cite_note-Meyden-1"><span class="cite-bracket">[</span>1<span class="cite-bracket">]</span></a></sup> Codd later reinforced his requirement that all RDBMSs support Null to indicate missing data in a 1985 two-part article published in <i><a href="/wiki/Computerworld" title="Computerworld">Computerworld</a></i> magazine.<sup id="cite_ref-2" class="reference"><a href="#cite_note-2"><span class="cite-bracket">[</span>2<span class="cite-bracket">]</span></a></sup><sup id="cite_ref-3" class="reference"><a href="#cite_note-3"><span class="cite-bracket">[</span>3<span class="cite-bracket">]</span></a></sup> </p><p>The 1986 SQL standard basically adopted Codd's proposal after an implementation prototype in <a href="/wiki/IBM_System_R" title="IBM System R">IBM System R</a>. Although <a href="/wiki/Don_Chamberlin" class="mw-redirect" title="Don Chamberlin">Don Chamberlin</a> recognized nulls (alongside duplicate rows) as one of the most controversial features of SQL, he defended the design of Nulls in SQL invoking the pragmatic arguments that it was the least expensive form of system support for missing information, saving the programmer from many duplicative application-level checks (see <a href="/wiki/Semipredicate_problem" title="Semipredicate problem">semipredicate problem</a>) while at the same time providing the database designer with the option not to use Nulls if they so desire; for example, to avoid well-known anomalies (discussed in the <a href="#missing-value_semantics">semantics section</a> of this article). Chamberlin also argued that besides providing some missing-value functionality, practical experience with Nulls also led to other language features that rely on Nulls, like certain grouping constructs and outer joins. Finally, he argued that in practice Nulls also end up being used as a quick way to patch an existing <a href="/wiki/Database_schema" title="Database schema">schema</a> when it needs to evolve beyond its original intent, coding not for missing but rather for inapplicable information; for example, a database that quickly needs to support electric cars while having a miles-per-gallon column.<sup id="cite_ref-Chamberlin1998_4-0" class="reference"><a href="#cite_note-Chamberlin1998-4"><span class="cite-bracket">[</span>4<span class="cite-bracket">]</span></a></sup> </p><p>Codd indicated in his 1990 book <i>The Relational Model for Database Management, Version 2</i> that the single Null mandated by the SQL standard was inadequate, and should be replaced by two separate Null-type markers to indicate why data is missing. In Codd's book, these two Null-type markers are referred to as 'A-Values' and 'I-Values', representing 'Missing But Applicable' and 'Missing But Inapplicable', respectively.<sup id="cite_ref-isbn0201141922_5-0" class="reference"><a href="#cite_note-isbn0201141922-5"><span class="cite-bracket">[</span>5<span class="cite-bracket">]</span></a></sup> Codd's recommendation would have required SQL's logic system be expanded to accommodate a four-valued logic system. Because of this additional complexity, the idea of multiple Nulls with different definitions has not gained widespread acceptance in the database practitioners' domain. It remains an active field of research though, with numerous papers still being published. </p> <div class="mw-heading mw-heading3"><h3 id="Challenges">Challenges</h3><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=2" title="Edit section: Challenges"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <p>Null has been the focus of controversy and a source of debate because of its associated <a href="/wiki/Three-valued_logic" title="Three-valued logic">three-valued logic</a> (3VL), special requirements for its use in <a href="/wiki/Join_(SQL)" title="Join (SQL)">SQL joins</a>, and the special handling required by aggregate functions and SQL grouping operators. Computer science professor Ron van der Meyden summarized the various issues as: "The inconsistencies in the SQL standard mean that it is not possible to ascribe any intuitive logical semantics to the treatment of nulls in SQL."<sup id="cite_ref-Meyden_1-1" class="reference"><a href="#cite_note-Meyden-1"><span class="cite-bracket">[</span>1<span class="cite-bracket">]</span></a></sup> Although various proposals have been made for resolving these issues, the complexity of the alternatives has prevented their widespread adoption. </p> <div class="mw-heading mw-heading2"><h2 id="Null_propagation">Null propagation</h2><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=3" title="Edit section: Null propagation"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <div class="mw-heading mw-heading3"><h3 id="Arithmetic_operations">Arithmetic operations</h3><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=4" title="Edit section: Arithmetic operations"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <p>Because Null is not a data value, but a marker for an absent value, using mathematical operators on Null gives an unknown result, which is represented by Null.<sup id="cite_ref-SQL2003-Part2-Sec626_6-0" class="reference"><a href="#cite_note-SQL2003-Part2-Sec626-6"><span class="cite-bracket">[</span>6<span class="cite-bracket">]</span></a></sup> In the following example, multiplying 10 by Null results in Null: </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="mi">10</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="k">NULL</span><span class="w"> </span><span class="c1">-- Result is NULL</span> </pre></div> <p>This can lead to unanticipated results. For instance, when an attempt is made to divide Null by zero, platforms may return Null instead of throwing an expected "data exception –  division by zero".<sup id="cite_ref-SQL2003-Part2-Sec626_6-1" class="reference"><a href="#cite_note-SQL2003-Part2-Sec626-6"><span class="cite-bracket">[</span>6<span class="cite-bracket">]</span></a></sup> Though this behavior is not defined by the ISO SQL standard many DBMS vendors treat this operation similarly. For instance, the Oracle, PostgreSQL, MySQL Server, and Microsoft SQL Server platforms all return a Null result for the following: </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">NULL</span><span class="w"> </span><span class="o">/</span><span class="w"> </span><span class="mi">0</span> </pre></div> <div class="mw-heading mw-heading3"><h3 id="String_concatenation">String concatenation</h3><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=5" title="Edit section: String concatenation"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <p>String <a href="/wiki/Concatenation" title="Concatenation">concatenation</a> operations, which are common in SQL, also result in Null when one of the operands is Null.<sup id="cite_ref-SQL2003-Part2-Sec628_7-0" class="reference"><a href="#cite_note-SQL2003-Part2-Sec628-7"><span class="cite-bracket">[</span>7<span class="cite-bracket">]</span></a></sup> The following example demonstrates the Null result returned by using Null with the SQL <code>||</code> string concatenation operator. </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="s1">'Fish '</span><span class="w"> </span><span class="o">||</span><span class="w"> </span><span class="k">NULL</span><span class="w"> </span><span class="o">||</span><span class="w"> </span><span class="s1">'Chips'</span><span class="w"> </span><span class="c1">-- Result is NULL</span> </pre></div> <p>This is not true for all database implementations. In an Oracle RDBMS, for example, NULL and the empty string are considered the same thing and therefore 'Fish ' || NULL || 'Chips' results in 'Fish Chips'.<sup id="cite_ref-8" class="reference"><a href="#cite_note-8"><span class="cite-bracket">[</span>8<span class="cite-bracket">]</span></a></sup> </p> <div class="mw-heading mw-heading2"><h2 id="Comparisons_with_NULL_and_the_three-valued_logic_(3VL)"><span id="Comparisons_with_NULL_and_the_three-valued_logic_.283VL.29"></span>Comparisons with NULL and the three-valued logic (3VL)</h2><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=6" title="Edit section: Comparisons with NULL and the three-valued logic (3VL)"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <style data-mw-deduplicate="TemplateStyles:r1236090951">.mw-parser-output .hatnote{font-style:italic}.mw-parser-output div.hatnote{padding-left:1.6em;margin-bottom:0.5em}.mw-parser-output .hatnote i{font-style:normal}.mw-parser-output .hatnote+link+.hatnote{margin-top:-0.5em}@media print{body.ns-0 .mw-parser-output .hatnote{display:none!important}}</style><div role="note" class="hatnote navigation-not-searchable">Further information: <a href="/wiki/Three-valued_logic" title="Three-valued logic">Three-valued logic</a></div> <p>Since Null is not a member of any <a href="/wiki/Data_domain" title="Data domain">data domain</a>, it is not considered a "value", but rather a marker (or placeholder) indicating the <a href="/wiki/Undefined_value" title="Undefined value">undefined value</a>. Because of this, comparisons with Null can never result in either True or False, but always in a third logical result, Unknown.<sup id="cite_ref-SQL2003-Part1-Sec442_9-0" class="reference"><a href="#cite_note-SQL2003-Part1-Sec442-9"><span class="cite-bracket">[</span>9<span class="cite-bracket">]</span></a></sup> The logical result of the expression below, which compares the value 10 to Null, is Unknown: </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">SELECT</span><span class="w"> </span><span class="mi">10</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="k">NULL</span><span class="w"> </span><span class="c1">-- Results in Unknown</span> </pre></div> <p>However, certain operations on Null can return values if the absent value is not relevant to the outcome of the operation. Consider the following example: </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">SELECT</span><span class="w"> </span><span class="k">NULL</span><span class="w"> </span><span class="k">OR</span><span class="w"> </span><span class="k">TRUE</span><span class="w"> </span><span class="c1">-- Results in True</span> </pre></div> <p>In this case, the fact that the value on the left of OR is unknowable is irrelevant, because the outcome of the OR operation would be True regardless of the value on the left. </p><p>SQL implements three logical results, so SQL implementations must provide for a specialized <a href="/wiki/Ternary_logic" class="mw-redirect" title="Ternary logic">three-valued logic (3VL)</a>. The rules governing SQL three-valued logic are shown in the tables below (<b>p</b> and <b>q</b> represent logical states)"<sup id="cite_ref-fourrules_10-0" class="reference"><a href="#cite_note-fourrules-10"><span class="cite-bracket">[</span>10<span class="cite-bracket">]</span></a></sup> The truth tables SQL uses for AND, OR, and NOT correspond to a common fragment of the Kleene and Łukasiewicz three-valued logic (which differ in their definition of implication, however, SQL defines no such operation).<sup id="cite_ref-Klein_11-0" class="reference"><a href="#cite_note-Klein-11"><span class="cite-bracket">[</span>11<span class="cite-bracket">]</span></a></sup> </p> <table class="wikitable"> <tbody><tr> <th><i>p</i></th> <th><i>q</i></th> <th><i>p</i> OR <i>q</i></th> <th><i>p</i> AND <i>q</i></th> <th><i>p</i> = <i>q</i> </th></tr> <tr> <td style="background:#bfd; color:black; vertical-align:middle; text-align:center;" class="table-yes2">True</td> <td style="background:#bfd; color:black; vertical-align:middle; text-align:center;" class="table-yes2">True</td> <td style="background:#bfd; color:black; vertical-align:middle; text-align:center;" class="table-yes2">True</td> <td style="background:#bfd; color:black; vertical-align:middle; text-align:center;" class="table-yes2">True</td> <td style="background:#bfd; color:black; vertical-align:middle; text-align:center;" class="table-yes2">True </td></tr> <tr> <td style="background:#bfd; color:black; vertical-align:middle; text-align:center;" class="table-yes2">True</td> <td style="background: #FFE3E3; color: black; vertical-align: middle; text-align: center;" class="table-no2">False</td> <td style="background:#bfd; color:black; vertical-align:middle; text-align:center;" class="table-yes2">True</td> <td style="background: #FFE3E3; color: black; vertical-align: middle; text-align: center;" class="table-no2">False</td> <td style="background: #FFE3E3; color: black; vertical-align: middle; text-align: center;" class="table-no2">False </td></tr> <tr> <td style="background:#bfd; color:black; vertical-align:middle; text-align:center;" class="table-yes2">True</td> <td style="background: var(--background-color-interactive, #EEE); color: var(--color-base, black); vertical-align: middle; white-space: nowrap; text-align: center;" class="table-Unknown">Unknown</td> <td style="background:#bfd; color:black; vertical-align:middle; text-align:center;" class="table-yes2">True</td> <td style="background: var(--background-color-interactive, #EEE); color: var(--color-base, black); vertical-align: middle; white-space: nowrap; text-align: center;" class="table-Unknown">Unknown</td> <td style="background: var(--background-color-interactive, #EEE); color: var(--color-base, black); vertical-align: middle; white-space: nowrap; text-align: center;" class="table-Unknown">Unknown </td></tr> <tr> <td style="background: #FFE3E3; color: black; vertical-align: middle; text-align: center;" class="table-no2">False</td> <td style="background:#bfd; color:black; vertical-align:middle; text-align:center;" class="table-yes2">True</td> <td style="background:#bfd; color:black; vertical-align:middle; text-align:center;" class="table-yes2">True</td> <td style="background: #FFE3E3; color: black; vertical-align: middle; text-align: center;" class="table-no2">False</td> <td style="background: #FFE3E3; color: black; vertical-align: middle; text-align: center;" class="table-no2">False </td></tr> <tr> <td style="background: #FFE3E3; color: black; vertical-align: middle; text-align: center;" class="table-no2">False</td> <td style="background: #FFE3E3; color: black; vertical-align: middle; text-align: center;" class="table-no2">False</td> <td style="background: #FFE3E3; color: black; vertical-align: middle; text-align: center;" class="table-no2">False</td> <td style="background: #FFE3E3; color: black; vertical-align: middle; text-align: center;" class="table-no2">False</td> <td style="background:#bfd; color:black; vertical-align:middle; text-align:center;" class="table-yes2">True </td></tr> <tr> <td style="background: #FFE3E3; color: black; vertical-align: middle; text-align: center;" class="table-no2">False</td> <td style="background: var(--background-color-interactive, #EEE); color: var(--color-base, black); vertical-align: middle; white-space: nowrap; text-align: center;" class="table-Unknown">Unknown</td> <td style="background: var(--background-color-interactive, #EEE); color: var(--color-base, black); vertical-align: middle; white-space: nowrap; text-align: center;" class="table-Unknown">Unknown</td> <td style="background: #FFE3E3; color: black; vertical-align: middle; text-align: center;" class="table-no2">False</td> <td style="background: var(--background-color-interactive, #EEE); color: var(--color-base, black); vertical-align: middle; white-space: nowrap; text-align: center;" class="table-Unknown">Unknown </td></tr> <tr> <td style="background: var(--background-color-interactive, #EEE); color: var(--color-base, black); vertical-align: middle; white-space: nowrap; text-align: center;" class="table-Unknown">Unknown</td> <td style="background:#bfd; color:black; vertical-align:middle; text-align:center;" class="table-yes2">True</td> <td style="background:#bfd; color:black; vertical-align:middle; text-align:center;" class="table-yes2">True</td> <td style="background: var(--background-color-interactive, #EEE); color: var(--color-base, black); vertical-align: middle; white-space: nowrap; text-align: center;" class="table-Unknown">Unknown</td> <td style="background: var(--background-color-interactive, #EEE); color: var(--color-base, black); vertical-align: middle; white-space: nowrap; text-align: center;" class="table-Unknown">Unknown </td></tr> <tr> <td style="background: var(--background-color-interactive, #EEE); color: var(--color-base, black); vertical-align: middle; white-space: nowrap; text-align: center;" class="table-Unknown">Unknown</td> <td style="background: #FFE3E3; color: black; vertical-align: middle; text-align: center;" class="table-no2">False</td> <td style="background: var(--background-color-interactive, #EEE); color: var(--color-base, black); vertical-align: middle; white-space: nowrap; text-align: center;" class="table-Unknown">Unknown</td> <td style="background: #FFE3E3; color: black; vertical-align: middle; text-align: center;" class="table-no2">False</td> <td style="background: var(--background-color-interactive, #EEE); color: var(--color-base, black); vertical-align: middle; white-space: nowrap; text-align: center;" class="table-Unknown">Unknown </td></tr> <tr> <td style="background: var(--background-color-interactive, #EEE); color: var(--color-base, black); vertical-align: middle; white-space: nowrap; text-align: center;" class="table-Unknown">Unknown</td> <td style="background: var(--background-color-interactive, #EEE); color: var(--color-base, black); vertical-align: middle; white-space: nowrap; text-align: center;" class="table-Unknown">Unknown</td> <td style="background: var(--background-color-interactive, #EEE); color: var(--color-base, black); vertical-align: middle; white-space: nowrap; text-align: center;" class="table-Unknown">Unknown</td> <td style="background: var(--background-color-interactive, #EEE); color: var(--color-base, black); vertical-align: middle; white-space: nowrap; text-align: center;" class="table-Unknown">Unknown</td> <td style="background: var(--background-color-interactive, #EEE); color: var(--color-base, black); vertical-align: middle; white-space: nowrap; text-align: center;" class="table-Unknown">Unknown </td></tr></tbody></table> <table class="wikitable"> <tbody><tr> <th><i>p</i></th> <th>NOT <i>p</i> </th></tr> <tr> <td style="background:#bfd; color:black; vertical-align:middle; text-align:center;" class="table-yes2">True</td> <td style="background: #FFE3E3; color: black; vertical-align: middle; text-align: center;" class="table-no2">False </td></tr> <tr> <td style="background: #FFE3E3; color: black; vertical-align: middle; text-align: center;" class="table-no2">False</td> <td style="background:#bfd; color:black; vertical-align:middle; text-align:center;" class="table-yes2">True </td></tr> <tr> <td style="background: var(--background-color-interactive, #EEE); color: var(--color-base, black); vertical-align: middle; white-space: nowrap; text-align: center;" class="table-Unknown">Unknown</td> <td style="background: var(--background-color-interactive, #EEE); color: var(--color-base, black); vertical-align: middle; white-space: nowrap; text-align: center;" class="table-Unknown">Unknown </td></tr></tbody></table> <div class="mw-heading mw-heading3"><h3 id="Effect_of_Unknown_in_WHERE_clauses">Effect of Unknown in WHERE clauses</h3><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=7" title="Edit section: Effect of Unknown in WHERE clauses"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <p>SQL three-valued logic is encountered in <a href="/wiki/Data_Manipulation_Language" class="mw-redirect" title="Data Manipulation Language">Data Manipulation Language</a> (DML) in comparison predicates of DML statements and queries. The <code>WHERE</code> clause causes the DML statement to act on only those rows for which the predicate evaluates to True. Rows for which the predicate evaluates to either False or Unknown are not acted on by <code><a href="/wiki/Insert_(SQL)" title="Insert (SQL)">INSERT</a></code>, <code><a href="/wiki/Update_(SQL)" title="Update (SQL)">UPDATE</a></code>, or <code><a href="/wiki/Delete_(SQL)" title="Delete (SQL)">DELETE</a></code> DML statements, and are discarded by <code><a href="/wiki/Select_(SQL)" title="Select (SQL)">SELECT</a></code> queries. Interpreting Unknown and False as the same logical result is a common error encountered while dealing with Nulls.<sup id="cite_ref-fourrules_10-1" class="reference"><a href="#cite_note-fourrules-10"><span class="cite-bracket">[</span>10<span class="cite-bracket">]</span></a></sup> The following simple example demonstrates this fallacy: </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">SELECT</span><span class="w"> </span><span class="o">*</span> <span class="k">FROM</span><span class="w"> </span><span class="n">t</span> <span class="k">WHERE</span><span class="w"> </span><span class="n">i</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="k">NULL</span><span class="p">;</span> </pre></div> <p>The example query above logically always returns zero rows because the comparison of the <i>i</i> column with Null always returns Unknown, even for those rows where <i>i</i> is Null. The Unknown result causes the <code>SELECT</code> statement to summarily discard every row. (However, in practice, some SQL tools will retrieve rows using a comparison with Null.) </p> <div class="mw-heading mw-heading3"><h3 id="Null-specific_and_3VL-specific_comparison_predicates">Null-specific and 3VL-specific comparison predicates</h3><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=8" title="Edit section: Null-specific and 3VL-specific comparison predicates"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <p>Basic SQL comparison operators always return Unknown when comparing anything with Null, so the SQL standard provides for two special Null-specific comparison predicates. The <code>IS NULL</code> and <code>IS NOT NULL</code> predicates (which use a <a href="/wiki/Reverse_Polish_notation" title="Reverse Polish notation">postfix</a> syntax) test whether data is, or is not, Null.<sup id="cite_ref-SQL2003-Part2-Sec87_12-0" class="reference"><a href="#cite_note-SQL2003-Part2-Sec87-12"><span class="cite-bracket">[</span>12<span class="cite-bracket">]</span></a></sup> </p><p>The SQL standard contains the optional feature F571 "Truth value tests" that introduces three additional logical unary operators (six in fact, if we count their negation, which is part of their syntax), also using postfix notation. They have the following truth tables:<sup id="cite_ref-13" class="reference"><a href="#cite_note-13"><span class="cite-bracket">[</span>13<span class="cite-bracket">]</span></a></sup> </p> <table class="wikitable"> <tbody><tr> <th>p</th> <th>p IS TRUE</th> <th>p IS NOT TRUE</th> <th>p IS FALSE</th> <th>p IS NOT FALSE</th> <th>p IS UNKNOWN</th> <th>p IS NOT UNKNOWN </th></tr> <tr> <td style="background:#bfd; color:black; vertical-align:middle; text-align:center;" class="table-yes2">True</td> <td style="background:#bfd; color:black; vertical-align:middle; text-align:center;" class="table-yes2">True</td> <td style="background: #FFE3E3; color: black; vertical-align: middle; text-align: center;" class="table-no2">False</td> <td style="background: #FFE3E3; color: black; vertical-align: middle; text-align: center;" class="table-no2">False</td> <td style="background:#bfd; color:black; vertical-align:middle; text-align:center;" class="table-yes2">True</td> <td style="background: #FFE3E3; color: black; vertical-align: middle; text-align: center;" class="table-no2">False</td> <td style="background:#bfd; color:black; vertical-align:middle; text-align:center;" class="table-yes2">True </td></tr> <tr> <td style="background: #FFE3E3; color: black; vertical-align: middle; text-align: center;" class="table-no2">False</td> <td style="background: #FFE3E3; color: black; vertical-align: middle; text-align: center;" class="table-no2">False</td> <td style="background:#bfd; color:black; vertical-align:middle; text-align:center;" class="table-yes2">True</td> <td style="background:#bfd; color:black; vertical-align:middle; text-align:center;" class="table-yes2">True</td> <td style="background: #FFE3E3; color: black; vertical-align: middle; text-align: center;" class="table-no2">False</td> <td style="background: #FFE3E3; color: black; vertical-align: middle; text-align: center;" class="table-no2">False</td> <td style="background:#bfd; color:black; vertical-align:middle; text-align:center;" class="table-yes2">True </td></tr> <tr> <td style="background: var(--background-color-interactive, #EEE); color: var(--color-base, black); vertical-align: middle; white-space: nowrap; text-align: center;" class="table-Unknown">Unknown</td> <td style="background: #FFE3E3; color: black; vertical-align: middle; text-align: center;" class="table-no2">False</td> <td style="background:#bfd; color:black; vertical-align:middle; text-align:center;" class="table-yes2">True</td> <td style="background: #FFE3E3; color: black; vertical-align: middle; text-align: center;" class="table-no2">False</td> <td style="background:#bfd; color:black; vertical-align:middle; text-align:center;" class="table-yes2">True</td> <td style="background:#bfd; color:black; vertical-align:middle; text-align:center;" class="table-yes2">True</td> <td style="background: #FFE3E3; color: black; vertical-align: middle; text-align: center;" class="table-no2">False </td></tr></tbody></table> <p>The F571 feature is orthogonal to the presence of the <a href="#BOOLEAN_data_type">Boolean datatype</a> in SQL (discussed later in this article) and, despite syntactic similarities, F571 does not introduce Boolean or three-valued <a href="/wiki/Literal_(computer_programming)" title="Literal (computer programming)">literals</a> in the language. The F571 feature was actually present in <a href="/wiki/SQL92" class="mw-redirect" title="SQL92">SQL92</a>,<sup id="cite_ref-MeltonSimon1993_14-0" class="reference"><a href="#cite_note-MeltonSimon1993-14"><span class="cite-bracket">[</span>14<span class="cite-bracket">]</span></a></sup> well before the Boolean datatype was introduced to the standard in 1999. The F571 feature is implemented by few systems however; PostgreSQL is one of those implementing it. </p><p>The addition of IS UNKNOWN to the other operators of SQL's three-valued logic makes the SQL three-valued logic <a href="/wiki/Functionally_complete" class="mw-redirect" title="Functionally complete">functionally complete</a>,<sup id="cite_ref-15" class="reference"><a href="#cite_note-15"><span class="cite-bracket">[</span>15<span class="cite-bracket">]</span></a></sup> meaning its logical operators can express (in combination) any conceivable three-valued logical function. </p><p>On systems that don't support the F571 feature, it is possible to emulate IS UNKNOWN <i>p</i> by going over every argument that could make the expression <i>p</i> Unknown and test those arguments with IS NULL or other NULL-specific functions, although this may be more cumbersome. </p> <div class="mw-heading mw-heading3"><h3 id="Law_of_the_excluded_fourth_(in_WHERE_clauses)"><span id="Law_of_the_excluded_fourth_.28in_WHERE_clauses.29"></span>Law of the excluded fourth (in WHERE clauses)</h3><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=9" title="Edit section: Law of the excluded fourth (in WHERE clauses)"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <p>In SQL's three-valued logic the <a href="/wiki/Law_of_excluded_middle" title="Law of excluded middle">law of the excluded middle</a>, <i>p</i> OR NOT <i>p</i>, no longer evaluates to true for all <i>p</i>. More precisely, in SQL's three-valued logic <i>p</i> OR NOT <i>p</i> is unknown precisely when <i>p</i> is unknown and true otherwise. Because direct comparisons with Null result in the unknown logical value, the following query </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">SELECT</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">stuff</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="p">(</span><span class="w"> </span><span class="n">x</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="mi">10</span><span class="w"> </span><span class="p">)</span><span class="w"> </span><span class="k">OR</span><span class="w"> </span><span class="k">NOT</span><span class="w"> </span><span class="p">(</span><span class="w"> </span><span class="n">x</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="mi">10</span><span class="w"> </span><span class="p">);</span> </pre></div> <p>is not equivalent in SQL with </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">SELECT</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">stuff</span><span class="p">;</span> </pre></div> <p>if the column x contains any Nulls; in that case, the second query would return some rows the first one does not return, namely all those in which x is Null. In classical two-valued logic, the law of the excluded middle would allow the simplification of the WHERE clause predicate, in fact its elimination. Attempting to apply the law of the excluded middle to SQL's 3VL is effectively a <a href="/wiki/False_dilemma" title="False dilemma">false dichotomy</a>. The second query is actually equivalent with: </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">SELECT</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">stuff</span><span class="p">;</span> <span class="c1">-- is (because of 3VL) equivalent to:</span> <span class="k">SELECT</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">stuff</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="p">(</span><span class="w"> </span><span class="n">x</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="mi">10</span><span class="w"> </span><span class="p">)</span><span class="w"> </span><span class="k">OR</span><span class="w"> </span><span class="k">NOT</span><span class="w"> </span><span class="p">(</span><span class="w"> </span><span class="n">x</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="mi">10</span><span class="w"> </span><span class="p">)</span><span class="w"> </span><span class="k">OR</span><span class="w"> </span><span class="n">x</span><span class="w"> </span><span class="k">IS</span><span class="w"> </span><span class="k">NULL</span><span class="p">;</span> </pre></div> <p>Thus, to correctly simplify the first statement in SQL requires that we return all rows in which x is not null. </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">SELECT</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">stuff</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">x</span><span class="w"> </span><span class="k">IS</span><span class="w"> </span><span class="k">NOT</span><span class="w"> </span><span class="k">NULL</span><span class="p">;</span> </pre></div> <p>In view of the above, observe that for SQL's WHERE clause a <a href="/wiki/Tautology_(logic)" title="Tautology (logic)">tautology</a> similar to the law of excluded middle can be written. Assuming the IS UNKNOWN operator is present, <i>p</i> OR (NOT <i>p</i>) OR (<i>p</i> IS UNKNOWN) is true for every predicate <i>p</i>. Among logicians, this is called <a href="/wiki/Law_of_excluded_fourth" class="mw-redirect" title="Law of excluded fourth">law of excluded fourth</a>. </p><p>There are some SQL expressions in which it is less obvious where the false dilemma occurs, for example: </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">SELECT</span><span class="w"> </span><span class="s1">'ok'</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="k">NOT</span><span class="w"> </span><span class="k">IN</span><span class="w"> </span><span class="p">(</span><span class="k">SELECT</span><span class="w"> </span><span class="k">CAST</span><span class="w"> </span><span class="p">(</span><span class="k">NULL</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="nb">INTEGER</span><span class="p">))</span> <span class="k">UNION</span> <span class="k">SELECT</span><span class="w"> </span><span class="s1">'ok'</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="k">IN</span><span class="w"> </span><span class="p">(</span><span class="k">SELECT</span><span class="w"> </span><span class="k">CAST</span><span class="w"> </span><span class="p">(</span><span class="k">NULL</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="nb">INTEGER</span><span class="p">));</span> </pre></div> <p>produces no rows because <code>IN</code> translates to an iterated version of equality over the argument set and 1<>NULL is Unknown, just as a 1=NULL is Unknown. (The CAST in this example is needed only in some SQL implementations like PostgreSQL, which would reject it with a type checking error otherwise. In many systems plain SELECT NULL works in the subquery.) The missing case above is of course: </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">SELECT</span><span class="w"> </span><span class="s1">'ok'</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="p">(</span><span class="mi">1</span><span class="w"> </span><span class="k">IN</span><span class="w"> </span><span class="p">(</span><span class="k">SELECT</span><span class="w"> </span><span class="k">CAST</span><span class="w"> </span><span class="p">(</span><span class="k">NULL</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="nb">INTEGER</span><span class="p">)))</span><span class="w"> </span><span class="k">IS</span><span class="w"> </span><span class="k">UNKNOWN</span><span class="p">;</span> </pre></div> <div class="mw-heading mw-heading3"><h3 id="Effect_of_Null_and_Unknown_in_other_constructs">Effect of Null and Unknown in other constructs</h3><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=10" title="Edit section: Effect of Null and Unknown in other constructs"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <div class="mw-heading mw-heading4"><h4 id="Joins">Joins</h4><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=11" title="Edit section: Joins"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <p>Joins evaluate using the same comparison rules as for WHERE clauses. Therefore, care must be taken when using nullable columns in SQL join criteria. In particular a table containing any nulls is <i>not equal</i> with a natural self-join of itself, meaning that whereas <span class="mwe-math-element"><span class="mwe-math-mathml-inline mwe-math-mathml-a11y" style="display: none;"><math xmlns="http://www.w3.org/1998/Math/MathML" alttext="{\displaystyle R\bowtie R=R}"> <semantics> <mrow class="MJX-TeXAtom-ORD"> <mstyle displaystyle="true" scriptlevel="0"> <mi>R</mi> <mo>⋈</mo> <mi>R</mi> <mo>=</mo> <mi>R</mi> </mstyle> </mrow> <annotation encoding="application/x-tex">{\displaystyle R\bowtie R=R}</annotation> </semantics> </math></span><img src="https://wikimedia.org/api/rest_v1/media/math/render/svg/fea5c8deb93a6c8f2e34a160f63109ae40952306" class="mwe-math-fallback-image-inline mw-invert skin-invert" aria-hidden="true" style="vertical-align: -0.338ex; width:11.772ex; height:2.176ex;" alt="{\displaystyle R\bowtie R=R}" /></span> is true for any relation <i>R</i> in <a href="/wiki/Relational_algebra" title="Relational algebra">relational algebra</a>, a SQL self-join will exclude all rows having a Null anywhere.<sup id="cite_ref-16" class="reference"><a href="#cite_note-16"><span class="cite-bracket">[</span>16<span class="cite-bracket">]</span></a></sup> An example of this behavior is given in the section analyzing the missing-value semantics of Nulls. </p><p>The SQL <code>COALESCE</code> function or <code>CASE</code> expressions can be used to "simulate" Null equality in join criteria, and the <code>IS NULL</code> and <code>IS NOT NULL</code> predicates can be used in the join criteria as well. The following predicate tests for equality of the values A and B and treats Nulls as being equal. </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="p">(</span><span class="n">A</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">B</span><span class="p">)</span><span class="w"> </span><span class="k">OR</span><span class="w"> </span><span class="p">(</span><span class="n">A</span><span class="w"> </span><span class="k">IS</span><span class="w"> </span><span class="k">NULL</span><span class="w"> </span><span class="k">AND</span><span class="w"> </span><span class="n">B</span><span class="w"> </span><span class="k">IS</span><span class="w"> </span><span class="k">NULL</span><span class="p">)</span> </pre></div> <div class="mw-heading mw-heading4"><h4 id="CASE_expressions">CASE expressions</h4><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=12" title="Edit section: CASE expressions"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <p>SQL provides <a href="/wiki/Case_(SQL)" class="mw-redirect" title="Case (SQL)">two flavours of conditional expressions</a>. One is called "simple CASE" and operates like a <a href="/wiki/Switch_statement" title="Switch statement">switch statement</a>. The other is called a "searched CASE" in the standard, and operates like an <a href="/wiki/Conditional_(programming)#Else_if" class="mw-redirect" title="Conditional (programming)">if...elseif</a>. </p><p>The simple <code>CASE</code> expressions use implicit equality comparisons which operate under the same rules as the DML <code>WHERE</code> clause rules for Null. Thus, a <i>simple <code>CASE</code> expression</i> cannot check for the existence of Null directly. A check for Null in a simple <code>CASE</code> expression always results in Unknown, as in the following: </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">SELECT</span><span class="w"> </span><span class="k">CASE</span><span class="w"> </span><span class="n">i</span><span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="k">NULL</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="s1">'Is Null'</span><span class="w"> </span><span class="c1">-- This will never be returned</span> <span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="mi">0</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="s1">'Is Zero'</span><span class="w"> </span><span class="c1">-- This will be returned when i = 0</span> <span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="s1">'Is One'</span><span class="w"> </span><span class="c1">-- This will be returned when i = 1</span> <span class="w"> </span><span class="k">END</span> <span class="k">FROM</span><span class="w"> </span><span class="n">t</span><span class="p">;</span> </pre></div> <p>Because the expression <code>i = NULL</code> evaluates to Unknown no matter what value column <i>i</i> contains (even if it contains Null), the string <code>'Is Null'</code> will never be returned. </p><p>On the other hand, a "searched" <code>CASE</code> expression can use predicates like <code>IS NULL</code> and <code>IS NOT NULL</code> in its conditions. The following example shows how to use a searched <code>CASE</code> expression to properly check for Null: </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">SELECT</span><span class="w"> </span><span class="k">CASE</span><span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="n">i</span><span class="w"> </span><span class="k">IS</span><span class="w"> </span><span class="k">NULL</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="s1">'Null Result'</span><span class="w"> </span><span class="c1">-- This will be returned when i is NULL</span> <span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="n">i</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="mi">0</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="s1">'Zero'</span><span class="w"> </span><span class="c1">-- This will be returned when i = 0</span> <span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="n">i</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="s1">'One'</span><span class="w"> </span><span class="c1">-- This will be returned when i = 1</span> <span class="w"> </span><span class="k">END</span> <span class="k">FROM</span><span class="w"> </span><span class="n">t</span><span class="p">;</span> </pre></div> <p>In the searched <code>CASE</code> expression, the string <code>'Null Result'</code> is returned for all rows in which <i>i</i> is Null. </p><p>Oracle's dialect of SQL provides a built-in function <code>DECODE</code> which can be used instead of the simple CASE expressions and considers two nulls equal. </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">SELECT</span><span class="w"> </span><span class="n">DECODE</span><span class="p">(</span><span class="n">i</span><span class="p">,</span><span class="w"> </span><span class="k">NULL</span><span class="p">,</span><span class="w"> </span><span class="s1">'Null Result'</span><span class="p">,</span><span class="w"> </span><span class="mi">0</span><span class="p">,</span><span class="w"> </span><span class="s1">'Zero'</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="s1">'One'</span><span class="p">)</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">t</span><span class="p">;</span> </pre></div> <p>Finally, all these constructs return a NULL if no match is found; they have a default <code>ELSE NULL</code> clause. </p> <div class="mw-heading mw-heading4"><h4 id="IF_statements_in_procedural_extensions">IF statements in procedural extensions</h4><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=13" title="Edit section: IF statements in procedural extensions"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <p><a href="/wiki/SQL/PSM" title="SQL/PSM">SQL/PSM</a> (SQL Persistent Stored Modules) defines <a href="/wiki/Procedural_programming" title="Procedural programming">procedural</a> extensions for SQL, such as the <code><a href="/wiki/Conditional_(programming)" class="mw-redirect" title="Conditional (programming)">IF</a></code> statement. However, the major SQL vendors have historically included their own proprietary procedural extensions. Procedural extensions for looping and comparisons operate under Null comparison rules similar to those for DML statements and queries. The following code fragment, in ISO SQL standard format, demonstrates the use of Null 3VL in an <code>IF</code> statement. </p> <div class="mw-highlight mw-highlight-lang-plpgsql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">IF</span><span class="w"> </span><span class="n">i</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="k">NULL</span><span class="w"> </span><span class="k">THEN</span> <span class="w"> </span><span class="k">SELECT</span><span class="w"> </span><span class="s1">'Result is True'</span> <span class="n">ELSEIF</span><span class="w"> </span><span class="k">NOT</span><span class="p">(</span><span class="n">i</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="k">NULL</span><span class="p">)</span><span class="w"> </span><span class="k">THEN</span> <span class="w"> </span><span class="k">SELECT</span><span class="w"> </span><span class="s1">'Result is False'</span> <span class="k">ELSE</span> <span class="w"> </span><span class="k">SELECT</span><span class="w"> </span><span class="s1">'Result is Unknown'</span><span class="p">;</span> </pre></div> <p>The <code>IF</code> statement performs actions only for those comparisons that evaluate to True. For statements that evaluate to False or Unknown, the <code>IF</code> statement passes control to the <code><a href="/wiki/Conditional_(programming)" class="mw-redirect" title="Conditional (programming)">ELSEIF</a></code> clause, and finally to the <code><a href="/wiki/Conditional_(programming)" class="mw-redirect" title="Conditional (programming)">ELSE</a></code> clause. The result of the code above will always be the message <code>'Result is Unknown'</code> since the comparisons with Null always evaluate to Unknown. </p> <div class="mw-heading mw-heading2"><h2 id="Analysis_of_SQL_Null_missing-value_semantics"><span class="anchor" id="missing-value_semantics"></span> Analysis of SQL Null missing-value semantics</h2><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=14" title="Edit section: Analysis of SQL Null missing-value semantics"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <p>The groundbreaking work of <a href="/wiki/Tomasz_Imieli%C5%84ski" title="Tomasz Imieliński">T. Imieliński</a> and <a href="/wiki/Witold_Lipski" title="Witold Lipski">W. Lipski Jr.</a> (1984)<sup id="cite_ref-JACM_1984_17-0" class="reference"><a href="#cite_note-JACM_1984-17"><span class="cite-bracket">[</span>17<span class="cite-bracket">]</span></a></sup> provided a framework in which to evaluate the intended semantics of various proposals to implement missing-value semantics, that is referred to as <a href="/wiki/Imieli%C5%84ski-Lipski_Algebras" class="mw-redirect" title="Imieliński-Lipski Algebras">Imieliński-Lipski Algebras</a>. This section roughly follows chapter 19 of the "Alice" textbook.<sup id="cite_ref-18" class="reference"><a href="#cite_note-18"><span class="cite-bracket">[</span>18<span class="cite-bracket">]</span></a></sup> A similar presentation appears in the review of Ron van der Meyden, §10.4.<sup id="cite_ref-Meyden_1-2" class="reference"><a href="#cite_note-Meyden-1"><span class="cite-bracket">[</span>1<span class="cite-bracket">]</span></a></sup> </p> <div class="mw-heading mw-heading3"><h3 id="In_selections_and_projections:_weak_representation">In selections and projections: weak representation</h3><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=15" title="Edit section: In selections and projections: weak representation"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <p>Constructs representing missing information, such as Codd tables, are actually intended to represent a set of relations, one for each possible instantiation of their parameters; in the case of Codd tables, this means replacement of Nulls with some concrete value. For example, </p> <div class="center"><div class="thumb tnone" style=""><div class="thumbinner" style="width:-moz-fit-content; width:fit-content;"><div class="thumbimage noresize" style="width:auto;display: flex; flex-flow: row wrap; justify-content: space-evenly;"> <p><i style="display:none;speak:none;"> </i> </p> <table class="wikitable" style="display: inline-table !important"> <caption><i>Emp</i> </caption> <tbody><tr> <th>Name</th> <th>Age </th></tr> <tr> <td>George</td> <td>43 </td></tr> <tr> <td>Harriet</td> <td><code style="color: white; background-color: gray; padding: 2px 4px; font-size: smaller;">NULL</code> </td></tr> <tr> <td>Charles</td> <td>56 </td></tr></tbody></table> <table class="wikitable" style="display: inline-table !important"> <caption><i>EmpH22</i> </caption> <tbody><tr> <th>Name</th> <th>Age </th></tr> <tr> <td>George</td> <td>43 </td></tr> <tr> <td>Harriet</td> <td>22 </td></tr> <tr> <td>Charles</td> <td>56 </td></tr></tbody></table> <table class="wikitable" style="display: inline-table !important"> <caption><i>EmpH37</i> </caption> <tbody><tr> <th>Name</th> <th>Age </th></tr> <tr> <td>George</td> <td>43 </td></tr> <tr> <td>Harriet</td> <td>37 </td></tr> <tr> <td>Charles</td> <td>56 </td></tr></tbody></table></div><div class="thumbcaption">The Codd table <i>Emp</i> may represent the relation <i>EmpH22</i> or <i>EmpH37</i>, as pictured.</div></div></div></div> <p>A construct (such as a Codd table) is said to be a <i>strong representation</i> system (of missing information) if any answer to a query made on the construct can be particularized to obtain an answer for <i>any</i> corresponding query on the relations it represents, which are seen as <a href="/wiki/Structure_(mathematical_logic)" title="Structure (mathematical logic)">models</a> of the construct. More precisely, if <span class="texhtml mvar" style="font-style:italic;">q</span> is a query formula in the <a href="/wiki/Relational_algebra" title="Relational algebra">relational algebra</a> (of "pure" relations) and if <span class="texhtml mvar" style="font-style:italic;"><span style="text-decoration:overline;">q</span></span> is its lifting to a construct intended to represent missing information, a strong representation has the property that for any query <i>q</i> and (table) construct <i>T</i>, <span class="texhtml mvar" style="font-style:italic;"><span style="text-decoration:overline;">q</span></span> lifts <i>all</i> the answers to the construct, i.e.: </p> <dl><dd><span class="mwe-math-element"><span class="mwe-math-mathml-inline mwe-math-mathml-a11y" style="display: none;"><math xmlns="http://www.w3.org/1998/Math/MathML" alttext="{\displaystyle \mathop {\mathrm {Models} } ({\bar {q}}(T))=\{q(R)\,|R\in \mathop {\mathrm {Models} } (T)\}}"> <semantics> <mrow class="MJX-TeXAtom-ORD"> <mstyle displaystyle="true" scriptlevel="0"> <mrow class="MJX-TeXAtom-OP"> <mrow class="MJX-TeXAtom-ORD"> <mi mathvariant="normal">M</mi> <mi mathvariant="normal">o</mi> <mi mathvariant="normal">d</mi> <mi mathvariant="normal">e</mi> <mi mathvariant="normal">l</mi> <mi mathvariant="normal">s</mi> </mrow> </mrow> <mo>⁡</mo> <mo stretchy="false">(</mo> <mrow class="MJX-TeXAtom-ORD"> <mrow class="MJX-TeXAtom-ORD"> <mover> <mi>q</mi> <mo stretchy="false">¯</mo> </mover> </mrow> </mrow> <mo stretchy="false">(</mo> <mi>T</mi> <mo stretchy="false">)</mo> <mo stretchy="false">)</mo> <mo>=</mo> <mo fence="false" stretchy="false">{</mo> <mi>q</mi> <mo stretchy="false">(</mo> <mi>R</mi> <mo stretchy="false">)</mo> <mspace width="thinmathspace"></mspace> <mrow class="MJX-TeXAtom-ORD"> <mo stretchy="false">|</mo> </mrow> <mi>R</mi> <mo>∈</mo> <mrow class="MJX-TeXAtom-OP"> <mrow class="MJX-TeXAtom-ORD"> <mi mathvariant="normal">M</mi> <mi mathvariant="normal">o</mi> <mi mathvariant="normal">d</mi> <mi mathvariant="normal">e</mi> <mi mathvariant="normal">l</mi> <mi mathvariant="normal">s</mi> </mrow> </mrow> <mo>⁡</mo> <mo stretchy="false">(</mo> <mi>T</mi> <mo stretchy="false">)</mo> <mo fence="false" stretchy="false">}</mo> </mstyle> </mrow> <annotation encoding="application/x-tex">{\displaystyle \mathop {\mathrm {Models} } ({\bar {q}}(T))=\{q(R)\,|R\in \mathop {\mathrm {Models} } (T)\}}</annotation> </semantics> </math></span><img src="https://wikimedia.org/api/rest_v1/media/math/render/svg/d384680a6606fda6d0a50966d979f8aca2d23980" class="mwe-math-fallback-image-inline mw-invert skin-invert" aria-hidden="true" style="vertical-align: -0.838ex; width:40.145ex; height:2.843ex;" alt="{\displaystyle \mathop {\mathrm {Models} } ({\bar {q}}(T))=\{q(R)\,|R\in \mathop {\mathrm {Models} } (T)\}}" /></span></dd></dl> <p>(The above has to hold for queries taking any number of tables as arguments, but the restriction to one table suffices for this discussion.) Clearly Codd tables do not have this strong property if selections and projections are considered as part of the query language. For example, <i>all</i> the answers to </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">SELECT</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">Emp</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">Age</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="mi">22</span><span class="p">;</span> </pre></div> <p>should include the possibility that a relation like EmpH22 may exist. However, Codd tables cannot represent the disjunction "result with possibly 0 or 1 rows". A device, mostly of theoretical interest, called <a href="/w/index.php?title=Conditional_table&action=edit&redlink=1" class="new" title="Conditional table (page does not exist)">conditional table</a> (or c-table) can however represent such an answer: </p> <table class="wikitable"> <caption><i>Result</i> </caption> <tbody><tr> <th>Name</th> <th>Age</th> <th>condition </th></tr> <tr> <td>Harriet</td> <td>ω<sub>1</sub></td> <td>ω<sub>1</sub> = 22 </td></tr> </tbody></table> <p>where the condition column is interpreted as the row doesn't exist if the condition is false. It turns out that because the formulas in the condition column of a c-table can be arbitrary <a href="/wiki/Propositional_logic" class="mw-redirect" title="Propositional logic">propositional logic</a> formulas, an algorithm for the problem whether a c-table represents some concrete relation has a <a href="/wiki/Co-NP-complete" title="Co-NP-complete">co-NP-complete</a> complexity, thus is of little practical worth. </p><p>A weaker notion of representation is therefore desirable. Imielinski and Lipski introduced the notion of <i>weak representation</i>, which essentially allows (lifted) queries over a construct to return a representation only for <i>sure</i> information, i.e. if it's valid for all "<a href="/wiki/Possible_world" title="Possible world">possible world</a>" instantiations (models) of the construct. Concretely, a construct is a weak representation system if </p> <dl><dd><span class="mwe-math-element"><span class="mwe-math-mathml-inline mwe-math-mathml-a11y" style="display: none;"><math xmlns="http://www.w3.org/1998/Math/MathML" alttext="{\displaystyle \bigcap \mathop {\mathrm {Models} } ({\bar {q}}(T))=\bigcap \{q(R)\,|R\in \mathop {\mathrm {Models} } (T)\}}"> <semantics> <mrow class="MJX-TeXAtom-ORD"> <mstyle displaystyle="true" scriptlevel="0"> <mo>⋂</mo> <mrow class="MJX-TeXAtom-OP"> <mrow class="MJX-TeXAtom-ORD"> <mi mathvariant="normal">M</mi> <mi mathvariant="normal">o</mi> <mi mathvariant="normal">d</mi> <mi mathvariant="normal">e</mi> <mi mathvariant="normal">l</mi> <mi mathvariant="normal">s</mi> </mrow> </mrow> <mo>⁡</mo> <mo stretchy="false">(</mo> <mrow class="MJX-TeXAtom-ORD"> <mrow class="MJX-TeXAtom-ORD"> <mover> <mi>q</mi> <mo stretchy="false">¯</mo> </mover> </mrow> </mrow> <mo stretchy="false">(</mo> <mi>T</mi> <mo stretchy="false">)</mo> <mo stretchy="false">)</mo> <mo>=</mo> <mo>⋂</mo> <mo fence="false" stretchy="false">{</mo> <mi>q</mi> <mo stretchy="false">(</mo> <mi>R</mi> <mo stretchy="false">)</mo> <mspace width="thinmathspace"></mspace> <mrow class="MJX-TeXAtom-ORD"> <mo stretchy="false">|</mo> </mrow> <mi>R</mi> <mo>∈</mo> <mrow class="MJX-TeXAtom-OP"> <mrow class="MJX-TeXAtom-ORD"> <mi mathvariant="normal">M</mi> <mi mathvariant="normal">o</mi> <mi mathvariant="normal">d</mi> <mi mathvariant="normal">e</mi> <mi mathvariant="normal">l</mi> <mi mathvariant="normal">s</mi> </mrow> </mrow> <mo>⁡</mo> <mo stretchy="false">(</mo> <mi>T</mi> <mo stretchy="false">)</mo> <mo fence="false" stretchy="false">}</mo> </mstyle> </mrow> <annotation encoding="application/x-tex">{\displaystyle \bigcap \mathop {\mathrm {Models} } ({\bar {q}}(T))=\bigcap \{q(R)\,|R\in \mathop {\mathrm {Models} } (T)\}}</annotation> </semantics> </math></span><img src="https://wikimedia.org/api/rest_v1/media/math/render/svg/a8e6bdf6a19ca3cc622a47cf85cebca2d28f2b7a" class="mwe-math-fallback-image-inline mw-invert skin-invert" aria-hidden="true" style="vertical-align: -1.338ex; width:45.695ex; height:3.843ex;" alt="{\displaystyle \bigcap \mathop {\mathrm {Models} } ({\bar {q}}(T))=\bigcap \{q(R)\,|R\in \mathop {\mathrm {Models} } (T)\}}" /></span></dd></dl> <p>The right-hand side of the above equation is the <i>sure</i> information, i.e. information which can be certainly extracted from the database regardless of what values are used to replace Nulls in the database. In the example we considered above, it's easy to see that the intersection of all possible models (i.e. the sure information) of the query selecting <code class="mw-highlight mw-highlight-lang-sql mw-content-ltr" style="" dir="ltr"><span class="k">WHERE</span><span class="w"> </span><span class="n">Age</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="mi">22</span></code> is actually empty because, for instance, the (unlifted) query returns no rows for the relation EmpH37. More generally, it was shown by Imielinski and Lipski that Codd tables are a weak representation system if the query language is restricted to projections, selections (and renaming of columns). However, as soon as we add either joins or unions to the query language, even this weak property is lost, as evidenced in the next section. </p> <div class="mw-heading mw-heading3"><h3 id="If_joins_or_unions_are_considered:_not_even_weak_representation">If joins or unions are considered: not even weak representation</h3><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=16" title="Edit section: If joins or unions are considered: not even weak representation"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <p>Consider the following query over the same Codd table <style data-mw-deduplicate="TemplateStyles:r886049734">.mw-parser-output .monospaced{font-family:monospace,monospace}</style><span class="monospaced">Emp</span> from the previous section: </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">SELECT</span><span class="w"> </span><span class="n">Name</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">Emp</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">Age</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="mi">22</span> <span class="k">UNION</span> <span class="k">SELECT</span><span class="w"> </span><span class="n">Name</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">Emp</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">Age</span><span class="w"> </span><span class="o"><></span><span class="w"> </span><span class="mi">22</span><span class="p">;</span> </pre></div> <p>Whatever concrete value one would choose for the <code style="color: white; background-color: gray; padding: 2px 4px; font-size: smaller;">NULL</code> age of Harriet, the above query will return the full column of names of any model of <link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r886049734" /><span class="monospaced">Emp</span>, but when the (lifted) query is run on <link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r886049734" /><span class="monospaced">Emp</span> itself, Harriet will always be missing, i.e. we have: </p> <table style="margin: 0 auto;" cellpadding="20"> <tbody><tr valign="center"> <td>Query result on <link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r886049734" /><span class="monospaced">Emp</span>: </td> <td> <table class="wikitable"> <tbody><tr> <th>Name </th></tr> <tr> <td>George </td></tr> <tr> <td>Charles </td></tr></tbody></table> </td> <td>Query result on any model of <link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r886049734" /><span class="monospaced">Emp</span>: </td> <td> <table class="wikitable"> <tbody><tr> <th>Name </th></tr> <tr> <td>George </td></tr> <tr> <td>Harriet </td></tr> <tr> <td>Charles </td></tr></tbody></table> </td></tr></tbody></table> <p>Thus when unions are added to the query language, Codd tables are not even a weak representation system of missing information, meaning that queries over them don't even report all <i>sure</i> information. It's important to note here that semantics of UNION on Nulls, which are discussed in a later section, did not even come into play in this query. The "forgetful" nature of the two sub-queries was all that it took to guarantee that some sure information went unreported when the above query was run on the Codd table Emp. </p><p>For <a href="/wiki/Natural_join" class="mw-redirect" title="Natural join">natural joins</a>, the example needed to show that sure information may be unreported by some query is slightly more complicated. Consider the table </p> <table class="wikitable"> <caption><i>J</i> </caption> <tbody><tr> <th>F1</th> <th>F2</th> <th>F3 </th></tr> <tr> <td>11</td> <td><code style="color: white; background-color: gray; padding: 2px 4px; font-size: smaller;">NULL</code></td> <td>13 </td></tr> <tr> <td>21</td> <td><code style="color: white; background-color: gray; padding: 2px 4px; font-size: smaller;">NULL</code></td> <td>23 </td></tr> <tr> <td>31</td> <td>32</td> <td>33 </td></tr></tbody></table> <p>and the query </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">SELECT</span><span class="w"> </span><span class="n">F1</span><span class="p">,</span><span class="w"> </span><span class="n">F3</span><span class="w"> </span><span class="k">FROM</span> <span class="w"> </span><span class="p">(</span><span class="k">SELECT</span><span class="w"> </span><span class="n">F1</span><span class="p">,</span><span class="w"> </span><span class="n">F2</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">J</span><span class="p">)</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">F12</span> <span class="w"> </span><span class="k">NATURAL</span><span class="w"> </span><span class="k">JOIN</span> <span class="w"> </span><span class="p">(</span><span class="k">SELECT</span><span class="w"> </span><span class="n">F2</span><span class="p">,</span><span class="w"> </span><span class="n">F3</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">J</span><span class="p">)</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">F23</span><span class="p">;</span> </pre></div> <table style="margin: 0 auto;" cellpadding="20"> <tbody><tr valign="center"> <td>Query result on J: </td> <td> <table class="wikitable"> <tbody><tr> <th>F1</th> <th>F3 </th></tr> <tr> <td>31</td> <td>33 </td></tr></tbody></table> </td> <td>Query result on any model of J: </td> <td> <table class="wikitable"> <tbody><tr> <th>F1</th> <th>F3 </th></tr> <tr> <td>11</td> <td>13 </td></tr> <tr> <td>21</td> <td>23 </td></tr> <tr> <td>31</td> <td>33 </td></tr></tbody></table> </td></tr></tbody></table> <p>The intuition for what happens above is that the Codd tables representing the projections in the subqueries lose track of the fact that the Nulls in the columns F12.F2 and F23.F2 are actually copies of the originals in the table J. This observation suggests that a relatively simple improvement of Codd tables (which works correctly for this example) would be to use <i>Skolem constants</i> (meaning <a href="/wiki/Skolem_function" class="mw-redirect" title="Skolem function">Skolem functions</a> which are also <a href="/wiki/Constant_function" title="Constant function">constant functions</a>), say ω<sub>12</sub> and ω<sub>22</sub> instead of a single NULL symbol. Such an approach, called v-tables or Naive tables, is computationally less expensive that the c-tables discussed above. However, it is still not a complete solution for incomplete information in the sense that v-tables are only a weak representation for queries not using any negations in selection (and not using any set difference either). The first example considered in this section is using a negative selection clause, <code class="mw-highlight mw-highlight-lang-sql mw-content-ltr" style="" dir="ltr"><span class="k">WHERE</span><span class="w"> </span><span class="n">Age</span><span class="w"> </span><span class="o"><></span><span class="w"> </span><span class="mi">22</span></code>, so it is also an example where v-tables queries would not report sure information. </p> <div class="mw-heading mw-heading2"><h2 id="Check_constraints_and_foreign_keys">Check constraints and foreign keys</h2><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=17" title="Edit section: Check constraints and foreign keys"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <p>The primary place in which SQL three-valued logic intersects with SQL <a href="/wiki/Data_Definition_Language" class="mw-redirect" title="Data Definition Language">Data Definition Language</a> (DDL) is in the form of <a href="/wiki/Check_constraint" title="Check constraint">check constraints</a>. A check constraint placed on a column operates under a slightly different set of rules than those for the DML <code>WHERE</code> clause. While a DML <code>WHERE</code> clause must evaluate to True for a row, a check constraint must not evaluate to False. (From a logic perspective, the <a href="/w/index.php?title=Designated_value&action=edit&redlink=1" class="new" title="Designated value (page does not exist)">designated values</a> are True and Unknown.) This means that a check constraint will succeed if the result of the check is either True or Unknown. The following example table with a check constraint will prohibit any integer values from being inserted into column <i>i</i>, but will allow Null to be inserted since the result of the check will always evaluate to Unknown for Nulls.<sup id="cite_ref-nullversusnull_19-0" class="reference"><a href="#cite_note-nullversusnull-19"><span class="cite-bracket">[</span>19<span class="cite-bracket">]</span></a></sup> </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">CREATE</span><span class="w"> </span><span class="k">TABLE</span><span class="w"> </span><span class="n">t</span><span class="w"> </span><span class="p">(</span> <span class="w"> </span><span class="n">i</span><span class="w"> </span><span class="nb">INTEGER</span><span class="p">,</span> <span class="w"> </span><span class="k">CONSTRAINT</span><span class="w"> </span><span class="n">ck_i</span><span class="w"> </span><span class="k">CHECK</span><span class="w"> </span><span class="p">(</span><span class="w"> </span><span class="n">i</span><span class="w"> </span><span class="o"><</span><span class="w"> </span><span class="mi">0</span><span class="w"> </span><span class="k">AND</span><span class="w"> </span><span class="n">i</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="mi">0</span><span class="w"> </span><span class="k">AND</span><span class="w"> </span><span class="n">i</span><span class="w"> </span><span class="o">></span><span class="w"> </span><span class="mi">0</span><span class="w"> </span><span class="p">)</span><span class="w"> </span><span class="p">);</span> </pre></div> <p>Because of the change in designated values relative to the <link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r886049734" /><span class="monospaced">WHERE</span> clause, from a logic perspective the law of excluded middle is a tautology for <link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r886049734" /><span class="monospaced">CHECK</span> constraints, meaning <code>CHECK (<i>p</i> OR NOT <i>p</i>)</code> always succeeds. Furthermore, assuming Nulls are to be interpreted as existing but unknown values, some pathological CHECKs like the one above allow insertion of Nulls that could never be replaced by any non-null value. </p><p>In order to constrain a column to reject Nulls, the <code>NOT NULL</code> constraint can be applied, as shown in the example below. The <code>NOT NULL</code> constraint is semantically equivalent to a <a href="/wiki/Check_constraint" title="Check constraint">check constraint</a> with an <code>IS NOT NULL</code> predicate. </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">CREATE</span><span class="w"> </span><span class="k">TABLE</span><span class="w"> </span><span class="n">t</span><span class="w"> </span><span class="p">(</span><span class="w"> </span><span class="n">i</span><span class="w"> </span><span class="nb">INTEGER</span><span class="w"> </span><span class="k">NOT</span><span class="w"> </span><span class="k">NULL</span><span class="w"> </span><span class="p">);</span> </pre></div> <p>By default check constraints against <a href="/wiki/Foreign_key" title="Foreign key">foreign keys</a> succeed if any of the fields in such keys are Null. For example, the table </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">CREATE</span><span class="w"> </span><span class="k">TABLE</span><span class="w"> </span><span class="n">Books</span> <span class="p">(</span><span class="w"> </span><span class="n">title</span><span class="w"> </span><span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">100</span><span class="p">),</span> <span class="w"> </span><span class="n">author_last</span><span class="w"> </span><span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">20</span><span class="p">),</span> <span class="w"> </span><span class="n">author_first</span><span class="w"> </span><span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">20</span><span class="p">),</span> <span class="k">FOREIGN</span><span class="w"> </span><span class="k">KEY</span><span class="w"> </span><span class="p">(</span><span class="n">author_last</span><span class="p">,</span><span class="w"> </span><span class="n">author_first</span><span class="p">)</span> <span class="w"> </span><span class="k">REFERENCES</span><span class="w"> </span><span class="n">Authors</span><span class="p">(</span><span class="n">last_name</span><span class="p">,</span><span class="w"> </span><span class="n">first_name</span><span class="p">));</span> </pre></div> <p>would allow insertion of rows where author_last or author_first are <code style="color: white; background-color: gray; padding: 2px 4px; font-size: smaller;">NULL</code> irrespective of how the table Authors is defined or what it contains. More precisely, a null in any of these fields would allow any value in the other one, even on that is not found in Authors table. For example, if Authors contained only <code class="mw-highlight mw-highlight-lang-text mw-content-ltr" style="" dir="ltr">('Doe', 'John')</code>, then <code class="mw-highlight mw-highlight-lang-text mw-content-ltr" style="" dir="ltr">('Smith', NULL)</code> would satisfy the foreign key constraint. <a href="/wiki/SQL-92" title="SQL-92">SQL-92</a> added two extra options for narrowing down the matches in such cases. If <code>MATCH PARTIAL</code> is added after the <code>REFERENCES</code> declaration then any non-null must match the foreign key, e.g. <code class="mw-highlight mw-highlight-lang-text mw-content-ltr" style="" dir="ltr">('Doe', NULL)</code> would still match, but <code class="mw-highlight mw-highlight-lang-text mw-content-ltr" style="" dir="ltr">('Smith', NULL)</code> would not. Finally, if <code>MATCH FULL</code> is added then <code class="mw-highlight mw-highlight-lang-text mw-content-ltr" style="" dir="ltr">('Doe', NULL)</code> would not match the constraint either, but <code class="mw-highlight mw-highlight-lang-text mw-content-ltr" style="" dir="ltr">(NULL, NULL)</code> would still match it. </p> <div class="mw-heading mw-heading2"><h2 id="Outer_joins">Outer joins</h2><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=18" title="Edit section: Outer joins"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <figure class="mw-halign-right" typeof="mw:File/Thumb"><a href="/wiki/File:Sql_query1.png" class="mw-file-description"><img src="//upload.wikimedia.org/wikipedia/en/thumb/8/87/Sql_query1.png/270px-Sql_query1.png" decoding="async" width="270" height="168" class="mw-file-element" srcset="//upload.wikimedia.org/wikipedia/en/8/87/Sql_query1.png 1.5x" data-file-width="399" data-file-height="249" /></a><figcaption>Example <a href="/wiki/SQL" title="SQL">SQL</a> <a href="/wiki/Join_(SQL)" title="Join (SQL)">outer join</a> query with Null placeholders in the result set. The Null markers are represented by the word <code>NULL</code> in place of data in the results. Results are from <a href="/wiki/Microsoft_SQL_Server" title="Microsoft SQL Server">Microsoft SQL Server</a>, as shown in SQL Server Management Studio.</figcaption></figure> <p>SQL <a href="/wiki/Join_(SQL)" title="Join (SQL)">outer joins</a>, including left outer joins, right outer joins, and full outer joins, automatically produce Nulls as placeholders for missing values in related tables. For left outer joins, for instance, Nulls are produced in place of rows missing from the table appearing on the right-hand side of the <code>LEFT OUTER JOIN</code> operator. The following simple example uses two tables to demonstrate Null placeholder production in a left outer join. </p><p>The first table (<b>Employee</b>) contains employee ID numbers and names, while the second table (<b>PhoneNumber</b>) contains related employee ID numbers and <a href="/wiki/Telephone_number" title="Telephone number">phone numbers</a>, as shown below. </p> <table> <tbody><tr> <td valign="top"> <table class="wikitable"> <caption>Employee </caption> <tbody><tr> <th>ID </th> <th>LastName </th> <th>FirstName </th></tr> <tr> <td>1 </td> <td>Johnson </td> <td>Joe </td></tr> <tr> <td>2 </td> <td>Lewis </td> <td>Larry </td></tr> <tr> <td>3 </td> <td>Thompson </td> <td>Thomas </td></tr> <tr> <td>4 </td> <td>Patterson </td> <td>Patricia </td></tr> </tbody></table> </td> <td valign="top"> <table class="wikitable"> <caption>PhoneNumber </caption> <tbody><tr> <th>ID </th> <th>Number </th></tr> <tr> <td>1 </td> <td>555-2323 </td></tr> <tr> <td>3 </td> <td>555-9876 </td></tr> </tbody></table> </td></tr></tbody></table> <p>The following sample SQL query performs a left outer join on these two tables. </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">SELECT</span><span class="w"> </span><span class="n">e</span><span class="p">.</span><span class="n">ID</span><span class="p">,</span><span class="w"> </span><span class="n">e</span><span class="p">.</span><span class="n">LastName</span><span class="p">,</span><span class="w"> </span><span class="n">e</span><span class="p">.</span><span class="n">FirstName</span><span class="p">,</span><span class="w"> </span><span class="n">pn</span><span class="p">.</span><span class="nb">Number</span> <span class="k">FROM</span><span class="w"> </span><span class="n">Employee</span><span class="w"> </span><span class="n">e</span> <span class="k">LEFT</span><span class="w"> </span><span class="k">OUTER</span><span class="w"> </span><span class="k">JOIN</span><span class="w"> </span><span class="n">PhoneNumber</span><span class="w"> </span><span class="n">pn</span> <span class="k">ON</span><span class="w"> </span><span class="n">e</span><span class="p">.</span><span class="n">ID</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">pn</span><span class="p">.</span><span class="n">ID</span><span class="p">;</span> </pre></div> <p>The <a href="/wiki/Result_set" title="Result set">result set</a> generated by this query demonstrates how SQL uses Null as a placeholder for values missing from the right-hand (<b>PhoneNumber</b>) table, as shown below. </p> <table class="wikitable"> <caption>Query result </caption> <tbody><tr> <th>ID </th> <th>LastName </th> <th>FirstName </th> <th>Number </th></tr> <tr> <td>1 </td> <td>Johnson </td> <td>Joe </td> <td>555-2323 </td></tr> <tr> <td>2 </td> <td>Lewis </td> <td>Larry </td> <td><code style="color: white; background-color: gray; padding: 2px 4px; font-size: smaller;">NULL</code> </td></tr> <tr> <td>3 </td> <td>Thompson </td> <td>Thomas </td> <td>555-9876 </td></tr> <tr> <td>4 </td> <td>Patterson </td> <td>Patricia </td> <td><code style="color: white; background-color: gray; padding: 2px 4px; font-size: smaller;">NULL</code> </td></tr> </tbody></table> <div class="mw-heading mw-heading2"><h2 id="Aggregate_functions">Aggregate functions</h2><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=19" title="Edit section: Aggregate functions"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <p>SQL defines <a href="/wiki/Aggregate_function" title="Aggregate function">aggregate functions</a> to simplify server-side aggregate calculations on data. Except for the <code>COUNT(*)</code> function, all aggregate functions perform a Null-elimination step, so that Nulls are not included in the final result of the calculation.<sup id="cite_ref-SQL2003-Part2-Sec4154_20-0" class="reference"><a href="#cite_note-SQL2003-Part2-Sec4154-20"><span class="cite-bracket">[</span>20<span class="cite-bracket">]</span></a></sup> </p><p>Note that the elimination of Null is not equivalent to replacing Null with zero. For example, in the following table, <code>AVG(i)</code> (the average of the values of <code>i</code>) will give a different result from that of <code>AVG(j)</code>: </p> <table class="wikitable" style="font-family:monospace"> <tbody><tr> <th>i </th> <th>j </th></tr> <tr> <td>150 </td> <td>150 </td></tr> <tr> <td>200 </td> <td>200 </td></tr> <tr> <td>250 </td> <td>250 </td></tr> <tr> <td><code style="color: white; background-color: gray; padding: 2px 4px; font-size: smaller;">NULL</code> </td> <td>0 </td></tr></tbody></table> <p>Here <code>AVG(i)</code> is 200 (the average of 150, 200, and 250), while <code>AVG(j)</code> is 150 (the average of 150, 200, 250, and 0). A well-known side effect of this is that in SQL, <code>AVG(z)</code> is not equivalent with <code>SUM(z)/COUNT(*)</code> but with <code>SUM(z)/COUNT(z)</code>.<sup id="cite_ref-Chamberlin1998_4-1" class="reference"><a href="#cite_note-Chamberlin1998-4"><span class="cite-bracket">[</span>4<span class="cite-bracket">]</span></a></sup> </p><p>The output of an aggregate function can also be Null. Here is an example: </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">SELECT</span><span class="w"> </span><span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">),</span><span class="w"> </span><span class="k">MIN</span><span class="p">(</span><span class="n">e</span><span class="p">.</span><span class="n">Wage</span><span class="p">),</span><span class="w"> </span><span class="k">MAX</span><span class="p">(</span><span class="n">e</span><span class="p">.</span><span class="n">Wage</span><span class="p">)</span> <span class="k">FROM</span><span class="w"> </span><span class="n">Employee</span><span class="w"> </span><span class="n">e</span> <span class="k">WHERE</span><span class="w"> </span><span class="n">e</span><span class="p">.</span><span class="n">LastName</span><span class="w"> </span><span class="k">LIKE</span><span class="w"> </span><span class="s1">'%Jones%'</span><span class="p">;</span> </pre></div> <p>This query will always output exactly one row, counting the number of employees whose last name contains "Jones", and giving the minimum and maximum wage found for those employees. However, what happens if none of the employees fit the given criteria? Calculating the minimum or maximum value of an empty set is impossible, so those results must be NULL, indicating there is no answer. This is not an Unknown value, it is a Null representing the absence of a value. The result would be: </p> <table class="wikitable" style="font-family:monospace"> <tbody><tr> <th>COUNT(*) </th> <th>MIN(e.Wage) </th> <th>MAX(e.Wage) </th></tr> <tr> <td>0 </td> <td><code style="color: white; background-color: gray; padding: 2px 4px; font-size: smaller;">NULL</code> </td> <td><code style="color: white; background-color: gray; padding: 2px 4px; font-size: smaller;">NULL</code> </td></tr></tbody></table> <div class="mw-heading mw-heading2"><h2 id="When_two_nulls_are_equal:_grouping,_sorting,_and_some_set_operations"><span id="When_two_nulls_are_equal:_grouping.2C_sorting.2C_and_some_set_operations"></span>When two nulls are equal: grouping, sorting, and some set operations</h2><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=20" title="Edit section: When two nulls are equal: grouping, sorting, and some set operations"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <p>Because <a href="/wiki/SQL:2003" title="SQL:2003">SQL:2003</a> defines all Null markers as being unequal to one another, a special definition was required in order to group Nulls together when performing certain operations. SQL defines "any two values that are equal to one another, or any two Nulls", as "not distinct".<sup id="cite_ref-SQL2003-Part2-Sec3168_21-0" class="reference"><a href="#cite_note-SQL2003-Part2-Sec3168-21"><span class="cite-bracket">[</span>21<span class="cite-bracket">]</span></a></sup> This definition of <i>not distinct</i> allows SQL to group and sort Nulls when the <code>GROUP BY</code> clause (or another SQL language feature that performs grouping) is used. </p><p>Other SQL operations, clauses, and keywords using the "not distinct" definition in their treatment of Nulls include: </p> <ul><li>The <code>PARTITION BY</code> clause of the ranking and windowing functions such as <code>ROW_NUMBER</code></li> <li>The <code>UNION</code>, <code>INTERSECT</code>, and <code>EXCEPT</code> operators, which treat NULLs as the same for row comparison/elimination purposes</li> <li>The <code>DISTINCT</code> keyword used in <code>SELECT</code> queries</li></ul> <p>The principle that Nulls are not equal to each other (but rather that the result is Unknown) is effectively violated in the SQL specification for the <code>UNION</code> operator, which does identify nulls with each other.<sup id="cite_ref-Meyden_1-3" class="reference"><a href="#cite_note-Meyden-1"><span class="cite-bracket">[</span>1<span class="cite-bracket">]</span></a></sup> Consequently, some set operations in SQL, such as union and difference, may produce results not representing sure information, unlike operations involving explicit comparisons with NULL (e.g. those in a <code>WHERE</code> clause discussed above). In Codd's 1979 proposal (which was adopted by SQL92) this semantic inconsistency is rationalized by arguing that removal of duplicates in set operations happens "at a lower level of detail than equality testing in the evaluation of retrieval operations."<sup id="cite_ref-Klein_11-1" class="reference"><a href="#cite_note-Klein-11"><span class="cite-bracket">[</span>11<span class="cite-bracket">]</span></a></sup> </p><p>The SQL standard does not explicitly define a default sorting order for Nulls. Instead, on conforming systems, Nulls can be sorted before or after all data values by using the <code>NULLS FIRST</code> or <code>NULLS LAST</code> clauses of the <code>ORDER BY</code> list, respectively. Not all DBMS vendors implement this functionality, however. Vendors who do not implement this functionality may specify different treatments for Null sorting in the DBMS.<sup id="cite_ref-nullversusnull_19-1" class="reference"><a href="#cite_note-nullversusnull-19"><span class="cite-bracket">[</span>19<span class="cite-bracket">]</span></a></sup> </p> <div class="mw-heading mw-heading2"><h2 id="Effect_on_index_operation">Effect on index operation</h2><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=21" title="Edit section: Effect on index operation"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <p>Some SQL products do not index keys containing NULLs. For instance, <a href="/wiki/PostgreSQL" title="PostgreSQL">PostgreSQL</a> versions prior to 8.3 did not, with the documentation for a <a href="/wiki/B-tree" title="B-tree">B-tree</a> index stating that<sup id="cite_ref-22" class="reference"><a href="#cite_note-22"><span class="cite-bracket">[</span>22<span class="cite-bracket">]</span></a></sup> </p> <style data-mw-deduplicate="TemplateStyles:r1244412712">.mw-parser-output .templatequote{overflow:hidden;margin:1em 0;padding:0 32px}.mw-parser-output .templatequotecite{line-height:1.5em;text-align:left;margin-top:0}@media(min-width:500px){.mw-parser-output .templatequotecite{padding-left:1.6em}}</style><blockquote class="templatequote"> <p>B-trees can handle equality and range queries on data that can be sorted into some ordering. In particular, the PostgreSQL query planner will consider using a B-tree index whenever an indexed column is involved in a comparison using one of these operators: < ≤ = ≥ > </p><p>Constructs equivalent to combinations of these operators, such as BETWEEN and IN, can also be implemented with a B-tree index search. (But note that IS NULL is not equivalent to = and is not indexable.) </p> </blockquote> <p>In cases where the index enforces uniqueness, NULLs are excluded from the index and uniqueness is not enforced between NULLs. Again, quoting from the <a href="/wiki/PostgreSQL" title="PostgreSQL">PostgreSQL</a> documentation:<sup id="cite_ref-23" class="reference"><a href="#cite_note-23"><span class="cite-bracket">[</span>23<span class="cite-bracket">]</span></a></sup> </p> <link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1244412712" /><blockquote class="templatequote"> <p>When an index is declared unique, multiple table rows with equal indexed values will not be allowed. Nulls are not considered equal. A multicolumn unique index will only reject cases where all of the indexed columns are equal in two rows. </p> </blockquote> <p>This is consistent with the <a href="/wiki/SQL:2003" title="SQL:2003">SQL:2003</a>-defined behavior of scalar Null comparisons. </p><p>Another method of indexing Nulls involves handling them as <i>not distinct</i> in accordance with the SQL:2003-defined behavior. For example, <a href="/wiki/Microsoft_SQL_Server" title="Microsoft SQL Server">Microsoft SQL Server</a> documentation states the following:<sup id="cite_ref-24" class="reference"><a href="#cite_note-24"><span class="cite-bracket">[</span>24<span class="cite-bracket">]</span></a></sup> </p> <link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1244412712" /><blockquote class="templatequote"> <p>For indexing purposes, NULLs compare as equal. Therefore, a unique index, or UNIQUE constraint, cannot be created if the keys are NULL in more than one row. Select columns that are defined as NOT NULL when columns for a unique index or unique constraint are chosen. </p> </blockquote> <p>Both of these indexing strategies are consistent with the SQL:2003-defined behavior of Nulls. Because indexing methodologies are not explicitly defined by the SQL:2003 standard, indexing strategies for Nulls are left entirely to the vendors to design and implement. </p> <div class="mw-heading mw-heading2"><h2 id="Null-handling_functions">Null-handling functions</h2><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=22" title="Edit section: Null-handling functions"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <p>SQL defines two functions to explicitly handle Nulls: <code>NULLIF</code> and <code>COALESCE</code>. Both functions are abbreviations for <a href="/wiki/Case_(SQL)" class="mw-redirect" title="Case (SQL)">searched <code>CASE</code> expressions</a>.<sup id="cite_ref-SQL2003-Part2-Sec611_25-0" class="reference"><a href="#cite_note-SQL2003-Part2-Sec611-25"><span class="cite-bracket">[</span>25<span class="cite-bracket">]</span></a></sup> </p> <div class="mw-heading mw-heading3"><h3 id="NULLIF">NULLIF</h3><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=23" title="Edit section: NULLIF"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <p>The <code>NULLIF</code> function accepts two parameters. If the first parameter is equal to the second parameter, <code>NULLIF</code> returns Null. Otherwise, the value of the first parameter is returned. </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">NULLIF</span><span class="p">(</span><span class="n">value1</span><span class="p">,</span><span class="w"> </span><span class="n">value2</span><span class="p">)</span> </pre></div> <p>Thus, <code>NULLIF</code> is an abbreviation for the following <code>CASE</code> expression: </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">CASE</span><span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="n">value1</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">value2</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="k">NULL</span><span class="w"> </span><span class="k">ELSE</span><span class="w"> </span><span class="n">value1</span><span class="w"> </span><span class="k">END</span> </pre></div> <div class="mw-heading mw-heading3"><h3 id="COALESCE">COALESCE</h3><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=24" title="Edit section: COALESCE"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <p>The <code>COALESCE</code> function accepts a list of parameters, returning the first non-Null value from the list: </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">COALESCE</span><span class="p">(</span><span class="n">value1</span><span class="p">,</span><span class="w"> </span><span class="n">value2</span><span class="p">,</span><span class="w"> </span><span class="n">value3</span><span class="p">,</span><span class="w"> </span><span class="p">...)</span> </pre></div> <p><code>COALESCE</code> is defined as shorthand for the following SQL <code>CASE</code> expression: </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">CASE</span><span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="n">value1</span><span class="w"> </span><span class="k">IS</span><span class="w"> </span><span class="k">NOT</span><span class="w"> </span><span class="k">NULL</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="n">value1</span> <span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="n">value2</span><span class="w"> </span><span class="k">IS</span><span class="w"> </span><span class="k">NOT</span><span class="w"> </span><span class="k">NULL</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="n">value2</span> <span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="n">value3</span><span class="w"> </span><span class="k">IS</span><span class="w"> </span><span class="k">NOT</span><span class="w"> </span><span class="k">NULL</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="n">value3</span> <span class="w"> </span><span class="p">...</span> <span class="w"> </span><span class="k">END</span> </pre></div> <p>Some SQL DBMSs implement vendor-specific functions similar to <code>COALESCE</code>. Some systems (e.g. <a href="/wiki/Transact-SQL" title="Transact-SQL">Transact-SQL</a>) implement an <code>ISNULL</code> function, or other similar functions that are functionally similar to <code>COALESCE</code>. (See <a href="/wiki/Is_functions" title="Is functions"><code>Is</code> functions</a> for more on the <code>IS</code> functions in Transact-SQL.) </p> <div class="mw-heading mw-heading3"><h3 id="NVL">NVL</h3><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=25" title="Edit section: NVL"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1236090951" /><div role="note" class="hatnote navigation-not-searchable">"NVL" redirects here. For other uses, see <a href="/wiki/NVL_(disambiguation)" class="mw-disambig" title="NVL (disambiguation)">NVL (disambiguation)</a>.</div> <p>The Oracle <code>NVL</code> function accepts two parameters. It returns the first non-NULL parameter or NULL if all parameters are NULL. </p><p>A <code>COALESCE</code> expression can be converted into an equivalent <code>NVL</code> expression thus: </p> <div class="mw-highlight mw-highlight-lang-mysql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">COALESCE</span><span class="w"> </span><span class="p">(</span><span class="w"> </span><span class="n">val1</span><span class="p">,</span><span class="w"> </span><span class="p">...</span><span class="w"> </span><span class="p">,</span><span class="w"> </span><span class="n">val</span><span class="err">{</span><span class="n">n</span><span class="err">}</span><span class="w"> </span><span class="p">)</span> </pre></div> <p>turns into: </p> <div class="mw-highlight mw-highlight-lang-mysql mw-content-ltr" dir="ltr"><pre><span></span><span class="n">NVL</span><span class="p">(</span><span class="w"> </span><span class="n">val1</span><span class="w"> </span><span class="p">,</span><span class="w"> </span><span class="n">NVL</span><span class="p">(</span><span class="w"> </span><span class="n">val2</span><span class="w"> </span><span class="p">,</span><span class="w"> </span><span class="n">NVL</span><span class="p">(</span><span class="w"> </span><span class="n">val3</span><span class="w"> </span><span class="p">,</span><span class="w"> </span><span class="n">…</span><span class="w"> </span><span class="p">,</span><span class="w"> </span><span class="n">NVL</span><span class="w"> </span><span class="p">(</span><span class="w"> </span><span class="n">val</span><span class="err">{</span><span class="n">n</span><span class="o">-</span><span class="mi">1</span><span class="err">}</span><span class="w"> </span><span class="p">,</span><span class="w"> </span><span class="n">val</span><span class="err">{</span><span class="n">n</span><span class="err">}</span><span class="w"> </span><span class="p">)</span><span class="w"> </span><span class="n">…</span><span class="w"> </span><span class="p">)))</span> </pre></div> <p>A use case of this function is to replace in an expression a NULL by a value like in <code>NVL(SALARY, 0)</code> which says, 'if <code>SALARY</code> is NULL, replace it with the value 0'. </p><p>There is, however, one notable exception. In most implementations, <code>COALESCE</code> evaluates its parameters until it reaches the first non-NULL one, while <code>NVL</code> evaluates all of its parameters. This is important for several reasons. A parameter <i>after</i> the first non-NULL parameter could be a function, which could either be computationally expensive, invalid, or could create unexpected side effects. </p> <div class="mw-heading mw-heading2"><h2 id="Data_typing_of_Null_and_Unknown">Data typing of Null and Unknown</h2><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=26" title="Edit section: Data typing of Null and Unknown"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1236090951" /><div role="note" class="hatnote navigation-not-searchable">See also: <a href="/wiki/Option_type" title="Option type">Option type</a> and <a href="/wiki/Nullable_type" title="Nullable type">Nullable type</a></div> <p>The <code>NULL</code> <a href="/wiki/Literal_(computer_programming)" title="Literal (computer programming)">literal</a> is untyped in SQL, meaning that it is not designated as an integer, character, or any other specific <a href="/wiki/Data_type" title="Data type">data type</a>.<sup id="cite_ref-Understanding1999_26-0" class="reference"><a href="#cite_note-Understanding1999-26"><span class="cite-bracket">[</span>26<span class="cite-bracket">]</span></a></sup> Because of this, it is sometimes mandatory (or desirable) to explicitly convert Nulls to a specific data type. For instance, if <a href="/wiki/Function_overloading" title="Function overloading">overloaded</a> functions are supported by the RDBMS, SQL might not be able to automatically resolve to the correct function without knowing the data types of all parameters, including those for which Null is passed. </p><p>Conversion from the <code>NULL</code> literal to a Null of a specific type is possible using the <code>CAST</code> introduced in <a href="/wiki/SQL-92" title="SQL-92">SQL-92</a>. For example: </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">CAST</span><span class="w"> </span><span class="p">(</span><span class="k">NULL</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="nb">INTEGER</span><span class="p">)</span> </pre></div> <p>represents an absent value of type INTEGER. </p><p>The actual typing of Unknown (distinct or not from NULL itself) varies between SQL implementations. For example, the following </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">SELECT</span><span class="w"> </span><span class="s1">'ok'</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="p">(</span><span class="k">NULL</span><span class="w"> </span><span class="o"><></span><span class="w"> </span><span class="mi">1</span><span class="p">)</span><span class="w"> </span><span class="k">IS</span><span class="w"> </span><span class="k">NULL</span><span class="p">;</span> </pre></div> <p>parses and executes successfully in some environments (e.g. <a href="/wiki/SQLite" title="SQLite">SQLite</a> or <a href="/wiki/PostgreSQL" title="PostgreSQL">PostgreSQL</a>) which unify a NULL Boolean with Unknown but fails to parse in others (e.g. in <a href="/wiki/SQL_Server_Compact" title="SQL Server Compact">SQL Server Compact</a>). <a href="/wiki/MySQL" title="MySQL">MySQL</a> behaves similarly to <a href="/wiki/PostgreSQL" title="PostgreSQL">PostgreSQL</a> in this regard (with the minor exception that <a href="/wiki/MySQL" title="MySQL">MySQL</a> regards TRUE and FALSE as no different from the ordinary integers 1 and 0). PostgreSQL additionally implements a <code>IS UNKNOWN</code> predicate, which can be used to test whether a three-value logical outcome is Unknown, although this is merely syntactic sugar. </p> <div class="mw-heading mw-heading2"><h2 id="BOOLEAN_data_type">BOOLEAN data type</h2><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=27" title="Edit section: BOOLEAN data type"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <p>The ISO <a href="/wiki/SQL:1999" title="SQL:1999">SQL:1999</a> standard introduced the BOOLEAN data type to SQL, however it's still just an optional, non-core feature, coded T031.<sup id="cite_ref-ISO-9075-1_27-0" class="reference"><a href="#cite_note-ISO-9075-1-27"><span class="cite-bracket">[</span>27<span class="cite-bracket">]</span></a></sup> </p><p>When restricted by a <code>NOT NULL</code> constraint, the SQL BOOLEAN works like the <a href="/wiki/Boolean_type" class="mw-redirect" title="Boolean type">Boolean type</a> from other languages. Unrestricted however, the BOOLEAN datatype, despite its name, can hold the truth values TRUE, FALSE, and UNKNOWN, all of which are defined as Boolean literals according to the standard. The standard also asserts that NULL and UNKNOWN "may be used interchangeably to mean exactly the same thing".<sup id="cite_ref-Date2011_28-0" class="reference"><a href="#cite_note-Date2011-28"><span class="cite-bracket">[</span>28<span class="cite-bracket">]</span></a></sup><sup id="cite_ref-29" class="reference"><a href="#cite_note-29"><span class="cite-bracket">[</span>29<span class="cite-bracket">]</span></a></sup> </p><p>The Boolean type has been subject of criticism, particularly because of the mandated behavior of the UNKNOWN literal, which is never equal to itself because of the identification with NULL.<sup id="cite_ref-Prigmore2007_30-0" class="reference"><a href="#cite_note-Prigmore2007-30"><span class="cite-bracket">[</span>30<span class="cite-bracket">]</span></a></sup> </p><p>As discussed above, in the <a href="/wiki/PostgreSQL" title="PostgreSQL">PostgreSQL</a> implementation of <a href="/wiki/SQL" title="SQL">SQL</a>, Null is used to represent all UNKNOWN results, including the UNKNOWN BOOLEAN. PostgreSQL does not implement the UNKNOWN literal (although it does implement the IS UNKNOWN operator, which is an orthogonal feature.) Most other major vendors do not support the Boolean type (as defined in T031) as of 2012.<sup id="cite_ref-31" class="reference"><a href="#cite_note-31"><span class="cite-bracket">[</span>31<span class="cite-bracket">]</span></a></sup> The procedural part of Oracle's <a href="/wiki/PL/SQL" title="PL/SQL">PL/SQL</a> however supports BOOLEAN variables; these can also be assigned NULL and the value is considered the same as UNKNOWN.<sup id="cite_ref-FeuersteinPribyl2009_32-0" class="reference"><a href="#cite_note-FeuersteinPribyl2009-32"><span class="cite-bracket">[</span>32<span class="cite-bracket">]</span></a></sup> </p> <div class="mw-heading mw-heading2"><h2 id="Controversy">Controversy</h2><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=28" title="Edit section: Controversy"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <div class="mw-heading mw-heading3"><h3 id="Common_mistakes">Common mistakes</h3><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=29" title="Edit section: Common mistakes"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <p>Misunderstanding of how Null works is the cause of a great number of errors in SQL code, both in ISO standard SQL statements and in the specific SQL dialects supported by real-world database management systems. These mistakes are usually the result of confusion between Null and either 0 (zero) or an empty string (a string value with a length of zero, represented in SQL as <code>''</code>). Null is defined by the SQL standard as different from both an empty string and the numerical value <code>0</code>, however. While Null indicates the absence of any value, the empty string and numerical zero both represent actual values. </p><p>A classic error is the attempt to use the equals operator <code>=</code> in combination with the keyword <code>NULL</code> to find rows with Nulls. According to the SQL standard this is an invalid syntax and shall lead to an error message or an exception. But most implementations accept the syntax and evaluate such expressions to <code>UNKNOWN</code>. The consequence is that no rows are found –  regardless of whether rows with Nulls exist or not. The proposed way to retrieve rows with Nulls is the use of the predicate <code>IS NULL</code> instead of <code>= NULL</code>. </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">SELECT</span><span class="w"> </span><span class="o">*</span> <span class="k">FROM</span><span class="w"> </span><span class="n">sometable</span> <span class="k">WHERE</span><span class="w"> </span><span class="n">num</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="k">NULL</span><span class="p">;</span><span class="w"> </span><span class="c1">-- Should be "WHERE num IS NULL"</span> </pre></div> <p>In a related, but more subtle example, a <code>WHERE</code> clause or conditional statement might compare a column's value with a constant. It is often incorrectly assumed that a missing value would be "less than" or "not equal to" a constant if that field contains Null, but, in fact, such expressions return Unknown. An example is below: </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">SELECT</span><span class="w"> </span><span class="o">*</span> <span class="k">FROM</span><span class="w"> </span><span class="n">sometable</span> <span class="k">WHERE</span><span class="w"> </span><span class="n">num</span><span class="w"> </span><span class="o"><></span><span class="w"> </span><span class="mi">1</span><span class="p">;</span><span class="w"> </span><span class="c1">-- Rows where num is NULL will not be returned,</span> <span class="w"> </span><span class="c1">-- contrary to many users' expectations.</span> </pre></div> <p>These confusions arise because the <a href="/wiki/Law_of_Identity" class="mw-redirect" title="Law of Identity">Law of Identity</a> is restricted in SQL's logic. When dealing with equality comparisons using the <code>NULL</code> literal or the <code>UNKNOWN</code> truth-value, SQL will always return <code>UNKNOWN</code> as the result of the expression. This is a <a href="/wiki/Partial_equivalence_relation" title="Partial equivalence relation">partial equivalence relation</a> and makes SQL an example of a <i>Non-Reflexive logic</i>.<sup id="cite_ref-33" class="reference"><a href="#cite_note-33"><span class="cite-bracket">[</span>33<span class="cite-bracket">]</span></a></sup> </p><p>Similarly, Nulls are often confused with empty strings. Consider the <code>LENGTH</code> function, which returns the number of characters in a string. When a Null is passed into this function, the function returns Null. This can lead to unexpected results, if users are not well versed in 3-value logic. An example is below: </p> <div class="mw-highlight mw-highlight-lang-sql mw-content-ltr" dir="ltr"><pre><span></span><span class="k">SELECT</span><span class="w"> </span><span class="o">*</span> <span class="k">FROM</span><span class="w"> </span><span class="n">sometable</span> <span class="k">WHERE</span><span class="w"> </span><span class="k">LENGTH</span><span class="p">(</span><span class="n">string</span><span class="p">)</span><span class="w"> </span><span class="o"><</span><span class="w"> </span><span class="mi">20</span><span class="p">;</span><span class="w"> </span><span class="c1">-- Rows where string is NULL will not be returned.</span> </pre></div> <p>This is complicated by the fact that in some database interface programs (or even database implementations like Oracle's), NULL is reported as an empty string, and empty strings may be incorrectly stored as NULL. </p> <div class="mw-heading mw-heading3"><h3 id="Criticisms">Criticisms</h3><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=30" title="Edit section: Criticisms"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <p>The ISO SQL implementation of Null is the subject of criticism, debate and calls for change. In <i>The Relational Model for Database Management: Version 2</i>, Codd suggested that the SQL implementation of Null was flawed and should be replaced by two distinct Null-type markers. The markers he proposed were to stand for <i>"Missing but Applicable"</i> and <i>"Missing but Inapplicable"</i>, known as <i>A-values</i> and <i>I-values</i>, respectively. Codd's recommendation, if accepted, would have required the implementation of a four-valued logic in SQL.<sup id="cite_ref-isbn0201141922_5-1" class="reference"><a href="#cite_note-isbn0201141922-5"><span class="cite-bracket">[</span>5<span class="cite-bracket">]</span></a></sup> Others have suggested adding additional Null-type markers to Codd's recommendation to indicate even more reasons that a data value might be "Missing", increasing the complexity of SQL's logic system. At various times, proposals have also been put forth to implement multiple user-defined Null markers in SQL. Because of the complexity of the Null-handling and logic systems required to support multiple Null markers, none of these proposals have gained widespread acceptance. </p><p><a href="/wiki/Chris_Date" class="mw-redirect" title="Chris Date">Chris Date</a> and <a href="/wiki/Hugh_Darwen" title="Hugh Darwen">Hugh Darwen</a>, authors of <i>The Third Manifesto</i>, have suggested that the SQL Null implementation is inherently flawed and should be eliminated altogether,<sup id="cite_ref-3rdmanifesto_34-0" class="reference"><a href="#cite_note-3rdmanifesto-34"><span class="cite-bracket">[</span>34<span class="cite-bracket">]</span></a></sup> pointing to inconsistencies and flaws in the implementation of SQL Null-handling (particularly in aggregate functions) as proof that the entire concept of Null is flawed and should be removed from the relational model.<sup id="cite_ref-askew-wall_35-0" class="reference"><a href="#cite_note-askew-wall-35"><span class="cite-bracket">[</span>35<span class="cite-bracket">]</span></a></sup> Others, like author <a href="/wiki/Fabian_Pascal" title="Fabian Pascal">Fabian Pascal</a>, have stated a belief that "how the function calculation should treat missing values is not governed by the relational model."<sup class="noprint Inline-Template Template-Fact" style="white-space:nowrap;">[<i><a href="/wiki/Wikipedia:Citation_needed" title="Wikipedia:Citation needed"><span title="This claim needs references to reliable sources. (November 2012)">citation needed</span></a></i>]</sup> </p> <div class="mw-heading mw-heading3"><h3 id="Closed-world_assumption">Closed-world assumption</h3><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=31" title="Edit section: Closed-world assumption"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <p>Another point of conflict concerning Nulls is that they violate the <a href="/wiki/Closed-world_assumption" title="Closed-world assumption">closed-world assumption</a> model of relational databases by introducing an <a href="/wiki/Open-world_assumption" class="mw-redirect" title="Open-world assumption">open-world assumption</a> into it.<sup id="cite_ref-isbn0596100124_36-0" class="reference"><a href="#cite_note-isbn0596100124-36"><span class="cite-bracket">[</span>36<span class="cite-bracket">]</span></a></sup> The closed world assumption, as it pertains to databases, states that "Everything stated by the database, either explicitly or implicitly, is true; everything else is false."<sup id="cite_ref-cwa_37-0" class="reference"><a href="#cite_note-cwa-37"><span class="cite-bracket">[</span>37<span class="cite-bracket">]</span></a></sup> This view assumes that the knowledge of the world stored within a database is complete. Nulls, however, operate under the open world assumption, in which some items stored in the database are considered unknown, making the database's stored knowledge of the world incomplete. </p> <div class="mw-heading mw-heading2"><h2 id="See_also">See also</h2><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=32" title="Edit section: See also"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <style data-mw-deduplicate="TemplateStyles:r1266661725">.mw-parser-output .portalbox{padding:0;margin:0.5em 0;display:table;box-sizing:border-box;max-width:175px;list-style:none}.mw-parser-output .portalborder{border:1px solid var(--border-color-base,#a2a9b1);padding:0.1em;background:var(--background-color-neutral-subtle,#f8f9fa)}.mw-parser-output .portalbox-entry{display:table-row;font-size:85%;line-height:110%;height:1.9em;font-style:italic;font-weight:bold}.mw-parser-output .portalbox-image{display:table-cell;padding:0.2em;vertical-align:middle;text-align:center}.mw-parser-output .portalbox-link{display:table-cell;padding:0.2em 0.2em 0.2em 0.3em;vertical-align:middle}@media(min-width:720px){.mw-parser-output .portalleft{margin:0.5em 1em 0.5em 0}.mw-parser-output .portalright{clear:right;float:right;margin:0.5em 0 0.5em 1em}}</style><ul role="navigation" aria-label="Portals" class="noprint portalbox portalborder portalright"> <li class="portalbox-entry"><span class="portalbox-image"><span class="noviewer" typeof="mw:File"><a href="/wiki/File:Octicons-terminal.svg" class="mw-file-description"><img alt="icon" src="//upload.wikimedia.org/wikipedia/commons/thumb/6/6f/Octicons-terminal.svg/24px-Octicons-terminal.svg.png" decoding="async" width="24" height="28" class="mw-file-element" srcset="//upload.wikimedia.org/wikipedia/commons/thumb/6/6f/Octicons-terminal.svg/37px-Octicons-terminal.svg.png 1.5x, //upload.wikimedia.org/wikipedia/commons/thumb/6/6f/Octicons-terminal.svg/49px-Octicons-terminal.svg.png 2x" data-file-width="896" data-file-height="1024" /></a></span></span><span class="portalbox-link"><a href="/wiki/Portal:Computer_programming" title="Portal:Computer programming">Computer programming portal</a></span></li></ul> <ul><li><a href="/wiki/SQL" title="SQL">SQL</a></li> <li><a href="https://en.wikibooks.org/wiki/Structured_Query_Language/NULLs_and_the_Three_Valued_Logic" class="extiw" title="b:Structured Query Language/NULLs and the Three Valued Logic">NULLs in: Wikibook SQL</a></li> <li><a href="/wiki/Three-valued_logic" title="Three-valued logic">Three-valued logic</a></li> <li><a href="/wiki/Data_manipulation_language" title="Data manipulation language">Data manipulation language</a></li> <li><a href="/wiki/Codd%27s_12_rules" title="Codd's 12 rules">Codd's 12 rules</a></li> <li><a href="/wiki/Check_constraint" title="Check constraint">Check constraint</a></li> <li><a href="/wiki/Relational_Model/Tasmania" title="Relational Model/Tasmania">Relational Model/Tasmania</a></li> <li><a href="/wiki/RDBMS" class="mw-redirect" title="RDBMS">Relational database management system</a></li> <li><a href="/wiki/Join_(SQL)" title="Join (SQL)">Join (SQL)</a></li></ul> <div class="mw-heading mw-heading2"><h2 id="References">References</h2><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=33" title="Edit section: References"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <style data-mw-deduplicate="TemplateStyles:r1239543626">.mw-parser-output .reflist{margin-bottom:0.5em;list-style-type:decimal}@media screen{.mw-parser-output .reflist{font-size:90%}}.mw-parser-output .reflist .references{font-size:100%;margin-bottom:0;list-style-type:inherit}.mw-parser-output .reflist-columns-2{column-width:30em}.mw-parser-output .reflist-columns-3{column-width:25em}.mw-parser-output .reflist-columns{margin-top:0.3em}.mw-parser-output .reflist-columns ol{margin-top:0}.mw-parser-output .reflist-columns li{page-break-inside:avoid;break-inside:avoid-column}.mw-parser-output .reflist-upper-alpha{list-style-type:upper-alpha}.mw-parser-output .reflist-upper-roman{list-style-type:upper-roman}.mw-parser-output .reflist-lower-alpha{list-style-type:lower-alpha}.mw-parser-output .reflist-lower-greek{list-style-type:lower-greek}.mw-parser-output .reflist-lower-roman{list-style-type:lower-roman}</style><div class="reflist reflist-columns references-column-width" style="column-width: 45em;"> <ol class="references"> <li id="cite_note-Meyden-1"><span class="mw-cite-backlink">^ <a href="#cite_ref-Meyden_1-0"><sup><i><b>a</b></i></sup></a> <a href="#cite_ref-Meyden_1-1"><sup><i><b>b</b></i></sup></a> <a href="#cite_ref-Meyden_1-2"><sup><i><b>c</b></i></sup></a> <a href="#cite_ref-Meyden_1-3"><sup><i><b>d</b></i></sup></a></span> <span class="reference-text">Ron van der Meyden, "<a rel="nofollow" class="external text" href="https://books.google.com/books?id=gF0b85IuqQwC&pg=PA344">Logical approaches to incomplete information: a survey</a>" in Chomicki, Jan; Saake, Gunter (Eds.) <i>Logics for Databases and Information Systems</i>, Kluwer Academic Publishers <style data-mw-deduplicate="TemplateStyles:r1238218222">.mw-parser-output cite.citation{font-style:inherit;word-wrap:break-word}.mw-parser-output .citation q{quotes:"\"""\"""'""'"}.mw-parser-output .citation:target{background-color:rgba(0,127,255,0.133)}.mw-parser-output .id-lock-free.id-lock-free a{background:url("//upload.wikimedia.org/wikipedia/commons/6/65/Lock-green.svg")right 0.1em center/9px no-repeat}.mw-parser-output .id-lock-limited.id-lock-limited a,.mw-parser-output .id-lock-registration.id-lock-registration a{background:url("//upload.wikimedia.org/wikipedia/commons/d/d6/Lock-gray-alt-2.svg")right 0.1em center/9px no-repeat}.mw-parser-output .id-lock-subscription.id-lock-subscription a{background:url("//upload.wikimedia.org/wikipedia/commons/a/aa/Lock-red-alt-2.svg")right 0.1em center/9px no-repeat}.mw-parser-output .cs1-ws-icon a{background:url("//upload.wikimedia.org/wikipedia/commons/4/4c/Wikisource-logo.svg")right 0.1em center/12px no-repeat}body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-free a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-limited a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-registration a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-subscription a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .cs1-ws-icon a{background-size:contain;padding:0 1em 0 0}.mw-parser-output .cs1-code{color:inherit;background:inherit;border:none;padding:inherit}.mw-parser-output .cs1-hidden-error{display:none;color:var(--color-error,#d33)}.mw-parser-output .cs1-visible-error{color:var(--color-error,#d33)}.mw-parser-output .cs1-maint{display:none;color:#085;margin-left:0.3em}.mw-parser-output .cs1-kern-left{padding-left:0.2em}.mw-parser-output .cs1-kern-right{padding-right:0.2em}.mw-parser-output .citation .mw-selflink{font-weight:inherit}@media screen{.mw-parser-output .cs1-format{font-size:95%}html.skin-theme-clientpref-night .mw-parser-output .cs1-maint{color:#18911f}}@media screen and (prefers-color-scheme:dark){html.skin-theme-clientpref-os .mw-parser-output .cs1-maint{color:#18911f}}</style><a href="/wiki/ISBN_(identifier)" class="mw-redirect" title="ISBN (identifier)">ISBN</a> <a href="/wiki/Special:BookSources/978-0-7923-8129-7" title="Special:BookSources/978-0-7923-8129-7">978-0-7923-8129-7</a>, p. 344; <a rel="nofollow" class="external text" href="http://www.cse.unsw.edu.au/~meyden/research/indef-review.ps">PS preprint</a> (note: page numbering differs in preprint from the published version)</span> </li> <li id="cite_note-2"><span class="mw-cite-backlink"><b><a href="#cite_ref-2">^</a></b></span> <span class="reference-text"><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite id="CITEREFCodd1985" class="citation journal cs1">Codd, E.F. (October 14, 1985). "Is Your Database Really Relational?". <i><a href="/wiki/Computerworld" title="Computerworld">Computerworld</a></i>.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Ajournal&rft.genre=article&rft.jtitle=Computerworld&rft.atitle=Is+Your+Database+Really+Relational%3F&rft.date=1985-10-14&rft.aulast=Codd&rft.aufirst=E.F.&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span></span> </li> <li id="cite_note-3"><span class="mw-cite-backlink"><b><a href="#cite_ref-3">^</a></b></span> <span class="reference-text"><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite id="CITEREFCodd1985" class="citation journal cs1">Codd, E.F. (October 21, 1985). "Does Your DBMS Run By The Rules?". <i><a href="/wiki/Computerworld" title="Computerworld">Computerworld</a></i>.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Ajournal&rft.genre=article&rft.jtitle=Computerworld&rft.atitle=Does+Your+DBMS+Run+By+The+Rules%3F&rft.date=1985-10-21&rft.aulast=Codd&rft.aufirst=E.F.&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span></span> </li> <li id="cite_note-Chamberlin1998-4"><span class="mw-cite-backlink">^ <a href="#cite_ref-Chamberlin1998_4-0"><sup><i><b>a</b></i></sup></a> <a href="#cite_ref-Chamberlin1998_4-1"><sup><i><b>b</b></i></sup></a></span> <span class="reference-text"><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite id="CITEREFDon_Chamberlin1998" class="citation book cs1">Don Chamberlin (1998). <a rel="nofollow" class="external text" href="https://books.google.com/books?id=hb4zskzHrWYC&pg=PA28"><i>A Complete Guide to DB2 Universal Database</i></a>. Morgan Kaufmann. pp. <span class="nowrap">28–</span>32. <a href="/wiki/ISBN_(identifier)" class="mw-redirect" title="ISBN (identifier)">ISBN</a> <a href="/wiki/Special:BookSources/978-1-55860-482-7" title="Special:BookSources/978-1-55860-482-7"><bdi>978-1-55860-482-7</bdi></a>.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Abook&rft.genre=book&rft.btitle=A+Complete+Guide+to+DB2+Universal+Database&rft.pages=%3Cspan+class%3D%22nowrap%22%3E28-%3C%2Fspan%3E32&rft.pub=Morgan+Kaufmann&rft.date=1998&rft.isbn=978-1-55860-482-7&rft.au=Don+Chamberlin&rft_id=https%3A%2F%2Fbooks.google.com%2Fbooks%3Fid%3Dhb4zskzHrWYC%26pg%3DPA28&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span></span> </li> <li id="cite_note-isbn0201141922-5"><span class="mw-cite-backlink">^ <a href="#cite_ref-isbn0201141922_5-0"><sup><i><b>a</b></i></sup></a> <a href="#cite_ref-isbn0201141922_5-1"><sup><i><b>b</b></i></sup></a></span> <span class="reference-text"><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite id="CITEREFCodd1990" class="citation book cs1">Codd, E.F. (1990). <i>The Relational Model for Database Management</i> (Version 2 ed.). <a href="/wiki/Pearson_PLC" class="mw-redirect" title="Pearson PLC">Addison Wesley Publishing Company</a>. <a href="/wiki/ISBN_(identifier)" class="mw-redirect" title="ISBN (identifier)">ISBN</a> <a href="/wiki/Special:BookSources/978-0-201-14192-4" title="Special:BookSources/978-0-201-14192-4"><bdi>978-0-201-14192-4</bdi></a>.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Abook&rft.genre=book&rft.btitle=The+Relational+Model+for+Database+Management&rft.edition=Version+2&rft.pub=Addison+Wesley+Publishing+Company&rft.date=1990&rft.isbn=978-0-201-14192-4&rft.aulast=Codd&rft.aufirst=E.F.&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span></span> </li> <li id="cite_note-SQL2003-Part2-Sec626-6"><span class="mw-cite-backlink">^ <a href="#cite_ref-SQL2003-Part2-Sec626_6-0"><sup><i><b>a</b></i></sup></a> <a href="#cite_ref-SQL2003-Part2-Sec626_6-1"><sup><i><b>b</b></i></sup></a></span> <span class="reference-text"><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite id="CITEREFISO/IEC2003" class="citation book cs1">ISO/IEC (2003). <i>ISO/IEC 9075-2:2003, "SQL/Foundation"</i>. ISO/IEC. Section 6.2.6: <i>numeric value expressions</i>.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Abook&rft.genre=book&rft.btitle=ISO%2FIEC+9075-2%3A2003%2C+%22SQL%2FFoundation%22&rft.pages=Section+6.2.6%3A+%27%27numeric+value+expressions%27%27&rft.pub=ISO%2FIEC&rft.date=2003&rft.au=ISO%2FIEC&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span>.</span> </li> <li id="cite_note-SQL2003-Part2-Sec628-7"><span class="mw-cite-backlink"><b><a href="#cite_ref-SQL2003-Part2-Sec628_7-0">^</a></b></span> <span class="reference-text"> <link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite id="CITEREFISO/IEC2003" class="citation book cs1">ISO/IEC (2003). <i>ISO/IEC 9075-2:2003, "SQL/Foundation"</i>. ISO/IEC. Section 6.2.8: <i>string value expression</i>.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Abook&rft.genre=book&rft.btitle=ISO%2FIEC+9075-2%3A2003%2C+%22SQL%2FFoundation%22&rft.pages=Section+6.2.8%3A+%27%27string+value+expression%27%27&rft.pub=ISO%2FIEC&rft.date=2003&rft.au=ISO%2FIEC&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span></span> </li> <li id="cite_note-8"><span class="mw-cite-backlink"><b><a href="#cite_ref-8">^</a></b></span> <span class="reference-text"><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite class="citation web cs1"><a rel="nofollow" class="external text" href="https://aws.amazon.com/blogs/database/handle-empty-strings-when-migrating-from-oracle-to-postgresql/">"Handle empty strings when migrating from Oracle to PostgreSQL | AWS Database Blog"</a>. <i>aws.amazon.com</i>. 2022-05-23<span class="reference-accessdate">. Retrieved <span class="nowrap">2023-12-30</span></span>.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Ajournal&rft.genre=unknown&rft.jtitle=aws.amazon.com&rft.atitle=Handle+empty+strings+when+migrating+from+Oracle+to+PostgreSQL+%7C+AWS+Database+Blog&rft.date=2022-05-23&rft_id=https%3A%2F%2Faws.amazon.com%2Fblogs%2Fdatabase%2Fhandle-empty-strings-when-migrating-from-oracle-to-postgresql%2F&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span></span> </li> <li id="cite_note-SQL2003-Part1-Sec442-9"><span class="mw-cite-backlink"><b><a href="#cite_ref-SQL2003-Part1-Sec442_9-0">^</a></b></span> <span class="reference-text"> <link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite id="CITEREFISO/IEC2003" class="citation book cs1">ISO/IEC (2003). <a rel="nofollow" class="external text" href="http://www.iso.org"><i>ISO/IEC 9075-1:2003, "SQL/Framework"</i></a>. ISO/IEC. Section 4.4.2: <i>The null value</i>.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Abook&rft.genre=book&rft.btitle=ISO%2FIEC+9075-1%3A2003%2C+%22SQL%2FFramework%22&rft.pages=Section+4.4.2%3A+%27%27The+null+value%27%27&rft.pub=ISO%2FIEC&rft.date=2003&rft.au=ISO%2FIEC&rft_id=http%3A%2F%2Fwww.iso.org&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span></span> </li> <li id="cite_note-fourrules-10"><span class="mw-cite-backlink">^ <a href="#cite_ref-fourrules_10-0"><sup><i><b>a</b></i></sup></a> <a href="#cite_ref-fourrules_10-1"><sup><i><b>b</b></i></sup></a></span> <span class="reference-text"><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite id="CITEREFColes,_Michael2005" class="citation journal cs1">Coles, Michael (June 27, 2005). <a rel="nofollow" class="external text" href="http://www.sqlservercentral.com/columnists/mcoles/fourrulesfornulls.asp">"Four Rules for Nulls"</a>. <i>SQL Server Central</i>. Red Gate Software.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Ajournal&rft.genre=article&rft.jtitle=SQL+Server+Central&rft.atitle=Four+Rules+for+Nulls&rft.date=2005-06-27&rft.au=Coles%2C+Michael&rft_id=http%3A%2F%2Fwww.sqlservercentral.com%2Fcolumnists%2Fmcoles%2Ffourrulesfornulls.asp&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span></span> </li> <li id="cite_note-Klein-11"><span class="mw-cite-backlink">^ <a href="#cite_ref-Klein_11-0"><sup><i><b>a</b></i></sup></a> <a href="#cite_ref-Klein_11-1"><sup><i><b>b</b></i></sup></a></span> <span class="reference-text"><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite id="CITEREFHans-Joachim2003" class="citation book cs1">Hans-Joachim, K. (2003). <a rel="nofollow" class="external text" href="http://www.is.informatik.uni-kiel.de/~hjk/sqlni.ps">"Null Values in Relational Databases and Sure Information Answers"</a>. <i>Semantics in Databases. Second International Workshop Dagstuhl Castle, Germany, January 7–12, 2001. Revised Papers</i>. Lecture Notes in Computer Science. Vol. 2582. pp. <span class="nowrap">119–</span>138. <a href="/wiki/Doi_(identifier)" class="mw-redirect" title="Doi (identifier)">doi</a>:<a rel="nofollow" class="external text" href="https://doi.org/10.1007%2F3-540-36596-6_7">10.1007/3-540-36596-6_7</a>. <a href="/wiki/ISBN_(identifier)" class="mw-redirect" title="ISBN (identifier)">ISBN</a> <a href="/wiki/Special:BookSources/978-3-540-00957-3" title="Special:BookSources/978-3-540-00957-3"><bdi>978-3-540-00957-3</bdi></a>.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Abook&rft.genre=bookitem&rft.atitle=Null+Values+in+Relational+Databases+and+Sure+Information+Answers&rft.btitle=Semantics+in+Databases.+Second+International+Workshop+Dagstuhl+Castle%2C+Germany%2C+January+7%E2%80%9312%2C+2001.+Revised+Papers&rft.series=Lecture+Notes+in+Computer+Science&rft.pages=%3Cspan+class%3D%22nowrap%22%3E119-%3C%2Fspan%3E138&rft.date=2003&rft_id=info%3Adoi%2F10.1007%2F3-540-36596-6_7&rft.isbn=978-3-540-00957-3&rft.aulast=Hans-Joachim&rft.aufirst=K.&rft_id=http%3A%2F%2Fwww.is.informatik.uni-kiel.de%2F~hjk%2Fsqlni.ps&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span></span> </li> <li id="cite_note-SQL2003-Part2-Sec87-12"><span class="mw-cite-backlink"><b><a href="#cite_ref-SQL2003-Part2-Sec87_12-0">^</a></b></span> <span class="reference-text"> <link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite id="CITEREFISO/IEC2003" class="citation book cs1">ISO/IEC (2003). <i>ISO/IEC 9075-2:2003, "SQL/Foundation"</i>. ISO/IEC. Section 8.7: <i>null predicate</i>.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Abook&rft.genre=book&rft.btitle=ISO%2FIEC+9075-2%3A2003%2C+%22SQL%2FFoundation%22&rft.pages=Section+8.7%3A+%27%27null+predicate%27%27&rft.pub=ISO%2FIEC&rft.date=2003&rft.au=ISO%2FIEC&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span></span> </li> <li id="cite_note-13"><span class="mw-cite-backlink"><b><a href="#cite_ref-13">^</a></b></span> <span class="reference-text">C.J. Date (2004), <i>An introduction to database systems</i>, 8th ed., Pearson Education, p. 594</span> </li> <li id="cite_note-MeltonSimon1993-14"><span class="mw-cite-backlink"><b><a href="#cite_ref-MeltonSimon1993_14-0">^</a></b></span> <span class="reference-text"><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite id="CITEREFJim_MeltonJim_Melton_Alan_R._Simon1993" class="citation book cs1">Jim Melton; Jim Melton Alan R. Simon (1993). <a rel="nofollow" class="external text" href="https://books.google.com/books?id=ZOOMSTZ4T_QC&pg=PA145"><i>Understanding The New SQL: A Complete Guide</i></a>. Morgan Kaufmann. pp. <span class="nowrap">145–</span>147. <a href="/wiki/ISBN_(identifier)" class="mw-redirect" title="ISBN (identifier)">ISBN</a> <a href="/wiki/Special:BookSources/978-1-55860-245-8" title="Special:BookSources/978-1-55860-245-8"><bdi>978-1-55860-245-8</bdi></a>.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Abook&rft.genre=book&rft.btitle=Understanding+The+New+SQL%3A+A+Complete+Guide&rft.pages=%3Cspan+class%3D%22nowrap%22%3E145-%3C%2Fspan%3E147&rft.pub=Morgan+Kaufmann&rft.date=1993&rft.isbn=978-1-55860-245-8&rft.au=Jim+Melton&rft.au=Jim+Melton+Alan+R.+Simon&rft_id=https%3A%2F%2Fbooks.google.com%2Fbooks%3Fid%3DZOOMSTZ4T_QC%26pg%3DPA145&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span></span> </li> <li id="cite_note-15"><span class="mw-cite-backlink"><b><a href="#cite_ref-15">^</a></b></span> <span class="reference-text">C. J. Date, <i>Relational database writings, 1991-1994</i>, Addison-Wesley, 1995, p. 371</span> </li> <li id="cite_note-16"><span class="mw-cite-backlink"><b><a href="#cite_ref-16">^</a></b></span> <span class="reference-text">C.J. Date (2004), <i>An introduction to database systems</i>, 8th ed., Pearson Education, p. 584</span> </li> <li id="cite_note-JACM_1984-17"><span class="mw-cite-backlink"><b><a href="#cite_ref-JACM_1984_17-0">^</a></b></span> <span class="reference-text"><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite id="CITEREFImielińskiLipski_Jr.1984" class="citation journal cs1"><a href="/wiki/Tomasz_Imieli%C5%84ski" title="Tomasz Imieliński">Imieliński, T.</a>; Lipski Jr., W. (1984). <a rel="nofollow" class="external text" href="https://doi.org/10.1145%2F1634.1886">"Incomplete information in relational databases"</a>. <i><a href="/wiki/Journal_of_the_ACM" title="Journal of the ACM">Journal of the ACM</a></i>. <b>31</b> (4): <span class="nowrap">761–</span>791. <a href="/wiki/Doi_(identifier)" class="mw-redirect" title="Doi (identifier)">doi</a>:<span class="id-lock-free" title="Freely accessible"><a rel="nofollow" class="external text" href="https://doi.org/10.1145%2F1634.1886">10.1145/1634.1886</a></span>. <a href="/wiki/S2CID_(identifier)" class="mw-redirect" title="S2CID (identifier)">S2CID</a> <a rel="nofollow" class="external text" href="https://api.semanticscholar.org/CorpusID:288040">288040</a>.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Ajournal&rft.genre=article&rft.jtitle=Journal+of+the+ACM&rft.atitle=Incomplete+information+in+relational+databases&rft.volume=31&rft.issue=4&rft.pages=%3Cspan+class%3D%22nowrap%22%3E761-%3C%2Fspan%3E791&rft.date=1984&rft_id=info%3Adoi%2F10.1145%2F1634.1886&rft_id=https%3A%2F%2Fapi.semanticscholar.org%2FCorpusID%3A288040%23id-name%3DS2CID&rft.aulast=Imieli%C5%84ski&rft.aufirst=T.&rft.au=Lipski+Jr.%2C+W.&rft_id=https%3A%2F%2Fdoi.org%2F10.1145%252F1634.1886&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span></span> </li> <li id="cite_note-18"><span class="mw-cite-backlink"><b><a href="#cite_ref-18">^</a></b></span> <span class="reference-text"><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite id="CITEREFAbiteboulHullVianu1995" class="citation book cs1"><a href="/wiki/Serge_Abiteboul" title="Serge Abiteboul">Abiteboul, Serge</a>; <a href="/w/index.php?title=Richard_B._Hull&action=edit&redlink=1" class="new" title="Richard B. Hull (page does not exist)">Hull, Richard B.</a>; <a href="/wiki/Victor_Vianu" title="Victor Vianu">Vianu, Victor</a> (1995). <span class="id-lock-registration" title="Free registration required"><a rel="nofollow" class="external text" href="https://archive.org/details/foundationsofdat0000abit"><i>Foundations of Databases</i></a></span>. Addison-Wesley. <a href="/wiki/ISBN_(identifier)" class="mw-redirect" title="ISBN (identifier)">ISBN</a> <a href="/wiki/Special:BookSources/978-0-201-53771-0" title="Special:BookSources/978-0-201-53771-0"><bdi>978-0-201-53771-0</bdi></a>.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Abook&rft.genre=book&rft.btitle=Foundations+of+Databases&rft.pub=Addison-Wesley&rft.date=1995&rft.isbn=978-0-201-53771-0&rft.aulast=Abiteboul&rft.aufirst=Serge&rft.au=Hull%2C+Richard+B.&rft.au=Vianu%2C+Victor&rft_id=https%3A%2F%2Farchive.org%2Fdetails%2Ffoundationsofdat0000abit&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span></span> </li> <li id="cite_note-nullversusnull-19"><span class="mw-cite-backlink">^ <a href="#cite_ref-nullversusnull_19-0"><sup><i><b>a</b></i></sup></a> <a href="#cite_ref-nullversusnull_19-1"><sup><i><b>b</b></i></sup></a></span> <span class="reference-text"><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite id="CITEREFColes,_Michael2007" class="citation journal cs1">Coles, Michael (February 26, 2007). <a rel="nofollow" class="external text" href="http://www.sqlservercentral.com/columnists/mcoles/2829.asp">"Null Versus Null?"</a>. <i>SQL Server Central</i>. Red Gate Software.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Ajournal&rft.genre=article&rft.jtitle=SQL+Server+Central&rft.atitle=Null+Versus+Null%3F&rft.date=2007-02-26&rft.au=Coles%2C+Michael&rft_id=http%3A%2F%2Fwww.sqlservercentral.com%2Fcolumnists%2Fmcoles%2F2829.asp&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span></span> </li> <li id="cite_note-SQL2003-Part2-Sec4154-20"><span class="mw-cite-backlink"><b><a href="#cite_ref-SQL2003-Part2-Sec4154_20-0">^</a></b></span> <span class="reference-text"> <link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite id="CITEREFISO/IEC2003" class="citation book cs1">ISO/IEC (2003). <i>ISO/IEC 9075-2:2003, "SQL/Foundation"</i>. ISO/IEC. Section 4.15.4: <i>Aggregate functions</i>.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Abook&rft.genre=book&rft.btitle=ISO%2FIEC+9075-2%3A2003%2C+%22SQL%2FFoundation%22&rft.pages=Section+4.15.4%3A+%27%27Aggregate+functions%27%27&rft.pub=ISO%2FIEC&rft.date=2003&rft.au=ISO%2FIEC&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span></span> </li> <li id="cite_note-SQL2003-Part2-Sec3168-21"><span class="mw-cite-backlink"><b><a href="#cite_ref-SQL2003-Part2-Sec3168_21-0">^</a></b></span> <span class="reference-text"><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite id="CITEREFISO/IEC2003" class="citation book cs1">ISO/IEC (2003). <i>ISO/IEC 9075-2:2003, "SQL/Foundation"</i>. ISO/IEC. Section 3.1.6.8: <i>Definitions: distinct</i>.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Abook&rft.genre=book&rft.btitle=ISO%2FIEC+9075-2%3A2003%2C+%22SQL%2FFoundation%22&rft.pages=Section+3.1.6.8%3A+%27%27Definitions%3A+distinct%27%27&rft.pub=ISO%2FIEC&rft.date=2003&rft.au=ISO%2FIEC&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span></span> </li> <li id="cite_note-22"><span class="mw-cite-backlink"><b><a href="#cite_ref-22">^</a></b></span> <span class="reference-text"><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite class="citation web cs1"><a rel="nofollow" class="external text" href="http://www.postgresql.org/docs/8.0/interactive/indexes-types.html">"PostgreSQL 8.0.14 Documentation: Index Types"</a>. PostgreSQL<span class="reference-accessdate">. Retrieved <span class="nowrap">6 November</span> 2008</span>.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Abook&rft.genre=unknown&rft.btitle=PostgreSQL+8.0.14+Documentation%3A+Index+Types&rft.pub=PostgreSQL&rft_id=http%3A%2F%2Fwww.postgresql.org%2Fdocs%2F8.0%2Finteractive%2Findexes-types.html&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span></span> </li> <li id="cite_note-23"><span class="mw-cite-backlink"><b><a href="#cite_ref-23">^</a></b></span> <span class="reference-text"><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite class="citation web cs1"><a rel="nofollow" class="external text" href="http://www.postgresql.org/docs/8.0/interactive/indexes-unique.html">"PostgreSQL 8.0.14 Documentation: Unique Indexes"</a>. PostgreSQL<span class="reference-accessdate">. Retrieved <span class="nowrap">November 6,</span> 2008</span>.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Abook&rft.genre=unknown&rft.btitle=PostgreSQL+8.0.14+Documentation%3A+Unique+Indexes&rft.pub=PostgreSQL&rft_id=http%3A%2F%2Fwww.postgresql.org%2Fdocs%2F8.0%2Finteractive%2Findexes-unique.html&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span></span> </li> <li id="cite_note-24"><span class="mw-cite-backlink"><b><a href="#cite_ref-24">^</a></b></span> <span class="reference-text"><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite class="citation web cs1"><a rel="nofollow" class="external text" href="http://msdn2.microsoft.com/en-us/library/ms175132.aspx">"Creating Unique Indexes"</a>. PostfreSQL. September 2007<span class="reference-accessdate">. Retrieved <span class="nowrap">November 6,</span> 2008</span>.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Abook&rft.genre=unknown&rft.btitle=Creating+Unique+Indexes&rft.pub=PostfreSQL&rft.date=2007-09&rft_id=http%3A%2F%2Fmsdn2.microsoft.com%2Fen-us%2Flibrary%2Fms175132.aspx&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span></span> </li> <li id="cite_note-SQL2003-Part2-Sec611-25"><span class="mw-cite-backlink"><b><a href="#cite_ref-SQL2003-Part2-Sec611_25-0">^</a></b></span> <span class="reference-text"> <link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite id="CITEREFISO/IEC2003" class="citation book cs1">ISO/IEC (2003). <i>ISO/IEC 9075-2:2003, "SQL/Foundation"</i>. ISO/IEC. Section 6.11: <i>case expression</i>.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Abook&rft.genre=book&rft.btitle=ISO%2FIEC+9075-2%3A2003%2C+%22SQL%2FFoundation%22&rft.pages=Section+6.11%3A+%27%27case+expression%27%27&rft.pub=ISO%2FIEC&rft.date=2003&rft.au=ISO%2FIEC&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span></span> </li> <li id="cite_note-Understanding1999-26"><span class="mw-cite-backlink"><b><a href="#cite_ref-Understanding1999_26-0">^</a></b></span> <span class="reference-text"><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite id="CITEREFJim_MeltonAlan_R._Simon2002" class="citation book cs1"><a href="/w/index.php?title=Jim_Melton&action=edit&redlink=1" class="new" title="Jim Melton (page does not exist)">Jim Melton</a>; Alan R. Simon (2002). <a rel="nofollow" class="external text" href="https://archive.org/details/sql1999understan0000melt/page/53"><i>SQL:1999: Understanding Relational Language Components</i></a>. Morgan Kaufmann. p. <a rel="nofollow" class="external text" href="https://archive.org/details/sql1999understan0000melt/page/53">53</a>. <a href="/wiki/ISBN_(identifier)" class="mw-redirect" title="ISBN (identifier)">ISBN</a> <a href="/wiki/Special:BookSources/978-1-55860-456-8" title="Special:BookSources/978-1-55860-456-8"><bdi>978-1-55860-456-8</bdi></a>.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Abook&rft.genre=book&rft.btitle=SQL%3A1999%3A+Understanding+Relational+Language+Components&rft.pages=53&rft.pub=Morgan+Kaufmann&rft.date=2002&rft.isbn=978-1-55860-456-8&rft.au=Jim+Melton&rft.au=Alan+R.+Simon&rft_id=https%3A%2F%2Farchive.org%2Fdetails%2Fsql1999understan0000melt%2Fpage%2F53&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span></span> </li> <li id="cite_note-ISO-9075-1-27"><span class="mw-cite-backlink"><b><a href="#cite_ref-ISO-9075-1_27-0">^</a></b></span> <span class="reference-text"><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite class="citation web cs1">"ISO/IEC 9075-1:1999 SQL Standard". ISO. 1999.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Abook&rft.genre=unknown&rft.btitle=ISO%2FIEC+9075-1%3A1999+SQL+Standard&rft.pub=ISO&rft.date=1999&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span> <span class="cs1-visible-error citation-comment"><code class="cs1-code">{{<a href="/wiki/Template:Cite_web" title="Template:Cite web">cite web</a>}}</code>: </span><span class="cs1-visible-error citation-comment">Missing or empty <code class="cs1-code">|url=</code> (<a href="/wiki/Help:CS1_errors#cite_web_url" title="Help:CS1 errors">help</a>)</span></span> </li> <li id="cite_note-Date2011-28"><span class="mw-cite-backlink"><b><a href="#cite_ref-Date2011_28-0">^</a></b></span> <span class="reference-text"><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite id="CITEREFC._Date2011" class="citation book cs1">C. Date (2011). <a rel="nofollow" class="external text" href="https://books.google.com/books?id=Ew06OZtjuJEC&pg=PA83"><i>SQL and Relational Theory: How to Write Accurate SQL Code</i></a>. O'Reilly Media, Inc. p. 83. <a href="/wiki/ISBN_(identifier)" class="mw-redirect" title="ISBN (identifier)">ISBN</a> <a href="/wiki/Special:BookSources/978-1-4493-1640-2" title="Special:BookSources/978-1-4493-1640-2"><bdi>978-1-4493-1640-2</bdi></a>.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Abook&rft.genre=book&rft.btitle=SQL+and+Relational+Theory%3A+How+to+Write+Accurate+SQL+Code&rft.pages=83&rft.pub=O%27Reilly+Media%2C+Inc.&rft.date=2011&rft.isbn=978-1-4493-1640-2&rft.au=C.+Date&rft_id=https%3A%2F%2Fbooks.google.com%2Fbooks%3Fid%3DEw06OZtjuJEC%26pg%3DPA83&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span></span> </li> <li id="cite_note-29"><span class="mw-cite-backlink"><b><a href="#cite_ref-29">^</a></b></span> <span class="reference-text">ISO/IEC 9075-2:2011 §4.5</span> </li> <li id="cite_note-Prigmore2007-30"><span class="mw-cite-backlink"><b><a href="#cite_ref-Prigmore2007_30-0">^</a></b></span> <span class="reference-text"><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite id="CITEREFMartyn_Prigmore2007" class="citation book cs1">Martyn Prigmore (2007). <a rel="nofollow" class="external text" href="https://books.google.com/books?id=PKggKqIZnN0C&pg=PA197"><i>Introduction to Databases With Web Applications</i></a>. Pearson Education Canada. p. 197. <a href="/wiki/ISBN_(identifier)" class="mw-redirect" title="ISBN (identifier)">ISBN</a> <a href="/wiki/Special:BookSources/978-0-321-26359-9" title="Special:BookSources/978-0-321-26359-9"><bdi>978-0-321-26359-9</bdi></a>.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Abook&rft.genre=book&rft.btitle=Introduction+to+Databases+With+Web+Applications&rft.pages=197&rft.pub=Pearson+Education+Canada&rft.date=2007&rft.isbn=978-0-321-26359-9&rft.au=Martyn+Prigmore&rft_id=https%3A%2F%2Fbooks.google.com%2Fbooks%3Fid%3DPKggKqIZnN0C%26pg%3DPA197&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span></span> </li> <li id="cite_note-31"><span class="mw-cite-backlink"><b><a href="#cite_ref-31">^</a></b></span> <span class="reference-text">Troels Arvin, <a rel="nofollow" class="external text" href="http://troels.arvin.dk/db/rdbms/#data_types-boolean">Survey of BOOLEAN data type implementation</a></span> </li> <li id="cite_note-FeuersteinPribyl2009-32"><span class="mw-cite-backlink"><b><a href="#cite_ref-FeuersteinPribyl2009_32-0">^</a></b></span> <span class="reference-text"><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite id="CITEREFSteven_FeuersteinBill_Pribyl2009" class="citation book cs1">Steven Feuerstein; Bill Pribyl (2009). <i>Oracle PL/SQL Programming</i>. O'Reilly Media, Inc. pp. 74, 91. <a href="/wiki/ISBN_(identifier)" class="mw-redirect" title="ISBN (identifier)">ISBN</a> <a href="/wiki/Special:BookSources/978-0-596-51446-4" title="Special:BookSources/978-0-596-51446-4"><bdi>978-0-596-51446-4</bdi></a>.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Abook&rft.genre=book&rft.btitle=Oracle+PL%2FSQL+Programming&rft.pages=74%2C+91&rft.pub=O%27Reilly+Media%2C+Inc.&rft.date=2009&rft.isbn=978-0-596-51446-4&rft.au=Steven+Feuerstein&rft.au=Bill+Pribyl&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span></span> </li> <li id="cite_note-33"><span class="mw-cite-backlink"><b><a href="#cite_ref-33">^</a></b></span> <span class="reference-text"><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite id="CITEREFArenhart,_Krause2012" class="citation cs2">Arenhart, Krause (2012), "Classical Logic or Non-Reflexive Logic? A case of Semantic Underdetermination", <i>Revista Portuguesa de Filosofia</i>, <b>68</b> (1/2): <span class="nowrap">73–</span>86, <a href="/wiki/Doi_(identifier)" class="mw-redirect" title="Doi (identifier)">doi</a>:<a rel="nofollow" class="external text" href="https://doi.org/10.17990%2FRPF%2F2012_68_1_0073">10.17990/RPF/2012_68_1_0073</a>, <a href="/wiki/JSTOR_(identifier)" class="mw-redirect" title="JSTOR (identifier)">JSTOR</a> <a rel="nofollow" class="external text" href="https://www.jstor.org/stable/41955624">41955624</a></cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Ajournal&rft.genre=article&rft.jtitle=Revista+Portuguesa+de+Filosofia&rft.atitle=Classical+Logic+or+Non-Reflexive+Logic%3F+A+case+of+Semantic+Underdetermination&rft.volume=68&rft.issue=1%2F2&rft.pages=%3Cspan+class%3D%22nowrap%22%3E73-%3C%2Fspan%3E86&rft.date=2012&rft_id=info%3Adoi%2F10.17990%2FRPF%2F2012_68_1_0073&rft_id=https%3A%2F%2Fwww.jstor.org%2Fstable%2F41955624%23id-name%3DJSTOR&rft.au=Arenhart%2C+Krause&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span>.</span> </li> <li id="cite_note-3rdmanifesto-34"><span class="mw-cite-backlink"><b><a href="#cite_ref-3rdmanifesto_34-0">^</a></b></span> <span class="reference-text"> <link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite id="CITEREFDarwenChris_Date" class="citation web cs1">Darwen, Hugh; Chris Date. <a rel="nofollow" class="external text" href="http://www.thethirdmanifesto.com/">"The Third Manifesto"</a><span class="reference-accessdate">. Retrieved <span class="nowrap">May 29,</span> 2007</span>.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Abook&rft.genre=unknown&rft.btitle=The+Third+Manifesto&rft.aulast=Darwen&rft.aufirst=Hugh&rft.au=Chris+Date&rft_id=http%3A%2F%2Fwww.thethirdmanifesto.com%2F&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span></span> </li> <li id="cite_note-askew-wall-35"><span class="mw-cite-backlink"><b><a href="#cite_ref-askew-wall_35-0">^</a></b></span> <span class="reference-text"> <link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite id="CITEREFDarwen" class="citation web cs1">Darwen, Hugh. <a rel="nofollow" class="external text" href="http://www.dcs.warwick.ac.uk/~hugh/TTM/TTM-TheAskewWall-printable.pdf">"The Askew Wall"</a> <span class="cs1-format">(PDF)</span><span class="reference-accessdate">. Retrieved <span class="nowrap">May 29,</span> 2007</span>.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Abook&rft.genre=unknown&rft.btitle=The+Askew+Wall&rft.aulast=Darwen&rft.aufirst=Hugh&rft_id=http%3A%2F%2Fwww.dcs.warwick.ac.uk%2F~hugh%2FTTM%2FTTM-TheAskewWall-printable.pdf&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span></span> </li> <li id="cite_note-isbn0596100124-36"><span class="mw-cite-backlink"><b><a href="#cite_ref-isbn0596100124_36-0">^</a></b></span> <span class="reference-text"><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite id="CITEREFDate2005" class="citation book cs1">Date, Chris (May 2005). <i>Database in Depth: Relational Theory for Practitioners</i>. O'Reilly Media, Inc. p. 73. <a href="/wiki/ISBN_(identifier)" class="mw-redirect" title="ISBN (identifier)">ISBN</a> <a href="/wiki/Special:BookSources/978-0-596-10012-4" title="Special:BookSources/978-0-596-10012-4"><bdi>978-0-596-10012-4</bdi></a>.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Abook&rft.genre=book&rft.btitle=Database+in+Depth%3A+Relational+Theory+for+Practitioners&rft.pages=73&rft.pub=O%27Reilly+Media%2C+Inc.&rft.date=2005-05&rft.isbn=978-0-596-10012-4&rft.aulast=Date&rft.aufirst=Chris&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span></span> </li> <li id="cite_note-cwa-37"><span class="mw-cite-backlink"><b><a href="#cite_ref-cwa_37-0">^</a></b></span> <span class="reference-text"><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite id="CITEREFDate" class="citation web cs1">Date, Chris. <a rel="nofollow" class="external text" href="https://web.archive.org/web/20070519134146/http://www.sfdama.org/ChrisDate_20070110.htm">"Abstract: The Closed World Assumption"</a>. <a href="/wiki/Data_Management_Association" title="Data Management Association">Data Management Association</a>, San Francisco Bay Area Chapter. Archived from <a rel="nofollow" class="external text" href="http://www.sfdama.org/ChrisDate_20070110.htm">the original</a> on 2007-05-19<span class="reference-accessdate">. Retrieved <span class="nowrap">May 29,</span> 2007</span>.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Abook&rft.genre=unknown&rft.btitle=Abstract%3A+The+Closed+World+Assumption&rft.pub=Data+Management+Association%2C+San+Francisco+Bay+Area+Chapter&rft.aulast=Date&rft.aufirst=Chris&rft_id=http%3A%2F%2Fwww.sfdama.org%2FChrisDate_20070110.htm&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span></span> </li> </ol></div> <div class="mw-heading mw-heading2"><h2 id="Further_reading">Further reading</h2><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=34" title="Edit section: Further reading"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <ul><li>E. F. Codd. Understanding relations (installment #7). FDT Bulletin of ACM-SIGMOD, 7(3-4):23–28, 1975.</li> <li><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite id="CITEREFCodd1979" class="citation journal cs1">Codd, E. F. (1979). "Extending the database relational model to capture more meaning". <i>ACM Transactions on Database Systems</i>. <b>4</b> (4): <span class="nowrap">397–</span>434. <a href="/wiki/CiteSeerX_(identifier)" class="mw-redirect" title="CiteSeerX (identifier)">CiteSeerX</a> <span class="id-lock-free" title="Freely accessible"><a rel="nofollow" class="external text" href="https://citeseerx.ist.psu.edu/viewdoc/summary?doi=10.1.1.508.5701">10.1.1.508.5701</a></span>. <a href="/wiki/Doi_(identifier)" class="mw-redirect" title="Doi (identifier)">doi</a>:<a rel="nofollow" class="external text" href="https://doi.org/10.1145%2F320107.320109">10.1145/320107.320109</a>. <a href="/wiki/S2CID_(identifier)" class="mw-redirect" title="S2CID (identifier)">S2CID</a> <a rel="nofollow" class="external text" href="https://api.semanticscholar.org/CorpusID:17517212">17517212</a>.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Ajournal&rft.genre=article&rft.jtitle=ACM+Transactions+on+Database+Systems&rft.atitle=Extending+the+database+relational+model+to+capture+more+meaning&rft.volume=4&rft.issue=4&rft.pages=%3Cspan+class%3D%22nowrap%22%3E397-%3C%2Fspan%3E434&rft.date=1979&rft_id=https%3A%2F%2Fciteseerx.ist.psu.edu%2Fviewdoc%2Fsummary%3Fdoi%3D10.1.1.508.5701%23id-name%3DCiteSeerX&rft_id=https%3A%2F%2Fapi.semanticscholar.org%2FCorpusID%3A17517212%23id-name%3DS2CID&rft_id=info%3Adoi%2F10.1145%2F320107.320109&rft.aulast=Codd&rft.aufirst=E.+F.&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span> Especially §2.3.</li> <li><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite id="CITEREFDate2000" class="citation book cs1"><a href="/wiki/C.J._Date" class="mw-redirect" title="C.J. Date">Date, C.J.</a> (2000). <a rel="nofollow" class="external text" href="https://archive.org/details/databaserelation00date"><i>The Database Relational Model: A Retrospective Review and Analysis: A Historical Account and Assessment of E. F. Codd's Contribution to the Field of Database Technology</i></a>. <a href="/wiki/Pearson_PLC" class="mw-redirect" title="Pearson PLC">Addison Wesley Longman</a>. <a href="/wiki/ISBN_(identifier)" class="mw-redirect" title="ISBN (identifier)">ISBN</a> <a href="/wiki/Special:BookSources/978-0-201-61294-3" title="Special:BookSources/978-0-201-61294-3"><bdi>978-0-201-61294-3</bdi></a>.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Abook&rft.genre=book&rft.btitle=The+Database+Relational+Model%3A+A+Retrospective+Review+and+Analysis%3A+A+Historical+Account+and+Assessment+of+E.+F.+Codd%27s+Contribution+to+the+Field+of+Database+Technology&rft.pub=Addison+Wesley+Longman&rft.date=2000&rft.isbn=978-0-201-61294-3&rft.aulast=Date&rft.aufirst=C.J.&rft_id=https%3A%2F%2Farchive.org%2Fdetails%2Fdatabaserelation00date&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span></li> <li><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite id="CITEREFKlein1994" class="citation journal cs1">Klein, Hans-Joachim (1994). <a rel="nofollow" class="external text" href="https://doi.org/10.1145%2F187436.187445">"How to modify SQL queries in order to guarantee sure answers"</a>. <i>ACM SIGMOD Record</i>. <b>23</b> (3): <span class="nowrap">14–</span>20. <a href="/wiki/Doi_(identifier)" class="mw-redirect" title="Doi (identifier)">doi</a>:<span class="id-lock-free" title="Freely accessible"><a rel="nofollow" class="external text" href="https://doi.org/10.1145%2F187436.187445">10.1145/187436.187445</a></span>. <a href="/wiki/S2CID_(identifier)" class="mw-redirect" title="S2CID (identifier)">S2CID</a> <a rel="nofollow" class="external text" href="https://api.semanticscholar.org/CorpusID:17354724">17354724</a>.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Ajournal&rft.genre=article&rft.jtitle=ACM+SIGMOD+Record&rft.atitle=How+to+modify+SQL+queries+in+order+to+guarantee+sure+answers&rft.volume=23&rft.issue=3&rft.pages=%3Cspan+class%3D%22nowrap%22%3E14-%3C%2Fspan%3E20&rft.date=1994&rft_id=info%3Adoi%2F10.1145%2F187436.187445&rft_id=https%3A%2F%2Fapi.semanticscholar.org%2FCorpusID%3A17354724%23id-name%3DS2CID&rft.aulast=Klein&rft.aufirst=Hans-Joachim&rft_id=https%3A%2F%2Fdoi.org%2F10.1145%252F187436.187445&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span></li> <li>Claude Rubinson, <a rel="nofollow" class="external text" href="http://www.u.arizona.edu/~rubinson/scrawl/Rubinson.2007.Nulls_Three-Valued_Logic_and_Ambiguity_in_SQL.pdf">Nulls, Three-Valued Logic, and Ambiguity in SQL: Critiquing Date's Critique</a> <a rel="nofollow" class="external text" href="https://web.archive.org/web/20160305071445/http://www.u.arizona.edu/~rubinson/scrawl/Rubinson.2007.Nulls_Three-Valued_Logic_and_Ambiguity_in_SQL.pdf">Archived</a> 2016-03-05 at the <a href="/wiki/Wayback_Machine" title="Wayback Machine">Wayback Machine</a>, SIGMOD Record, December 2007 (Vol. 36, No. 4)</li> <li>John Grant, <a rel="nofollow" class="external text" href="http://www09.sigmod.org/sigmod/record/issues/0809/p23.grant.pdf">Null Values in SQL</a>. SIGMOD Record, September 2008 (Vol. 37, No. 3)</li> <li>Waraporn, Narongrit, and Kriengkrai Porkaew. "<a rel="nofollow" class="external text" href="http://www.iaeng.org/IJCS/issues_v35/issue_3/IJCS_35_3_08.pdf">Null semantics for subqueries and atomic predicates</a>". <a href="/wiki/IAENG" class="mw-redirect" title="IAENG">IAENG</a> International Journal of Computer Science 35.3 (2008): 305-313.</li> <li><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1238218222" /><cite id="CITEREFBernhard_ThalheimKlaus-Dieter_Schewe2011" class="citation journal cs1">Bernhard Thalheim; Klaus-Dieter Schewe (2011). <a rel="nofollow" class="external text" href="https://zenodo.org/record/1038419">"NULL 'Value' Algebras and Logics"</a>. <i>Frontiers in Artificial Intelligence and Applications</i>. <b>225</b> (Information Modelling and Knowledge Bases XXII). <a href="/wiki/Doi_(identifier)" class="mw-redirect" title="Doi (identifier)">doi</a>:<a rel="nofollow" class="external text" href="https://doi.org/10.3233%2F978-1-60750-690-4-354">10.3233/978-1-60750-690-4-354</a>.</cite><span title="ctx_ver=Z39.88-2004&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Ajournal&rft.genre=article&rft.jtitle=Frontiers+in+Artificial+Intelligence+and+Applications&rft.atitle=NULL+%27Value%27+Algebras+and+Logics&rft.volume=225&rft.issue=Information+Modelling+and+Knowledge+Bases+XXII&rft.date=2011&rft_id=info%3Adoi%2F10.3233%2F978-1-60750-690-4-354&rft.au=Bernhard+Thalheim&rft.au=Klaus-Dieter+Schewe&rft_id=https%3A%2F%2Fzenodo.org%2Frecord%2F1038419&rfr_id=info%3Asid%2Fen.wikipedia.org%3ANull+%28SQL%29" class="Z3988"></span></li> <li>Enrico Franconi and Sergio Tessaris, <a rel="nofollow" class="external text" href="http://ceur-ws.org/Vol-866/paper8.pdf">On the Logic of SQL Nulls</a>, Proceedings of the 6th Alberto Mendelzon International Workshop on Foundations of Data Management, Ouro Preto, Brazil, June 27–30, 2012. pp. 114–128</li></ul> <div class="mw-heading mw-heading2"><h2 id="External_links">External links</h2><span class="mw-editsection"><span class="mw-editsection-bracket">[</span><a href="/w/index.php?title=Null_(SQL)&action=edit&section=35" title="Edit section: External links"><span>edit</span></a><span class="mw-editsection-bracket">]</span></span></div> <ul><li><a rel="nofollow" class="external text" href="http://www.psoug.org/reference/null.html">Oracle NULLs</a> <a rel="nofollow" class="external text" href="https://web.archive.org/web/20130412031441/http://psoug.org/reference/null.html">Archived</a> 2013-04-12 at the <a href="/wiki/Wayback_Machine" title="Wayback Machine">Wayback Machine</a></li> <li><a rel="nofollow" class="external text" href="http://www.thethirdmanifesto.com/">The Third Manifesto</a></li> <li><a rel="nofollow" class="external text" href="https://web.archive.org/web/20130405060544/http://www.sqlexpert.co.uk/2006/05/treatment-of-nulls-by-oracle-sql.html">Implications of NULLs in sequencing of data</a></li> <li><a rel="nofollow" class="external text" href="https://bugs.java.com/bugdatabase/view_bug.do?bug_id=4032732">Java bug report about jdbc not distinguishing null and empty string, which Sun closed as "not a bug"</a></li></ul> <div class="navbox-styles"><style data-mw-deduplicate="TemplateStyles:r1129693374">.mw-parser-output .hlist dl,.mw-parser-output .hlist ol,.mw-parser-output .hlist ul{margin:0;padding:0}.mw-parser-output .hlist dd,.mw-parser-output .hlist dt,.mw-parser-output .hlist li{margin:0;display:inline}.mw-parser-output .hlist.inline,.mw-parser-output .hlist.inline dl,.mw-parser-output .hlist.inline ol,.mw-parser-output .hlist.inline ul,.mw-parser-output .hlist dl dl,.mw-parser-output .hlist dl ol,.mw-parser-output .hlist dl ul,.mw-parser-output .hlist ol dl,.mw-parser-output .hlist ol ol,.mw-parser-output .hlist ol ul,.mw-parser-output .hlist ul dl,.mw-parser-output .hlist ul ol,.mw-parser-output .hlist ul ul{display:inline}.mw-parser-output .hlist .mw-empty-li{display:none}.mw-parser-output .hlist dt::after{content:": "}.mw-parser-output .hlist dd::after,.mw-parser-output .hlist li::after{content:" · ";font-weight:bold}.mw-parser-output .hlist dd:last-child::after,.mw-parser-output .hlist dt:last-child::after,.mw-parser-output .hlist li:last-child::after{content:none}.mw-parser-output .hlist dd dd:first-child::before,.mw-parser-output .hlist dd dt:first-child::before,.mw-parser-output .hlist dd li:first-child::before,.mw-parser-output .hlist dt dd:first-child::before,.mw-parser-output .hlist dt dt:first-child::before,.mw-parser-output .hlist dt li:first-child::before,.mw-parser-output .hlist li dd:first-child::before,.mw-parser-output .hlist li dt:first-child::before,.mw-parser-output .hlist li li:first-child::before{content:" (";font-weight:normal}.mw-parser-output .hlist dd dd:last-child::after,.mw-parser-output .hlist dd dt:last-child::after,.mw-parser-output .hlist dd li:last-child::after,.mw-parser-output .hlist dt dd:last-child::after,.mw-parser-output .hlist dt dt:last-child::after,.mw-parser-output .hlist dt li:last-child::after,.mw-parser-output .hlist li dd:last-child::after,.mw-parser-output .hlist li dt:last-child::after,.mw-parser-output .hlist li li:last-child::after{content:")";font-weight:normal}.mw-parser-output .hlist ol{counter-reset:listitem}.mw-parser-output .hlist ol>li{counter-increment:listitem}.mw-parser-output .hlist ol>li::before{content:" "counter(listitem)"\a0 "}.mw-parser-output .hlist dd ol>li:first-child::before,.mw-parser-output .hlist dt ol>li:first-child::before,.mw-parser-output .hlist li ol>li:first-child::before{content:" ("counter(listitem)"\a0 "}</style><style data-mw-deduplicate="TemplateStyles:r1236075235">.mw-parser-output .navbox{box-sizing:border-box;border:1px solid #a2a9b1;width:100%;clear:both;font-size:88%;text-align:center;padding:1px;margin:1em auto 0}.mw-parser-output .navbox .navbox{margin-top:0}.mw-parser-output .navbox+.navbox,.mw-parser-output .navbox+.navbox-styles+.navbox{margin-top:-1px}.mw-parser-output .navbox-inner,.mw-parser-output .navbox-subgroup{width:100%}.mw-parser-output .navbox-group,.mw-parser-output .navbox-title,.mw-parser-output .navbox-abovebelow{padding:0.25em 1em;line-height:1.5em;text-align:center}.mw-parser-output .navbox-group{white-space:nowrap;text-align:right}.mw-parser-output .navbox,.mw-parser-output .navbox-subgroup{background-color:#fdfdfd}.mw-parser-output .navbox-list{line-height:1.5em;border-color:#fdfdfd}.mw-parser-output .navbox-list-with-group{text-align:left;border-left-width:2px;border-left-style:solid}.mw-parser-output tr+tr>.navbox-abovebelow,.mw-parser-output tr+tr>.navbox-group,.mw-parser-output tr+tr>.navbox-image,.mw-parser-output tr+tr>.navbox-list{border-top:2px solid #fdfdfd}.mw-parser-output .navbox-title{background-color:#ccf}.mw-parser-output .navbox-abovebelow,.mw-parser-output .navbox-group,.mw-parser-output .navbox-subgroup .navbox-title{background-color:#ddf}.mw-parser-output .navbox-subgroup .navbox-group,.mw-parser-output .navbox-subgroup .navbox-abovebelow{background-color:#e6e6ff}.mw-parser-output .navbox-even{background-color:#f7f7f7}.mw-parser-output .navbox-odd{background-color:transparent}.mw-parser-output .navbox .hlist td dl,.mw-parser-output .navbox .hlist td ol,.mw-parser-output .navbox .hlist td ul,.mw-parser-output .navbox td.hlist dl,.mw-parser-output .navbox td.hlist ol,.mw-parser-output .navbox td.hlist ul{padding:0.125em 0}.mw-parser-output .navbox .navbar{display:block;font-size:100%}.mw-parser-output .navbox-title .navbar{float:left;text-align:left;margin-right:0.5em}body.skin--responsive .mw-parser-output .navbox-image img{max-width:none!important}@media print{body.ns-0 .mw-parser-output .navbox{display:none!important}}</style><style data-mw-deduplicate="TemplateStyles:r920966791">.mw-parser-output span.smallcaps{font-variant:small-caps}.mw-parser-output span.smallcaps-smaller{font-size:85%}</style><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r920966791" /><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r920966791" /><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r920966791" /><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r920966791" /><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r920966791" /><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r920966791" /><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r920966791" /><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r920966791" /><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r920966791" /><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r920966791" /><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r920966791" /><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r920966791" /><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r920966791" /><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r920966791" /><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r920966791" /><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r920966791" /><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r920966791" /><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r920966791" /></div><div role="navigation" class="navbox" aria-labelledby="SQL70" style="padding:3px"><table class="nowraplinks mw-collapsible autocollapse navbox-inner" style="border-spacing:0;background:transparent;color:inherit"><tbody><tr><th scope="col" class="navbox-title" colspan="2"><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1129693374" /><style data-mw-deduplicate="TemplateStyles:r1239400231">.mw-parser-output .navbar{display:inline;font-size:88%;font-weight:normal}.mw-parser-output .navbar-collapse{float:left;text-align:left}.mw-parser-output .navbar-boxtext{word-spacing:0}.mw-parser-output .navbar ul{display:inline-block;white-space:nowrap;line-height:inherit}.mw-parser-output .navbar-brackets::before{margin-right:-0.125em;content:"[ "}.mw-parser-output .navbar-brackets::after{margin-left:-0.125em;content:" ]"}.mw-parser-output .navbar li{word-spacing:-0.125em}.mw-parser-output .navbar a>span,.mw-parser-output .navbar a>abbr{text-decoration:inherit}.mw-parser-output .navbar-mini abbr{font-variant:small-caps;border-bottom:none;text-decoration:none;cursor:inherit}.mw-parser-output .navbar-ct-full{font-size:114%;margin:0 7em}.mw-parser-output .navbar-ct-mini{font-size:114%;margin:0 4em}html.skin-theme-clientpref-night .mw-parser-output .navbar li a abbr{color:var(--color-base)!important}@media(prefers-color-scheme:dark){html.skin-theme-clientpref-os .mw-parser-output .navbar li a abbr{color:var(--color-base)!important}}@media print{.mw-parser-output .navbar{display:none!important}}</style><div class="navbar plainlinks hlist navbar-mini"><ul><li class="nv-view"><a href="/wiki/Template:SQL" title="Template:SQL"><abbr title="View this template">v</abbr></a></li><li class="nv-talk"><a href="/wiki/Template_talk:SQL" title="Template talk:SQL"><abbr title="Discuss this template">t</abbr></a></li><li class="nv-edit"><a href="/wiki/Special:EditPage/Template:SQL" title="Special:EditPage/Template:SQL"><abbr title="Edit this template">e</abbr></a></li></ul></div><div id="SQL70" style="font-size:114%;margin:0 4em"><a href="/wiki/SQL" title="SQL">SQL</a></div></th></tr><tr><th scope="row" class="navbox-group" style="width:1%">Versions</th><td class="navbox-list-with-group navbox-list navbox-odd hlist" style="width:100%;padding:0"><div style="padding:0 0.25em"> <ul><li><a href="/wiki/SEQUEL" class="mw-redirect" title="SEQUEL">SEQUEL</a></li> <li><a href="/w/index.php?title=SQL-86&action=edit&redlink=1" class="new" title="SQL-86 (page does not exist)">SQL-86</a></li> <li><a href="/w/index.php?title=SQL-89&action=edit&redlink=1" class="new" title="SQL-89 (page does not exist)">SQL-89</a></li> <li><a href="/wiki/SQL-92" title="SQL-92">SQL-92</a></li> <li><a href="/wiki/SQL:1999" title="SQL:1999">SQL:1999</a></li> <li><a href="/wiki/SQL:2003" title="SQL:2003">SQL:2003</a></li> <li><a href="/wiki/SQL:2006" title="SQL:2006">SQL:2006</a></li> <li><a href="/wiki/SQL:2008" title="SQL:2008">SQL:2008</a></li> <li><a href="/wiki/SQL:2011" title="SQL:2011">SQL:2011</a></li> <li><a href="/wiki/SQL:2016" title="SQL:2016">SQL:2016</a></li> <li><a href="/wiki/SQL:2023" title="SQL:2023">SQL:2023</a></li></ul> </div></td></tr><tr><th scope="row" class="navbox-group" style="width:1%"><a href="/wiki/SQL_reserved_words" class="mw-redirect" title="SQL reserved words">Keywords</a></th><td class="navbox-list-with-group navbox-list navbox-even hlist" style="width:100%;padding:0"><div style="padding:0 0.25em"> <ul><li><span class="smallcaps"><a href="/wiki/Alias_(SQL)" title="Alias (SQL)">As</a></span></li> <li><span class="smallcaps"><a href="/wiki/Case_(SQL)" class="mw-redirect" title="Case (SQL)">Case</a></span></li> <li><span class="smallcaps"><a href="/wiki/Create_(SQL)" class="mw-redirect" title="Create (SQL)">Create</a></span></li> <li><span class="smallcaps"><a href="/wiki/Delete_(SQL)" title="Delete (SQL)">Delete</a></span></li> <li><span class="smallcaps"><a href="/wiki/From_(SQL)" title="From (SQL)">From</a></span></li> <li><span class="smallcaps"><a href="/wiki/Group_by_(SQL)" title="Group by (SQL)">Group by</a></span></li> <li><span class="smallcaps"><a href="/wiki/Having_(SQL)" title="Having (SQL)">Having</a></span></li> <li><span class="smallcaps"><a href="/wiki/Insert_(SQL)" title="Insert (SQL)">Insert</a></span></li> <li><span class="smallcaps"><a href="/wiki/Join_(SQL)" title="Join (SQL)">Join</a></span></li> <li><span class="smallcaps"><a href="/wiki/Merge_(SQL)" title="Merge (SQL)">Merge</a></span></li> <li><span class="smallcaps"><a class="mw-selflink selflink">Null</a></span></li> <li><span class="smallcaps"><a href="/wiki/Order_by" title="Order by">Order by</a></span></li> <li><span class="smallcaps"><a href="/wiki/Window_function_(SQL)" title="Window function (SQL)">Over</a></span></li> <li><span class="smallcaps"><a href="/wiki/Prepare_(SQL)" class="mw-redirect" title="Prepare (SQL)">Prepare</a></span></li> <li><span class="smallcaps"><a href="/wiki/Select_(SQL)" title="Select (SQL)">Select</a></span></li> <li><span class="smallcaps"><a href="/wiki/Truncate_(SQL)" title="Truncate (SQL)">Truncate</a></span></li> <li><span class="smallcaps"><a href="/wiki/Set_operations_(SQL)" title="Set operations (SQL)">Union</a></span></li> <li><span class="smallcaps"><a href="/wiki/Update_(SQL)" title="Update (SQL)">Update</a></span></li> <li><span class="smallcaps"><a href="/wiki/With_(SQL)" class="mw-redirect" title="With (SQL)">With</a></span></li></ul> </div></td></tr><tr><th scope="row" class="navbox-group" style="width:1%">Related</th><td class="navbox-list-with-group navbox-list navbox-odd hlist" style="width:100%;padding:0"><div style="padding:0 0.25em"> <ul><li><a href="/wiki/Edgar_F._Codd" title="Edgar F. Codd">Edgar Codd</a></li> <li><a href="/wiki/Relational_database" title="Relational database">Relational database</a></li></ul> </div></td></tr><tr><th scope="row" class="navbox-group" style="width:1%">ISO/IEC SQL parts</th><td class="navbox-list-with-group navbox-list navbox-even hlist" style="width:100%;padding:0"><div style="padding:0 0.25em"> <ul><li><a href="/w/index.php?title=SQL/Framework&action=edit&redlink=1" class="new" title="SQL/Framework (page does not exist)">Framework</a></li> <li><a href="/w/index.php?title=SQL/Foundation&action=edit&redlink=1" class="new" title="SQL/Foundation (page does not exist)">Foundation</a></li> <li><a href="/wiki/SQL/CLI" class="mw-redirect" title="SQL/CLI">Call-Level Interface</a></li> <li><a href="/wiki/SQL/PSM" title="SQL/PSM">Persistent Stored Modules</a></li> <li><a href="/wiki/SQL/MED" title="SQL/MED">Management of External Data</a></li> <li><a href="/wiki/SQL/OLB" title="SQL/OLB">Object Language Bindings</a></li> <li><a href="/wiki/SQL/Schemata" title="SQL/Schemata">Information and Definition Schemas</a></li> <li><a href="/wiki/SQL/JRT" title="SQL/JRT">SQL Routines and Types for the Java Programming Language</a></li> <li><a href="/wiki/SQL/XML" title="SQL/XML">XML-Related Specifications</a></li></ul> </div></td></tr></tbody></table></div> <div class="navbox-styles"><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1129693374" /><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1236075235" /></div><div role="navigation" class="navbox" aria-labelledby="Nulls_in_computing108" style="padding:3px"><table class="nowraplinks mw-collapsible autocollapse navbox-inner" style="border-spacing:0;background:transparent;color:inherit"><tbody><tr><th scope="col" class="navbox-title" colspan="2"><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1129693374" /><link rel="mw-deduplicated-inline-style" href="mw-data:TemplateStyles:r1239400231" /><div class="navbar plainlinks hlist navbar-mini"><ul><li class="nv-view"><a href="/wiki/Template:Nulls" title="Template:Nulls"><abbr title="View this template">v</abbr></a></li><li class="nv-talk"><a href="/wiki/Template_talk:Nulls" title="Template talk:Nulls"><abbr title="Discuss this template">t</abbr></a></li><li class="nv-edit"><a href="/wiki/Special:EditPage/Template:Nulls" title="Special:EditPage/Template:Nulls"><abbr title="Edit this template">e</abbr></a></li></ul></div><div id="Nulls_in_computing108" style="font-size:114%;margin:0 4em">Nulls in <a href="/wiki/Computing" title="Computing">computing</a></div></th></tr><tr><td colspan="2" class="navbox-list navbox-odd hlist" style="width:100%;padding:0"><div style="padding:0 0.25em"> <ul><li><a href="/wiki/Null_character" title="Null character">Null character</a></li> <li><a href="/wiki/Null_device" title="Null device">Null device</a></li> <li><a href="/wiki/Null_function" title="Null function">Null function</a></li> <li><a href="/wiki/Null_modem" title="Null modem">Null modem</a></li> <li><a href="/wiki/Null_object_pattern" title="Null object pattern">Null object pattern</a></li> <li><a href="/wiki/Null_pointer" title="Null pointer">Null pointer</a></li> <li><a class="mw-selflink selflink">Null in SQL</a></li> <li><a href="/wiki/Empty_string" title="Empty string">Null string</a></li> <li><a href="/wiki/Null_coalescing_operator" title="Null coalescing operator">Null coalescing operator</a></li></ul> </div></td></tr><tr><td class="navbox-abovebelow hlist" colspan="2"><div> <ul><li><i>See also:</i> <a href="/wiki/Null_coalescing_operator" title="Null coalescing operator">Null coalescing operator</a></li> <li><a href="/wiki/Nullable_type" title="Nullable type">Nullable type</a></li> <li><a href="/wiki/Undefined_value" title="Undefined value">Undefined value</a></li></ul> </div></td></tr></tbody></table></div>    </div><noscript><img src="https://login.wikimedia.org/wiki/Special:CentralAutoLogin/start?useformat=desktop&type=1x1&usesul3=0" alt="" width="1" height="1" style="border: none; position: absolute;"></noscript> <div class="printfooter" data-nosnippet="">Retrieved from "<a dir="ltr" href="https://en.wikipedia.org/w/index.php?title=Null_(SQL)&oldid=1271809523">https://en.wikipedia.org/w/index.php?title=Null_(SQL)&oldid=1271809523</a>"</div></div> <div id="catlinks" class="catlinks" data-mw="interface"><div id="mw-normal-catlinks" class="mw-normal-catlinks"><a href="/wiki/Help:Category" title="Help:Category">Categories</a>: <ul><li><a href="/wiki/Category:SQL_keywords" title="Category:SQL keywords">SQL keywords</a></li><li><a href="/wiki/Category:Unknown_content" title="Category:Unknown content">Unknown content</a></li></ul></div><div id="mw-hidden-catlinks" class="mw-hidden-catlinks mw-hidden-cats-hidden">Hidden categories: <ul><li><a href="/wiki/Category:CS1_errors:_requires_URL" title="Category:CS1 errors: requires URL">CS1 errors: requires URL</a></li><li><a href="/wiki/Category:Articles_with_short_description" title="Category:Articles with short description">Articles with short description</a></li><li><a href="/wiki/Category:Short_description_is_different_from_Wikidata" title="Category:Short description is different from Wikidata">Short description is different from Wikidata</a></li><li><a href="/wiki/Category:All_articles_with_unsourced_statements" title="Category:All articles with unsourced statements">All articles with unsourced statements</a></li><li><a href="/wiki/Category:Articles_with_unsourced_statements_from_November_2012" title="Category:Articles with unsourced statements from November 2012">Articles with unsourced statements from November 2012</a></li><li><a href="/wiki/Category:Webarchive_template_wayback_links" title="Category:Webarchive template wayback links">Webarchive template wayback links</a></li><li><a href="/wiki/Category:Articles_with_example_SQL_code" title="Category:Articles with example SQL code">Articles with example SQL code</a></li></ul></div></div> </div> </main> </div> <div class="mw-footer-container"> <footer id="footer" class="mw-footer" > <ul id="footer-info"> <li id="footer-info-lastmod"> This page was last edited on 25 January 2025, at 20:58<span class="anonymous-show"> (UTC)</span>.</li> <li id="footer-info-copyright">Text is available under the <a href="/wiki/Wikipedia:Text_of_the_Creative_Commons_Attribution-ShareAlike_4.0_International_License" title="Wikipedia:Text of the Creative Commons Attribution-ShareAlike 4.0 International License">Creative Commons Attribution-ShareAlike 4.0 License</a>; additional terms may apply. By using this site, you agree to the <a href="https://foundation.wikimedia.org/wiki/Special:MyLanguage/Policy:Terms_of_Use" class="extiw" title="foundation:Special:MyLanguage/Policy:Terms of Use">Terms of Use</a> and <a href="https://foundation.wikimedia.org/wiki/Special:MyLanguage/Policy:Privacy_policy" class="extiw" title="foundation:Special:MyLanguage/Policy:Privacy policy">Privacy Policy</a>. Wikipedia® is a registered trademark of the <a rel="nofollow" class="external text" href="https://wikimediafoundation.org/">Wikimedia Foundation, Inc.</a>, a non-profit organization.</li> </ul> <ul id="footer-places"> <li id="footer-places-privacy"><a href="https://foundation.wikimedia.org/wiki/Special:MyLanguage/Policy:Privacy_policy">Privacy policy</a></li> <li id="footer-places-about"><a href="/wiki/Wikipedia:About">About Wikipedia</a></li> <li id="footer-places-disclaimers"><a href="/wiki/Wikipedia:General_disclaimer">Disclaimers</a></li> <li id="footer-places-contact"><a href="//en.wikipedia.org/wiki/Wikipedia:Contact_us">Contact Wikipedia</a></li> <li id="footer-places-wm-codeofconduct"><a href="https://foundation.wikimedia.org/wiki/Special:MyLanguage/Policy:Universal_Code_of_Conduct">Code of Conduct</a></li> <li id="footer-places-developers"><a href="https://developer.wikimedia.org">Developers</a></li> <li id="footer-places-statslink"><a href="https://stats.wikimedia.org/#/en.wikipedia.org">Statistics</a></li> <li id="footer-places-cookiestatement"><a href="https://foundation.wikimedia.org/wiki/Special:MyLanguage/Policy:Cookie_statement">Cookie statement</a></li> <li id="footer-places-mobileview"><a href="//en.m.wikipedia.org/w/index.php?title=Null_(SQL)&mobileaction=toggle_view_mobile" class="noprint stopMobileRedirectToggle">Mobile view</a></li> </ul> <ul id="footer-icons" class="noprint"> <li id="footer-copyrightico"><a href="https://wikimediafoundation.org/" class="cdx-button cdx-button--fake-button cdx-button--size-large cdx-button--fake-button--enabled"><picture><source media="(min-width: 500px)" srcset="/static/images/footer/wikimedia-button.svg" width="84" height="29"><img src="/static/images/footer/wikimedia.svg" width="25" height="25" alt="Wikimedia Foundation" lang="en" loading="lazy"></picture></a></li> <li id="footer-poweredbyico"><a href="https://www.mediawiki.org/" class="cdx-button cdx-button--fake-button cdx-button--size-large cdx-button--fake-button--enabled"><picture><source media="(min-width: 500px)" srcset="/w/resources/assets/poweredby_mediawiki.svg" width="88" height="31"><img src="/w/resources/assets/mediawiki_compact.svg" alt="Powered by MediaWiki" lang="en" width="25" height="25" loading="lazy"></picture></a></li> </ul> </footer> </div> </div> </div> <div class="vector-header-container vector-sticky-header-container"> <div id="vector-sticky-header" class="vector-sticky-header"> <div class="vector-sticky-header-start"> <div class="vector-sticky-header-icon-start vector-button-flush-left vector-button-flush-right" aria-hidden="true"> <button class="cdx-button cdx-button--weight-quiet cdx-button--icon-only vector-sticky-header-search-toggle" tabindex="-1" data-event-name="ui.vector-sticky-search-form.icon"><span class="vector-icon mw-ui-icon-search mw-ui-icon-wikimedia-search"></span> <span>Search</span> </button> </div> <div role="search" class="vector-search-box-vue vector-search-box-show-thumbnail vector-search-box"> <div class="vector-typeahead-search-container"> <div class="cdx-typeahead-search cdx-typeahead-search--show-thumbnail"> <form action="/w/index.php" id="vector-sticky-search-form" class="cdx-search-input cdx-search-input--has-end-button"> <div class="cdx-search-input__input-wrapper" data-search-loc="header-moved"> <div class="cdx-text-input cdx-text-input--has-start-icon"> <input class="cdx-text-input__input" type="search" name="search" placeholder="Search Wikipedia"> <span class="cdx-text-input__icon cdx-text-input__start-icon"></span> </div> <input type="hidden" name="title" value="Special:Search"> </div> <button class="cdx-button cdx-search-input__end-button">Search</button> </form> </div> </div> </div> <div class="vector-sticky-header-context-bar"> <nav aria-label="Contents" class="vector-toc-landmark"> <div id="vector-sticky-header-toc" class="vector-dropdown mw-portlet mw-portlet-sticky-header-toc vector-sticky-header-toc vector-button-flush-left" > <input type="checkbox" id="vector-sticky-header-toc-checkbox" role="button" aria-haspopup="true" data-event-name="ui.dropdown-vector-sticky-header-toc" class="vector-dropdown-checkbox " aria-label="Toggle the table of contents" > <label id="vector-sticky-header-toc-label" for="vector-sticky-header-toc-checkbox" class="vector-dropdown-label cdx-button cdx-button--fake-button cdx-button--fake-button--enabled cdx-button--weight-quiet cdx-button--icon-only " aria-hidden="true" ><span class="vector-icon mw-ui-icon-listBullet mw-ui-icon-wikimedia-listBullet"></span> <span class="vector-dropdown-label-text">Toggle the table of contents</span> </label> <div class="vector-dropdown-content"> <div id="vector-sticky-header-toc-unpinned-container" class="vector-unpinned-container"> </div> </div> </div> </nav> <div class="vector-sticky-header-context-bar-primary" aria-hidden="true" ><span class="mw-page-title-main">Null (SQL)</span></div> </div> </div> <div class="vector-sticky-header-end" aria-hidden="true"> <div class="vector-sticky-header-icons"> <a href="#" class="cdx-button cdx-button--fake-button cdx-button--fake-button--enabled cdx-button--weight-quiet cdx-button--icon-only" id="ca-talk-sticky-header" tabindex="-1" data-event-name="talk-sticky-header"><span class="vector-icon mw-ui-icon-speechBubbles mw-ui-icon-wikimedia-speechBubbles"></span> <span></span> </a> <a href="#" class="cdx-button cdx-button--fake-button cdx-button--fake-button--enabled cdx-button--weight-quiet cdx-button--icon-only" id="ca-subject-sticky-header" tabindex="-1" data-event-name="subject-sticky-header"><span class="vector-icon mw-ui-icon-article mw-ui-icon-wikimedia-article"></span> <span></span> </a> <a href="#" class="cdx-button cdx-button--fake-button cdx-button--fake-button--enabled cdx-button--weight-quiet cdx-button--icon-only" id="ca-history-sticky-header" tabindex="-1" data-event-name="history-sticky-header"><span class="vector-icon mw-ui-icon-wikimedia-history mw-ui-icon-wikimedia-wikimedia-history"></span> <span></span> </a> <a href="#" class="cdx-button cdx-button--fake-button cdx-button--fake-button--enabled cdx-button--weight-quiet cdx-button--icon-only mw-watchlink" id="ca-watchstar-sticky-header" tabindex="-1" data-event-name="watch-sticky-header"><span class="vector-icon mw-ui-icon-wikimedia-star mw-ui-icon-wikimedia-wikimedia-star"></span> <span></span> </a> <a href="#" class="cdx-button cdx-button--fake-button cdx-button--fake-button--enabled cdx-button--weight-quiet cdx-button--icon-only" id="ca-edit-sticky-header" tabindex="-1" data-event-name="wikitext-edit-sticky-header"><span class="vector-icon mw-ui-icon-wikimedia-wikiText mw-ui-icon-wikimedia-wikimedia-wikiText"></span> <span></span> </a> <a href="#" class="cdx-button cdx-button--fake-button cdx-button--fake-button--enabled cdx-button--weight-quiet cdx-button--icon-only" id="ca-ve-edit-sticky-header" tabindex="-1" data-event-name="ve-edit-sticky-header"><span class="vector-icon mw-ui-icon-wikimedia-edit mw-ui-icon-wikimedia-wikimedia-edit"></span> <span></span> </a> <a href="#" class="cdx-button cdx-button--fake-button cdx-button--fake-button--enabled cdx-button--weight-quiet cdx-button--icon-only" id="ca-viewsource-sticky-header" tabindex="-1" data-event-name="ve-edit-protected-sticky-header"><span class="vector-icon mw-ui-icon-wikimedia-editLock mw-ui-icon-wikimedia-wikimedia-editLock"></span> <span></span> </a> </div> <div class="vector-sticky-header-buttons"> <button class="cdx-button cdx-button--weight-quiet mw-interlanguage-selector" id="p-lang-btn-sticky-header" tabindex="-1" data-event-name="ui.dropdown-p-lang-btn-sticky-header"><span class="vector-icon mw-ui-icon-wikimedia-language mw-ui-icon-wikimedia-wikimedia-language"></span> <span>11 languages</span> </button> <a href="#" class="cdx-button cdx-button--fake-button cdx-button--fake-button--enabled cdx-button--weight-quiet cdx-button--action-progressive" id="ca-addsection-sticky-header" tabindex="-1" data-event-name="addsection-sticky-header"><span class="vector-icon mw-ui-icon-speechBubbleAdd-progressive mw-ui-icon-wikimedia-speechBubbleAdd-progressive"></span> <span>Add topic</span> </a> </div> <div class="vector-sticky-header-icon-end"> <div class="vector-user-links"> </div> </div> </div> </div> </div> <div class="mw-portlet mw-portlet-dock-bottom emptyPortlet" id="p-dock-bottom"> <ul> </ul> </div> <script>(RLQ=window.RLQ||[]).push(function(){mw.config.set({"wgHostname":"mw-web.eqiad.main-78bdfcd464-7ppj7","wgBackendResponseTime":205,"wgPageParseReport":{"limitreport":{"cputime":"0.807","walltime":"3.982","ppvisitednodes":{"value":4460,"limit":1000000},"postexpandincludesize":{"value":102726,"limit":2097152},"templateargumentsize":{"value":6626,"limit":2097152},"expansiondepth":{"value":16,"limit":100},"expensivefunctioncount":{"value":52,"limit":500},"unstrip-depth":{"value":1,"limit":20},"unstrip-size":{"value":177952,"limit":5000000},"entityaccesscount":{"value":0,"limit":400},"timingprofile":["100.00% 3783.652 1 -total"," 8.78% 332.201 1 Template:Reflist"," 7.29% 275.734 8 Template:Code"," 3.43% 129.737 1 Template:Short_description"," 3.26% 123.299 8 Template:Cite_journal"," 2.70% 102.182 1 Template:SQL"," 2.65% 100.362 2 Template:Navbox"," 2.48% 93.863 18 Template:Cite_book"," 2.27% 85.854 1 Template:Citation_needed"," 2.16% 81.710 2 Template:Pagetype"]},"scribunto":{"limitreport-timeusage":{"value":"0.400","limit":"10.000"},"limitreport-memusage":{"value":6546367,"limit":52428800}},"cachereport":{"origin":"mw-web.eqiad.main-8669bc5c8-z4f8w","timestamp":"20250318155716","ttl":2592000,"transientcontent":false}}});});</script> <script type="application/ld+json">{"@context":"https:\/\/schema.org","@type":"Article","name":"Null (SQL)","url":"https:\/\/en.wikipedia.org\/wiki\/Null_(SQL)","sameAs":"http:\/\/www.wikidata.org\/entity\/Q371029","mainEntity":"http:\/\/www.wikidata.org\/entity\/Q371029","author":{"@type":"Organization","name":"Contributors to Wikimedia projects"},"publisher":{"@type":"Organization","name":"Wikimedia Foundation, Inc.","logo":{"@type":"ImageObject","url":"https:\/\/www.wikimedia.org\/static\/images\/wmf-hor-googpub.png"}},"datePublished":"2005-06-06T20:22:34Z","dateModified":"2025-01-25T20:58:43Z","image":"https:\/\/upload.wikimedia.org\/wikipedia\/commons\/3\/3a\/Db_null.png","headline":"special marker and keyword in SQL indicating that something has no value"}</script> </body> </html>