[Basic Grammar] SQL NULL Values

NULL value is a field of no value. 

 

• Test for NULL Values

• IS NULL Syntax 

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

• IS NOT NULL Syntax

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

 

• IS NULL Operator 

The NULL operator is used to test for empty values which is NULL Values.

 

Example 

Write SQL lists all guests with a NULL values in the "City" field:

SELECT Country, City, Address
FROM Guests 
WHERE City IS NULL;

** Tip: Always use IS NULL to look for NULL values**

 

 

• IS NOT NULL Operator 

It is used to test for non-empty values (NOT NULL values).

 

Example

Write SQL lists all guests with a value in the "Country" field:

SELECT Country, City, Address
FROM Guests 
WHERE City IS NOT NULL;

 

Exercises

1. Retrieve all products from the "Products" table where the "Description" column is empty.

SELECT * FROM Products 
WHERE Description IS NULL;

 

2. Select all orders from the "Orders" table where the "ShipDate" column is not specified (empty).

SELECT * FROM Orders 
WHERE ShipDate IS NULL;

 

3. Write an SQL query to select all customers from the "Customers" table who have a non-empty value in the "Address" field.

SELECT * FROM Customers 
WHERE Address IS NOT NULL;