条件JOIN不同的表 [英] Conditional JOIN different tables

问题描述

我想知道用户是否在 任何 2 个相关表中都有条目.

I want to know if a user has an entry in any of 2 related 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.

推荐答案

你可以使用外连接:

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

或者(如果您对 EMPLOYEE 或 STUDENT 表中的数据不感兴趣)

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)

这篇关于条件JOIN不同的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！