MySQL select boolean fields and create 3rd states

I’m using MySQL and I want to get a boolean with 3 states (-1,0,1) on existing fields.

E.g if I have col1 = null or row doesn't exist col2 = 0 col3 = 1

Then I do :

SELECT col1, col2, col3 FROM customers

I want to get that :

Output
col1 = -1
col2 = 0
col3 = 1

How it’s possible ?

note : In my code I’ll use LEFT JOIN in my query so when the row doesn’t exist so I think I’ll get null.

Answer

Something like this:

SELECT
  a.a,
  COALESCE(b.b, -1) as b
FROM
  a LEFT JOIN B ON a.id = b.aid

Suppose we have tables:

A.id, A.a
1, 'hello'
2, 'goodbye'
3, 'what'

B.aid, b.b
1, 1
2, null

When we join them with left join we will get:

A.id, a.a,       B.aid, b.b
1,    'hello',   1,     1
2,    'goodbye', 2,     null     --b.b is null because the row value is null
3,    'what',    null,  null     --b.b is null because there is no row, all b columns are null

COALESCE looks at the arguments in order from left to right, returning the first non null one. If the row in b doesn’t exist, or it does exist but b.b is NULL in the table, then COALESCE will turn it into -1:

A.id, a.a,       B.aid, b.b
1,    'hello',   1,     1
2,    'goodbye', 2,     -1       --b.b is -1 because COALESCE found -1 as the first non null value
3,    'what',    null,  -1       --b.b is -1 because COALESCE found -1 as the first non null value

If however you want to use 0 if b.b is null but -1 if b.b doesn’t exist, you need to CASE WHEN using something that is a value:

SELECT
  a.a,
  CASE WHEN b.aid IS NULL THEN -1 ELSE COALESCE(b.b, 0) END as b 
FROM
  a LEFT JOIN B ON a.id = b.aid

Here we inspect one of the columns used in the join, b.aid. If it is null we know the join failed and there is no row in B for that row in A, hence we can CASE WHEN test b.aid for being null and if it is, then put -1 as our “-1 means no row was in b”, ELSE we can COALESCE to turn b.b into 0 if it is null. This gives a result like:

A.id, a.a,    B.aid, b.b
1, 'hello',   1,     1
2, 'goodbye', 2,     0        --b.b is 0 because CASE WHEN's ELSE block was used, and COALESCE found 0 as the first non null value
3, 'what',    null, -1       --b.b is -1 because CASE WHEN's WHEN block fired, because b.aid was null, and CASE WHEN provided -1 as the value