To record missing or unknown values, users of relational databases can assign NULL markers as a value to a columns. NULL is not a data value, but a marker representing the absence of a value.

NULL markers can mean one of two things:

1. The column does not apply to the other columns in the record.
2. The column applies, but the information is unknown.

      Because NULL markers represent the absence of a value, NULL markers can be a source of much confusion and trouble for developers. To best understand NULL markers, one must understand the three-valued logic of TRUE, FALSE, or UNKNOWN, and recognize how NULL markers are treated within the different constructs of the SQL language.

Because NULL markers do not represent a value, SQL has two conditions specific to the SQL language:

1. IS NULL
2. IS NOT NULL

SQL also provides three functions to evaluate NULL markers:

1. NULLIF
2. ISNULL
3. COALESCE

We will cover these aspects and many more in the following document.

⌨️       The examples provided are written in Microsoft SQL Server T-SQL. The provided SQL statements can be easily modified to fit your flavor of SQL.

📫       I welcome any corrections, new tricks, new techniques, dead links, misspellings, or bugs!

👍 I recomend navigating using the Table Of Contents.

1. Predicate Logic
2. ANSI_NULLS
3. IS NULL and IS NOT NULL
4. Sample Data
5. Join Syntax
6. Semi and Anti Joins
7. Set Operators
8. COUNT and AVERAGE Functions
9. Constraints
10. Referential Integrity
11. Computed Columns
12. SQL NULL Functions (NULLIF, ISNULL, COALESCE)
13. Empty Strings, NULL, and ASCII Values
14. CONCAT
15. Views
16. Boolean Values
17. Return Statement
18. Identity Columns

🔵   Table Of Contents

      NULL markers in relational databases are a source of debate, with some proponents rejecting them entirely, while others, including Edgar F. Codd, advocate for their use. Codd, a computer scientist who revolutionized database management with his work on relational database theory, introduced the concept of NULL markers in the late 1960s and early 1970s to represent the absence of a value.

      Codd's work ensured data consistency and accuracy in relational databases, as they could better handle real-world scenarios where information may not always be complete. However, some critics argue that NULL markers can lead to ambiguity and confusion, lack of default values, performance issues, and affect data quality.

      Despite these criticisms, NULL markers are widely used and accepted, but must be used appropriately to understand their limitations and impact on data quality and performance. For further information, refer to C.J. Date's book, Database in Depth: Relational Theory for Practitioners.

🔵   Table Of Contents

      To best understand NULL markers in SQL, we need to understand the three-valued logic outcomes of TRUE, FALSE, and UNKNOWN. Unique to SQL, the logic result will always be UNKNOWN when comparing a NULL marker to any other value. SQL’s use of the three-valued logic system presents a surprising amount of complexity into a seemingly straightforward query!

Three-Valued Logic

The following truth tables display how the three-valued logic is applied.

A good example of the complexity is shown below in the following examples. De Morgan's Law is also included below as I show both version of negation.

--TRUE OR UNKNOWN = TRUE
SELECT 1 WHERE ((1=1) OR (NULL=1))

--NOT(FALSE OR UNKNOWN) = UNKNOWN
SELECT 2 WHERE NOT((1=2) OR (NULL=1))

--NOT(FALSE) AND NOT(UNKNOWN) = UNKNOWN
SELECT 3 WHERE NOT(1=2) AND NOT(NULL=1);

🔵   Table Of Contents

❗      The standard setting for ANSI_NULLS is ON. In a future version of Microsoft SQL Server ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will produce an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

      In SQL Server, the SET ANSI_NULLS setting specifies the ISO compliant behavior of the equality (=) and inequality (<>) comparison operators. The following table shows how the ANSI_NULLS session setting affects the results of Boolean expressions using NULL markers.

🔵   Table Of Contents

      We can experiment with setting the ANSI_NULLS to ON and OFF to review how the behavior of NULL markers change. In the following examples we will set the default ANSI_NULLS setting to ON.

