Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://docs.snowflake.com/sql-reference/functions/is-null below:

Website Navigation

• Reference
• Function and stored procedure reference
• Conditional expression

IS [ NOT ] NULL

Categories:

Conditional expression functions

Determines whether an expression is NULL or is not NULL.

Returns a BOOLEAN.

• When IS NULL is specified, the value is TRUE if the expression is NULL. Otherwise, returns FALSE.

• When IS NOT NULL is specified, the value is TRUE if the expression is not NULL. Otherwise, returns FALSE.

Create the test_is_not_null table and load the data:

CREATE OR REPLACE TABLE test_is_not_null (id NUMBER, col1 NUMBER, col2 NUMBER);
INSERT INTO test_is_not_null (id, col1, col2) VALUES 
  (1, 0, 5), 
  (2, 0, NULL), 
  (3, NULL, 5), 
  (4, NULL, NULL);

Copy

Show the data in the test_is_not_null table:

SELECT * 
  FROM test_is_not_null
  ORDER BY id;

Copy

+----+------+------+
| ID | COL1 | COL2 |
|----+------+------|
|  1 |    0 |    5 |
|  2 |    0 | NULL |
|  3 | NULL |    5 |
|  4 | NULL | NULL |
+----+------+------+

Use IS NOT NULL to return the rows for which the values in col1 are not NULL:

SELECT * 
  FROM test_is_not_null 
  WHERE col1 IS NOT NULL
  ORDER BY id;

Copy

+----+------+------+
| ID | COL1 | COL2 |
|----+------+------|
|  1 |    0 |    5 |
|  2 |    0 | NULL |
+----+------+------+

Use IS NULL to return the rows for which the values in col2 are NULL:

SELECT * 
  FROM test_is_not_null 
  WHERE col2 IS NULL
  ORDER BY id;

Copy

+----+------+------+
| ID | COL1 | COL2 |
|----+------+------|
|  2 |    0 | NULL |
|  4 | NULL | NULL |
+----+------+------+

Use a combination of IS NOT NULL and IS NULL to return the rows for which either of the following conditions is met:

• The values in col1 are not NULL.

• The values in col2 are NULL.

SELECT * 
  FROM test_is_not_null 
  WHERE col1 IS NOT NULL OR col2 IS NULL
  ORDER BY id;

Copy

+----+------+------+
| ID | COL1 | COL2 |
|----+------+------|
|  1 |    0 |    5 |
|  2 |    0 | NULL |
|  4 | NULL | NULL |
+----+------+------+

Use a combination of IS NOT NULL and IS NULL to return the rows for which both of the following conditions are met:

• The values in col1 are not NULL.

• The values in col2 are NULL.

SELECT *
  FROM test_is_not_null
  WHERE col1 IS NOT NULL AND col2 IS NULL
  ORDER BY id;

Copy

+----+------+------+
| ID | COL1 | COL2 |
|----+------+------|
|  2 |    0 | NULL |
+----+------+------+

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4