PostgreSQL IS NULL Query

PostgreSQL: Using NULL and Not NULL clauses

Unknown values of the database table are treated differently by SQL. When one or more fields of a table needs to be set blank then the NULL keyword is used at the time of table creation and NULL value can be used for selecting or inserting or updating data of the table. NULL value does not indicate zero or empty value. It is mainly used for that field where the value is missing or unknown or no value is applicable. But if any field of a table needs to set mandatory then you have to use NOT NULL keyword during the time of table creation. How you can use NULL and NOT NULL clauses in PostgreSQL to run select, insert, update and delete SQL statements are shown in this tutorial.

Create table using NULL and NOT NULL

CREATE TABLE company
( company_id int NOT NULL PRIMARY KEY,
name varchar(40) NOT NULL,
address varchar(150),
phone char(20),
country varchar(50),
website_url varchar(50) NULL );

CREATE TABLE items
( item_id SERIAL,
name varchar(40) DEFAULT('Not Assign'),
quantity INT NULL,
company_id INT,
PRIMARY KEY (item_id),
FOREIGN KEY (company_id) REFERENCES company(company_id) );

Insert Some Data in tables:

Insert into Company table:

INSERT INTO company (company_id, name, address, phone, country, website_url)
VALUES (1, 'Samsung', '123....','+337277888', 'Korea', 'www.samsung.com');
INSERT INTO company (company_id, name, address, phone, country, website_url)
VALUES (2, 'Symphony', '67/A ….', '+42343567', 'Chaina', 'www.symphony.com');
INSERT INTO company (company_id, name, address, phone, country)
VALUES (3, 'LG', '45/B ….', '', 'Japan');

Insert into items table:

INSERT INTO items (name, quantity, company_id)
VALUES ( 'LG 122', 4000,3 );
INSERT INTO items (name, quantity, company_id)
VALUES ( 'Samsung 460', 7000, 1 );
INSERT INTO items (name, quantity, company_id)
VALUES ( 'Symphony E80', 2200,2 );

Example-1: Using NULL and NOT NULL in SELECT Query

a) NULL

The following query will retrieve all name and address data from company table where website_url value is null. There is only one record where the website_url value is NULL.

SELECT name, address
FROM company
WHERE website_url is NULL;

b) NOT NULL

The output of NOT NULL is opposite of NULL. The following select query will return all records from company table where website_url field contains any data.

SELECT name, phone
FROM company
WHERE website_url is NOT NULL;

Example-2: Using NULL or NOT NULL in INSERT Query

The following query will insert company_id value from company table to items table which has no website_url value. There is one record in company where website_url is NULL. So, one record will be inserted after executing the query.

Insert into items
(company_id)
SELECT company_id
FROM company
WHERE website_url is NULL;

Example-3: Using NULL in UPDATE Query

name field value of items table will be updated which record contains NULL in quantity field. According to the data, one record will be updated after executing the query.

UPDATE items
SET name = ‘New Value’
WHERE quantity NULL;

Example-4: Using NULL or NOT NULL in UPDATE Query

The following query will delete records from items table where quantity value is NULL. There is only one record in items table where quantity value is NULL. So, one record will be deleted after executing the query.

DELETE from items
WHERE quantity is NULL;

You can apply NULL and NOT NULL clauses with WHERE clause for executing any query based on the table if the table contains any NULL value. But NULL value and empty string are not identical. If you create any field of the table without NULL option and keep empty data in that field then NULL clause will not work for that field. You can apply NULL clause for any table based on the table structure.