NULL values fail comparisons both with < and with >= : String Compare « String « SQL / MySQL






  1. SQL / MySQL
  2. String
  3. String Compare

NULL values fail comparisons both with < and with >=

      
mysql>
mysql> CREATE TABLE mytable
    -> (
    ->  name    CHAR(20),
    ->  id              CHAR(20)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO mytable (name,id) VALUES ('Tom','198-48');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO mytable (name,id) VALUES ('Jack',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO mytable (name,id) VALUES ('Mary',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO mytable (name,id) VALUES ('Jane','475-83');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql>
mysql> SELECT * FROM mytable WHERE id >= '200-00' OR id IS NULL;
+------+--------+
| name | id     |
+------+--------+
| Jack | NULL   |
| Mary | NULL   |
| Jane | 475-83 |
+------+--------+
3 rows in set (0.00 sec)

mysql>
mysql> drop table mytable;
Query OK, 0 rows affected (0.00 sec)

mysql>








Related examples in the same category

1.Compare String in where clause
2.String comparisons are performed from left to right, one character at a time:
3.The two strings '4200' and '4200.0' are not the same:
4.Case-insensitive nature of string comparisons:
5.String comparisons in MySQL are not case sensitive by default:
6.To make a string comparison case sensitive, cast (convert) one of the strings to binary form by using the BINA
7.To make a string comparison not case sensitive, convert both strings to the same lettercase using UPPER( ) or