sql - Conditional JOIN different tables

Question

I want to know if a user has an entry in any of 2 related tables.

Tables

USER (user_id)
EMPLOYEE (id, user_id)
STUDENT (id, user_id)

A User may have an employee and/or student entry. How can I get that info in one query? I tried:

select * from [user] u
inner join employee e 
    on e.user_id = case when e.user_id is not NULL 
                        then u.user_id 
                        else null 
                   end
inner join student s 
    on s.user_id = case when s.user_id is not NULL 
                        then u.user_id 
                        else null 
                   end

But it will return only users with entries in both tables.

SQL Fiddle example

Answer 1

You could use an outer join:

select *
  from USER u
  left outer join EMPLOYEE e ON u.user_id = e.user_id
  left outer join STUDENT s ON u.user_id = s.user_id
 where s.user_id is not null or e.user_id is not null

alternatively (if you're not interested in the data from the EMPLOYEE or STUDENT table)

select *
  from USER u
 where exists (select 1 from EMPLOYEE e where e.user_id = u.user_id)
    or exists (select 1 from STUDENT s  where s.user_id = u.user_id)