Sql 外连接重复行
我有两个由以下查询连接的两个表。
SELECT
dbo.ORDERLINE.DDCVNB,
dbo.ORDERLINE.DDAFAB,
dbo.ORDERLINE.DDAITX, dbo.ORDERLINE.DDALDT,
dbo.ORDERLINE.DDDUVA, dbo.ORDERLINE.DDARQT,
dbo.ORDERSHIPH.DHCANB, dbo.ORDERSHIPH.DHGGNB,
dbo.ORDERLINE.DDAAGM, dbo.ORDERLINE.DDCHNB,
dbo.ORDERLINE.DDAAGQ
FROM
dbo.ORDERLINE
LEFT OUTER JOIN
dbo.ORDERSHIPH ON dbo.ORDERLINE.DDAFAB = dbo.ORDERSHIPH.DHAFAB AND dbo.ORDERLINE.DDCVNB = dbo.ORDERSHIPH.DHCVNB
ORDERLINE 表有 700 万行，ORDERSHP 表有 200 万行。
我的查询结果集有 1400 万行。
这怎么可能用左外连接呢？结果集不应该等于 ORDERLINE 中的行数（700 万）吗？
I have two two table joined by the following query.
SELECT
dbo.ORDERLINE.DDCVNB,
dbo.ORDERLINE.DDAFAB,
dbo.ORDERLINE.DDAITX, dbo.ORDERLINE.DDALDT,
dbo.ORDERLINE.DDDUVA, dbo.ORDERLINE.DDARQT,
dbo.ORDERSHIPH.DHCANB, dbo.ORDERSHIPH.DHGGNB,
dbo.ORDERLINE.DDAAGM, dbo.ORDERLINE.DDCHNB,
dbo.ORDERLINE.DDAAGQ
FROM
dbo.ORDERLINE
LEFT OUTER JOIN
dbo.ORDERSHIPH ON dbo.ORDERLINE.DDAFAB = dbo.ORDERSHIPH.DHAFAB AND dbo.ORDERLINE.DDCVNB = dbo.ORDERSHIPH.DHCVNB
The ORDERLINE table has 7 million rows and the ORDERSHP has 2 million rows.
My query result-set has 14 million rows.
How is this possible with a left outer join? Shouldn't the result set be equal to the number of rows in the ORDERLINE which is 7 million?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您的 ON 子句没有足够的连接条件，并且为每个左行匹配 2 个右行。本质上是一个叉积。
另外，您甚至可能不需要 LEFT OUTER JOIN。如果您通过 RI 等知道右表中至少有一行对应左表中的每一行，您应该使用 INNER JOIN。
Your ON clause does not have enough join criteria and is matching 2 right rows for every left row. Essentially a cross product.
Also, you may not even want a LEFT OUTER JOIN. If you know via RI or such that there will be AT LEAST one row in the right table for every one in the left you should use an INNER JOIN.
我发现了关于外连接的困难方法：
如果 ORDERSHIPH 表有多个具有相同 DHAFAB 和 DHCVNB 值的记录（比方说两个），那么 select 子句将返回 ORDERSHIPH 中两个记录的一组行。这意味着 700 万乘以 2。如果 ORDERSHIPH 有 3 条具有相同值的记录，您将收到 3 组数据作为回报。
I found this out the hard way about outer joins:
If the ORDERSHIPH table has multiple records with the same DHAFAB and DHCVNB values (let's say two), then the select clause will return a SET of rows FOR BOTH RECORDS IN ORDERSHIPH. Which means 7 million times two. If ORDERSHIPH has lets say 3 records with those same values you'll receive 3 sets of data in return.
您可能缺少其他加入标准。如果不了解数据库中的所有字段，我们将很难提供帮助。听起来您还需要订单表中的空记录。如果没有，请遵循 Cory 的建议并使用内部联接。
Your probably missing other join criteria. Without knowing all fields in your database, its going to be difficult for us to assist. Its sounds like you also need empty records from the ordership table. If not, Go with Cory's suggestion and use an inner join.