Pgsql return from second table even if no record exist

Question

I have columns in the first table

permission_id - permission

and columns second table

user_id - permission_id -- is_allowed

Now The below query is working fine when row exists for user_id

select 
                f.permission,
                coalesce(s.is_allowed,false) as is_allowed
                
            from table1 f
        left join table2 s on f.permission_id = s.permission_id
            where s.user_id = 282 

But it returns null when there are no rows in the second table for the user

What I need, it should return all rows from the first table and is_allowed as it exists in the second table, if not exists it should return false.

I tried where s.is_allowed is null but the output is not correct

first table is master table of permissions, and second table has users with permission allowed, so I need to show all the permissions and then against it if allowed true else false, by default it should be false if there is no entry for users in second table

Thanks,

Answer 1

If I understood you correctly, that should be

from table2 s left join table1 f on f.permission_id = s.permission_id

i.e. just the opposite of what you have because table2 will contain data for user_id = 282, but its permission_id column will be empty (NULL) and there's no value you could join with table of all permissions.

Alternatively, use right join:

from table1 f right join table2 s on f.permission_id = s.permission_id

---

Sample data:

SQL> select * from table1;

PERMISSION_ID PERMISSION
------------- ------------
            1 permission 1
            2 permission 2

SQL> select * From table2;

   USER_ID PERMISSION_ID IS_A
---------- ------------- ----
       100             1 true
       282

If everything is OK (permission_id exists for user 100):

SQL> select s.user_id,
  2         f.permission,
  3         coalesce(s.is_allowed, 'false') as is_allowed
  4  from table2 s left join table1 f on f.permission_id = s.permission_id
  5  where s.user_id = 100;

   USER_ID PERMISSION   IS_AL
---------- ------------ -----
       100 permission 1 true

Permission ID is NULL for user 282:

SQL> select s.user_id,
  2         f.permission,
  3         coalesce(s.is_allowed, 'false') as is_allowed
  4  from table2 s left join table1 f on f.permission_id = s.permission_id
  5  where s.user_id = 282;

   USER_ID PERMISSION   IS_AL
---------- ------------ -----
       282              false

SQL>

---

[EDIT]

If it is the other way round (as you commented), then:

This is your "original" query which doesn't return anything if user 282 is in where clause; that's expected because it filters rows out:

SQL> select s.user_id,
  2         f.permission,
  3         coalesce(s.is_allowed, 'false') as is_allowed
  4  from table1 f left join table2 s on f.permission_id = s.permission_id
  5  where s.user_id = 282;

no rows selected

What happens if you remove that condition entirely? Query then returns row for user 100 (because there's a join between tables through permission ID), and leaves another one without user ID because there's no join for that user:

SQL> select s.user_id,
  2         f.permission,
  3         coalesce(s.is_allowed, 'false') as is_allowed
  4  from table1 f left join table2 s on f.permission_id = s.permission_id;

   USER_ID PERMISSION   IS_AL
---------- ------------ -----
       100 permission 1 true
           permission 2 false

Or, if you want to apply filter on user 282, don't use it in where clause, but expand join condition; that result is, though different because it says that user 282 doesn't have neither permission:

SQL> select s.user_id,
  2         f.permission,
  3         coalesce(s.is_allowed, 'false') as is_allowed
  4  from table1 f left join table2 s on f.permission_id = s.permission_id and s.user_id = 282;

   USER_ID PERMISSION   IS_AL
---------- ------------ -----
           permission 1 false
           permission 2 false