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)