Skip to content
Tutorials Center
My learning

Topics

Lesson 9 of 40 ยท SQL Basics

NULL, Variables and Quoting in MariaDB

Handle NULL correctly in MariaDB (IS NULL, COALESCE, IFNULL, NULL-safe equals), set columns to NULL, use user-defined and local variables, and quote strings and identifiers the right way.

Three small topics cause a surprising share of real-world bugs: NULL, variables and quotes. NULL means "unknown" and does not behave like zero or an empty string. Variables let you carry values between statements. Quotes decide whether text is a value or a name. This lesson makes all three predictable.

What NULL means

NULL is the absence of a value. It is not 0, not '' and not equal to itself. Any comparison with NULL yields NULL (unknown), and a WHERE clause keeps only rows where the condition is true:

SQL
SELECT NULL = NULL;     -- NULL, not 1
SELECT NULL <> 1;       -- NULL
SELECT 1 + NULL;        -- NULL
SELECT NULL IS NULL;    -- 1

This three-valued logic gives the classic surprises:

  • WHERE price = NULL matches nothing. Use IS NULL or IS NOT NULL.
  • WHERE status <> 'cancelled' drops rows where status is NULL.
  • COUNT(column) ignores NULL values, while COUNT(*) counts rows.
  • NOT IN (1, 2, NULL) is never true.

MariaDB has a NULL-safe equality operator, <=>, which returns 1 when both sides are NULL and 0 when only one is:

SQL
SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;   -- 1, 1, 0
SQL NULL comparisons ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Replacing NULL with a value

Several functions substitute a default for NULL:

SQL
SELECT IFNULL(phone, 'n/a') FROM contacts;            -- MariaDB/MySQL
SELECT COALESCE(mobile, phone, email, 'unknown') FROM contacts;   -- standard: first non-NULL
SELECT NULLIF(stock, 0) FROM products;                -- turns 0 into NULL (avoids divide-by-zero)

COALESCE takes any number of arguments and is portable. NULLIF(a, b) returns NULL when the two are equal, and is the idiom for safe division: total / NULLIF(count, 0).

SQL COALESCE and NULLIF ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Setting a value to NULL

Assign it like any other value, as long as the column allows it:

SQL
UPDATE employees SET manager_id = NULL WHERE id = 7;
INSERT INTO employees (first_name, last_name, manager_id) VALUES ('Ana', 'Cruz', NULL);
ALTER TABLE employees MODIFY phone VARCHAR(20) NULL;   -- allow NULL

A NOT NULL column rejects it with an error in strict mode. The difference between "unknown" (NULL) and "known to be empty" ('' or 0) is a design decision, so decide it per column and stay consistent.

SQL Set a column to NULL

Press Run (or Ctrl + Enter) to see the result here.

User-defined variables

A user variable starts with @, needs no declaration, lives for your connection and holds a scalar value:

SQL
SET @tax_rate = 0.2;
SET @today = CURRENT_DATE, @limit = 10;
SELECT name, price * (1 + @tax_rate) AS gross FROM products LIMIT @limit;   -- error: LIMIT needs a constant
SELECT @max := MAX(price) FROM products;     -- assign inside a query
SELECT name FROM products WHERE price = @max;

Details to remember:

  • SET @x = value assigns; SELECT @x := value assigns and returns the value. Within SELECT use :=, because = is a comparison there.
  • An unset variable is NULL. Types follow the last value assigned.
  • Variables are per connection. They vanish when you disconnect, and a connection pool may hand your session to another request, so never rely on them across requests.
  • A common use is a running counter: SET @n := 0; SELECT @n := @n + 1 AS rank, name FROM products ORDER BY price DESC;. Today you should prefer window functions (ROW_NUMBER()), which are reliable and clearer.

Local variables and system variables

Inside a stored program (a procedure, function or trigger) you declare typed local variables with DECLARE, at the top of the block:

SQL
DELIMITER //
CREATE PROCEDURE price_report()
BEGIN
  DECLARE v_total DECIMAL(12,2) DEFAULT 0;
  DECLARE v_count INT;
  SELECT SUM(price), COUNT(*) INTO v_total, v_count FROM products;
  SELECT v_total AS total, v_count AS items, v_total / NULLIF(v_count, 0) AS average;
END //
DELIMITER ;

Local variables have no @, a declared type, and exist only while the block runs. This is covered in depth in the stored programs chapter.

System variables configure the server, with @@ in front:

SQL
SELECT @@version, @@sql_mode, @@autocommit;
SET SESSION sql_mode = 'STRICT_ALL_TABLES';   -- this connection
SET GLOBAL max_connections = 300;             -- needs privileges, resets on restart

Quoting

Quote characters have different jobs:

Quote Use Example
'single' String and date values WHERE name = 'O''Brien'
`backticks` Identifiers (table, column names) SELECT `order` FROM `select`
"double" Strings by default; identifiers if ANSI_QUOTES is set avoid

To put a single quote inside a string, double it ('O''Brien') or escape it ('O\'Brien'). Backticks are required when a name is a reserved word (order, group, desc) or has spaces, and the best fix is to avoid such names. To include a backtick inside a name, double it.

Never build SQL by gluing user input into a string. Use prepared statements in your driver; they send the value separately, so quotes in the data cannot change the query. That also fixes quoting troubles for good.

SQL Quotes inside strings

Press Run (or Ctrl + Enter) to see the result here.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does SELECT NULL = NULL return?

    Comparing unknown with unknown gives unknown (NULL). Use IS NULL, or the NULL-safe operator <=>.
  2. 2. Which function returns its first non-NULL argument?

    COALESCE accepts any number of arguments and is standard SQL. IFNULL is the two-argument MariaDB version.
  3. 3. How long does a user variable such as @total live?

    User variables are session-scoped. They disappear when the connection closes.
  4. 4. How do you write the text O'Brien in a MariaDB string?

    A doubled single quote represents one quote inside a single-quoted string; a backslash also works.
  5. 5. Why is COUNT(manager_id) smaller than COUNT(*) when some managers are missing?

    COUNT(column) counts only non-NULL values, whereas COUNT(*) counts rows.

Summary

  • NULL is "unknown": compare with IS NULL / <=>, and expect NULL from arithmetic and comparisons with it.
  • COALESCE, IFNULL and NULLIF give defaults and avoid division by zero.
  • @name variables are per-connection; DECLARE variables belong to stored programs; @@name are system settings.
  • Use single quotes for values, backticks for identifiers, and prepared statements for user input.

Next: Grouping and aggregate functions