How to delete a row in SQL based on a NULL condition

Question

I just started learning SQL; I've created a table. Learned insert command and inserted values in 2 rows. However I've inserted null values in 3rd.

Now I want to delete the third row which has 2 columns with no values in it.

I'm using the following query:

delete employee where city=null;

It doesn't seem to be working!

possible duplicate of [SQL is null and = null](http://stackoverflow.com/questions/9581745/sql-is-null-and-null) — Yaroslav Shabalin, Feb 24 '14 at 13:40

score 8 · Accepted Answer · answered Feb 24 '14 at 13:14

According SQL 92 standard many logical operations with null values like

   > null
   = null
  and null
   or null
  not null

should always return null (and never true). Some DBMS (e.g. Oracle) follow this rule rigorously, some (MS SQL) can have a mode that null = null returns true, not required null. In order to be compartible with SQL 92 and so with (almost) all DBMSs, you should use is null or is not null standard comparisons, in your case

  delete from employee 
        where city is null -- <- Standard comparison

score 4 · Answer 2 · answered Feb 24 '14 at 13:06

4

You need the is null "operator":

delete from employee where city is null;

This is because in SQL, nothing is equal to NULL.

answered Feb 24 '14 at 13:06

René Nyffenegger

39,402
33
158
293

score 3 · Answer 3 · answered Feb 24 '14 at 13:06

3

You can't use = with NULL. Instead, use:

delete employee where city is null;

answered Feb 24 '14 at 13:06

Tobberoth

9,327
2
19
17

score 1 · Answer 4 · answered Aug 28 '19 at 21:15

1

To achieve this you will have to write this query

DELETE from table_name WHERE city IS NULL;

this query will delete the rows/records WHERE city is null

answered Aug 28 '19 at 21:15

Umer Khan Yousefzai

21
2

i'm sorry i didnt saw the answer :( – Umer Khan Yousefzai Aug 29 '19 at 15:04

score 0 · Answer 5 · answered Feb 24 '14 at 13:17

Here you know you have added Null in city column so checking with is null will work but it's also possible to add an empty string in city column. So if your business condition is like delete all records from employee table where city is either null or empty i.e. with no values you should write as:

delete from employee where isnull(city,'')='';

score -3 · Answer 6 · answered Feb 24 '14 at 13:11

-3

change it :

delete from table_name where city is null

or

delete from table_name where city = null

answered Feb 24 '14 at 13:11

Vaimin

7
1
6

The from is not needed (at least in SQL Server), and your second option has the same problem: Equality always shows false when something is compared to NULL. – Tobberoth Feb 24 '14 at 13:21

How to delete a row in SQL based on a NULL condition

6 Answers6