MySQL IS NULL/IS NOT NULL Tutorial

NULL vrednosti su one koje nisu definisane u nekom redu u tabeli, ali za to postoji polje.

Da bi lakse izvlacili izvestaje iz tabela, potrebno je razumeti termine "IS NULL" i "IS NOT NULL".

Koristicemo tabelu "prodavnice", sa opcionom kolonom "menadzer". Neka polja su namerno ostala nepopunjena. (Mozete u njih ubaciti podatke rucno prekp Update ili phpMyAdmin-a u WAMP-u):

mysql> SELECT * FROM prodavnice;
+------+-----------+------------+-----------+-------------+------+------------------+
| p_id | gradovi   | prodavnice | proizvod  | raspolozivo | cena | menadzer         |
+------+-----------+------------+-----------+-------------+------+------------------+
|    1 | Beograd   | prbg1      | Kafa      |         121 |  150 | Peric Mirko      |
|    2 | Beograd   | prbg2      | Cigare    |          50 |  300 | NULL             |
|    3 | Subotica  | prsub1     | Hleb      |          43 |   45 | Zivkovic Milica  |
|    4 | Nis       | prnis1     | Hleb      |          23 |   55 | NULL             |
|    5 | Kraljevo  | prkrl1     | Pavlaka   |         155 |   50 | NULL             |
|    6 | Uzice     | pruzi1     | Mleko     |          68 |   90 | Stanojevic Petar |
|    7 | Majdanpek | prmaj1     | Mleko     |          91 |   87 | NULL             |
|    8 | Bor       | prbor1     | Pavlaka   |         155 |   50 | NULL             |
|    9 | Kikinda   | prkik1     | Ajzaklija |          38 |  650 | Igor Vukicevic   |
|   10 | Obrenovac | prob1      | Jabuke    |          35 |  120 | NULL             |
|   11 | Smederevo | prsm1      | Ajvar     |          28 |  270 | NULL             |
|   12 | Zrenjanin | przr1      | Somun     |          10 |   59 | Zorica Milicevic |
|   13 | Jagodina  | prjg1      | Bakalar   |          29 |  400 | NULL             |
+------+-----------+------------+-----------+-------------+------+------------------+
13 rows in set (0.00 sec)

mysql>

SQL IS NULL Primer

Sta ukoliko zelimo "izvuci" iz tabele sva polja koja nisu popunjena?

SELECT * FROM prodavnice
WHERE menadzer IS NULL

mysql> SELECT * FROM prodavnice
    -> WHERE menadzer IS NULL;
+------+-----------+------------+----------+-------------+------+----------+
| p_id | gradovi   | prodavnice | proizvod | raspolozivo | cena | menadzer |
+------+-----------+------------+----------+-------------+------+----------+
|    2 | Beograd   | prbg2      | Cigare   |          50 |  300 | NULL     |
|    4 | Nis       | prnis1     | Hleb     |          23 |   55 | NULL     |
|    5 | Kraljevo  | prkrl1     | Pavlaka  |         155 |   50 | NULL     |
|    7 | Majdanpek | prmaj1     | Mleko    |          91 |   87 | NULL     |
|    8 | Bor       | prbor1     | Pavlaka  |         155 |   50 | NULL     |
|   10 | Obrenovac | prob1      | Jabuke   |          35 |  120 | NULL     |
|   11 | Smederevo | prsm1      | Ajvar    |          28 |  270 | NULL     |
|   13 | Jagodina  | prjg1      | Bakalar  |          29 |  400 | NULL     |
+------+-----------+------------+----------+-------------+------+----------+
8 rows in set (0.01 sec)

mysql>

SQL IS NOT NULL Primer

A ako nam trebaju svi redovi gde je polje "menadzer" popunjeno ?

SELECT * FROM prodavnice
WHERE menadzer IS NOT NULL

mysql> SELECT * FROM prodavnice
    -> WHERE menadzer IS NOT NULL;
+------+-----------+------------+-----------+-------------+------+------------------+
| p_id | gradovi   | prodavnice | proizvod  | raspolozivo | cena | menadzer         |
+------+-----------+------------+-----------+-------------+------+------------------+
|    1 | Beograd   | prbg1      | Kafa      |         121 |  150 | Peric Mirko      |
|    3 | Subotica  | prsub1     | Hleb      |          43 |   45 | Zivkovic Milica  |
|    6 | Uzice     | pruzi1     | Mleko     |          68 |   90 | Stanojevic Petar |
|    9 | Kikinda   | prkik1     | Ajzaklija |          38 |  650 | Igor Vukicevic   |
|   12 | Zrenjanin | przr1      | Somun     |          10 |   59 | Zorica Milicevic |
+------+-----------+------------+-----------+-------------+------+------------------+
5 rows in set (0.00 sec)

mysql>