blocks|key|718991|text|type|unstyled|depth|inlineStyleRanges|entityRanges|data|718992|使用，并包括Actual+Execution+Plan、SET+STATISTICS+TIME和SET+STATISTICS+IO。|ordered-list-item|offset|length|style|CODE|718993|在这方面相当于一个JOIN，但是重写可能无关紧要。一个猜测可能是您需要enquiry_courses.enquiry_id和enquiries.session_id上的索引来改进查询enquiries.session_id。|718994|718995|entityMap^0|0|6|L|S|J|1C|H|0|9|4|Z|Q|1Q|K|2J|K|0|0^^$0|@$1|2|3|-4|4|5|6|M|7|@]|8|@]|9|$]]|$1|A|3|B|4|C|6|N|7|@$D|O|E|P|F|G]|$D|Q|E|R|F|G]|$D|S|E|T|F|G]]|8|@]|9|$]]|$1|H|3|I|4|C|6|U|7|@$D|V|E|W|F|G]|$D|X|E|Y|F|G]|$D|Z|E|10|F|G]|$D|11|E|12|F|G]]|8|@]|9|$]]|$1|J|3|-4|4|5|6|13|7|@]|8|@]|9|$]]|$1|K|3|-4|4|5|6|14|7|@]|8|@]|9|$]]]|L|$]]

<ol>
<li>Use the SQL Server Management Studio, and include <code>Actual Execution Plan</code> and <code>SET STATISTICS TIME</code> and <code>SET STATISTICS IO</code>. </li>
<li>This in corresponds to a <code>JOIN</code>, but rewriting probably won't matter. A guess could be that you need indexes on <code>enquiry_courses.enquiry_id</code> and on <code>enquiries.session_id</code> to improve query performance. </li>
</ol>

blocks|key|1530505|text|MSSQL通常附带一个内置的gui工具，称为Query，它描述如何执行查询。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1530506|2)您可以将其重写为：|1530507|SELECT+*+
FROM+enquiry_courses+ec+
WHERE+EXISTS+(select+1+FROM+enquiries+e+
++++++++++++++WHERE+e.enquiry_id+=+ec.enquiry_id+
++++++++++++++and+e.session_id+='4cd3420a16dbd61c6af58f6199ac00f1'+)|code-block|syntax|javascript|1530508|但我不敢相信，在现代的RDBMS中，它的性能会有任何不同。|1530509|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

MSSQL generally comes with a built in gui tool called Query Analyser which describes how the query will be executed.

For 2) you could rewrite as:

<pre><code>SELECT * 
FROM enquiry_courses ec 
WHERE EXISTS (select 1 FROM enquiries e 
 WHERE e.enquiry_id = ec.enquiry_id 
 and e.session_id ='4cd3420a16dbd61c6af58f6199ac00f1' )
</code></pre>

but I can't believe it would make any performance difference in a modern RDBMS.

blocks|key|1496573|text|3:我希望数据库引擎将IN或EXIST子句简化为JOIN，因此性能不应该有差异。我不知道SQL+Server，但在Oracle中，您可以通过检查执行计划来验证这一点。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1496574|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

3: I would expect an IN or EXIST clause to be flattened to a JOIN by the database engine, so there shouldn't be a difference in performance. I don't know about SQL Server, but in Oracle you can verify this by checking the execution plan.

blocks|key|719029|text|这个问题表明存在的速度更快，这也是我被告知的IN+()+vs+EXISTS+()+in+SqlServer+2005+(or+generally+in+any+RDBMS)。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|719030|需要注意的一点是，存在和IN应该优先使用，而不是不存在，而不是in。|719031|一点与性能的切线，但这是一篇很好的文章，介绍IN和存在的http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx之间的细微差别。|719032|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/1071828/in-vs-exists-in-sqlserver-2005-or-generally-in-any-rdbms|1|http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx^0|M|1S|0|0|0|S|1S|1|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@$A|S|B|T|1|U]]|C|$]]|$1|D|3|E|5|6|7|V|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|W|8|@]|9|@$A|X|B|Y|1|Z]]|C|$]]|$1|H|3|-4|5|6|7|10|8|@]|9|@]|C|$]]]|I|$J|$5|K|L|M|C|$N|O]]|P|$5|K|L|M|C|$N|Q]]]]

This question suggests that EXISTS is quicker which is what I had been taught <a href="https://stackoverflow.com/questions/1071828/in-vs-exists-in-sqlserver-2005-or-generally-in-any-rdbms">IN () vs EXISTS () in SqlServer 2005 (or generally in any RDBMS)</a>

