{"id":413295,"date":"2024-02-28T16:22:18","date_gmt":"2024-02-29T00:22:18","guid":{"rendered":"https:\/\/linuxhint.com\/?p=413295"},"modified":"2024-02-29T18:16:23","modified_gmt":"2024-03-01T02:16:23","slug":"sql-is-not-null","status":"publish","type":"post","link":"https:\/\/linuxhint.com\/sql-is-not-null\/","title":{"rendered":"SQL \u201cIs Not Null\u201d Operator"},"content":{"rendered":"
\n

In SQL, we can use the IS NOT NULL operator to filter out the results from a database table where the value of a specific column is not null. This can help to ensure that you get a clean data that does not contain any missing values which can cause problems especially in functions that do not handle NULL values.<\/p>\n

A NULL value refers to the absence of data which does not incorporate the empty strings, numerical zero, or NaN.<\/p>\n

In this tutorial, we will learn how we can use the IS NOT NULL operator to filter out the results that contain NULL values in a given query.<\/p>\n

Syntax:<\/h2>\n

We can express the basic syntax of the IS NOT NULL condition as shown in the following code snippet:<\/p>\n

SELECT column1, column2, ...
\nFROM table_name
\nWHERE column_name IS NOT NULL;<\/div><\/div>\n

 
\nWe start with the \u201cselect\u201d keyword and specify the columns that we wish to retrieve in the result set followed by the name of the table from which we wish to fetch the data.<\/p>\n

Next, we use the WHERE clause to introduce the data filtering based on a specific column. Finally, we specify the condition on which we wish to filter.<\/p>\n

Example 1:<\/h2>\n

Let us look at some examples on how we can use the IS NOT NULL condition to filter out the results.<\/p>\n

Consider the Sakila sample database which is available freely for download on the official MySQL page.<\/p>\n

Suppose we wish to retrieve the name of customers whose \u201clast_name\u201d is not equal to null. We can use the query as shown in the following to accomplish this:<\/p>\n

select<\/span>
\n    *<\/span>
\nfrom
\n    customer c
\nwhere
\n    last_name is not null;<\/div><\/div>\n

 
\nIn this case, the query returns all the rows from the customer table where the value of the \u201clast_name\u201d column is not null.<\/p>\n

Example 2: AND and OR Operators<\/h2>\n

We can combine the IS NOT NULL condition with other conditions using the AND and OR operators. This creates a more granular filtering.<\/p>\n

For example, suppose we want to retrieve the customers whose \u201clast_name\u201d is not null and whose first name is Nancy or Holly.<\/p>\n

We can use a query as demonstrated in the following:<\/p>\n

SELECT
\n    customer_id,
\n    first_name,
\n    last_name,
\n    email
\nFROM
\n    customer
\nWHERE
\n    last_name IS NOT NULL
\n    AND (<\/span>first_name = 'Nancy'<\/span>
\n        OR first_name = 'Holly'<\/span>)<\/span>;<\/div><\/div>\n

 
\nIn this query, we are combining the IS NOT NULL condition with the AND and OR operators to filter out the records where the last name is not null or the first name is equal to either Nancy or Holly.<\/p>\n

The resulting output is as follows:<\/p>\n

\"\"
\nAs you can see, this provides a way of performing a more granular and miniscule filtering on the target data.<\/p>\n

Example 3: Using the Aggregate Functions<\/h2>\n

We can also use the IS NOT NULL function in conjunction with the SQL aggregate functions. For example, we can use it with the count() function to count the number of non-null values in a given column.<\/p>\n

For example, suppose we want to determine the number of customers with non-null email addresses. We can use the query as follows:<\/p>\n

SELECT
\n    COUNT(<\/span>customer_id)<\/span> AS total
\nFROM
\n    customer
\nWHERE
\n    email IS NOT NULL;<\/div><\/div>\n

 
\nThis should return the number of non-null values in the column as a numerical value as follows:<\/p>\n

total|<\/span>
\n-----+
\n  599<\/span>|<\/span><\/div><\/div>\n

 <\/p>\n

Conclusion<\/h2>\n

In this tutorial, we learned all about the IS NOT NULL condition in SQL to filter out the null values from a given result set or database table. We also learned how we can create a more complex filtering by combining the IS NOT NULL condition with other conditions using the AND and OR operators.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Tutorial on how we can use the IS NOT NULL operator to filter out the results that contain NULL values in a result set or database table along with examples.<\/p>\n","protected":false},"author":111,"featured_media":413305,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_wp_convertkit_post_meta":{"form":"-1","landing_page":"","tag":"0","restrict_content":"0"},"footnotes":""},"categories":[2451],"tags":[],"class_list":["post-413295","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-standard"],"_links":{"self":[{"href":"https:\/\/linuxhint.com\/wp-json\/wp\/v2\/posts\/413295","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/linuxhint.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/linuxhint.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/linuxhint.com\/wp-json\/wp\/v2\/users\/111"}],"replies":[{"embeddable":true,"href":"https:\/\/linuxhint.com\/wp-json\/wp\/v2\/comments?post=413295"}],"version-history":[{"count":0,"href":"https:\/\/linuxhint.com\/wp-json\/wp\/v2\/posts\/413295\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/linuxhint.com\/wp-json\/wp\/v2\/media\/413305"}],"wp:attachment":[{"href":"https:\/\/linuxhint.com\/wp-json\/wp\/v2\/media?parent=413295"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/linuxhint.com\/wp-json\/wp\/v2\/categories?post=413295"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/linuxhint.com\/wp-json\/wp\/v2\/tags?post=413295"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}