我是一个老派的MySQL用户,总是更喜欢JOIN而不是子查询。但是现在每个人都用子查询,我讨厌它;我不知道为什么。

我缺乏理论知识来判断是否有任何不同。子查询是否与JOIN一样好,因此没有什么可担心的?


当前回答

在大多数情况下,join比子查询快,子查询比子查询快的情况非常罕见。

在join中,RDBMS可以为您的查询创建一个更好的执行计划,并可以预测应该加载哪些数据来处理并节省时间,不像子查询,它将运行所有的查询并加载所有的数据来进行处理。

子查询的好处是它们比join更可读:这就是为什么大多数新学习SQL的人更喜欢它们;这是简单的方法;但是当涉及到性能时,join在大多数情况下更好,尽管它们也不难读。

其他回答

子查询是解决“从A获取事实,以B的事实为条件”这种形式的问题的逻辑正确方法。在这种情况下,在子查询中插入B比进行连接更具逻辑意义。从实际意义上讲,它也更安全,因为您不必担心由于与B的多个匹配而从a获得重复的事实。

然而,实际上,答案通常归结于性能。当给出连接和子查询时,一些优化器会很糟糕,而另一些则相反,这是特定于优化器、特定于dbms版本和特定于查询的。

从历史上看,显式连接通常会胜出,因此已经建立的智慧是连接更好,但优化器一直在变得更好,因此我更喜欢先以逻辑一致的方式编写查询,然后在性能限制的情况下重新构造查询。

子查询通常用于将单行作为原子值返回,不过它们也可以用于用IN关键字比较多行之间的值。在SQL语句中几乎任何有意义的地方都允许使用它们,包括目标列表、WHERE子句等等。一个简单的子查询可以用作搜索条件。例如,在一对表之间:

SELECT title 
FROM books 
WHERE author_id = (
    SELECT id 
    FROM authors 
    WHERE last_name = 'Bar' AND first_name = 'Foo'
);

注意,在子查询的结果上使用普通值操作符要求只返回一个字段。如果你想检查一个值是否存在于一组其他值中,请使用in:

SELECT title 
FROM books 
WHERE author_id IN (
    SELECT id FROM authors WHERE last_name ~ '^[A-E]'
);

这显然不同于LEFT-JOIN,你只是想连接表a和表B的东西,即使连接条件在表B中没有找到任何匹配的记录,等等。

如果你只是担心速度,你必须检查你的数据库,写一个好的查询,看看是否有显著的性能差异。

如果你想用join加速你的查询:

对于“inner join/join”, 不要使用where条件,而是使用“ON”条件。 例如:

     select id,name from table1 a  
   join table2 b on a.name=b.name
   where id='123'

 Try,

    select id,name from table1 a  
   join table2 b on a.name=b.name and a.id='123'

对于“左/右连接”, 不要在“ON”条件下使用,因为如果你使用左/右连接,它将获得任何一个表的所有行。所以,在"开"里也没用。所以,尝试使用“Where”条件

根据我的观察,就像两种情况,如果一个表的记录少于10万条,那么连接将工作得很快。

但是如果一个表有超过100,000条记录,那么子查询是最好的结果。

我有一个表,其中有500,000条记录,我在查询下面创建了它,它的结果时间是

SELECT * 
FROM crv.workorder_details wd 
inner join  crv.workorder wr on wr.workorder_id = wd.workorder_id;

结果:13.3秒

select * 
from crv.workorder_details 
where workorder_id in (select workorder_id from crv.workorder)

结果:1.65秒

我不是关系数据库专家,所以对此持保留态度。

子查询与连接的一般思想是较大查询的求值路径。

为了执行较大的查询,必须首先执行每个子查询,然后将结果集存储为与较大查询交互的临时表。

这个临时表没有索引,因此,任何比较都需要扫描整个结果集。

相反,当您使用连接时,所有索引都在使用中,因此,比较需要遍历索引树(或哈希表),这在速度方面成本要低得多。

现在,我不知道最流行的关系引擎的新版本是否在反向执行求值,只是将必要的元素加载到临时表中,作为优化方法。