Return value of comparison operator in SELECT clause

Background

Some RDBMS can contain the result of comparison operator in SELECT clause. However, the returned values are different between RDBMS.

Result

MySQL

I used MySQL 5.6 on SQL Fiddle.

DDL (MySQL)

CREATE TABLE Table1 (`value1` int, `value2` int, `value3` int);

INSERT INTO Table1 (`value1`, `value2`, `value3`)
VALUES             (1, 2, 3),
                   (2, 4, 6),
                   (3, 6, 9);

DML (MySQL)

select value1, value2, value3, value1=1, value2=4, value3=9 from table1;

Result (MySQL)

| value1 | value2 | value3 | value1=1 | value2=4 | value3=9 |
|--------|--------|--------|----------|----------|----------|
|      1 |      2 |      3 |        1 |        0 |        0 |
|      2 |      4 |      6 |        0 |        1 |        0 |
|      3 |      6 |      9 |        0 |        0 |        1 |

PostgreSQL

I used PostgreSQL 9.6 on SQL Fiddle.

DDL (PostgreSQL)

CREATE TABLE Table1 ("value1" int, "value2" int, "value3" int);

INSERT INTO Table1 ("value1", "value2", "value3")
VALUES             (1, 2, 3),
                   (2, 4, 6),
                   (3, 6, 9);

DML (PostgreSQL)

select value1, value2, value3, value1=1, value2=4, value3=9 from table1;

Result (PostgreSQL)

| value1 | value2 | value3 | ?column? | ?column? | ?column? |
|--------|--------|--------|----------|----------|----------|
|      1 |      2 |      3 |     true |    false |    false |
|      2 |      4 |      6 |    false |     true |    false |
|      3 |      6 |      9 |    false |    false |     true |

SQL Server 2014

I used SQL SQL Server 2014 on SQL Fiddle.

DDL (SQL Server)

CREATE TABLE Table1
    ("value1" int, "value2" int, "value3" int)
;
    
INSERT INTO Table1 ("value1", "value2", "value3") VALUES (1, 2, 3);
INSERT INTO Table1 ("value1", "value2", "value3") VALUES (2, 4, 6);
INSERT INTO Table1 ("value1", "value2", "value3") VALUES (3, 6, 9);

DML (SQL Server)

Note that value2=4 returns always 4. It doesn't cause any error, but it must be unexpected result. You have to use case when instead of = operator.

select
  value1, value2, value3, 
  case when value1 = 1 then 1 else 0 end,
  case when value2 = 4 then 1 else 0 end,
  case when value3 = 9 then 1 else 0 end
from table1;

Result (SQL Server)

| value1 | value2 | value3 |   |   |   |
|--------|--------|--------|---|---|---|
|      1 |      2 |      3 | 1 | 0 | 0 |
|      2 |      4 |      6 | 0 | 1 | 0 |
|      3 |      6 |      9 | 0 | 0 | 1 |

Oracle

I used Oracle 11g R2 on SQL Fiddle.

DDL (Oracle)

CREATE TABLE Table1
    (value1 int, value2 int, value3 int)
;
    
INSERT INTO Table1 (value1, value2, value3) VALUES (1, 2, 3);
INSERT INTO Table1 (value1, value2, value3) VALUES (2, 4, 6);
INSERT INTO Table1 (value1, value2, value3) VALUES (3, 6, 9);

DML (Oracle)

Note that value2=4 in SELECT clause causes "ORA-00923: FROM keyword not found where expected" error. You have to use case when instead of = operator.

select
  value1, value2, value3,
  case when value1 = 1 then 1 else 0 end,
  case when value2 = 4 then 1 else 0 end,
  case when value3 = 9 then 1 else 0 end
from table1;

Result (Oracle)

| VALUE1 | VALUE2 | VALUE3 | CASEWHENVALUE1=1THEN1ELSE0END | CASEWHENVALUE2=4THEN1ELSE0END | CASEWHENVALUE3=9THEN1ELSE0END |
|--------|--------|--------|-------------------------------|-------------------------------|-------------------------------|
|      1 |      2 |      3 |                             1 |                             0 |                             0 |
|      2 |      4 |      6 |                             0 |                             1 |                             0 |
|      3 |      6 |      9 |                             0 |                             0 |                             1 |