CODEWITHSUNDEEP
sqlnull
Jessica O'Connor
Jessica O'Connor

Reputation: 11

How to filter out null values

The results of my SQL query include null values. How do I filter out null values?

Upvotes: 0

Views: 2580

Answers (2)

Geysa O. Marinho
Geysa O. Marinho

Reputation: 79

My example works on every database I know, so it should work for you =)

SELECT *
FROM TABLE_NAME
WHERE COLUMN_NAME IS NOT NULL

Here you can find a simple explanation and some examples: https://www.w3schools.com/sql/sql_null_values.asp

But some times you want to replace null values for a default value, like 'X', in this case, we should know the database for correct syntax, here some examples:

Oracle:

SELECT nvl(column_name,'X')
FROM TABLE_NAME

Sqlite:

SELECT ifnull(column_name,'X')
FROM TABLE_NAME

SqlServer:

SELECT coalesce(column_name,'X')
FROM TABLE_NAME

Upvotes: 2

Max888
Max888

Reputation: 142

The syntax may vary depending on the database you are using but you can explicitly exclude nulls in the where clause. For example, the following will exclude null values in the primary_author field:

SELECT
  date,
  primary_author,
  ISBN
FROM
  books
WHERE
  primary_author IS NOT NULL;

Upvotes: 1

Related Questions