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.