mysql - 从不相关的 SQL 子查询返回多行

Question

我正在编写一个 SQL 脚本来检查 MySQL 数据库的完整性。查询检查不同表的不同方面，因此它们不相关。我不想把它们联系起来。我只想一个接一个地查看不同查询的结果。

单个查询工作正常，但我想同时运行它们，但将结果分开。它们可以不返回任何内容，也可以返回多行。 (理想情况下，我希望标题能使结果更清晰。)

错误是

"Error code: 1242. Subquery returns more than 1 row"

这是我尝试过的:

SELECT
(-- Teams not associated with any employees
SELECT id 
  FROM teams
 WHERE NOT EXISTS
          (SELECT id 
             FROM employee_teams
            WHERE employee_teams.teamID IN(teams.id))) AS TeamNoEmployee,

(-- Teams deleted but associated with employees
SELECT teamID 
  FROM employee_teams
 WHERE NOT EXISTS
          (SELECT id 
             FROM teams
            WHERE teams.id IN(employee_teams.teamID))) AS TeamsDeleted

更新:@JohnHC 知道了。这是我的新版本。我需要能够为每个查询添加 header ，以便区分它们。

-- Teams not associated with any employees
SELECT 'Team No Employee' AS Test, id
  FROM teams
 WHERE NOT EXISTS
          (SELECT id 
             FROM employee_teams
            WHERE employee_teams.teamID IN(teams.id))
UNION
-- Teams deleted but associated with employees
SELECT 'Teams Deleted', teamID 
  FROM employee_teams
 WHERE NOT EXISTS
          (SELECT id 
             FROM teams
            WHERE teams.id IN(employee_teams.teamID))

Answer 1

您并没有真正将表格中的那些关联起来。反过来试试，像这样:

select 'Empty Team' as Scenario, id
from teams t1
left join employee_teams et
  on et.teamID = t1.id
where et.id is null
union 
select 'Deleted Team', teamid
from employee_teams et
left join teams t2
  on t2.id = et.teamID
where t2.id is null