SQL provides two functions for handling NULL markers, IS NULL and IS NOT NULL which we will also demonstrate below.

--2.1
SET ANSI_NULLS ON;

--UNKNOWN
SELECT 1 WHERE NULL = NULL;
SELECT 1 WHERE 1 = NULL;
SELECT 1 WHERE NULL <> NULL;
SELECT 1 WHERE NULL = NULL;
SELECT 1 WHERE 1 = NULL;
SELECT 1 WHERE NULL <> NULL;
SELECT 1 WHERE 1 <> NULL;
SELECT 1 WHERE NULL > NULL;
SELECT 1 WHERE 1 > NULL;

--TRUE
SELECT 1 WHERE NULL IS NULL;
SELECT 1 WHERE 1 IS NOT NULL;

--FALSE
SELECT 1 WHERE 1 IS NULL;
SELECT 1 WHERE NULL IS NOT NULL;

Now that we have covered the basics of NULL markers, lets create two sample data tables and start working through examples.

🔵 Table Of Contents

      We will use the following tables of fruits and their quantities in our quest to understand the behavior of NULL markers. Using two tables of the same type gives us the best example for understanding NULL markers. We will work with this data throughout these exercises.

##TableA

##TableB

DROP TABLE IF EXISTS ##TableA;
DROP TABLE IF EXISTS ##TableB;
GO

CREATE TABLE ##TableA
(
ID          INTEGER,
Fruit       VARCHAR(255),
Quantity    INTEGER
);
GO

CREATE TABLE ##TableB
(
ID          INTEGER,
Fruit       VARCHAR(255),
Quantity    INTEGER
);
GO

INSERT INTO ##TableA VALUES(1,'Apple',17);
INSERT INTO ##TableA VALUES(2,'Peach',20);
INSERT INTO ##TableA VALUES(3,'Mango',11);
INSERT INTO ##TableA VALUES(4,'Mango',15);
INSERT INTO ##TableA VALUES(5,NULL,5);
INSERT INTO ##TableA VALUES(6,NULL,3);
GO

INSERT INTO ##TableB VALUES(1,'Apple',17);
INSERT INTO ##TableB VALUES(2,'Peach',25);
INSERT INTO ##TableB VALUES(3,'Kiwi',20);
INSERT INTO ##TableB VALUES(4,NULL,NULL);
GO

SELECT * FROM ##TableA;
SELECT * from ##TableB;

🔵   Table Of Contents

      The standard ANSI:SQL joins are INNER, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, and CROSS JOIN. For NULL markers, all 5 of these joins treat the NULL marker as UNKOWN. For this reason I do not demonstrate each of these joins, but only the relevant joins needed to understand the behavior of NULL markers. Also, I include some alternative methods for joining if you need to treat NULL markers as equals, these methods use the ISNULL, ON EXISTS, and the IS [NOT] DISTINCT FROM clauses. See my documentation Advanced SQL Joins for more examples of these clauses.

INNER JOIN

      NULL markers are neither equal to nor not equal to each other. They are treated as UNKNOWN. This is best demonstrated by the below INNER JOIN statement, where NULL markers are not present in the result set. Note here we are looking for both equality and inequality on the Fruit column (and a DISTINCT is applied as well).

SELECT  a.ID,
        a.Fruit,
        b.ID,
        b.Fruit
FROM    ##TableA a INNER JOIN
        ##TableB b ON a.Fruit = b.Fruit OR a.Fruit <> b.Fruit;

FULL OUTER JOIN

      The FULL OUTER JOIN will give an illusion that is matches on the NULL markers but looking closely at the number of NULL markers returned vs the number of NULL markers in our sample data, we can determine this is indeed not true. Also, the below query demonstrates the ORDER BY sorts NULL markers in ascending order.

SELECT  a.ID,
        a.Fruit,
        b.ID,
        b.Fruit
