Left outer join - how to return a boolean for existence in the second table?
coalesce, join, left-join, outer-join, postgresql
Solution
A CASE or COALESCE statement with an outer join IS the proper way to do this.
select
CASE
WHEN b.id IS NULL THEN true
ELSE false
END AS banned,
u.id,
u.first_name,
u.last_name,
u.city,
u.last_ip,
to_char(u.login, 'DD.MM.YYYY') as day
from pref_users u
left outer join pref_ban2 b
on u.id=b.id
limit 10;
Problem
In PostgreSQL 9 on CentOS 6 there are 60000 records in `pref_users` table: ``` # \d pref_users Table "public.pref_users" Column | Type | Modifiers ------------+-----------------------------+-------------------- id | character varying(32) | not null first_name | character varying(64) | not null last_name | character varying(64) | login | timestamp without time zone | default now() last_ip | inet | (... more columns skipped...) ``` And another table holds around 500 ids of users which are not allowed to play anymore: ``` # \d pref_ban2 Table "public.pref_ban2" Column | Type | Modifiers ------------+-----------------------------+--------------- id | character varying(32) | not null first_name | character varying(64) | last_name | character varying(64) | city | character varying(64) | last_ip | inet | reason | character varying(128) | created | timestamp without time zone | default now() Indexes: "pref_ban2_pkey" PRIMARY KEY, btree (id) ``` In a PHP script I am trying to display all 60000 users from `pref_users` in a jQuery-dataTable. And I would like to mark the banned users (the users found in `pref_ban2`). Which means I need a column named `ban` for each record in my query holding `true` or `false`. So I am trying a left outer join query: ``` # select b.id, -- how to make this column a boolean? u.id, u.first_name, u.last_name, u.city, u.last_ip, to_char(u.login, 'DD.MM.YYYY') as day from pref_users u left outer join pref_ban2 b on u.id=b.id limit 10; id | id | first_name | last_name | city | last_ip | day ----+----------+-------------+-----------+-------------+-----------------+------------ | DE1 | Alex | | Bochum | 2.206.0.224 | 21.11.2014 | DE100032 | Княжна Мэри | | London | 151.50.61.131 | 01.02.2014 | DE10011 | Aлександр Ш | | Симферополь | 37.57.108.13 | 01.01.2014 | DE10016 | Semen10 | | usa | 69.123.171.15 | 25.06.2014 | DE10018 | Горловка | | Горловка | 178.216.97.214 | 25.09.2011 | DE10019 | -Дмитрий- | | пермь | 5.140.81.95 | 21.11.2014 | DE10047 | Василий | | Cумы | 95.132.42.185 | 25.07.2014 | DE10054 | Maedhros | | Чикаго | 207.246.176.110 | 26.06.2014 | DE10062 | ssergw | | москва | 46.188.125.206 | 12.09.2014 | DE10086 | Вадим | | Тула | 109.111.26.176 | 26.02.2012 (10 rows) ``` As you can see the `b.id` column above is empty - because these 10 users aren't banned. How to get a `false` value in that column instead of a String? And I am not after some `coalesce`or `case` expression, but am looking for "the proper" way to do such a query.