One thing to note is that EXISTS and IN should be used in preference to NOT EXISTS and NOT IN

Bit of a tangent from performance but this is a good article on the subtle differences between IN and EXISTS <a href="http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx" rel="nofollow noreferrer">http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx</a>

blocks|key|1530631|text|正如其他人所说的，|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1530632|检查“执行计划”。可以显示两种执行计划，估计的和实际的。估计是Server如何猜测它将执行查询并在不实际执行查询的情况下返回，实际计划与结果集一起返回，并显示实际执行的操作。|ordered-list-item|1530633|该查询看起来不错，但您必须确保在enquiry_courses.enquiry_id上有索引，最好是enquiries.enquiry_id不为空。|1530634|+IN和enquiries.enquiry_id的语义略有不同(如果子查询中有一个或多个NULLs，则in不会返回任何行)。如果保证子查询不为null，则与无关。您应该在SQL+Server和Oracle上使用某种“网络真理”，但是当恐龙统治这个星球但它不再适用时，这可能是真的。中和存在都执行半连接，并且优化器能够决定如何执行此联接.|offset|length|style|BOLD|1530635|。|1530636|entityMap^0|0|0|0|25|1|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$]]|$1|E|3|F|5|D|7|S|8|@]|9|@]|A|$]]|$1|G|3|H|5|D|7|T|8|@$I|U|J|V|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|W|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|X|8|@]|9|@]|A|$]]]|P|$]]

<ol>
<li>As others have said, check the "execution plan". SQL Server Management studio can show you two kinds of execution plans, estimated and actual. Estimated is how SQL Server guesses it would execute the query and is returned without actually executing the query, and the actual plan is returned together with a result set and shows what was actually done.</li>
<li>That query looks good, but you have to make sure that you have an index on enquiry_courses.enquiry_id, and it's probably best that enquiries.enquiry_id is not nullable.</li>
<li>The semantics of IN and EXISTS are slightly different (IN will return no rows if there is one or more NULLs in the subquery). If the subquery is guaranteed to be not null, it doesn't matter. There is some kind of "internet truth" that you should use EXISTS on SQL Server and IN on Oracle, but this might have been true when dinosaurs ruled the planet but it doesn't apply anymore. IN and EXISTS both perform a semi-join, and the optimizer is more than capable of deciding how to execute this join.</li>
</ol>

blocks|key|1531431|text|我想联接为引擎提供了更多的自由，可以选择最佳的查询计划。在您的情况下，可能有所有的解决方案类似的性能。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1531432|SELECT+enquiry_courses.*+
FROM+enquiry_courses+
INNER+JOIN+enquiries+ON+enquiries.enquiry_id=enquiry_courses+
++++++++++++++++++++++++AND+session_id+=+'4cd3420a16dbd61c6af58f6199ac00f1'+|code-block|syntax|javascript|1531433|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I guess the join gives more free to the engine for choice the best query plan.
In your exactly case, probably have all solutions similar performances.

<pre><code>SELECT enquiry_courses.* 
FROM enquiry_courses 
INNER JOIN enquiries ON enquiries.enquiry_id=enquiry_courses 
 AND session_id = '4cd3420a16dbd61c6af58f6199ac00f1' 
</code></pre>

blocks|key|719092|text|他们的行为各不相同:这不是性能上的选择。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|719093|唯一正确和可靠的选择是存在或不存在始终有效的选择。|719094|DISTINCT|719095|WHERE/LEFT连接可能需要|unordered-list-item|719096|联接将需要正确放置过滤器|719097|，而不是IN在NULL|719098|上失败|719099|719100|示例：|719101|DECLARE+@Parent+TABLE+(foo+int+NULL)
INSERT+@Parent+(foo)+VALUES+(1)
INSERT+@Parent+(foo)+VALUES+(2)
INSERT+@Parent+(foo)+VALUES+(3)
INSERT+@Parent+(foo)+VALUES+(4)

DECLARE+@Child+TABLE+(bar+int+NULL,+foo+int+NULL)
INSERT+@Child+(bar,+foo)+VALUES+(100,+1)
INSERT+@Child+(bar,+foo)+VALUES+(200,+2)
INSERT+@Child+(bar,+foo)+VALUES+(201,+2)
INSERT+@Child+(bar,+foo)+VALUES+(300,+NULL)
INSERT+@Child+(bar,+foo)+VALUES+(301,+NULL)
INSERT+@Child+(bar,+foo)+VALUES+(400,+4)
INSERT+@Child+(bar,+foo)+VALUES+(500,+NULL)

