使用“OR”组合两个单独的 SQL 查询的结果；

Question

框架：Rails 2.3.8 数据库; PostgreSQL

我目前有以下 SQL 语句（由 Rails 生成）

SELECT "outbreaks".* FROM "outbreaks" INNER JOIN "bacterial_agents" ON bacterial_agents.outbreak_id = outbreaks.id INNER JOIN "bacteria" ON "bacteria".id = "bacterial_agents".bacterium_id WHERE (bacteria.name ILIKE E'%VTEC O157%')

SELECT "outbreaks".* FROM "outbreaks" INNER JOIN "viral_agents" ON viral_agents.outbreak_id = outbreaks.id INNER JOIN "virus" ON "virus".id = "viral_agents".virus_id WHERE (virus.name ILIKE E'%NOROVIRUS%')

和下表（我跳过了未使用的属性，布局/键命名遵循 Rails 约定）

outbreaks
  id

bacterial_agents
  id
  outbreak_id
  bacterium_id

bacteria
  id
  name

viral_agents
  id
  outbreak_id
  virus_id

viruses
  id
  name

任何想法我将如何加入上面两条带有“OR”条件的SQL语句，选择与细菌表相关的爆发记录，其中细菌名称为“VTEC O157”，或者与病毒表相关，其中病毒名称为“NOROVIRUS”？

*编辑

对 SQL 的 Rails 生成进行澄清 - 它当前输出以下 SQL

SELECT "outbreaks".* FROM "outbreaks" INNER JOIN "bacterial_agents" ON bacterial_agents.outbreak_id = outbreaks.id INNER JOIN "bacteria" ON "bacteria".id = "bacterial_agents".bacterium_id INNER JOIN "viral_agents" ON outbreaks.id = viral_agents.outbreak_id INNER JOIN "viruses" ON "viral_agents".virus_id = "viruses".id WHERE ((bacteria.name ILIKE E'%VTEC O157%') AND (viruses.name ILIKE E'%NOROVIRUS%')) LIMIT 1

本质上我想将前两个语句的组合结果输出为与上述语句类似的格式（用“OR”条件代替“与”）。但是，如果这是不可能的，我只需要编写一个范围来处理 SQL 联合。

从下面的回复来看，我将结合这两个单独的声明，欢呼:)

Answer 1

我认为您将 OR 与 SQL 联合运算符 混淆了，以连接两个查询的返回结果。

OR 用于管理语句 WHERE 部分中的条件，而 union 用于将一个查询附加到另一个查询。

您应该注意，只有当两个查询具有相同的域时，联合才会起作用。快速查看您的查询，它应该完全符合您的想法。

试试这个：

SELECT "outbreaks".* FROM "outbreaks" INNER JOIN "bacterial_agents" ON bacterial_agents.outbreak_id = outbreaks.id INNER JOIN "bacteria" ON "bacteria".id = "bacterial_agents".bacterium_id WHERE (bacteria.name ILIKE E'%VTEC O157%')
UNION
SELECT "outbreaks".* FROM "outbreaks" INNER JOIN "viral_agents" ON viral_agents.outbreak_id = outbreaks.id INNER JOIN "virus" ON "virus".id = "viral_agents".virus_id WHERE (virus.name ILIKE E'%NOROVIRUS%')

Answer 2

只需在查询之间放置 UNION ALL

SELECT "outbreaks".* 
  FROM "outbreaks"
  INNER JOIN "bacterial_agents"     ON bacterial_agents.outbreak_id = outbreaks.id 
  INNER JOIN "bacteria"         ON "bacteria".id = "bacterial_agents".bacterium_id
WHERE (bacteria.name ILIKE E'%VTEC O157%')

UNION ALL

SELECT "outbreaks".*
   FROM "outbreaks"
   INNER JOIN "viral_agents"    ON viral_agents.outbreak_id = outbreaks.id 
   INNER JOIN "virus"       ON "virus".id = "viral_agents".virus_id
 WHERE (virus.name ILIKE E'%NOROVIRUS%')