NULL  SQL. 18     
 


   NULL  SQL  ,         - .  18    : IS NULL, IS NOT NULL, COUNT, COALESCE, AVG, CASE WHEN, LEFT JOIN, NOT IN,   ,   ,  NULL     .     SQLite,     .





 

NULL  SQL. 18     





  





 NULL    


NULL  SQL     ,   ,       .

- NULL    ,     : COUNT , AVG  ,     .

      ,   :  ,  ,      .




    NULL


       NULL.    ,    ,     .

       NULL  ,    .     IS NULL  IS NOT NULL.

COALESCE     ,         ,     .




  


1.     (#task-1)

2.    email (#task-2)

3.  COUNT(*)  COUNT(column) (#task-3)

4.      (#litres_trial_promo)

5.      (#litres_trial_promo)

6. ,  AVG  NULL (#litres_trial_promo)

7.      (#litres_trial_promo)

8.     LEFT JOIN (#litres_trial_promo)

9.   NOT IN  NULL (#litres_trial_promo)

10.      (#litres_trial_promo)

11.    (#litres_trial_promo)

12.      NULL (#litres_trial_promo)

13.    NULL (#litres_trial_promo)

14.    NULL   (#litres_trial_promo)

15.    (#litres_trial_promo)

16.     (#litres_trial_promo)

17.      (#litres_trial_promo)

18.      (#litres_trial_promo)




 





 1.    



 

  ,    ,      .




CREATE TABLE clients(id INTEGER, name TEXT, phone TEXT);

INSERT INTO clients VALUES

(1,'','+79990000001'),

(2,'',NULL),

(3,'','+79990000003'),

(4,'',NULL);




SELECT id, name

FROM clients

WHERE phone IS NULL

ORDER BY id;


 

[[2, ""], [4, ""]]


 

IS NULL    .

  2  4   NULL.

ORDER BY    .


  

 phone = NULL    ,   NULL     .


 

      IS NULL      ?

:   NULL   ,   phone = NULL   .




 2.    email



 

   ,   email       .




CREATE TABLE clients(id INTEGER, email TEXT);

INSERT INTO clients VALUES

(1,'a@example.test'),

(2,NULL),

(3,''),

(4,'n@example.test');




SELECT id, email

FROM clients

WHERE email IS NOT NULL

AND email <> ''

ORDER BY id;


 

[[1, "a@example.test"], [4, "n@example.test"]]


 

IS NOT NULL   email.

email <> ''    .

     1  4.


  

   NULL   . COUNT(email)      .


 

     ,      NULL?

:     email <> ''.




 3.  COUNT(*)  COUNT(column)



 

 ,            .




CREATE TABLE orders(id INTEGER, total INTEGER);

INSERT INTO orders VALUES

(1,500),

(2,NULL),

(3,300),

(4,NULL);




SELECT COUNT(*) AS rows_count,

COUNT(total) AS known_total_count

FROM orders;


 

[[4, 2]]


 

COUNT(*)    .




  .


   .

   ,     (https://www.litres.ru/book/raznoe/null-v-sql-18-zadach-pro-propuski-v-dannykh-74419432/)  .

      Visa, MasterCard, Maestro,    ,   ,     ,  PayPal, WebMoney, ., QIWI ,       .


