Today I learned SQL is weird

Coffee Lounge
#1

Goal: Select all rows whose column “enabled” (boolean, nullable) is different than true

Pick your first attempt:

  • SELECT * FROM table WHERE enabled != true
  • SELECT * FROM table WHERE enabled IS NOT true
  • SELECT * FROM table WHERE enabled <> true
  • Other
  • I checked the spoiler before answering (don’t do it!)

0 voters

This is on a PostgreSQL database, don’t know if there’s any difference to MySQL/MariaDB

Spoiler (only click this after answering the poll) 
SELECT * FROM table WHERE enabled != true

Nope

SELECT * FROM table WHERE enabled IS NOT true

Nope

SELECT * FROM table WHERE enabled <> true

Yes

#2

Postgresql is weired than my/maria coz it is ACID complaint.
Why did you not use = false?

#3

This is a very specific case of mine where a boolean may be null by intention, hence I’d need to use = false OR = null if I were to go that route

My first attempt was != true, and didn’t return what I wanted, and I didn’t want to use =false OR =null without knowing why != true failed… I still don’t know why, but now I know I have to use <> instead :sweat_smile:

#4

Are you sure != doesn’t work? In Pg <> and != are the same. <> is a standard SQL operator and Pg actually converts != to <> when parsing.

2 Likes
#5

I don’t know what to tell you - I’ve justed tested your theory, and it’s correct

Ghosts :ghost:

I don’t remember the exact query I experienced this issue - as far as I remember, simply swapping != to <> things started working as I wanted :roll_eyes:

1 Like
#6

We’ve all been there buddy…we’ve all been there.

#7

According to this link below:
using = would only work for PostgreSQL if you treated true like a string value with single or double quotes

AND

Comparing a string with IS won’t work. For example, WHERE a IS 'true' , will cause an error.

Supposedly working –

SELECT * FROM table WHERE enabled = ‘true’

or using LIKE

SELECT * FROM table WHERE enabled LIKE ‘true’

(I haven’t opened up an example yet to test)

1 Like
#8

This guy has some good SQL query lessons

following his suggestion I also went to Kaggle, signed up (free) and I can download realworld datasets to use for testing

#9

NewMedia’s here! :smile:

I’ve tested here a bit, looks like != true or != ‘true’ has the same behaviour

Although, I’ve discovered none of them pick NULL values!

#10

Correct way is

SELECT * FROM table WHERE enabled <> true OR enabled IS NULL

or

SELECT * FROM table WHERE enabled != true OR enabled IS NULL

The <> and != do the same but will never include the null values, so you have to add the null check also.

#11

However, you need to use WHERE enabled IS to test against NULL` options.

From the article I linked to.

#12

With MS SQL Server you can change the behavior of NULL values

SET ANSI_NULLS OFF

SELECT * FROM table WHERE enabled <> true

By setting ANSI_NULLS to OFF it will return also the NULL values with the query above. There is not something like that in Postgres as far I know.

SQL Server ANSI_NULLS Explained (database.guide)

#13

Imo, if you want three things: true/false/null better use int or enum. boolean, as the name suggests is for two things: true/false. I keep my bool columns default false/true and use then fir false or true only. My thought.