Select from column including null values

I’m trying to create SELECT that includes various foreign key to list all the rows it has, but there is two foreign key that can be null.

bank_id and bbank_id can be null, so with this example it only will list rows that doesn’t have null values in bank_id and bbank_id, but I need it to return rows where bank_id and bbank_id are null or not null. I tried using IS NOT NULL AND NULL, well it doesn’t work.

SELECT E.emp_id, E.emp_name, E.emp_surname1, E.emp_surname2,  
J.job_name, U.ubi_name, D.dep_name, POR.por_type, 
B.bank_name, BB.bbank_name
FROM public.employee E, public.ubi U, 
public.jobs J, public.department D, 
public.percenttable POR, public.bank B, 
public.bbank BB
WHERE E.ubi_id = U.ubi_id AND E.job_id = J.job_id
AND E.dep_id = D.dep_id AND E.por_id = POR.por_id
AND E.bank_id = B.bank_id AND E.bbank_id = BB.bbank_id
AND E.ubi_id IS NOT NULL AND E.job_id IS NOT NULL AND E.dep_id IS NOT NULL
AND E.por_id IS NOT NULL AND E.bank_id IS NOT NULL AND E.bbank_id IS NOT NULL
ORDER BY E.emp_id ASC;

I read something about using LEFT JOIN but no idea how to implement it here.

Answer

You need to use modern JOIN syntax defined in the SQL-92 standard, 27 years ago. Here’s how your query should look using left outer joins:

select 
  e.emp_id, e.emp_name, e.emp_surname1, e.emp_surname2,  
  j.job_name, u.ubi_name, d.dep_name, por.por_type, 
  b.bank_name, bb.bbank_name
from public.employee e
left join public.ubi u on e.ubi_id = u.ubi_id 
left join public.jobs j on e.job_id = j.job_id
left join public.department d on e.dep_id = d.dep_id 
left join public.percent por on e.por_id = por.por_id
left join public.bank b on e.bank_id = b.bank_id 
left join public.bbank bb on e.bbank_id = bb.bbank_id
where e.ubi_id is not null 
  and e.job_id is not null 
  and e.dep_id is not null
  and e.por_id is not null 
  -- and e.bank_id is not null -- removed per your requirement
  -- and e.bbank_id is not null -- removed per your requirement
order by e.emp_id asc

Welcome to the 21st century! 😉