问题描述
今天，我与自己的辩论关于笛卡尔产品的项目经理。他说，自然联接比使用从何处选择要好得多，因为后者会导致db引擎在内部执行笛卡尔积，但是前者使用另一种方法来防止这种情况。据我所知，就性能或含义而言，自然连接语法与从哪里选择没有任何不同，我的意思是您可以根据自己的喜好使用。
Today I got into a debate with my project manager about Cartesian products. He says a 'natural join' is somehow much better than using 'select from where' because the later cause the db engine to internally perform a Cartesian product but the former uses another approach that prevents this. As far as I know, the natural join syntax is not any different in anyway than 'select from where' in terms of performance or meaning, I mean you can use either based on your taste.
SELECT * FROM table1,table2 WHERE table1.id=table2.id
SELECT * FROM table1 NATURAL JOIN table2
请详细说明第一个导致笛卡尔积的查询，但第二个查询以某种方式比更聪明
please elaborate about the first query causing a Cartesian product but the second one being somehow more smart
推荐答案
正确的方法应该是显式，并使用过滤器和联接分隔
The correct way should be explicit with filters and joins separated
SELECT * FROM table1 JOIN table2 ON table1.id = table2.id
自然连接可能很容易且干净，但更可能是完全不可预测的...
NATURAL JOINS may be easy and "clean" but more likely to be utterly unpredictable...
编辑，2012年5月。
Edit, May 2012.
接受的重复答案实际上并没有回答NATURAL JOIN。
这些链接将进一步详细讨论。
The accepted answer for the duplicate doesn't actually answer NATURAL JOIN.
These links discuss in further detail.
- （DBA.SE）
- https://dba.stackexchange.com/a/6917/630 (DBA.SE)
- Natural join in SQL Server
- SQL Server - lack of NATURAL JOIN / x JOIN y USING(field)
- SQL JOIN: is there a difference between USING, ON or WHERE?
tl; dr
性能不是问题：但是您的查询应该可靠且可预测，而自然联接当然不是。
Performance isn't the issue: but your queries should be reliable and predictable which NATURAL JOIN certainly isn't.
在哪里加入又称 JOIN（又称为笛卡尔），根据这些链接，它们也不好（同样适用于Oracle作为SQL Server）
"JOIN in the WHERE" aka implied JOIN aka what you call "Cartesian" is also bad as per these links (the same applies to Oracle as well as SQL Server)
这篇关于就性能而言，NATURAL JOIN是否比SELECT FROM WHERE更好？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！