Left outer join - how to return a boolean to exist in the second table?

Question

PostgreSQL 9 on CentOS 6 has 60,000 entries in the 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 contains about 500 user IDs that are no longer allowed to play:

 # \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 map all 60,000 users from pref_users to jQuery-dataTable. And I would like to mention the banned users (users found in pref_ban2 ).

This means that I need a column named ban for each record in my query containing true or false .

So, I'm trying to execute the query on the left of the outer join :

 # 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 are not banned.

How to get false value in this column instead of row?

And I'm not after some coalesce or case expression, but I'm looking for the “right” way to make such a request.

Answer 1

A CASE or COALESCE statement with an external join is the right 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; 

Answer 2

"IS NULL" and "IS NOT NULL" return a boolean value, so this should make the task easier.

I think this is all you need?

 SELECT b.id IS NOT NULL as is_banned, -- The value of "is_banned" will be a boolean 

Not sure if you need “NOT” or not, but you will get a bool anyway.