PostgreSQL IS NULL Query

28/12/2020

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.

ONET IDC thành lập vào năm 2012, là công ty chuyên nghiệp tại Việt Nam trong lĩnh vực cung cấp dịch vụ Hosting, VPS, máy chủ vật lý, dịch vụ Firewall Anti DDoS, SSL… Với 10 năm xây dựng và phát triển, ứng dụng nhiều công nghệ hiện đại, ONET IDC đã giúp hàng ngàn khách hàng tin tưởng lựa chọn, mang lại sự ổn định tuyệt đối cho website của khách hàng để thúc đẩy việc kinh doanh đạt được hiệu quả và thành công.
Bài viết liên quan

PostgreSQL Full Text Search Tutorial

Full Text Search is supported in PostgreSQL. Full Text Search is used by search engines, shops, and many other websites...
28/12/2020

PostgreSQL vs MySQL 2018

In this article, we compare one of the most popular database management systems (DBMS) in the world, MySQL, with DB-Engines’...
28/12/2020

PostgreSQL to Manage JSON

One of the many data types that PostgreSQL support is JSON. Since most of the web APIs communication uses JSON payload...
29/12/2020