--"positive"+checks
SELECT+--+multiple+"2"+=+FAIL+without+DISTINCT
++++P.*
FROM
++++@Parent+P+JOIN+@Child+C+ON+P.foo+=+C.foo

SELECT+--+correct
++++P.*
FROM
++++@Parent+P
WHERE
++++P.foo+IN+(SELECT+c.foo+FROM+@Child+C)

SELECT+--+correct
++++P.*
FROM
++++@Parent+P
WHERE
++++EXISTS+(SELECT+*+FROM+@Child+C+WHERE+P.foo+=+C.foo)

--"negative"+checks
SELECT+--+correct
++++P.*
FROM
++++@Parent+P+LEFT+JOIN+@Child+C+ON+P.foo+=+C.foo
WHERE
++++C.foo+IS+NULL

SELECT+--+no+rows+=+FAIL
++++P.*
FROM
++++@Parent+P
WHERE
++++P.foo+NOT+IN+(SELECT+c.foo+FROM+@Child+C)

SELECT+--+correct
++++P.*
FROM
++++@Parent+P
WHERE
++++NOT+EXISTS+(SELECT+*+FROM+@Child+C+WHERE+P.foo+=+C.foo)|code-block|syntax|javascript|719102|注意:如果存在，子查询中的SELECT与ANSI+92标准中提到的内容无关。|719103|NOT+EXISTS+(SELECT+*+FROM+@Child+C+WHERE+P.foo+=+C.foo)
NOT+EXISTS+(SELECT+NULL+FROM+@Child+C+WHERE+P.foo+=+C.foo)
NOT+EXISTS+(SELECT+1+FROM+@Child+C+WHERE+P.foo+=+C.foo)
NOT+EXISTS+(SELECT+1/0+FROM+@Child+C+WHERE+P.foo+=+C.foo)|719104|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|13|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|14|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|15|8|@]|9|@]|A|$]]|$1|I|3|J|5|H|7|16|8|@]|9|@]|A|$]]|$1|K|3|L|5|H|7|17|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|18|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|19|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|1A|8|@]|9|@]|A|$]]|$1|R|3|S|5|T|7|1B|8|@]|9|@]|A|$U|V]]|$1|W|3|X|5|6|7|1C|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|T|7|1D|8|@]|9|@]|A|$U|V]]|$1|10|3|-4|5|6|7|1E|8|@]|9|@]|A|$]]]|11|$]]

They each behave differently: it is not a performance choice

The only correct and reliable choice is EXISTS or NOT EXISTS that works all the time.

<ul>
<li>JOIN may needs DISTINCT</li>
<li>WHERE/LEFT JOIN would needs correct placement of the filter</li>
<li>NOT IN fails on NULL</li>
</ul>

Example:

<pre><code>DECLARE @Parent TABLE (foo int NULL)
INSERT @Parent (foo) VALUES (1)
INSERT @Parent (foo) VALUES (2)
INSERT @Parent (foo) VALUES (3)
INSERT @Parent (foo) VALUES (4)

DECLARE @Child TABLE (bar int NULL, foo int NULL)
INSERT @Child (bar, foo) VALUES (100, 1)
INSERT @Child (bar, foo) VALUES (200, 2)
INSERT @Child (bar, foo) VALUES (201, 2)
INSERT @Child (bar, foo) VALUES (300, NULL)
INSERT @Child (bar, foo) VALUES (301, NULL)
INSERT @Child (bar, foo) VALUES (400, 4)
INSERT @Child (bar, foo) VALUES (500, NULL)

--"positive" checks
SELECT -- multiple "2" = FAIL without DISTINCT
 P.*
FROM
 @Parent P JOIN @Child C ON P.foo = C.foo

SELECT -- correct
 P.*
FROM
 @Parent P
WHERE
 P.foo IN (SELECT c.foo FROM @Child C)

SELECT -- correct
 P.*
FROM
 @Parent P
WHERE
 EXISTS (SELECT * FROM @Child C WHERE P.foo = C.foo)

--"negative" checks
SELECT -- correct
 P.*
FROM
 @Parent P LEFT JOIN @Child C ON P.foo = C.foo
WHERE
 C.foo IS NULL

SELECT -- no rows = FAIL
 P.*
FROM
 @Parent P
WHERE
 P.foo NOT IN (SELECT c.foo FROM @Child C)

SELECT -- correct
 P.*
FROM
 @Parent P
WHERE
 NOT EXISTS (SELECT * FROM @Child C WHERE P.foo = C.foo)
