IS

MySQL

Example

SELECT city, population
FROM cities
WHERE population IS NULL;

Output

city        | population
------------|-----------
Sacramento  | NULL
Riverside   | NULL

Explanation

The SQL query above checks the table cities for records where the population field is NULL. Two cities, Sacramento and Riverside have NULL values in the population field.

SQL Server

Example

SELECT EmployeeID, FullName
FROM Employees
WHERE Country IS NULL;

Output

| EmployeeID | FullName |
|------------|----------|
| 7          | Alice    |
| 8          | Bob      |

Explanation

In the given example, the IS keyword is used in the WHERE clause to filter the rows for which the Country field is NULL. As a result, the output displays the EmployeeID and FullName of those Employees who do not have a country specified in the Employees table.

Oracle

Example

SELECT
    CASE
        WHEN JOB_ID IS NULL THEN 'No Job'
        ELSE JOB_ID
    END AS JOB_STATUS
FROM
    EMPLOYEES
WHERE
    LAST_NAME = 'Williams';

Output

| JOB_STATUS |
|------------|
| No Job  |

Explanation

In the example provided, a CASE statement is used within a SELECT statement to manipulate the output of the query. If the JOB_ID is NULL for employee ‘Williams’, the output would be ‘No Job’. If there is a JOB_ID for ‘Williams’, the JOB_ID will be displayed as the output. This IS keyword is used to check whether the JOB_ID column in the given row IS NULL.

SQLite

Example

CREATE TABLE Sales (
    ID INTEGER PRIMARY KEY,
    Price INTEGER NOT NULL,
    IsSold INTEGER DEFAULT 0
);

INSERT INTO Sales (Price) VALUES (150), (200), (250);

SELECT * FROM Sales WHERE IsSold IS NULL;

Output

ID | Price | IsSold
---|-------|--------

Explanation

The example demonstrates the use of the IS operator in SQL, in particular SQLite. The IS operator is used to compare a value with null. In this case, it is used in the SELECT statement to retrieve records where the IsSold column is null. Since a default value was set for IsSold during table creation and none of the inserted records has null for IsSold, the resulting table is empty.