SQL Queries using Null fields -- Must use "is null" or "is not null"?

In summary, the conversation discusses the use of "Is null" or "Is not null" statements in SQL queries for null fields/attributes. It is determined that the usage depends on the desired result and whether the field is important. The behavior of null values in SQL is different from other programming languages and it is important to consider this when using functions or joining tables. In SQL, NULL does not equal itself and it is recommended to use the NOT NULL property for indexed columns.
  • #1
WWGD
Science Advisor
Gold Member
6,935
10,343
Hi all,
This is for SQL queries, sorry ran out of space in the title.
Say we are doing a query that includes a null field/attribute. Must we always use either an " Is null" or
" Is not null" statement?
Thanks.
 
Computer science news on Phys.org
  • #2
Depends if that factor is important for your result. I don't think there is a requirement. Are you getting errors?
 
  • #3
Greg Bernhardt said:
Depends if that factor is important for your result. I don't think there is a requirement. Are you getting errors?
Hi Greg, no, I am learning the topic and the source material did not seem clear on this, i.e., on whether we should use, for the null field 'field' either "where field is null" , or " where field is not null".
 
  • #4
WWGD said:
whether we should use, for the null field 'field' either "where field is null" , or " where field is not null".
Again depends on what result you are after. If you want to factor in nulls for a field then use IS NULL, if you want to factor in records that have a field that is not null, then use IS NOT NULL. If you don't care, then don't add the clause :)
 
  • Like
Likes WWGD
  • #6
NULL is not equal to anything - including itself - what jedishrfu is telling you. When you employ a where clause you are looking for a resultset that matches what you are looking for. There may be some 'yellow' values but you do have to say '!= yellow', just say '=red' instead.

So you can ignore NULLS

Except when the select statement uses some kind of function and operates on a field that can be NULL. You cannot have a NULL in a function, so you must either exclude those rows or wrap the result in a default NVL() function to transform the NULL to something usage.
Code:
select UPPER(NVL(column_name, 'dummy')) from some_table;

In this pretend example upper will barf if column_name returns a NULL, so you throw some 'dummy' value out there to feed it a usable value when the column_name is NULL.

All indexed columns in a table should have the NOT NULL property. For the same reason.
 
  • Like
Likes WWGD
  • #7
When people first get into computer science, they have trouble with certain special values. The concept that x = x is not necessarily true is hard to grasp, it's even worse, when that equal sign doesn't even resolve to true or false.

In most computer languages NULL is a constant, and it does equal itself. That's not true in SQL.

PHP
Code:
echo (NULL == NULL) ? 'true' : 'false';
echo (NULL === NULL) ? 'true' : 'false';
echo (NULL == 0) ? 'true' : 'false';
echo (NULL === 0) ? 'true' : 'false';
echo (NULL == 1) ? 'true' : 'false';
echo (NULL === 1) ? 'true' : 'false';
Will spit out
true
true
true
false
false
falseMySQL
Code:
SELECT NULL = NULL;
SELECT NULL IS NULL;
SELECT NULL = 0;
SELECT NULL = 1;
SELECT 0 = 0;
SELECT 0 = 1;
Will spit out
NULL
1
NULL
NULL
1
0
 
  • Like
Likes cellurl and WWGD

1. What are null fields in SQL queries?

Null fields in SQL queries refer to missing or unknown values in a database table. They are not the same as an empty string or a value of zero. Null fields can affect the results of a query if not handled properly.

2. Why do we need to use "is null" or "is not null" in SQL queries?

Using "is null" or "is not null" in SQL queries allows us to filter out or include null values in our results. This ensures that we get accurate and complete data from the database.

3. Can we use other operators, such as "=", "<", ">" with null fields?

No, we cannot use other operators with null fields in SQL queries. This is because null values cannot be compared or evaluated using these operators. Instead, we must use "is null" or "is not null" to check for null values.

4. How do null fields affect the results of a SQL query?

If null fields are not handled properly, they can affect the results of a SQL query in various ways. For example, if we use the "=" operator on a null field, it will not return any results, as null values cannot be compared. This can lead to inaccurate or incomplete data in our results.

5. How can we handle null fields in SQL queries?

To handle null fields in SQL queries, we can use the "is null" or "is not null" operators. We can also use functions like "coalesce" to replace null values with a default value. It is important to properly handle null fields to ensure accurate and complete data in our results.

Similar threads

Replies
11
Views
1K
  • Programming and Computer Science
Replies
7
Views
279
  • Computing and Technology
Replies
8
Views
2K
  • Programming and Computer Science
2
Replies
51
Views
3K
  • Computing and Technology
Replies
5
Views
2K
  • Computing and Technology
Replies
1
Views
1K
Replies
6
Views
1K
  • Computing and Technology
Replies
12
Views
1K
  • Computing and Technology
Replies
11
Views
1K
  • Programming and Computer Science
Replies
7
Views
2K
Back
Top