</code></pre>

Note: with EXISTS, the SELECT in the subquery is irrelevant as mentioned in ANSI 92 standard...

<pre><code>NOT EXISTS (SELECT * FROM @Child C WHERE P.foo = C.foo)
NOT EXISTS (SELECT NULL FROM @Child C WHERE P.foo = C.foo)
NOT EXISTS (SELECT 1 FROM @Child C WHERE P.foo = C.foo)
NOT EXISTS (SELECT 1/0 FROM @Child C WHERE P.foo = C.foo)
</code></pre>

blocks|key|1530529|text|Plan|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1530530|You可以通过以下方式优化您的查询：|unordered-list-item|1530531|进行“参数搜索”，而不是在|1530532|中搜索session_id+索引。|offset|length|style|BOLD|1530533|1530534|1530535|​|1530536|1530537|+SELECT+\*+FROM+enquiry\_courses+as+Courses,+enquiries+as+Enquiries+++++WHERE+Enquiries.session\_id+=+'4cd3420a16dbd61c6af58f6199ac00f1'++++++++AND+Courses.enquiry\_id+=+Enquiries.enquiry\_id|blockquote|1530538|1530539|3.存在具有较好的性能。|1530540|编辑：存在&中的要好于中的性能问题。|1530541|编辑:我重写了查询，这样它就更快了(我把最严格的条件放在WHERE中)|1530542|entityMap^0|0|0|0|E|3|0|0|0|0|1|0|0|0|0|0|C|0|0|6|8|3|0|0^^$0|@$1|2|3|4|5|6|7|13|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|14|8|@]|9|@]|A|$]]|$1|E|3|F|5|D|7|15|8|@]|9|@]|A|$]]|$1|G|3|H|5|D|7|16|8|@$I|17|J|18|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|19|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|1B|8|@$I|1C|J|1D|K|L]]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|1E|8|@]|9|@]|A|$]]|$1|R|3|S|5|T|7|1F|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|1G|8|@]|9|@]|A|$]]|$1|V|3|W|5|6|7|1H|8|@$I|1I|J|1J|K|L]]|9|@]|A|$]]|$1|X|3|Y|5|6|7|1K|8|@$I|1L|J|1M|K|L]|$I|1N|J|1O|K|L]]|9|@]|A|$]]|$1|Z|3|10|5|6|7|1P|8|@]|9|@]|A|$]]|$1|11|3|-4|5|6|7|1Q|8|@]|9|@]|A|$]]]|12|$]]

<ol>
<li>check the Excution Plan</li>
<li>You can optimise your query by:

<ul>
<li>Make a "arguments search" rather than IN</li>
<li>Put Index on session_id </li>
</ul></li>
</ol>

<blockquote>
<pre><code> SELECT * FROM enquiry_courses as Courses, enquiries as Enquiries
 WHERE Enquiries.session_id = '4cd3420a16dbd61c6af58f6199ac00f1' 
 AND Courses.enquiry_id = Enquiries.enquiry_id
</code></pre>
</blockquote>

3.Exists is better for performance. 

EDIT: Exists &amp; IN are better than JOIN for performance issues.

EDIT: I re-wrote the query so that it's faster (I put the most restrictive condition first in the WHERE close)

I have few questions on SQL..

<ol>
<li>How to analyze the performance of a
query? Any software, inbuilt
features of MSSQL server 2005/2008?</li>
<li>What should be used in place of <code>in</code>in queries so that the performance is better?
Eg: 

SELECT * 
FROM enquiry_courses 
WHERE 
enquiry_id IN 
( SELECT enquiry_id FROM enquiries WHERE session_id = '4cd3420a16dbd61c6af58f6199ac00f1' )</li>
<li>Which is better: <code>JOINS</code>, <code>EXISTS</code> or <code>IN</code> in terms of performance?</li>
</ol>

Comments/Help appreciated...

JOINS, EXISTS or IN which is better? Few questions on SQL

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

我对SQL没有几个问题。如何分析查询的性能？在查询中，应该使用MSSQL 2005/2008?What内置的任何软件特性来代替in，以便性能更好？例：从enquiry_courses WHERE enquiry_id IN中选择*(从enquiry_id = '4cd3420a16dbd61c6af58f6199ac00f1‘)中选择更好的：JOINS、EXISTS或IN (从enquiry_id

问加入，存在还是在哪一个更好？关于SQL的几个问题
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问加入，存在还是在哪一个更好？关于SQL的几个问题EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问加入，存在还是在哪一个更好？关于SQL的几个问题
EN