mysql union多张表_mysql – 使用UNION在多个表中进行选择

我将我的数据库从一个用户表重新编写为多个用户表(按角色划分)：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子句来检查电子邮件地址.我在我的数据库应用程序中运行查询,令人惊讶的是,当我执行例如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"

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