SQL 中外连接的 *= 和 =* 发生了什么？
我只是好奇为什么左外连接的*=运算符和'右外连接'的'=*'被从SQL标准中删除，或者，至少 SQL Server 2005 及以上版本不支持？我已经阅读了有关此特定运算符的其他一些帖子，并了解它可以给出一些意想不到的结果。但如果它在语义上是等价的，我认为在必须连接多个表时它会是一个更容易使用的运算符。如果有的话，我宁愿使用这个运算符来动态创建 sql 查询，而不是尝试获得正确的词序，例如：
FROM accounts
LEFT OUTER JOIN customers
ON accounts.accountnum = customers.accountnum
LEFT OUTER JOIN products
ON customers.accountnum = products.accountnum
AND customers.id = products.customerid
where
FROM accounts, customers, products
WHERE accounts.accountnum *= customers.accountnum AND
customers.accountnum *= products.accountnum AND
customers.id *= products.customer.id
似乎更容易一起解析。
但是回到现实世界，如果这些运算符不执行“真正的”外连接，他们的想法是什么？术语Short Hand意味着它应该完全执行以下操作：同样的事情，但显然不是在这种情况下。
I was just curious as to why the *= operator for left outer join and the '=*' for 'right outer join were taken out of the SQL standard, or, at least not supported by SQL server 2005 and on? I have read a few other posts on this particular operator and understand that it can give some unexpected results. But if it were semantically equivalent I would think it would be an easier operator to use when having to join multiple tables. If anything, I would rather use this operator to to dynamically create sql queries as opposed to trying to get the correct word order like:
FROM accounts
LEFT OUTER JOIN customers
ON accounts.accountnum = customers.accountnum
LEFT OUTER JOIN products
ON customers.accountnum = products.accountnum
AND customers.id = products.customerid
where
FROM accounts, customers, products
WHERE accounts.accountnum *= customers.accountnum AND
customers.accountnum *= products.accountnum AND
customers.id *= products.customer.id
would seem to be easier to parse together.
But back to the real world, what was the idea for these operators if they dont perfom a "true" outer join? The term Short Hand implies that it should do exactly the same thing, but obviosly not in this case.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
SQL Server MVP K. Brian Kelley 在这里做了很好的解释：为什么 SQL Server 2005 不允许非 ANSI 样式 OUTER加入
SQL Server MVP K. Brian Kelley does a good job of explaining it here: Why SQL Server 2005 Doesn't Permit Non-ANSI Style OUTER JOINs
我不认为它们被排除在标准之外。我认为他们从来没有参与其中。它们只是供应商遵循的约定，直到 SQL-92 出现并且供应商认为值得实施。
I don't think they were taken out of the standard. I think they were never in it. They were just a convention that vendors followed until SQL-92 came out and the vendors decided it was worth implementing.
在 google 上搜索 Chris Date 论文“Outer Join with No Nulls and Fewer Tears”，该论文早于 SQL-92 标准。虽然我无法在网上找到免费的副本，但我确实找到了一份文档，该文档分析了该论文并给出了 SQL 中外连接的详细历史记录。请注意，现在 Date 将为此类查询提供关系值属性，并且会敦促您通常避免外连接；）
Do a google search for the Chris Date paper, "Outer Join with No Nulls and Fewer Tears", which pre-dates the SQL-92 Standard. Although I couldn't find a free copy online, I did find a document that analyses the paper and gives a nice potted history of outer join in SQL. Note nowadays Date would relation-valued attributes for this kind of query and would urge you to generally avoid outer joins ;)