mysql - 使用 UNION 在多个表中进行选择

Question

我将我的数据库从一个用户表改成了多个用户表(按角色划分):tblStudents、tblTeachers、tblAdmin

登录时，我不想运行三个查询来检查用户是否存在于我的数据库中。所以我所做的是将以下查询与 union

放在一起

select s.id as id, s.email as email, s.password as password, s.role as role from tblStudents s
union
select a.id as id, a.email as email, a.password as password, a.role as role from tblAdmin a
union
select t.id as id, t.email as email, t.password as password, t.role as role from tblTeachers t

这会选择所有表中相同的字段并为我很好地输出结果。

所以，我决定尝试一下，但出于某种原因，我的登录表单无法正常工作。对于我的登录表单，我添加了一个检查电子邮件地址的 where 子句。我在我的数据库应用程序中运行了查询，令人惊讶的是，当我这样做时，例如 where email = "admin@admin.be"(此电子邮件存在于我的数据库 tblAdmin 中)，它还从我的学生表中选择一条记录。

where 子句:

select s.id as id, s.email as email, s.password as password, s.role as role from tblStudents s
union
select a.id as id, a.email as email, a.password as password, a.role as role from tblAdmin a
union
select t.id as id, t.email as email, t.password as password, t.role as role from tblTeachers t
where email = "admin@admin.be"

记录都 have id = 1 但我不明白为什么当我过滤管理员电子邮件地址时它会选择学生记录。为什么是这样？有人可以解释并为我提供更好的解决方案吗？我基本上只有一个登录表单，需要跨多个表进行选择以检查用户是否存在于我的数据库中。

Answer 1

感谢更新查询；现在我们可以看到 WHERE 条件仅应用于 last UNIONed 查询。您需要要么将该 WHERE 子句添加到每个查询，或者将其包装为子选择并对其应用 WHERE 子句。

select s.id as id, s.email as email, s.password as password, s.role as role from tblStudents s
where email = "admin@admin.be"
union
select a.id as id, a.email as email, a.password as password, a.role as role from tblAdmin a
where email = "admin@admin.be"
union
select t.id as id, t.email as email, t.password as password, t.role as role from tblTeachers t
where email = "admin@admin.be"

或

SELECT * FROM (
select s.id as id, s.email as email, s.password as password, s.role as role from tblStudents s
union
select a.id as id, a.email as email, a.password as password, a.role as role from tblAdmin a
union
select t.id as id, t.email as email, t.password as password, t.role as role from tblTeachers t
) foo where email = "admin@admin.be"