Home >> FAQs/Tutorials >> MySQL Tutorials >> Index

MySQL FAQs - Introduction to SQL Basics

By: FYIcenter.com

Part:   1  2  3  4  5   6  7 

(Continued from previous part...)

What Are NULL Values?

NULL is a special value that represents no value. Here are basic rules about NULL values:

• NULL presents no value.
• NULL is not the same as an empty string ''.
• NULL is not the same as a zero value 0.
• NULL can be used as any data type.
• NULL should not be used in any comparison options.
• NULL has its own equality operator "IS".
• NULL has its own not-equality operator "IS NOT".

The tutorial exercise shows you some interesting examples:

SELECT 0 IS NULL FROM DUAL;
   0

SELECT 0 IS NOT NULL FROM DUAL;
   1

SELECT '' IS NULL FROM DUAL;
   0

SELECT '' IS NOT NULL FROM DUAL;
   1

SELECT NULL IS NULL FROM DUAL;
   1

SELECT NULL IS NOT NULL FROM DUAL;
   0

What Happens If NULL Values Are Involved in Expressions?

If NULL values are used in expressions, the resulting values will be NULL values. In other words:

• Arithmetic expressions with NULL values result NULL values.
• Comparison expressions with NULL values result NULL values.
• Logical expressions with NULL values result NULL values.

The tutorial exercise shows you some interesting examples:

SELECT NULL + NULL FROM DUAL;
   NULL
  
SELECT NULL + 7 FROM DUAL;
   NULL

SELECT NULL * 7 FROM DUAL;
   NULL

SELECT NULL = NULL FROM DUAL;
   NULL

SELECT 0 < NULL FROM DUAL;
   NULL

SELECT '' > NULL FROM DUAL;
   NULL

SELECT NULL AND TRUE FROM DUAL;
   NULL

SELECT NULL OR TRUE FROM DUAL;
   1
   -- This is contradicting against the rules!

How To Convert Numeric Values to Character Strings?

You can convert numeric values to character strings by using the CAST(value AS CHAR) function as shown in the following examples:

SELECT CAST(4123.45700 AS CHAR) FROM DUAL;
   4123.45700
   -- How to get rid of the last 2 '0's?

SELECT CAST(4.12345700E+3 AS CHAR) FROM DUAL;
   4123.457
   
SELECT CAST(1/3 AS CHAR);
   0.3333
   -- Very poor conversion

(Continued on next part...)

Part:   1  2  3  4  5   6  7