FROM    ##TableA a FULL OUTER JOIN
        ##TableB b ON a.Fruit = b.Fruit
ORDER BY 1,2;

Other Methods For Returning NULL Markers

There are a few methods for returning NULL markers in a join, as shown below.

1. The first method uses the ISNULL function and sets the NULL markers to an empty string.
2. The second method uses the ON EXISTS clause. ON EXISTS is only available in SQL Server and PostgreSQL.
3. New to SQL Server 2022, the third method uses the IS [NOT] DISTINCT FROM clause.

--Method 1
SELECT  *
FROM    ##TableA a INNER JOIN
        ##TableB b ON ISNULL(a.Fruit,'') = ISNULL(b.Fruit,'');
        
--Method 2       
SELECT  *
FROM    ##TableA a INNER JOIN
        ##TableB b ON EXISTS (SELECT a.Fruit INTERSECT SELECT b.Fruit);

--Method 3        
SELECT  *
FROM    ##TableA a,
        ##TableB b
WHERE   a.Fruit IS DISTINCT FROM b.Fruit;

🔵   Table Of Contents

      Semi-joins and anti-joins are two closely related join methods. The semi-join and anti-join are types of joins between two tables where rows from the outer query are returned based upon the presence or absence of a matching row in the joined table.

Anti-joins use the NOT IN or NOT EXISTS operators. Semi-joins use the IN or EXISTS operators.

There are several benefits of using anti-joins and semi-joins over INNER JOINS:

1. Semi-joins and anti-joins remove the risk of returning duplicate rows.
2. Semi-joins and anti-joins increase readability as the result set can only contain the columns from the outer semi-joined table.

There are a few key differences between semi-joins and anti-joins:

1. The NOT IN operator will return an empty set if the anti-join contains a NULL marker. The NOT EXISTS will return a dataset that contains a NULL marker if the anti-join contains a NULL marker.
2. The IN and EXIST operators will return a dataset if the semi-join contains a NULL marker.
3. The IN and NOT IN can take a list of arguments, or an SQL statement. The SQL statement can return a set of values, or it can be a correlated subquery.
4. The EXISTS and NOT EXITS must be a correlated subquery.

🔺      If you are performing an anti-join to a NULLable column, consider using the NOT EXISTS operator over the NOT operator.

NOT IN

This statement returns an empty dataset as the anti-join contains a NULL marker.

SELECT  1 AS RowNumber,
        ID,
        Fruit
FROM    ##TableA
WHERE   Fruit NOT IN ('Banana',NULL);

<Empty Data Set>

IN

The opposite of anti-joins are semi-joins. Using the IN operator, this query will return a result set. A NOT IN operator would return an empty dataset.

SELECT  ID,
        Fruit
FROM    ##TableA
WHERE   Fruit IN ('Apple','Peach',NULL);

EXISTS

EXISTS is much the same as IN. But with EXISTS you must create a correlated subquery.

SELECT  ID,
        Fruit
