SQL/MySQL/Data Type/Null

Материал из SQL эксперт
Перейти к: навигация, поиск

Concatenate string with NULL value

   <source lang="sql">

/* mysql> select CONCAT("a",null); +------------------+ | CONCAT("a",null) | +------------------+ | NULL | +------------------+ 1 row in set (0.00 sec)

  • /

select CONCAT("a",null); 

      </source>


Dealing With NULL Data

   <source lang="sql">

/* mysql> SELECT ExamID,SustainedOn,Comments FROM Exam 

   -> WHERE Comments IS NULL;

+--------+-------------+----------+ | ExamID | SustainedOn | Comments | +--------+-------------+----------+ | 1 | 2003-03-12 | NULL | +--------+-------------+----------+ 1 row in set (0.00 sec)

  • /

/* Prepare the data */ DROP TABLE Exam; CREATE TABLE Exam ( 

  ExamID      INT NOT NULL PRIMARY KEY,
  SustainedOn DATE,
  Comments    VARCHAR(255)

)TYPE = InnoDB;

/* Insert data for testing */ INSERT INTO Exam (ExamID,SustainedOn,Comments) VALUES (1,"2003-03-12",NULL); INSERT INTO Exam (ExamID,SustainedOn,Comments) VALUES (2,"2004-03-13","C# test"); INSERT INTO Exam (ExamID,SustainedOn,Comments) VALUES (3,"2005-03-11","JavaScript Test");

/* Real command */

SELECT ExamID,SustainedOn,Comments FROM Exam WHERE Comments IS NULL; 

      </source>


Disallowing NULLs

   <source lang="sql">

