What's the difference between the queries? "SELECT * FROM bookings where code = null" and "SELECT * FROM bookings where code IS null"

Question

I get different outputs for these queries. Did anyone face this?

The `= null` comparison is invalid SQL. Use `is null`. – jarlh Aug 23 '18 at 07:49 — jarlh, Aug 23 '18 at 07:49

score 2 · Answer 1 · answered Aug 23 '18 at 07:54

2

The col= null would not work because

NULL means I don't know. which is a placeholder to say there is the absence of a value.

= evaluate for values, so that will not to work.

You need to use IS NULL to get NULL row value.

answered Aug 23 '18 at 07:54

D-Shih

44,943
6
31
51

Mischa · Answer 2 · 2021-09-13T17:31:41.000

0

That's completely normal. "X = NULL" is NULL, which amounts to false ("NOT X = NULL" is also false; NULL is falser than false). For most SQL dialects.

edited Sep 13 '21 at 17:31

answered Aug 23 '18 at 07:48

Mischa

2,240
20
18

2

Wrong. In postgres it always evaluates to NULL, not FALSE. – 404 Aug 23 '18 at 08:53

score 0 · Answer 3 · answered Aug 23 '18 at 07:54

0

=NULL IS is an expression of a value and IS NULL is the preferred method of evaluating the condition of a variable being NULL.

Here is the detail description Click here

answered Aug 23 '18 at 07:54

Saurabh Ghewari

689
3
8

1

OP using Postgres, not MS SQL. There `IS NULL` is **only** valid method for checking, not preferred as you mentioned. – Evgeny Nozdrev Aug 23 '18 at 09:22
Ohhk. Thank you. – Saurabh Ghewari Aug 23 '18 at 09:36

score 0 · Answer 4 · answered Aug 23 '18 at 08:01

0

The first statement, SELECT * FROM bookings where code = null--

It Checks Directly if any row has the value null

The Second Statement, SELECT * FROM bookings where code IS null

It Checks all the rows its a null or not..

answered Aug 23 '18 at 08:01

Ranjith

153
8

1

First query does not check on NULL at all. – Evgeny Nozdrev Aug 23 '18 at 09:23

What's the difference between the queries? "SELECT * FROM bookings where code = null" and "SELECT * FROM bookings where code IS null"

4 Answers4