FROM    ##TableA a
WHERE   EXISTS (SELECT 1 FROM ##TableB b WHERE a.Fruit = b.Fruit AND a.Quantity = b.Quantity);

NOT EXISTS

Here is the usage of the NOT EXISTS. A NULL marker is returned in the dataset (unlike the IN operator).

SELECT  a.ID,
        Fruit
FROM    ##TableA a
WHERE   NOT EXISTS (SELECT 1 FROM ##TableB b WHERE a.Fruit = b.Fruit AND a.Quantity = b.Quantity);

🔵   Table Of Contents

      The SQL standard for set operators does not use the term EQUAL TO or NOT EQUAL TO when describing their behavior. Instead, it uses the terminology of IS [NOT] DISTINCT FROM and the following expressions are TRUE when using set operators.

• NULL is not distinct from NULL
• NULL is distinct from "Apple".

In the following examples, we see the set operators treat the NULL markers differently than the join syntax.

UNION

When using UNION, the SQL standard dictates that duplicate rows should be removed from the result set. When dealing with NULL, however, NULL is considered to be unique and not a duplicate of another NULL. Simply, NULL is not distinct from NULL.

SELECT 1 AS Type, Fruit FROM ##TableA
UNION
SELECT 1 AS Type, Fruit FROM ##TableB;

UNION ALL

The UNION ALL operator returns all values including each NULL marker.

SELECT 1 AS Type, Fruit FROM ##TableA
UNION ALL
SELECT 1 AS Type, Fruit FROM ##TableB;

EXCEPT

The EXCEPT operator treats the NULL markers as being not distinct from each other.

SELECT Fruit FROM ##TableB
EXCEPT
SELECT Fruit FROM ##TableA;

INTERSECT

The INTERSECT returns the following records.

SELECT Fruit FROM ##TableA
INTERSECT
SELECT Fruit FROM ##TableB;

🔵   Table Of Contents

The GROUP BY clause aggregates the NULL markers together.

SELECT  Fruit,
        COUNT(*) AS Count_Star,
        COUNT(Fruit) AS Count_Fruit
FROM    ##TableA
GROUP BY Fruit;

🔵   Table Of Contents

The COUNT and AVG functions have a few nuances to NULL markers as will demonstrate below.

COUNT

The COUNT function removes the NULL markers when a specified field is included in the function but counts the NULL markers when using the asterisk.

SELECT  Fruit,
        COUNT(*) AS Count_Star,
        COUNT(Fruit) AS Count_Fruit
FROM    ##TableA
GROUP BY Fruit;

AVG

      The AVG function will remove records with NULL markers in its calculation, as shown below. For this example, we created the test data in the common table expression cte_Average, as this more easily demonstrates the functions behavior.

In SQL Server, when performing division between integers, you will need to use the CAST or CONVERT function on the values as shown below.

WITH cte_Average AS
(
SELECT 1 AS ID, 100 AS MyValue
UNION 
SELECT 2 AS ID, 100 AS MyValue
UNION 
SELECT 3 AS ID, NULL AS MyValue
UNION 
SELECT NULL AS ID, 100 AS MyValue
)
SELECT  SUM(MyValue) / CAST(COUNT(*) AS NUMERIC(10,2)) AS Average_CountStar,
        SUM(MyValue) / CAST(COUNT(ID) AS NUMERIC(5,2)) AS Average_CountId,
        AVG(CAST(MyValue AS NUMERIC(10,2))) AS Average_AvgFunction
FROM    cte_Average;

🔵   Table Of Contents

SQL provides the following constraints; NOT NULL, PRIMARY KEY, FOREIGN KEY. UNIQUE, and CHECK CONSTRAINTS.

PRIMARY KEYS

The PRIMARY KEY syntax will create a CLUSTERED INDEX unless specified otherwise. The following statements will error as a PRIMARY KEY does not allow for NULL markers.

ALTER TABLE ##TableA
ADD CONSTRAINT PK_NULLConstraints PRIMARY KEY NONCLUSTERED (Fruit);

ALTER TABLE ##TableA
ADD CONSTRAINT PK_NULLConstraints PRIMARY KEY CLUSTERED (Fruit);

❗      Msg 8111, Level 16, State 1, Line 1 Cannot define PRIMARY KEY constraint on nullable column in table ##TableA.

UNIQUE

To demonstrate that a UNIQUE CONSTRAINT allows only one NULL marker we can run the following statement. We add a UNIQUE CONSTRAINT to ##TableB, which already includes a NULL marker in the column Fruit.

ALTER TABLE ##TableB
ADD CONSTRAINT UNIQUE_NULLConstraints UNIQUE (Fruit);
GO

INSERT INTO ##TableB(Fruit) VALUES (NULL);
GO

The second statement produces the following error.

❗      Msg 2627, Level 14, State 1, Line 5 Violation of UNIQUE KEY constraint UNIQUE_NULLConstraints. Cannot insert duplicate key in object dbo.##TableB. The duplicate key value is ().

CHECK CONSTRAINTS

To demonstrate CHECK CONSTRAINTS, let's start by creating a new table with the constraints. The below insert does not error and allows for the operation to take place.

DROP TABLE IF EXISTS ##CheckConstraints;
GO

CREATE TABLE ##CheckConstraints
(
ID            INTEGER,
MyField       INTEGER,
CONSTRAINT Check_NULLConstraints CHECK (MyField > 0)
);
GO

INSERT INTO ##CheckConstraints (ID, MyField) VALUES (1,NULL);
GO

SELECT * FROM ##CheckConstraints;

🔵   Table Of Contents

      Multiple NULL markers can be inserted into the child column or a FOREIGN KEY constraint.

      In SQL Server, a FOREIGN KEY constraint must be linked to a column with either a PRIMARY KEY constraint or a UNIQUE constraint defined on the column. A PRIMARY KEY constraint does not allow NULL markers, but a UNIQUE constraint allows one NULL marker.

      In the below example, we demonstrate that multiple NULL markers can be inserted into a child column that references another column only if the referenced column has a UNIQUE CONSTRAINT on the table.

Referential integrity cannot be created on temporary tables, so for this example we create two tables in the dbo schema named Parent and Child.

DROP TABLE IF EXISTS dbo.Child;
DROP TABLE IF EXISTS dbo.Parent;
GO

CREATE TABLE dbo.Parent
(
ParentID INTEGER PRIMARY KEY
);
GO

CREATE TABLE dbo.Child
(
RowNumber INTEGER IDENTITY(1,1) UNIQUE NOT NULL,
ChildID INTEGER FOREIGN KEY REFERENCES dbo.Parent (ParentID)
);
GO

INSERT INTO dbo.Parent VALUES (1),(2),(3),(4),(5);
GO

INSERT INTO dbo.Child VALUES (1),(2),(NULL),(NULL);
GO

SELECT * FROM dbo.Parent;
SELECT * FROM dbo.Child;

Parent

Child

🔵   Table Of Contents

      A computed column is a virtual column that is not physically stored in a table. A computed column expression can use data from other columns to calculate a value. When an expression is applied to a column with a NULL marker, a NULL marker will be the return value. Here we add the value 2 to a the Quantity field in TableB (which includes a NULL marker in the Quantity field.

SELECT Fruit,
       Quantity + 2 AS QuantityPlus2
FROM   ##TableB;

PERSISTED

      When creating a table with a non-NULLable computed column, you must create the column as PERSISTED, else you will receive the error message:

❗      Only UNIQUE or PRIMARY KEY constraints can be created on computed columns, while CHECK, FOREIGN KEY, and NOT NULL constraints require that computed columns be persisted.

      From this error message we can see there are several rules we need to follow on computed columns if we want to add UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK and NOT NULL constraints.

Here we add a NOT NULL constraint to a PERSISTED computed column and add a PRIMARY KEY to the column.

DROP TABLE IF EXISTS MyComputed;
GO

CREATE TABLE MyComputed
(
Int1 INTEGER NOT NULL,
Int2 INTEGER NOT NULL,
Int3 AS MyInt1 + MyInt2 PERSISTED NOT NULL
);

ALTER TABLE MyComputed ADD PRIMARY KEY CLUSTERED (Int3);

DROP TABLE IF EXISTS MyComputed;
GO

Commands completed successfully.

🔵   Table Of Contents

Besides the IS NULL and IS NOT NULL predicate logic constructs, SQL also provides three functions to help evaluate NULL markers.

1. ISNULL
2. COALESCE
3. NULLIF

COALESCE and ISNULL have several key differences that should be noted. A full comparison can be found here.

The major differences between COALESCE and ISNULL from the documentation are:

1. COALESCE behaves the same as a CASE statement, and therefore can accept multiple parameters and return a NULL marker. ISNULL cannot return a NULL marker and accepts only two parameters.
2. COALESCE determines the type of output based upon data type precedence. ISNULL uses the data type of the first parameter.

COALESCE

• The COALESCE function returns the first non-NULL marker among its arguments. If all values are NULL, COALESCE returns a NULL marker.
• The basic usage of the function is COALESCE(expression [ ,…n ])

ISNULL

• The ISNULL function replaces NULL with the specified replacement value.
• The basic usage of the function is ISNULL(check_expression , replacement_value)

NULLIF

• The NULLIF returns a NULL marker if the two specified expressions are equal.
• The basic usage of the function is NULLIF(expression , expression)

SQL Function Examples

Below I provide a few quick examples to note their behavior.

SELECT  1 AS ID,
        ISNULL(NULL, 'foo') AS fnISNULL,
        COALESCE(NULL, NULL, 'foo') AS fnCOALESCE,
        NULLIF('foo', 'foo') AS fnNULLIF

🔵   Table Of Contents

      A useful feature to combat NULL markers in character fields is by using the empty string. The empty string character is not an ASCII value, and the following function returns a NULL marker for both the empty string and the NULL marker parameters. Also, you would assume the ASCII value for a NULL marker is 0 when reviewing an ASCII code chart, however this is not the case. SQL does not use the standard ANSI NULL marker.

SELECT  1 AS ID,
        ASCII('') AS ASCII_EmptyString,
        ASCII(NULL) AS ASCII_NULL;

ISNULL

Any queries that join on a field with an empty string will equate to true. Commonly the empty string is used with the ISNULL function, such as the following query.

SELECT  a.ID,
        a.Fruit,
        b.ID,
        b.Fruit
FROM    ##TableA a INNER JOIN
        ##TableB b ON ISNULL(a.Fruit,'') = ISNULL(b.Fruit,'');

🔵   Table Of Contents

❗      In SQL Server, the SET CONCAT_NULL_YIELDS_NULL database setting controls whether concatenation results are treated as NULL or empty string values. In a future version of SQL Server CONCAT_NULL_YIELDS_NULL will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

The CONCAT function will return an empty string if all the values are NULL.

SELECT 1 AS ID, CONCAT(NULL,NULL,NULL) AS fnConcat;

CONCAT Implicit Data Type Conversion

      This feature of CONCAT is especially helpful when you want to join to a table on multiple fields (with multiple data types) where NULL markers and empty strings are not used consistently in the tables, as shown below. You can include fields with different data types into the CONCAT function as this function performs an implicit data type conversion.

WITH
cte_Values AS 
(
SELECT NULL AS A,
       NULL AS B,
       ''   AS C,
       ''   AS D
)
SELECT v1.A, v2.B, v2.C, v2.D
FROM   cte_Values v1 INNER JOIN 
       cte_Values v2 ON CONCAT(v1.A, v1.B) = CONCAT(v2.C,v2.D);

In query editors NULLS and empty strings will appear different (by default, SSMS shows yellow for NULLS and a blank for empty strings).

🔵   Table Of Contents

      When creating a view, if you perform a CAST function or create a computed column on a column which has a NOT NULL constraint, the result will yield a NULLable column.

Here we create a table with NOT NULL constraints and then create a view where we perform a CAST and create a computed column. From the resulting screen shot, we can see the columns are NULLable.

DROP TABLE IF EXISTS MyTable;
GO

CREATE TABLE MyTable
(
MyInteger INT NOT NULL,
MyVarchar VARCHAR(100) NOT NULL,
MyDate DATE NOT NULL
);
GO

CREATE OR ALTER VIEW vwMyTable AS
SELECT  MyInteger,
        MyVarchar,
        MyDate,
        CAST(MyInteger AS INT) AS MyInteger_Cast,
        CAST(MyVarchar AS VARCHAR(100)) AS MyVarchar_Cast,
        CAST(MyDate AS DATETIME) AS MyDate_Cast,
        MyInteger * 10 AS MyInteger_Computed
FROM    MyTable;
GO

SELECT  c.name AS ColumnName,
        ty.name as DataType,
        c.is_nullable
FROM    sys.views t INNER JOIN
        sys.columns c ON t.object_id = c.object_id INNER JOIN
        sys.types ty ON ty.user_type_id = c.user_type_id
WHERE   t.name = 'vwMyTable' and c.is_nullable = 1
ORDER BY 1,2,3;

🔵   Table Of Contents

Here we will discuss two SQL constructs, the BIT data type and the NOT operator.

❗      Much like NULL markers and duplicate tuples, there is much debate in the SQL community if the BIT data type should be a permissible data type, as its allowance for the NULL marker does not mimic the real world. Joe Celko and C.J. Date advocate against using the BIT data type and give further details of this in many of their writings.

BIT

      Often, we think of the BIT data type as being a Boolean value (true or false, yes or no, on or off, one or zero…), however NULL markers are allowed for the BIT data type making the possible values 1, 0 and NULL. The BIT data type in SQL is not a true Boolean value.

      Because the only acceptable values for the BIT data type is 1, 0 or NULL. The BIT data type converts any nonzero value to 1. As discussed earlier, the NULL marker is neither a nonzero value nor a zero value, so it is not promoted to the value of 1. Here we can demonstrate that behavior.

SELECT 1 AS ID, CAST(NULL AS BIT) AS Bit
UNION
SELECT 2 AS ID, CAST(3 AS BIT) AS Bit;

NOT

      The NOT operator negates a Boolean input.

      Here we can see how the NOT operator acts on our test data. Placing the NOT operator on the predicate logic will not return a NULL marker.

--15.2
SELECT  *
FROM    ##TableA
WHERE   NOT(FRUIT = 'Mango');

🔵   Table Of Contents

      The RETURN statement exists unconditionally from a query or procedure. All stored procedures return a value of 0 for a successful execution, and a nonzero value for a failure. When the RETURN statement is used with a stored procedure, it cannot return a NULL marker. If a procedure tries to return a NULL marker in the RETURN statement, a warning message is generated and a value of 0 is returned.

Here we will create a stored procedure that overrides the default RETURN value and attempt to return a NULL marker.

CREATE OR ALTER PROCEDURE SpReturnStatement
AS
IF 1=2
    RETURN 1
ELSE
    RETURN NULL;
GO

DECLARE @return_status INT;

EXEC @return_status = SpReturnStatement;
SELECT 'Return Status' = @return_status;

The SpReturnStatement procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

🔵   Table Of Contents

      In some relational databases like MYSQL, when you insert a NULL marker into an identity column, the identity column will take the next available value and not error. However, in Microsoft SQL Server, an error is produced.

CREATE TABLE ##Identity 
(
Id INTEGER IDENTITY(1,2)
);

INSERT INTO ##Identity(Id) VALUES (NULL);

SELECT  Id
FROM    ##Identity;           

The SQL Server error returned is.

Msg 339, Level 16, State 1, Line 6 DEFAULT or NULL are not allowed as explicit identity values.

🔵   Table Of Contents

      Over the course of this document, we have touched on many of the SQL constructs and how they treat NULL markers. I hope this document serves as a guiding document for future development, and most importantly, always remember to include NULL markers in your test data.

      The most important concept to understand with NULL markers is the three-valued logic, where statements can equate to TRUE, FALSE, or UNKNOWN. Understanding the three-valued logic is instrumental in understanding the behavior of NULL markers. TRUE OR UNKNOWN equates to TRUE, and TRUE AND UNKNOWN equates to UNKNOWN.

📫      If you find any inaccuracies, misspellings, bugs, dead links, etc. please report an issue! No detail is too small, and I appreciate all the help.

😄      Happy coding!

https://advancedsqlpuzzles.com