Specific use of three-value logic and NULL in SQL

In SQL, three-value logic is an important concept, and its existence is mainly due toNULLIntroduction of values.NULLRepresents an unknown value, which is neither an empty string nor a number 0, but a special tag indicating that data is missing or unavailable.

In SQL, becauseNULLThe existence of a value leads to it using a special logical method:Three-Valued Logic (3VL)

It includes three possible results of any logical calculation:

• TRUE (true)

• FALSE (Fake)

• UNKNOWN (Unknown)

NULLIn SQL, it means "unknown" or "missed value", which is very different from normal values. becauseNULLIndicates unknown value, so any withNULLThe result of the calculation should beUNKNOWN, not TRUE or FALSE.

Features of NULL

NULLIt is not a specific value, but a special state, which has the following characteristics:

• NULLCan't compare with each other:NULL = NULLThe result is not TRUE, but UNKNOWN.

• NULLParticipate in numerical operations, the result isNULL：NULL + 10The result is stillNULL。

• NULLParticipating in logical operations will affect the logical results:

  • TRUE AND NULL,turn outUNKNOWN

  • FALSE OR NULL,turn outUNKNOWN

If the WHERE condition result isUNKNOWN, then the record will not be included in the query result.

The influence of NULL in SQL logical operations

1. Logical operations (AND, OR, NOT)

AND operation

OR operation

NOT operation

2. NULL participate in comparison (=, !=, >, <, etc.)

3. The impact of NULL in IN and NOT IN

ifNULLAppears inINorNOT INIn a statement, unpredictable results will be caused:

SELECT * FROM users WHERE age IN (20, 30, NULL);

becauseNULLIt is an unknown value, SQL does not knowNULLWhether it belongs toage,lead toUNKNOWN, the final query will only matchage=20andage=30, but will not matchNULL。

More serious problems arise inNOT INmiddle:

SELECT * FROM users WHERE age NOT IN (20, 30, NULL);

becauseNULLexistINThe statement will returnUNKNOWN, the wholeNOT INBecomeUNKNOWN, no data will be returned in the end.

Solution:

SELECT * FROM users WHERE age NOT IN (20, 30) OR age IS NULL;

4. NULL in DISTINCT, GROUP BY and ORDER BY

• DISTINCTSeeNULLis the same value:

  SELECT DISTINCT category FROM products;

  ifcategoryThere are multiple columnsNULL，DISTINCTOnly one will be retainedNULL。

• GROUP BYSeeNULLFor a group:

  SELECT category, COUNT(*) FROM products GROUP BY category;

  allNULLValues ​​will be classified into the same group.

• ORDER BYdeal withNULL：

  SELECT * FROM employees ORDER BY salary ASC;

  NULLThe default is the first or last, and the specific behavior depends on the database:

  • PostgreSQL: NULLS FIRSTorNULLS LAST

  • MySQL: NULLThe default is the top

  • SQL Server: NULLThe default is the top

5. NULL is handled in COALESCE and IFNULL

To avoidNULLInfluence query, you can useCOALESCEorIFNULLProcessing:

• COALESCE(expr1, expr2, ..., exprN): Return the first non-NULL value

  SELECT name, COALESCE(email, 'unknown') AS email FROM users;

• IFNULL(expr, default_value)(Special for MySQL)

  SELECT name, IFNULL(email, 'unknown') AS email FROM users;

The impact of NULL in JOIN

ifNULLExist inJOINIn the associated column, the row will not be matched:

SELECT * FROM orders
LEFT JOIN customers ON orders.customer_id = ;

iforders.customer_idyesNULL，= NULLturn outUNKNOWN,lead toINNER JOINfail.

LEFT JOINCan be retainedordersbutcustomersThe data isNULL。

How to handle NULL correctly

• Use IS NULL and IS NOT NULL when querying

  SELECT * FROM users WHERE email IS NULL;

• Avoid NULL affecting logical operations

  SELECT * FROM orders WHERE discount IS NULL OR discount > 10;

• Consider the possible problems that NULL can bring in JOIN

  SELECT * FROM orders
  LEFT JOIN customers ON orders.customer_id = 
  WHERE  IS NOT NULL;

• Use COALESCE() to process NULL

  SELECT name, COALESCE(email, 'unknown') AS email FROM users;

• Use NOT IN correctly

  SELECT * FROM users WHERE age NOT IN (20, 30) OR age IS NULL;

Summary (key point)

• NULL represents unknown, not an empty string or 0.
• SQL uses three-value logic (TRUE, FALSE, UNKNOWN), which causes NULL to return UNKNOWN when participating in operations.
• NULL cannot be compared directly with =, but should use IS NULL and IS NOT NULL.
• NULL may affect queries such as JOIN, GROUP BY, ORDER BY, IN/NOT IN, and must be handled with caution.
• Using COALESCE(), IFNULL() and other functions can avoid the problems caused by NULL.

This is the article about the specific use of three-value logic and NULL in SQL. For more related SQL three-value logic and NULL content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!