/* mysql> DROP TABLE Employee; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE Employee ( 

   ->     Name VARCHAR(50) NOT NULL,
   ->     Phone VARCHAR(15) NOT NULL
   -> );

Query OK, 0 rows affected (0.08 sec) mysql> Describe Employee; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | Name | varchar(50) | | | | | | Phone | varchar(15) | | | | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> INSERT INTO Employee (Name, Phone) 

   ->             VALUES ("Joe Wang", "666 2323");

Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO Employee (Name) VALUES ("John Doe"); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO Employee (Name, Phone) VALUES ("John Doe", NULL); ERROR 1048 (23000): Column "Phone" cannot be null mysql> Select * from Employee; +----------+----------+ | Name | Phone | +----------+----------+ | Joe Wang | 666 2323 | | John Doe | | +----------+----------+ 2 rows in set (0.00 sec)

  • /

DROP TABLE Employee; CREATE TABLE Employee ( 

   Name VARCHAR(50) NOT NULL, 
   Phone VARCHAR(15) NOT NULL

); Describe Employee; INSERT INTO Employee (Name, Phone) 

           VALUES ("Joe Wang", "666 2323");

INSERT INTO Employee (Name) VALUES ("John Doe"); INSERT INTO Employee (Name, Phone) VALUES ("John Doe", NULL);

Select * from Employee; 

      </source>


IS NOT NULL in where clause

   <source lang="sql">

/* mysql> Drop table Sale; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE Sale 

   -> (
   ->    ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   ->    Name VARCHAR(50) NOT NULL,
   ->    InStock SMALLINT UNSIGNED NOT NULL,
   ->    OnOrder SMALLINT UNSIGNED NOT NULL,
   ->    Reserved SMALLINT UNSIGNED NOT NULL,
   ->    Department ENUM("Classical", "Popular") NOT NULL,
   ->    Category VARCHAR(20)
   -> );

Query OK, 0 rows affected (0.12 sec) mysql> INSERT INTO Sale (Name, InStock, OnOrder, Reserved, Department, Cate gory) 

   -> VALUES          ("Bloodshot", 11,      6,       1,        "Popular",  "Ro

ck"), 

   ->                 ("Opera",     12,      5,       2,        "Classical","Op

era"), 

   ->                 ("Jazz",      13,      4,       3,        "Popular",  "Ja

zz"), 

   ->                 ("Music",     4,       3,       4,        "Classical","Da

nce"), 

   ->                 ("Violin",    25,      2,       5,        "Classical", NU

LL), 

   ->                 ("Toscana",   16,      1,       6,        "Classical", NU

LL), 

   ->                 ("Blues",     7,       22,      7,        "Popular",   "B

lues"), 

   ->                 ("Pure",      38,      5,       11,       "Popular",   NU

LL), 

   ->                 ("Mud",       19,      11,      12,       "Popular",  "Co

untry"), 

   ->                 ("Essence",   5,       23,      12,       "Popular", "New
Age"),
   ->                 ("Embrace",   21,      12,      14,       "Popular", "New
Age"),
   ->                 ("Satie",     42,      17,      15,       "Classical", NU

LL), 

   ->                 ("Lake",      23,      47,      28,       "Classical", "D

ance"), 

   ->                 ("Favorites", 34,      15,      12,       "Classical", "G

eneral"), 

   ->                 ("Boheme",    25,      12,       5,       "Classical", "O

pera"), 

   ->                 ("Cantatas",  26,      13,      8,        "Classical", "G

eneral"), 

   ->                 ("Road",      27,      13,      17,       "Popular", "Cou

ntry"), 

   ->                 ("Paris",     18,      25,      10,       "Popular", "Jaz

z"), 

   ->                 ("Woman",     29,      4,        7,       "Popular", "Blu

es"), 

   ->                 ("Bach",      21,      13,      16,       "Classical", "G

eneral"), 

   ->                 ("Opera",     12,      32,      12,       "Classical", "O

pera"), 

   ->                 ("Soul",      13,      30,      14,       "Popular", NULL

), 

   ->                 ("Stages",    44,      0,       8,        "Popular", "Blu

es"), 

   ->                 ("Bach",      15,      1,       8,        "Classical", "G

eneral"); Query OK, 24 rows affected (0.01 sec) Records: 24 Duplicates: 0 Warnings: 0 mysql> select * from Sale; +----+-----------+---------+---------+----------+------------+----------+ | ID | Name | InStock | OnOrder | Reserved | Department | Category | +----+-----------+---------+---------+----------+------------+----------+ | 1 | Bloodshot | 11 | 6 | 1 | Popular | Rock | | 2 | Opera | 12 | 5 | 2 | Classical | Opera | | 3 | Jazz | 13 | 4 | 3 | Popular | Jazz | | 4 | Music | 4 | 3 | 4 | Classical | Dance | | 5 | Violin | 25 | 2 | 5 | Classical | NULL | | 6 | Toscana | 16 | 1 | 6 | Classical | NULL | | 7 | Blues | 7 | 22 | 7 | Popular | Blues | | 8 | Pure | 38 | 5 | 11 | Popular | NULL | | 9 | Mud | 19 | 11 | 12 | Popular | Country | | 10 | Essence | 5 | 23 | 12 | Popular | New Age | | 11 | Embrace | 21 | 12 | 14 | Popular | New Age | | 12 | Satie | 42 | 17 | 15 | Classical | NULL | | 13 | Lake | 23 | 47 | 28 | Classical | Dance | | 14 | Favorites | 34 | 15 | 12 | Classical | General | | 15 | Boheme | 25 | 12 | 5 | Classical | Opera | | 16 | Cantatas | 26 | 13 | 8 | Classical | General | | 17 | Road | 27 | 13 | 17 | Popular | Country | | 18 | Paris | 18 | 25 | 10 | Popular | Jazz | | 19 | Woman | 29 | 4 | 7 | Popular | Blues | | 20 | Bach | 21 | 13 | 16 | Classical | General | | 21 | Opera | 12 | 32 | 12 | Classical | Opera | | 22 | Soul | 13 | 30 | 14 | Popular | NULL | | 23 | Stages | 44 | 0 | 8 | Popular | Blues | | 24 | Bach | 15 | 1 | 8 | Classical | General | +----+-----------+---------+---------+----------+------------+----------+ 24 rows in set (0.01 sec) mysql> SELECT Name, Department, Category 

   -> FROM Sale
   -> WHERE Category IS NOT NULL
   -> ORDER BY Name;

+-----------+------------+----------+ | Name | Department | Category | +-----------+------------+----------+ | Bach | Classical | General | | Bach | Classical | General | | Bloodshot | Popular | Rock | | Blues | Popular | Blues | | Boheme | Classical | Opera | | Cantatas | Classical | General | | Embrace | Popular | New Age | | Essence | Popular | New Age | | Favorites | Classical | General | | Jazz | Popular | Jazz | | Lake | Classical | Dance | | Mud | Popular | Country | | Music | Classical | Dance | | Opera | Classical | Opera | | Opera | Classical | Opera | | Paris | Popular | Jazz | | Road | Popular | Country | | Stages | Popular | Blues | | Woman | Popular | Blues | +-----------+------------+----------+ 19 rows in set (0.02 sec)

  • /

Drop table Sale;

CREATE TABLE Sale ( 

  ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  Name VARCHAR(50) NOT NULL,
  InStock SMALLINT UNSIGNED NOT NULL,
  OnOrder SMALLINT UNSIGNED NOT NULL,
  Reserved SMALLINT UNSIGNED NOT NULL,
  Department ENUM("Classical", "Popular") NOT NULL,
  Category VARCHAR(20)

);

INSERT INTO Sale (Name, InStock, OnOrder, Reserved, Department, Category) VALUES ("Bloodshot", 11, 6, 1, "Popular", "Rock"), 

               ("Opera",     12,      5,       2,        "Classical","Opera"),
               ("Jazz",      13,      4,       3,        "Popular",  "Jazz"),
               ("Music",     4,       3,       4,        "Classical","Dance"),
               ("Violin",    25,      2,       5,        "Classical", NULL),
               ("Toscana",   16,      1,       6,        "Classical", NULL),
               ("Blues",     7,       22,      7,        "Popular",   "Blues"),
               ("Pure",      38,      5,       11,       "Popular",   NULL),
               ("Mud",       19,      11,      12,       "Popular",  "Country"),
               ("Essence",   5,       23,      12,       "Popular", "New Age"),
               ("Embrace",   21,      12,      14,       "Popular", "New Age"),
               ("Satie",     42,      17,      15,       "Classical", NULL),
               ("Lake",      23,      47,      28,       "Classical", "Dance"),
               ("Favorites", 34,      15,      12,       "Classical", "General"),
               ("Boheme",    25,      12,       5,       "Classical", "Opera"),
               ("Cantatas",  26,      13,      8,        "Classical", "General"),
               ("Road",      27,      13,      17,       "Popular", "Country"),
               ("Paris",     18,      25,      10,       "Popular", "Jazz"),
               ("Woman",     29,      4,        7,       "Popular", "Blues"),
               ("Bach",      21,      13,      16,       "Classical", "General"),
               ("Opera",     12,      32,      12,       "Classical", "Opera"),
               ("Soul",      13,      30,      14,       "Popular", NULL),
               ("Stages",    44,      0,       8,        "Popular", "Blues"),
               ("Bach",      15,      1,       8,        "Classical", "General");

select * from Sale;


SELECT Name, Department, Category FROM Sale WHERE Category IS NOT NULL ORDER BY Name; 

      </source>


List NOT NULL value and order it

   <source lang="sql">

/* mysql> select * from employee_person; +----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+ | id | address | phone | email | birthday | sex | m_status | s_name | children | +----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+ | 1 | 200, Regina Street | 7176666 | net@hotmail.ru | 1971-04-26 | M | Y | Ane Regina | NULL | | 2 | 1232 Alberta Road | 5553312 | jo@hotmail.ru | 1968-03-02 | M | Y | Jane Van | 3 | | 3 | 90 Potter A | 3331211 | gp@ymail.ru | 1967-09-22 | M | N | Sandhya Pil | 2 | | 4 | 202, Donna Street | 7176167 | twink@hotmail.ru | 1976-08-09 | F | Y | Manish Sha | NULL | | 5 | Apartment #8, UBC, Van Street | 8973242 | holy@ymail.ru | 1974-10-14 | F | N | NULL | NULL | | 6 | 46 SFU Street | 6451234 | kill@el.ru | 1978-12-31 | M | N | NULL | NULL | | 7 | 432 Mercury Ave | 7932232 | mac@hotmail.ru | 1966-08-21 | M | Y | Mary Shelly | 3 | | 8 | 8 Little YaleTown | 5442994 | edd@gmail.ru | 1975-01-14 | M | N | NULL | NULL | | 9 | 64 Temp Road | 4327652 | nan@pmail.ru | 1969-05-19 | M | Y | Man Nanda | 1 | | 10 | 132 Metro House, Henry Street | 5552376 | ra@hotmail.ru | 1968-07-06 | M | N | NULL | NULL | | 11 | 1 Grace Town, Van Avenue | 5433879 | soundofsilence@boxer.net | 1957-11-04 | M | Y | Muriel Lovelace | 4 | | 12 | 97 Oakland Road | 5423311 | kingarthur@roundtable.org | 1968-02-15 | M | Y | Rina Brighton | 3 | | 13 | 543 Applegate Lane | 3434343 | levy@cmail.ru | 1968-09-03 | F | Y | Matt Shi | 2 | | 14 | 76 Fish Street | 7432433 | tink@email.ru | 1965-04-28 | M | N | NULL | NULL | | 15 | 98 Gun Street | 6500787 | danny@fhardy.ru | 1966-06-23 | M | Y | Betty Cudly | 3 | | 16 | #5 Winnepag Homes | 5433243 | mike@cmail.ru | 1964-03-06 | M | Y | Stella Stevens | 2 | | 17 | 652 Devon Building, 6th Jade Avenue | 5537885 | mona@darling.ru | 1970-04-18 | F | Y | Edgar Alan | 1 | | 18 | Apartment #9, Together Towers | 5476565 | odessey@hotmail.ru | 1973-10-09 | M | N | NULL | NULL | | 19 | Apartment #9, West Towers | 5476565 | jire@hotmail.ru | 1973-01-20 | M | N | NULL | NULL | | 20 | 90 Yale Town | 7528326 | help@more.org | 1968-01-25 | F | N | NULL | NULL | | 21 | 4329 Eucalyptus Avenue | 4254863 | money@cold.ru | 1964-06-13 | M | Y | Ruby Richer | 2 | +----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+ 21 rows in set (0.01 sec) mysql> select id, s_name 

   ->        from employee_person
   ->        where s_name IS NOT NULL
   ->        ORDER BY s_name;

+----+-----------------+ | id | s_name | +----+-----------------+ | 1 | Ane Regina | | 15 | Betty Cudly | | 17 | Edgar Alan | | 2 | Jane Van | | 9 | Man Nanda | | 4 | Manish Sha | | 7 | Mary Shelly | | 13 | Matt Shi | | 11 | Muriel Lovelace | | 12 | Rina Brighton | | 21 | Ruby Richer | | 3 | Sandhya Pil | | 16 | Stella Stevens | +----+-----------------+ 13 rows in set (0.00 sec)

  • /

Drop table employee_person; CREATE TABLE employee_person ( 

   id int unsigned not null primary key, 
   address varchar(60), 
   phone int, 
   email varchar(60), 
   birthday DATE, 
   sex ENUM("M", "F"), 
   m_status ENUM("Y","N"), 
   s_name varchar(40), 
   children int

);

INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name) values (1, "200, Regina Street", 7176666, "net@hotmail.ru", "1971-04-26", "M", "Y", "Ane Regina"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (2, "1232 Alberta Road", 5553312, "jo@hotmail.ru", "1968-03-02", "M", "Y", "Jane Van", 3); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (3, "90 Potter A", 3331211, "gp@ymail.ru", "1967-09-22", "M", "N", "Sandhya Pil", 2); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name) values (4, "202, Donna Street", 7176167, "twink@hotmail.ru", "1976-08-09", "F", "Y", "Manish Sha"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (5, "Apartment #8, UBC, Van Street", 8973242, "holy@ymail.ru", "1974-10-14", "F", "N"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (6, "46 SFU Street", "6451234", "kill@el.ru", "1978-12-31", "M", "N"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (7, "432 Mercury Ave", 7932232, "mac@hotmail.ru", "1966-8-21", "M", "Y", "Mary Shelly", "3"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (8, "8 Little YaleTown", 5442994, "edd@gmail.ru", "1975-01-14", "M", "N"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (9, "64 Temp Road", 4327652, "nan@pmail.ru", "1969-05-19", "M", "Y", "Man Nanda", "1"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (10, "132 Metro House, Henry Street", 5552376, "ra@hotmail.ru", "1968-07-06", "M", "N"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (11, "1 Grace Town, Van Avenue", 5433879, "soundofsilence@boxer.net", "1957-11-04", "M", "Y", "Muriel Lovelace", "4"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (12, "97 Oakland Road", 5423311, "kingarthur@roundtable.org", "1968-02-15", "M", "Y", "Rina Brighton", 3); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (13, "543 Applegate Lane", 3434343, "levy@cmail.ru", "1968-09-03", "F", "Y", "Matt Shi", "2"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (14, "76 Fish Street", 7432433, "tink@email.ru", "1965-04-28", "M", "N"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (15, "98 Gun Street", 6500787, "danny@fhardy.ru", "1966-06-23", "M", "Y", "Betty Cudly", 3); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (16, "#5 Winnepag Homes", 5433243, "mike@cmail.ru", "1964-03-06", "M", "Y", "Stella Stevens", 2); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (17, "652 Devon Building, 6th Jade Avenue", 5537885, "mona@darling.ru", "1970-04-18", "F", "Y", "Edgar Alan", 1); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (18, "Apartment #9, Together Towers", 5476565, "odessey@hotmail.ru", "1973-10-09", "M", "N"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (19, "Apartment #9, West Towers", 5476565, "jire@hotmail.ru", "1973-1-20", "M", "N"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (20, "90 Yale Town", 7528326, "help@more.org", "1968-01-25", "F", "N"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (21, "4329 Eucalyptus Avenue", 4254863, "money@cold.ru", "1964-06-13", "M", "Y", "Ruby Richer", 2);

select * from employee_person; select id, s_name 

      from employee_person
      where s_name IS NOT NULL 
      ORDER BY s_name;



      </source>


NULL means "not having a value"

   <source lang="sql">

/* mysql> SELECT 0 IS NULL, 0 IS NOT NULL, "" IS NULL, "" IS NOT NULL; +-----------+---------------+------------+----------------+ | 0 IS NULL | 0 IS NOT NULL | "" IS NULL | "" IS NOT NULL | +-----------+---------------+------------+----------------+ | 0 | 1 | 0 | 1 | +-----------+---------------+------------+----------------+ 1 row in set (0.00 sec)

  • /

SELECT 0 IS NULL, 0 IS NOT NULL, "" IS NULL, "" IS NOT NULL; 

      </source>


NULL value in where clause

   <source lang="sql">

/* mysql> Drop table Sale; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE Sale 

   -> (
   ->    ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   ->    Name VARCHAR(50) NOT NULL,
   ->    InStock SMALLINT UNSIGNED NOT NULL,
   ->    OnOrder SMALLINT UNSIGNED NOT NULL,
   ->    Reserved SMALLINT UNSIGNED NOT NULL,
   ->    Department ENUM("Classical", "Popular") NOT NULL,
   ->    Category VARCHAR(20)
   -> );

Query OK, 0 rows affected (0.12 sec) mysql> INSERT INTO Sale (Name, InStock, OnOrder, Reserved, Department, Cate gory) 

   -> VALUES          ("Bloodshot", 11,      6,       1,        "Popular",  "Ro

ck"), 

   ->                 ("Opera",     12,      5,       2,        "Classical","Op

era"), 

   ->                 ("Jazz",      13,      4,       3,        "Popular",  "Ja

zz"), 

   ->                 ("Music",     4,       3,       4,        "Classical","Da

nce"), 

   ->                 ("Violin",    25,      2,       5,        "Classical", NU

LL), 

   ->                 ("Toscana",   16,      1,       6,        "Classical", NU

LL), 

   ->                 ("Blues",     7,       22,      7,        "Popular",   "B

lues"), 

   ->                 ("Pure",      38,      5,       11,       "Popular",   NU

LL), 

   ->                 ("Mud",       19,      11,      12,       "Popular",  "Co

untry"), ("Essence", 5, 23, 12, "Popular", "New Age"), 

   ->                 ("Embrace",   21,      12,      14,       "Popular", "New
Age"),
   ->                 ("Satie",     42,      17,      15,       "Classical", NU

LL), 

   ->                 ("Lake",      23,      47,      28,       "Classical", "D

ance"), 

   ->                 ("Favorites", 34,      15,      12,       "Classical", "G

eneral"), 

   ->                 ("Boheme",    25,      12,       5,       "Classical", "O

pera"), 

   ->                 ("Cantatas",  26,      13,      8,        "Classical", "G

eneral"), 

   ->                 ("Road",      27,      13,      17,       "Popular", "Cou

ntry"), 

   ->                 ("Paris",     18,      25,      10,       "Popular", "Jaz

z"), 

   ->                 ("Woman",     29,      4,        7,       "Popular", "Blu

es"), 

   ->                 ("Bach",      21,      13,      16,       "Classical", "G

eneral"), 

   ->                 ("Opera",     12,      32,      12,       "Classical", "O

pera"), 

   ->                 ("Soul",      13,      30,      14,       "Popular", NULL

), 

   ->                 ("Stages",    44,      0,       8,        "Popular", "Blu

es"), 

   ->                 ("Bach",      15,      1,       8,        "Classical", "G

eneral"); Query OK, 24 rows affected (0.01 sec) Records: 24 Duplicates: 0 Warnings: 0 mysql> select * from Sale; +----+-----------+---------+---------+----------+------------+----------+ | ID | Name | InStock | OnOrder | Reserved | Department | Category | +----+-----------+---------+---------+----------+------------+----------+ | 1 | Bloodshot | 11 | 6 | 1 | Popular | Rock | | 2 | Opera | 12 | 5 | 2 | Classical | Opera | | 3 | Jazz | 13 | 4 | 3 | Popular | Jazz | | 4 | Music | 4 | 3 | 4 | Classical | Dance | | 5 | Violin | 25 | 2 | 5 | Classical | NULL | | 6 | Toscana | 16 | 1 | 6 | Classical | NULL | | 7 | Blues | 7 | 22 | 7 | Popular | Blues | | 8 | Pure | 38 | 5 | 11 | Popular | NULL | | 9 | Mud | 19 | 11 | 12 | Popular | Country | | 10 | Essence | 5 | 23 | 12 | Popular | New Age | | 11 | Embrace | 21 | 12 | 14 | Popular | New Age | | 12 | Satie | 42 | 17 | 15 | Classical | NULL | | 13 | Lake | 23 | 47 | 28 | Classical | Dance | | 14 | Favorites | 34 | 15 | 12 | Classical | General | | 15 | Boheme | 25 | 12 | 5 | Classical | Opera | | 16 | Cantatas | 26 | 13 | 8 | Classical | General | | 17 | Road | 27 | 13 | 17 | Popular | Country | | 18 | Paris | 18 | 25 | 10 | Popular | Jazz | | 19 | Woman | 29 | 4 | 7 | Popular | Blues | | 20 | Bach | 21 | 13 | 16 | Classical | General | | 21 | Opera | 12 | 32 | 12 | Classical | Opera | | 22 | Soul | 13 | 30 | 14 | Popular | NULL | | 23 | Stages | 44 | 0 | 8 | Popular | Blues | | 24 | Bach | 15 | 1 | 8 | Classical | General | +----+-----------+---------+---------+----------+------------+----------+ 24 rows in set (0.00 sec) mysql> SELECT Name, Department, Category 

   -> FROM Sale
   -> WHERE Category IS NOT NULL
   -> ORDER BY Name;

+-----------+------------+----------+ | Name | Department | Category | +-----------+------------+----------+ | Bach | Classical | General | | Bach | Classical | General | | Bloodshot | Popular | Rock | | Blues | Popular | Blues | | Boheme | Classical | Opera | | Cantatas | Classical | General | | Embrace | Popular | New Age | | Essence | Popular | New Age | | Favorites | Classical | General | | Jazz | Popular | Jazz | | Lake | Classical | Dance | | Mud | Popular | Country | | Music | Classical | Dance | | Opera | Classical | Opera | | Opera | Classical | Opera | | Paris | Popular | Jazz | | Road | Popular | Country | | Stages | Popular | Blues | | Woman | Popular | Blues | +-----------+------------+----------+ 19 rows in set (0.00 sec)

  • /

Drop table Sale;

CREATE TABLE Sale ( 

  ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  Name VARCHAR(50) NOT NULL,
  InStock SMALLINT UNSIGNED NOT NULL,
  OnOrder SMALLINT UNSIGNED NOT NULL,
  Reserved SMALLINT UNSIGNED NOT NULL,
  Department ENUM("Classical", "Popular") NOT NULL,
  Category VARCHAR(20)

);

INSERT INTO Sale (Name, InStock, OnOrder, Reserved, Department, Category) VALUES ("Bloodshot", 11, 6, 1, "Popular", "Rock"), 

               ("Opera",     12,      5,       2,        "Classical","Opera"),
               ("Jazz",      13,      4,       3,        "Popular",  "Jazz"),
               ("Music",     4,       3,       4,        "Classical","Dance"),
               ("Violin",    25,      2,       5,        "Classical", NULL),
               ("Toscana",   16,      1,       6,        "Classical", NULL),
               ("Blues",     7,       22,      7,        "Popular",   "Blues"),
               ("Pure",      38,      5,       11,       "Popular",   NULL),
               ("Mud",       19,      11,      12,       "Popular",  "Country"),
               ("Essence",   5,       23,      12,       "Popular", "New Age"),
               ("Embrace",   21,      12,      14,       "Popular", "New Age"),
               ("Satie",     42,      17,      15,       "Classical", NULL),
               ("Lake",      23,      47,      28,       "Classical", "Dance"),
               ("Favorites", 34,      15,      12,       "Classical", "General"),
               ("Boheme",    25,      12,       5,       "Classical", "Opera"),
               ("Cantatas",  26,      13,      8,        "Classical", "General"),
               ("Road",      27,      13,      17,       "Popular", "Country"),
               ("Paris",     18,      25,      10,       "Popular", "Jazz"),
               ("Woman",     29,      4,        7,       "Popular", "Blues"),
               ("Bach",      21,      13,      16,       "Classical", "General"),
               ("Opera",     12,      32,      12,       "Classical", "Opera"),
               ("Soul",      13,      30,      14,       "Popular", NULL),
               ("Stages",    44,      0,       8,        "Popular", "Blues"),
               ("Bach",      15,      1,       8,        "Classical", "General");

select * from Sale;

SELECT Name, Department, Category FROM Sale WHERE Category IS NOT NULL ORDER BY Name; 


      </source>


Read NULL value

   <source lang="sql">

/* mysql> Drop table Sale; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE Sale 

   -> (
   ->    ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   ->    Name VARCHAR(50) NOT NULL,
   ->    InStock SMALLINT UNSIGNED NOT NULL,
   ->    OnOrder SMALLINT UNSIGNED NOT NULL,
   ->    Reserved SMALLINT UNSIGNED NOT NULL,
   ->    Department ENUM("Classical", "Popular") NOT NULL,
   ->    Category VARCHAR(20)
   -> );

Query OK, 0 rows affected (0.07 sec) mysql> INSERT INTO Sale (Name, InStock, OnOrder, Reserved, Department, Cate gory) 

   -> VALUES          ("Bloodshot", 11,      6,       1,        "Popular",  "Ro

ck"), 

   ->                 ("Opera",     12,      5,       2,        "Classical","Op

era"), 

   ->                 ("Jazz",      13,      4,       3,        "Popular",  "Ja

zz"), 

   ->                 ("Music",     4,       3,       4,        "Classical","Da

nce"), 

   ->                 ("Violin",    25,      2,       5,        "Classical", NU

LL), 

   ->                 ("Toscana",   16,      1,       6,        "Classical", NU

LL), 

   ->                 ("Blues",     7,       22,      7,        "Popular",   "B

lues"), 

   ->                 ("Pure",      38,      5,       11,       "Popular",   NU

LL), 

   ->                 ("Mud",       19,      11,      12,       "Popular",  "Co

untry"), 

   ->                 ("Essence",   5,       23,      12,       "Popular", "New
Age"),
   ->                 ("Embrace",   21,      12,      14,       "Popular", "New
Age"),
   ->                 ("Satie",     42,      17,      15,       "Classical", NU

LL), 

   ->                 ("Lake",      23,      47,      28,       "Classical", "D

ance"), 

   ->                 ("Favorites", 34,      15,      12,       "Classical", "G

eneral"), 

   ->                 ("Boheme",    25,      12,       5,       "Classical", "O

pera"), 

   ->                 ("Cantatas",  26,      13,      8,        "Classical", "G

eneral"), 

   ->                 ("Road",      27,      13,      17,       "Popular", "Cou

ntry"), 

   ->                 ("Paris",     18,      25,      10,       "Popular", "Jaz

z"), 

   ->                 ("Woman",     29,      4,        7,       "Popular", "Blu

es"), 

   ->                 ("Bach",      21,      13,      16,       "Classical", "G

eneral"), 

   ->                 ("Opera",     12,      32,      12,       "Classical", "O

pera"), 

   ->                 ("Soul",      13,      30,      14,       "Popular", NULL

), 

   ->                 ("Stages",    44,      0,       8,        "Popular", "Blu

es"), 

   ->                 ("Bach",      15,      1,       8,        "Classical", "G

eneral"); Query OK, 24 rows affected (0.01 sec) Records: 24 Duplicates: 0 Warnings: 0 mysql> select * from Sale; +----+-----------+---------+---------+----------+------------+----------+ | ID | Name | InStock | OnOrder | Reserved | Department | Category | +----+-----------+---------+---------+----------+------------+----------+ | 1 | Bloodshot | 11 | 6 | 1 | Popular | Rock | | 2 | Opera | 12 | 5 | 2 | Classical | Opera | | 3 | Jazz | 13 | 4 | 3 | Popular | Jazz | | 4 | Music | 4 | 3 | 4 | Classical | Dance | | 5 | Violin | 25 | 2 | 5 | Classical | NULL | | 6 | Toscana | 16 | 1 | 6 | Classical | NULL | | 7 | Blues | 7 | 22 | 7 | Popular | Blues | | 8 | Pure | 38 | 5 | 11 | Popular | NULL | | 9 | Mud | 19 | 11 | 12 | Popular | Country | | 10 | Essence | 5 | 23 | 12 | Popular | New Age | | 11 | Embrace | 21 | 12 | 14 | Popular | New Age | | 12 | Satie | 42 | 17 | 15 | Classical | NULL | | 13 | Lake | 23 | 47 | 28 | Classical | Dance | | 14 | Favorites | 34 | 15 | 12 | Classical | General | | 15 | Boheme | 25 | 12 | 5 | Classical | Opera | | 16 | Cantatas | 26 | 13 | 8 | Classical | General | | 17 | Road | 27 | 13 | 17 | Popular | Country | | 18 | Paris | 18 | 25 | 10 | Popular | Jazz | | 19 | Woman | 29 | 4 | 7 | Popular | Blues | | 20 | Bach | 21 | 13 | 16 | Classical | General | | 21 | Opera | 12 | 32 | 12 | Classical | Opera | | 22 | Soul | 13 | 30 | 14 | Popular | NULL | | 23 | Stages | 44 | 0 | 8 | Popular | Blues | | 24 | Bach | 15 | 1 | 8 | Classical | General | +----+-----------+---------+---------+----------+------------+----------+ 24 rows in set (0.00 sec) mysql> SELECT Name, Department, Category 

   -> FROM Sale
   -> WHERE Category IS NULL
   -> ORDER BY Name;

+---------+------------+----------+ | Name | Department | Category | +---------+------------+----------+ | Pure | Popular | NULL | | Satie | Classical | NULL | | Soul | Popular | NULL | | Toscana | Classical | NULL | | Violin | Classical | NULL | +---------+------------+----------+ 5 rows in set (0.00 sec)

  • /

Drop table Sale;

CREATE TABLE Sale ( 

  ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  Name VARCHAR(50) NOT NULL,
  InStock SMALLINT UNSIGNED NOT NULL,
  OnOrder SMALLINT UNSIGNED NOT NULL,
  Reserved SMALLINT UNSIGNED NOT NULL,
  Department ENUM("Classical", "Popular") NOT NULL,
  Category VARCHAR(20)

);

INSERT INTO Sale (Name, InStock, OnOrder, Reserved, Department, Category) VALUES ("Bloodshot", 11, 6, 1, "Popular", "Rock"), 

               ("Opera",     12,      5,       2,        "Classical","Opera"),
               ("Jazz",      13,      4,       3,        "Popular",  "Jazz"),
               ("Music",     4,       3,       4,        "Classical","Dance"),
               ("Violin",    25,      2,       5,        "Classical", NULL),
               ("Toscana",   16,      1,       6,        "Classical", NULL),
               ("Blues",     7,       22,      7,        "Popular",   "Blues"),
               ("Pure",      38,      5,       11,       "Popular",   NULL),
               ("Mud",       19,      11,      12,       "Popular",  "Country"),
               ("Essence",   5,       23,      12,       "Popular", "New Age"),
               ("Embrace",   21,      12,      14,       "Popular", "New Age"),
               ("Satie",     42,      17,      15,       "Classical", NULL),
               ("Lake",      23,      47,      28,       "Classical", "Dance"),
               ("Favorites", 34,      15,      12,       "Classical", "General"),
               ("Boheme",    25,      12,       5,       "Classical", "Opera"),
               ("Cantatas",  26,      13,      8,        "Classical", "General"),
               ("Road",      27,      13,      17,       "Popular", "Country"),
               ("Paris",     18,      25,      10,       "Popular", "Jazz"),
               ("Woman",     29,      4,        7,       "Popular", "Blues"),
               ("Bach",      21,      13,      16,       "Classical", "General"),
               ("Opera",     12,      32,      12,       "Classical", "Opera"),
               ("Soul",      13,      30,      14,       "Popular", NULL),
               ("Stages",    44,      0,       8,        "Popular", "Blues"),
               ("Bach",      15,      1,       8,        "Classical", "General");

select * from Sale;


SELECT Name, Department, Category FROM Sale WHERE Category IS NULL ORDER BY Name; 

      </source>


Retrieve NOT NULL value

   <source lang="sql">

/* mysql> select * from employee_person; +----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+ | id | address | phone | email | birthday | sex | m_status | s_name | children | +----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+ | 1 | 200, Regina Street | 7176666 | net@hotmail.ru | 1971-04-26 | M | Y | Ane Regina | NULL | | 2 | 1232 Alberta Road | 5553312 | jo@hotmail.ru | 1968-03-02 | M | Y | Jane Van | 3 | | 3 | 90 Potter A | 3331211 | gp@ymail.ru | 1967-09-22 | M | N | Sandhya Pil | 2 | | 4 | 202, Donna Street | 7176167 | twink@hotmail.ru | 1976-08-09 | F | Y | Manish Sha | NULL | | 5 | Apartment #8, UBC, Van Street | 8973242 | holy@ymail.ru| 1974-10-14 | F | N | NULL | NULL | | 6 | 46 SFU Street | 6451234 | kill@el.ru | 1978-12-31 | M | N | NULL | NULL | | 7 | 432 Mercury Ave | 7932232 | mac@hotmail.ru | 1966-08-21 | M | Y | Mary Shelly | 3 | | 8 | 8 Little YaleTown | 5442994 | edd@gmail.ru | 1975-01-14 | M | N | NULL | NULL | | 9 | 64 Temp Road | 4327652 | nan@pmail.ru | 1969-05-19 | M | Y | Man Nanda | 1 | | 10 | 132 Metro House, Henry Street | 5552376 | ra@hotmail.ru | 1968-07-06 | M | N | NULL | NULL | | 11 | 1 Grace Town, Van Avenue | 5433879 | soundofsilence@boxer.net | 1957-11-04 | M | Y | Muriel Lovelace | 4 | | 12 | 97 Oakland Road | 5423311 | kingarthur@roundtable.org | 1968-02-15 | M | Y | Rina Brighton | 3 | | 13 | 543 Applegate Lane | 3434343 | levy@cmail.ru | 1968-09-03 | F | Y | Matt Shi | 2 | | 14 | 76 Fish Street | 7432433 | tink@email.ru | 1965-04-28 | M | N | NULL | NULL | | 15 | 98 Gun Street | 6500787 | danny@fhardy.ru | 1966-06-23 | M | Y | Betty Cudly | 3 | | 16 | #5 Winnepag Homes | 5433243 | mike@cmail.ru | 1964-03-06 | M | Y | Stella Stevens | 2 | | 17 | 652 Devon Building, 6th Jade Avenue | 5537885 | mona@darling.ru | 1970-04-18 | F | Y | Edgar Alan | 1 | | 18 | Apartment #9, Together Towers | 5476565 | odessey@hotmail.ru | 1973-10-09 | M | N | NULL | NULL | | 19 | Apartment #9, West Towers | 5476565 | jire@hotmail.ru | 1973-01-20 | M | N | NULL | NULL | | 20 | 90 Yale Town | 7528326 | help@more.org | 1968-01-25 | F | N | NULL | NULL | | 21 | 4329 Eucalyptus Avenue | 4254863 | money@cold.ru | 1964-06-13 | M | Y | Ruby Richer | 2 | +----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+ 21 rows in set (0.02 sec) mysql> select id, s_name from employee_person where s_name IS NOT NULL; +----+-----------------+ | id | s_name | +----+-----------------+ | 1 | Ane Regina | | 2 | Jane Van | | 3 | Sandhya Pil | | 4 | Manish Sha | | 7 | Mary Shelly | | 9 | Man Nanda | | 11 | Muriel Lovelace | | 12 | Rina Brighton | | 13 | Matt Shi | | 15 | Betty Cudly | | 16 | Stella Stevens | | 17 | Edgar Alan | | 21 | Ruby Richer | +----+-----------------+ 13 rows in set (0.00 sec)

  • /

Drop table employee_person; CREATE TABLE employee_person ( 

   id int unsigned not null primary key, 
   address varchar(60), 
   phone int, 
   email varchar(60), 
   birthday DATE, 
   sex ENUM("M", "F"), 
   m_status ENUM("Y","N"), 
   s_name varchar(40), 
   children int

);

INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name) values (1, "200, Regina Street", 7176666, "net@hotmail.ru", "1971-04-26", "M", "Y", "Ane Regina"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (2, "1232 Alberta Road", 5553312, "jo@hotmail.ru", "1968-03-02", "M", "Y", "Jane Van", 3); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (3, "90 Potter A", 3331211, "gp@ymail.ru", "1967-09-22", "M", "N", "Sandhya Pil", 2); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name) values (4, "202, Donna Street", 7176167, "twink@hotmail.ru", "1976-08-09", "F", "Y", "Manish Sha"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (5, "Apartment #8, UBC, Van Street", 8973242, "holy@ymail.ru", "1974-10-14", "F", "N"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (6, "46 SFU Street", "6451234", "kill@el.ru", "1978-12-31", "M", "N"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (7, "432 Mercury Ave", 7932232, "mac@hotmail.ru", "1966-8-21", "M", "Y", "Mary Shelly", "3"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (8, "8 Little YaleTown", 5442994, "edd@gmail.ru", "1975-01-14", "M", "N"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (9, "64 Temp Road", 4327652, "nan@pmail.ru", "1969-05-19", "M", "Y", "Man Nanda", "1"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (10, "132 Metro House, Henry Street", 5552376, "ra@hotmail.ru", "1968-07-06", "M", "N"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (11, "1 Grace Town, Van Avenue", 5433879, "soundofsilence@boxer.net", "1957-11-04", "M", "Y", "Muriel Lovelace", "4"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (12, "97 Oakland Road", 5423311, "kingarthur@roundtable.org", "1968-02-15", "M", "Y", "Rina Brighton", 3); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (13, "543 Applegate Lane", 3434343, "levy@cmail.ru", "1968-09-03", "F", "Y", "Matt Shi", "2"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (14, "76 Fish Street", 7432433, "tink@email.ru", "1965-04-28", "M", "N"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (15, "98 Gun Street", 6500787, "danny@fhardy.ru", "1966-06-23", "M", "Y", "Betty Cudly", 3); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (16, "#5 Winnepag Homes", 5433243, "mike@cmail.ru", "1964-03-06", "M", "Y", "Stella Stevens", 2); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (17, "652 Devon Building, 6th Jade Avenue", 5537885, "mona@darling.ru", "1970-04-18", "F", "Y", "Edgar Alan", 1); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (18, "Apartment #9, Together Towers", 5476565, "odessey@hotmail.ru", "1973-10-09", "M", "N"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (19, "Apartment #9, West Towers", 5476565, "jire@hotmail.ru", "1973-1-20", "M", "N"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (20, "90 Yale Town", 7528326, "help@more.org", "1968-01-25", "F", "N"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (21, "4329 Eucalyptus Avenue", 4254863, "money@cold.ru", "1964-06-13", "M", "Y", "Ruby Richer", 2);


select * from employee_person;

select id, s_name from employee_person where s_name IS NOT NULL; 


      </source>


Retrieve NULL value

   <source lang="sql">

/* mysql> select * from Student; +-----------+------------+-----------+ | StudentID | first_name | last_name | +-----------+------------+-----------+ | 1 | John | Jones | | 2 | NULL | Burton | | 3 | Emily | Scarlett | | 4 | Bruce | Lee | | 5 | Anna | Wolff | | 6 | Vic | Andrews | | 7 | NULL | Alaska | +-----------+------------+-----------+ 7 rows in set (0.01 sec) mysql> select * from Student WHERE first_name IS NULL; +-----------+------------+-----------+ | StudentID | first_name | last_name | +-----------+------------+-----------+ | 2 | NULL | Burton | | 7 | NULL | Alaska | +-----------+------------+-----------+ 2 rows in set (0.02 sec)

  • /

Drop table Student; CREATE TABLE Student ( 

  StudentID INT NOT NULL PRIMARY KEY,
  first_name      VARCHAR(50),
  last_name      VARCHAR(50) NOT NULL

)TYPE = InnoDB;

INSERT INTO Student (StudentID,first_name, last_name) VALUES (4,"Bruce", "Lee"); INSERT INTO Student (StudentID,first_name, last_name) VALUES (1,"John", "Jones"); INSERT INTO Student (StudentID, last_name) VALUES (2, "Burton"); INSERT INTO Student (StudentID, last_name) VALUES (7, "Alaska"); INSERT INTO Student (StudentID,first_name, last_name) VALUES (5,"Anna", "Wolff"); INSERT INTO Student (StudentID,first_name, last_name) VALUES (6,"Vic", "Andrews"); INSERT INTO Student (StudentID,first_name, last_name) VALUES (3,"Emily", "Scarlett"); select * from Student; select * from Student WHERE first_name IS NULL; 

      </source>


Select NOT NULL value

   <source lang="sql">

/* mysql> select * from employee_person; +----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+ | id | address | phone | email | birthday | sex | m_status | s_name | children | +----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+ | 1 | 200, Regina Street | 7176666 | net@hotmail.ru | 1971-04-26 | M | Y | Ane Regina | NULL | | 2 | 1232 Alberta Road | 5553312 | jo@hotmail.ru | 1968-03-02 | M | Y | Jane Van | 3 | | 3 | 90 Potter A | 3331211 | gp@ymail.ru | 1967-09-22 | M | N | Sandhya Pil | 2 | | 4 | 202, Donna Street | 7176167 | twink@hotmail.ru | 1976-08-09 | F | Y | Manish Sha | NULL | | 5 | Apartment #8, UBC, Van Street | 8973242 | holy@ymail.ru | 1974-10-14 | F | N | NULL | NULL | | 6 | 46 SFU Street | 6451234 | kill@el.ru | 1978-12-31 | M | N | NULL | NULL | | 7 | 432 Mercury Ave | 7932232 | mac@hotmail.ru | 1966-08-21 | M | Y | Mary Shelly | 3 | | 8 | 8 Little YaleTown | 5442994 | edd@gmail.ru | 1975-01-14 | M | N | NULL | NULL | | 9 | 64 Temp Road | 4327652 | nan@pmail.ru | 1969-05-19 | M | Y | Man Nanda | 1 | | 10 | 132 Metro House, Henry Street | 5552376 | ra@hotmail.ru | 1968-07-06 | M | N | NULL | NULL | | 11 | 1 Grace Town, Van Avenue | 5433879 | soundofsilence@boxer.net | 1957-11-04 | M | Y | Muriel Lovelace | 4 | | 12 | 97 Oakland Road | 5423311 | kingarthur@roundtable.org | 1968-02-15 | M | Y | Rina Brighton | 3 | | 13 | 543 Applegate Lane | 3434343 | levy@cmail.ru | 1968-09-03 | F | Y | Matt Shi | 2 | | 14 | 76 Fish Street | 7432433 | tink@email.ru | 1965-04-28 | M | N | NULL | NULL | | 15 | 98 Gun Street | 6500787 | danny@fhardy.ru | 1966-06-23 | M | Y | Betty Cudly | 3 | | 16 | #5 Winnepag Homes | 5433243 | mike@cmail.ru | 1964-03-06 | M | Y | Stella Stevens | 2 | | 17 | 652 Devon Building, 6th Jade Avenue | 5537885 | mona@darling.ru | 1970-04-18 | F | Y | Edgar Alan | 1 | | 18 | Apartment #9, Together Towers | 5476565 | odessey@hotmail.om | 1973-10-09 | M | N | NULL | NULL | | 19 | Apartment #9, West Towers | 5476565 | jire@hotmail.ru | 1973-01-20 | M | N | NULL | NULL | | 20 | 90 Yale Town | 7528326 | help@more.org | 1968-01-25 | F | N | NULL | NULL | | 21 | 4329 Eucalyptus Avenue | 4254863 | money@cold.ru | 1964-06-13 | M | Y | Ruby Richer | 2 | +----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+ 21 rows in set (0.00 sec) mysql> select id, children from employee_person where children IS NOT NULL; +----+----------+ | id | children | +----+----------+ | 2 | 3 | | 3 | 2 | | 7 | 3 | | 9 | 1 | | 11 | 4 | | 12 | 3 | | 13 | 2 | | 15 | 3 | | 16 | 2 | | 17 | 1 | | 21 | 2 | +----+----------+ 11 rows in set (0.00 sec)

  • /

Drop table employee_person; CREATE TABLE employee_person ( 

   id int unsigned not null primary key, 
   address varchar(60), 
   phone int, 
   email varchar(60), 
   birthday DATE, 
   sex ENUM("M", "F"), 
   m_status ENUM("Y","N"), 
   s_name varchar(40), 
   children int

);

INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name) values (1, "200, Regina Street", 7176666, "net@hotmail.ru", "1971-04-26", "M", "Y", "Ane Regina"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (2, "1232 Alberta Road", 5553312, "jo@hotmail.ru", "1968-03-02", "M", "Y", "Jane Van", 3); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (3, "90 Potter A", 3331211, "gp@ymail.ru", "1967-09-22", "M", "N", "Sandhya Pil", 2); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name) values (4, "202, Donna Street", 7176167, "twink@hotmail.ru", "1976-08-09", "F", "Y", "Manish Sha"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (5, "Apartment #8, UBC, Van Street", 8973242, "holy@ymail.ru", "1974-10-14", "F", "N"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (6, "46 SFU Street", "6451234", "kill@el.ru", "1978-12-31", "M", "N"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (7, "432 Mercury Ave", 7932232, "mac@hotmail.ru", "1966-8-21", "M", "Y", "Mary Shelly", "3"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (8, "8 Little YaleTown", 5442994, "edd@gmail.ru", "1975-01-14", "M", "N"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (9, "64 Temp Road", 4327652, "nan@pmail.ru", "1969-05-19", "M", "Y", "Man Nanda", "1"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (10, "132 Metro House, Henry Street", 5552376, "ra@hotmail.ru", "1968-07-06", "M", "N"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (11, "1 Grace Town, Van Avenue", 5433879, "soundofsilence@boxer.net", "1957-11-04", "M", "Y", "Muriel Lovelace", "4"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (12, "97 Oakland Road", 5423311, "kingarthur@roundtable.org", "1968-02-15", "M", "Y", "Rina Brighton", 3); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (13, "543 Applegate Lane", 3434343, "levy@cmail.ru", "1968-09-03", "F", "Y", "Matt Shi", "2"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (14, "76 Fish Street", 7432433, "tink@email.ru", "1965-04-28", "M", "N"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (15, "98 Gun Street", 6500787, "danny@fhardy.ru", "1966-06-23", "M", "Y", "Betty Cudly", 3); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (16, "#5 Winnepag Homes", 5433243, "mike@cmail.ru", "1964-03-06", "M", "Y", "Stella Stevens", 2); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (17, "652 Devon Building, 6th Jade Avenue", 5537885, "mona@darling.ru", "1970-04-18", "F", "Y", "Edgar Alan", 1); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (18, "Apartment #9, Together Towers", 5476565, "odessey@hotmail.ru", "1973-10-09", "M", "N"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (19, "Apartment #9, West Towers", 5476565, "jire@hotmail.ru", "1973-1-20", "M", "N"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (20, "90 Yale Town", 7528326, "help@more.org", "1968-01-25", "F", "N"); INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (21, "4329 Eucalyptus Avenue", 4254863, "money@cold.ru", "1964-06-13", "M", "Y", "Ruby Richer", 2); select * from employee_person; select id, children from employee_person where children IS NOT NULL; 

      </source>


Use the IS NULL and IS NOT NULL operators

   <source lang="sql">

/* mysql> SELECT 1 IS NULL, 1 IS NOT NULL; +-----------+---------------+ | 1 IS NULL | 1 IS NOT NULL | +-----------+---------------+ | 0 | 1 | +-----------+---------------+ 1 row in set (0.00 sec)

  • /

SELECT 1 IS NULL, 1 IS NOT NULL; 

      </source>


Working with NULL Values

   <source lang="sql">

/* mysql> /* mysql> NULL means a missing unknown value mysql> is treated somewhat differently than other values. mysql> */ mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL; +----------+-----------+----------+----------+ | 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL | +----------+-----------+----------+----------+ | NULL | NULL | NULL | NULL | +----------+-----------+----------+----------+ 1 row in set (0.00 sec)

  • /

/* NULL means a missing unknown value 

    is treated somewhat differently than other values.
  • /

SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL; 

      </source>
Источник — «http://sqle.ru/index.php?title=SQL/MySQL/Data_Type/Null&oldid=5243»