2 2 个表的左连接? [英] 2 Left join for 2 tables?

问题描述

我使用的是 MySQL:

I am on MySQL:

我有2张表，一张是主表，一张是附表，里面有一些支持主表记录的信息.

I have 2 table, one is the main table, the other is an accessory table that contains some information supporting the records of the main table.

示例:

表格门户:

id title desc
12  "aaa" "desc"
13  "bbb" "desc"
[etc]

次表(省略主id字段)

secondary table (omitting the primary id field)

type portalid
 x    12
 2    12
 3    12
 4    12
 5    12

 1    13
 2    13
 4    13

我需要选择表门户中的每条记录，这些记录在辅助表中有一条记录，类型 = 4 但 != 5.

I need to select every record in the table portal that got a record in the secondary table with type = 4 but != 5.

示例:

SELECT * 
  FROM portal,secondary_table s
 WHERE portal.id=s.portalid 
   AND type of secondary_table is 4 and is not 5

结果:

在这种情况下，只应返回门户的记录 13，因为记录 12 得到了类型 4 和类型 5.

In this case only the record 13 of portal should be returned because the record 12 got both type 4 and 5.

请注意，我问了一个类似的问题，但只考虑了一张表，并且用了 50 多秒的时间来阐述该查询.

Please note I asked a similar question but considering only one table, and with that query took over 50 secs to be elaborated.

感谢您的帮助

推荐答案

您应该考虑使用 NOT EXISTS 子句重新表述它.如果您想要的是来自 portal 的记录，那么双 EXISTS 子句将起作用并且非常清楚地揭示查询意图

You should consider rephrasing it using NOT EXISTS clauses. If all you want are records from portal, then a double EXISTS clause will work and very clearly reveal the query intentions

SELECT * 
FROM portal
WHERE EXISTS (select * from secondary_table s1
              where portal.id=s1.portalid
              and s1.type=4)
AND NOT EXISTS (select * from secondary_table s2
                where portal.id=s2.portalid
                and s2.type=5)

然而，由于 MySQL 处理 EXISTS 子句的方式(即使它更清晰)，您可以使用 LEFT JOIN/IS NULL 权衡性能.请阅读以下链接，但是每个查询的性能可能会因特定的数据分布而异，因此请尝试两者并使用更适合您数据的那个.

However, due to how MySQL process EXISTS clauses (even though it is clearer), you can trade off clarify for performance using LEFT JOIN / IS NULL. Please read the following link, however the performance of each query may vary with specific data distribution, so try both and use whichever works better for your data.

NOT IN vs. NOT EXISTS vs. LEFT JOIN/IS NULL:MySQL

LEFT JOIN/IS NULL 形式将被写入

The LEFT JOIN / IS NULL form would be written

SELECT * 
FROM portal
JOIN secondary_table s1 ON portal.id=s1.portalid and s1.type=4
LEFT JOIN secondary_table s2 ON portal.id=s2.portalid and s2.type=5
WHERE s2.portalid IS NULL

表的顺序(门户、内部、左)是为了允许处理前两个表(门户 + 辅助/类型 = 4)并在启动到 LEFT(外部)JOIN(保留左侧的所有内容)用于存在性测试.

The order of the tables (portal, inner, left) is to allow processing the first two tables (portal + secondary/type=4) and trimming the result set early before launching into the LEFT (outer) JOIN (that retains everything from the left side) for the existential test.

这篇关于2 2 个表的左连接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！