Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.mysqltutorial.org/mysql-basics/mysql-is-null/ below:

MySQL IS NULL Operator

Summary: in this tutorial, you will learn how to use the MySQL IS NULL operator to test whether a value is NULL or not.

To test whether a value is NULL or not, you use the  IS NULL operator.

Here’s the basic syntax of the IS NULL operator:

value IS NULLCode language: SQL (Structured Query Language) (sql)

If the value is NULL, the expression returns true. Otherwise, it returns false.

Note that MySQL does not have a built-in BOOLEAN type. It uses the TINYINT(1) to represent the BOOLEAN values i.e., true means 1 and false means 0.

Because the IS NULL is a comparison operator, you can use it anywhere that an operator can be used e.g., in the SELECT or WHERE clause.

See the following example:

SELECT 1 IS NULL,  
       0 IS NULL,  
       NULL IS NULL; Code language: SQL (Structured Query Language) (sql)

To check if a value is not NULL, you use IS NOT NULL operator:

value IS NOT NULLCode language: SQL (Structured Query Language) (sql)

This expression returns true (1) if the value is not NULL. Otherwise, it returns false (0).

Consider the following example:

SELECT 1 IS NOT NULL, 
       0 IS NOT NULL, 
       NULL IS NOT NULL; Code language: SQL (Structured Query Language) (sql)

We will use the customers table in the sample database for the demonstration:

The following query uses the IS NULL operator to find customers who do not have a sales representative:

SELECT 
    customerName, 
    country, 
    salesrepemployeenumber
FROM
    customers
WHERE
    salesrepemployeenumber IS NULL
ORDER BY 
    customerName;Code language: SQL (Structured Query Language) (sql)

Output:

+--------------------------------+--------------+------------------------+
| customerName                   | country      | salesrepemployeenumber |
+--------------------------------+--------------+------------------------+
| ANG Resellers                  | Spain        |                   NULL |
| Anton Designs, Ltd.            | Spain        |                   NULL |
| Asian Shopping Network, Co     | Singapore    |                   NULL |
| Asian Treasures, Inc.          | Ireland      |                   NULL |
...Code language: PHP (php)

This example uses the IS NOT NULL operator to get the customers who have a sales representative:

SELECT 
    customerName, 
    country, 
    salesrepemployeenumber
FROM
    customers
WHERE
    salesrepemployeenumber IS NOT NULL
ORDER BY 
   customerName;Code language: SQL (Structured Query Language) (sql)

Output:

+------------------------------------+-------------+------------------------+
| customerName                       | country     | salesrepemployeenumber |
+------------------------------------+-------------+------------------------+
| Alpha Cognac                       | France      |                   1370 |
| American Souvenirs Inc             | USA         |                   1286 |
| Amica Models & Co.                 | Italy       |                   1401 |
| Anna's Decorations, Ltd            | Australia   |                   1611 |
...

• Use the IS NULL operator to test if a value is NULL or not. The IS NOT NULL operator negates the result of the IS NULL operator.
• The value IS NULL returns true if the value is NULL or false if the value is not NULL.
• The value IS NOT NULL returns true if the value is not NULL or false if the value is NULL.

Was this tutorial helpful?

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4