<p>What is the difference between the <code>EXISTS</code> and <code>IN</code> clause in SQL?</p>

<p>When should we use <code>EXISTS</code>, and when should we use <code>IN</code>?</p>

[{'answer_id': 24930, 'author': 'Lasse V. Karlsen', 'author_id': 267, 'author_profile': 'https://Stackoverflow.com/users/267', 'pm_score': 6, 'selected': False, 'text': "<p>I'm assuming you know what they do, and thus are used differently, so I'm going to understand your question as: When would it be a good idea to rewrite the SQL to use IN instead of EXISTS, or vice versa.</p>\n\n<p>Is that a fair assumption?</p>\n\n<hr>\n\n<p><strong>Edit</strong>: The reason I'm asking is that in many cases you can rewrite an SQL based on IN to use an EXISTS instead, and vice versa, and for some database engines, the query optimizer will treat the two differently.</p>\n\n<p>For instance:</p>\n\n<pre><code>SELECT *\nFROM Customers\nWHERE EXISTS (\n    SELECT *\n    FROM Orders\n    WHERE Orders.CustomerID = Customers.ID\n)\n</code></pre>\n\n<p>can be rewritten to:</p>\n\n<pre><code>SELECT *\nFROM Customers\nWHERE ID IN (\n    SELECT CustomerID\n    FROM Orders\n)\n</code></pre>\n\n<p>or with a join:</p>\n\n<pre><code>SELECT Customers.*\nFROM Customers\n    INNER JOIN Orders ON Customers.ID = Orders.CustomerID\n</code></pre>\n\n<p>So my question still stands, is the original poster wondering about what IN and EXISTS does, and thus how to use it, or does he ask wether rewriting an SQL using IN to use EXISTS instead, or vice versa, will be a good idea?</p>\n"}, {'answer_id': 24932, 'author': 'Matt Hamilton', 'author_id': 615, 'author_profile': 'https://Stackoverflow.com/users/615', 'pm_score': 7, 'selected': False, 'text': '<p><code>EXISTS</code> will tell you whether a query returned any results. e.g.:</p>\n\n<pre><code>SELECT * \nFROM Orders o \nWHERE EXISTS (\n    SELECT * \n    FROM Products p \n    WHERE p.ProductNumber = o.ProductNumber)\n</code></pre>\n\n<p><code>IN</code> is used to compare one value to several, and can use literal values, like this:</p>\n\n<pre><code>SELECT * \nFROM Orders \nWHERE ProductNumber IN (1, 10, 100)\n</code></pre>\n\n<p>You can also use query results with the <code>IN</code> clause, like this:</p>\n\n<pre><code>SELECT * \nFROM Orders \nWHERE ProductNumber IN (\n    SELECT ProductNumber \n    FROM Products \n    WHERE ProductInventoryQuantity &gt; 0)\n</code></pre>\n'}, {'answer_id': 24933, 'author': 'Gishu', 'author_id': 1695, 'author_profile': 'https://Stackoverflow.com/users/1695', 'pm_score': 3, 'selected': False, 'text': '<p>I think, </p>\n\n<ul>\n<li><p><code>EXISTS</code> is when you need to match the results of query with another subquery.\nQuery#1 results need to be retrieved where SubQuery results match. Kind of a Join..\nE.g. select customers table#1 who have placed orders table#2 too</p></li>\n<li><p>IN is to retrieve if the value of a specific column lies <code>IN</code> a list (1,2,3,4,5)\nE.g. Select customers who lie in the following zipcodes i.e. zip_code values lies in (....) list.</p></li>\n</ul>\n\n<p>When to use one over the other... when you feel it reads appropriately (Communicates intent better). </p>\n'}, {'answer_id': 24948, 'author': 'Keith', 'author_id': 905, 'author_profile': 'https://Stackoverflow.com/users/905', 'pm_score': 8, 'selected': False, 'text': '<p>The <code>exists</code> keyword can be used in that way, but really it\'s intended as a way to avoid counting:</p>\n\n\n\n<pre class="lang-sql prettyprint-override"><code>--this statement needs to check the entire table\nselect count(*) from [table] where ...\n\n--this statement is true as soon as one match is found\nexists ( select * from [table] where ... )\n</code></pre>\n\n<p>This is most useful where you have <code>if</code> conditional statements, as <code>exists</code> can be a lot quicker than <code>count</code>.</p>\n\n<p>The <code>in</code> is best used where you have a static list to pass:</p>\n\n<pre class="lang-sql prettyprint-override"><code> select * from [table]\n where [field] in (1, 2, 3)\n</code></pre>\n\n<p>When you have a table in an <code>in</code> statement it makes more sense to use a <code>join</code>, but mostly it shouldn\'t matter. The query optimiser should return the same plan either way. In some implementations (mostly older, such as Microsoft SQL Server 2000) <code>in</code> queries will always get a <a href="https://technet.microsoft.com/en-us/library/ms191318(v=sql.105).aspx" rel="noreferrer">nested join</a> plan, while <code>join</code> queries will use nested, <a href="https://technet.microsoft.com/en-us/library/ms190967(v=sql.105).aspx" rel="noreferrer">merge</a> or <a href="https://technet.microsoft.com/en-us/library/ms189313(v=sql.105).aspx" rel="noreferrer">hash</a> as appropriate. More modern implementations are smarter and can adjust the plan even when <code>in</code> is used.</p>\n'}, {'answer_id': 3964770, 'author': 'jackson', 'author_id': 479956, 'author_profile': 'https://Stackoverflow.com/users/479956', 'pm_score': 6, 'selected': False, 'text': '<p>Based on <em>rule optimizer</em>: </p>\n\n<ul>\n<li><code>EXISTS</code> is much faster than <code>IN</code>, when the sub-query results is very large.</li>\n<li><code>IN</code> is  faster than <code>EXISTS</code>, when the sub-query results is very small.</li>\n</ul>\n\n<p>Based on <em>cost optimizer</em>:  </p>\n\n<ul>\n<li>There is no difference. </li>\n</ul>\n'}, {'answer_id': 9797674, 'author': 'ram', 'author_id': 1282333, 'author_profile': 'https://Stackoverflow.com/users/1282333', 'pm_score': 2, 'selected': False, 'text': '<p>As per my knowledge when a subquery returns a <code>NULL</code> value then the whole statement becomes <code>NULL</code>. In that cases we are using the <code>EXITS</code> keyword. If we want to compare particular values in subqueries then we are using the <code>IN</code> keyword.</p>\n'}, {'answer_id': 10748678, 'author': 'Arulraj.M', 'author_id': 1416571, 'author_profile': 'https://Stackoverflow.com/users/1416571', 'pm_score': 3, 'selected': False, 'text': '<p>The <code>Exists</code> keyword evaluates true or false, but <code>IN</code> keyword compare all value in the corresponding sub query column. \nAnother one <code>Select 1</code> can be use with <code>Exists</code> command. Example:</p>\n\n<pre><code>SELECT * FROM Temp1 where exists(select 1 from Temp2 where conditions...)\n</code></pre>\n\n<p>But <code>IN</code> is less efficient so <code>Exists</code> faster.</p>\n'}, {'answer_id': 10875484, 'author': 'Gagandeep Singh', 'author_id': 1434232, 'author_profile': 'https://Stackoverflow.com/users/1434232', 'pm_score': -1, 'selected': False, 'text': '<p>If you are using the IN operator, the SQL engine will scan all records fetched from the inner query. On the other hand if we are using EXISTS, the SQL engine will stop the scanning process as soon as it found a match. </p>\n'}, {'answer_id': 11329321, 'author': 'Alireza Masali', 'author_id': 1450585, 'author_profile': 'https://Stackoverflow.com/users/1450585', 'pm_score': 5, 'selected': False, 'text': "<ol>\n<li><p><code>EXISTS</code> is much faster than <code>IN</code> when the subquery results is very large.<br>\n<code>IN</code> is faster than <code>EXISTS</code> when the subquery results is very small.</p>\n\n<pre><code>CREATE TABLE t1 (id INT, title VARCHAR(20), someIntCol INT)\nGO\nCREATE TABLE t2 (id INT, t1Id INT, someData VARCHAR(20))\nGO\n\nINSERT INTO t1\nSELECT 1, 'title 1', 5 UNION ALL\nSELECT 2, 'title 2', 5 UNION ALL\nSELECT 3, 'title 3', 5 UNION ALL\nSELECT 4, 'title 4', 5 UNION ALL\nSELECT null, 'title 5', 5 UNION ALL\nSELECT null, 'title 6', 5\n\nINSERT INTO t2\nSELECT 1, 1, 'data 1' UNION ALL\nSELECT 2, 1, 'data 2' UNION ALL\nSELECT 3, 2, 'data 3' UNION ALL\nSELECT 4, 3, 'data 4' UNION ALL\nSELECT 5, 3, 'data 5' UNION ALL\nSELECT 6, 3, 'data 6' UNION ALL\nSELECT 7, 4, 'data 7' UNION ALL\nSELECT 8, null, 'data 8' UNION ALL\nSELECT 9, 6, 'data 9' UNION ALL\nSELECT 10, 6, 'data 10' UNION ALL\nSELECT 11, 8, 'data 11'\n</code></pre></li>\n<li><p><strong>Query 1</strong></p>\n\n<pre><code>SELECT\nFROM    t1 \nWHERE   not  EXISTS (SELECT * FROM t2 WHERE t1.id = t2.t1id)\n</code></pre>\n\n<p><strong>Query 2</strong></p>\n\n<pre><code>SELECT t1.* \nFROM   t1 \nWHERE  t1.id not in (SELECT  t2.t1id FROM t2 )\n</code></pre>\n\n<p>If in <code>t1</code> your id has null value then Query 1 will find them, but Query 2 cant find null parameters.</p>\n\n<p>I mean <code>IN</code> can't compare anything with null, so it has no result for null, but <code>EXISTS</code> can compare everything with null.</p></li>\n</ol>\n"}, {'answer_id': 13701788, 'author': 'djohn', 'author_id': 1875484, 'author_profile': 'https://Stackoverflow.com/users/1875484', 'pm_score': 0, 'selected': False, 'text': '<p>If a subquery returns more than one value, you might need to execute the outer query- if the values within the column specified in the condition match any value in the result set of the subquery. To perform this task, you need to use the <code>in</code> keyword.</p>\n\n<p>You can use a subquery to check if a set of records exists. For this, you need to use the <code>exists</code> clause with a subquery. The <code>exists</code> keyword always return true or false value.</p>\n'}, {'answer_id': 15910409, 'author': 'Deva', 'author_id': 2263223, 'author_profile': 'https://Stackoverflow.com/users/2263223', 'pm_score': -1, 'selected': False, 'text': '<p>EXISTS Is Faster in Performance than IN.\nIf Most of the filter criteria is in subquery then better to use IN and If most of the filter criteria is in main query then better to use EXISTS.</p>\n'}, {'answer_id': 17463906, 'author': 'Sumair Hussain Rajput', 'author_id': 2549343, 'author_profile': 'https://Stackoverflow.com/users/2549343', 'pm_score': 2, 'selected': False, 'text': "<p>Which one is faster depends on the number of queries fetched by the inner query:</p>\n\n<ul>\n<li>When your inner query fetching thousand of rows then EXIST would be better choice</li>\n<li>When your inner query fetching few rows, then IN will be faster</li>\n</ul>\n\n<p>EXIST evaluate on true or false but IN compare multiple value. When you don't know the record is exist or not, your should choose EXIST</p>\n"}, {'answer_id': 17671111, 'author': 'If you are using the IN operat', 'author_id': 2586350, 'author_profile': 'https://Stackoverflow.com/users/2586350', 'pm_score': 4, 'selected': False, 'text': '<p>If you are using the <code>IN</code> operator, the SQL engine will scan all records fetched from the inner query. On the other hand if we are using <code>EXISTS</code>, the SQL engine will stop the scanning process as soon as it found a match.</p>\n'}, {'answer_id': 30325975, 'author': 'rogue lad', 'author_id': 1400737, 'author_profile': 'https://Stackoverflow.com/users/1400737', 'pm_score': 2, 'selected': False, 'text': '<p>Difference lies here:</p>\n\n<pre><code>select * \nfrom abcTable\nwhere exists (select null)\n</code></pre>\n\n<p>Above query will return all the records while below one would return empty.</p>\n\n<pre><code>select *\nfrom abcTable\nwhere abcTable_ID in (select null)\n</code></pre>\n\n<p>Give it a try and observe the output.</p>\n'}, {'answer_id': 36471445, 'author': 'Vipin Jain', 'author_id': 2153834, 'author_profile': 'https://Stackoverflow.com/users/2153834', 'pm_score': 2, 'selected': False, 'text': '<p>The reason is that the EXISTS operator works based on the “at least found” principle. It returns true and stops scanning table once at least one matching row found.</p>\n\n<p>On the other hands, when the IN operator is combined with a subquery, MySQL must process the subquery first, and then uses the result of the subquery to process the whole query.</p>\n\n<blockquote>\n  <p>The general rule of thumb is that if the subquery contains a large\n  volume of data, the EXISTS operator provides a better performance.</p>\n  \n  <p>However, the query that uses the IN operator will perform faster if\n  the result set returned from the subquery is very small.</p>\n</blockquote>\n'}, {'answer_id': 39459450, 'author': 'Ranjeeth', 'author_id': 5559184, 'author_profile': 'https://Stackoverflow.com/users/5559184', 'pm_score': 1, 'selected': False, 'text': '<p>My understand is both should be the same as long as we are not dealing with NULL values. </p>\n\n<p>The same reason why the query does not return the value for = NULL vs is NULL. \n<a href="http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/" rel="nofollow">http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/</a> </p>\n\n<p>As for as boolean vs comparator argument goes, to generate a boolean both values needs to be compared and that is how any if condition works.So i fail to understand how IN and EXISTS behave differently\n.  </p>\n'}, {'answer_id': 39918818, 'author': 'David דודו Markovitz', 'author_id': 6336479, 'author_profile': 'https://Stackoverflow.com/users/6336479', 'pm_score': 4, 'selected': False, 'text': "<p><strong>IN</strong> supports only equality relations (or inequality when preceded by <strong>NOT</strong>).<br />\nIt is a synonym to <strong>=any</strong> / <strong>=some</strong>, e.g</p>\n<pre><code>select    * \nfrom      t1 \nwhere     x in (select x from t2)\n;\n</code></pre>\n<p><strong>EXISTS</strong> supports variant types of relations, that cannot be expressed using <strong>IN</strong>, e.g. -</p>\n<pre><code>select    * \nfrom      t1 \nwhere     exists (select    null \n                  from      t2 \n                  where     t2.x=t1.x \n                        and t2.y&gt;t1.y \n                        and t2.z like '℅' || t1.z || '℅'\n                  )\n;\n</code></pre>\n<hr />\n<h1>And on a different note -</h1>\n<p>The allegedly performance and technical differences between <strong>EXISTS</strong> and <strong>IN</strong> may result from specific vendor's implementations/limitations/bugs, but many times they are nothing but myths created due to lack of understanding of the databases internals.</p>\n<p>The tables' definition, statistics' accuracy, database configuration and optimizer's version have all impact on the execution plan and therefore on the performance metrics.</p>\n"}, {'answer_id': 42991746, 'author': 'FatalError', 'author_id': 1795136, 'author_profile': 'https://Stackoverflow.com/users/1795136', 'pm_score': 0, 'selected': False, 'text': '<p>I believe this has a straightforward answer. Why don\'t you check it from the people who developed that function in their systems?</p>\n\n<p>If you are a MS SQL developer, here is the answer directly from Microsoft.</p>\n\n<p><a href="https://msdn.microsoft.com/en-us/library/ms177682.aspx" rel="nofollow noreferrer"><code>IN</code></a>:</p>\n\n<blockquote>\n  <p>Determines whether a specified value matches any value in a subquery or a list.</p>\n</blockquote>\n\n<p><a href="https://msdn.microsoft.com/en-us/library/ms188336.aspx" rel="nofollow noreferrer"><code>EXISTS</code></a>:</p>\n\n<blockquote>\n  <p>Specifies a subquery to test for the existence of rows.</p>\n</blockquote>\n'}, {'answer_id': 44783510, 'author': 'Axel Der', 'author_id': 7900035, 'author_profile': 'https://Stackoverflow.com/users/7900035', 'pm_score': 0, 'selected': False, 'text': '<p>I found that using EXISTS keyword is often really slow (that is very true in Microsoft Access).\nI instead use the join operator in this manner :\n<a href="http://www.thegeeklegacy.com/t/should-i-use-the-keyword-exists-in-sql/60/?utm_source=stackoverflow" rel="nofollow noreferrer">should-i-use-the-keyword-exists-in-sql</a></p>\n'}, {'answer_id': 45737479, 'author': 'TOUZENE Mohamed Wassim', 'author_id': 6056511, 'author_profile': 'https://Stackoverflow.com/users/6056511', 'pm_score': 2, 'selected': False, 'text': '<p><em><code>In certain circumstances, it is better to use IN rather than EXISTS. In general, if the selective predicate is in the subquery, then use IN. If the selective predicate is in the parent query, then use EXISTS.</code></em></p>\n\n<p><a href="https://docs.oracle.com/cd/B19306_01/server.102/b14211/sql_1016.htm#i28403" rel="nofollow noreferrer">https://docs.oracle.com/cd/B19306_01/server.102/b14211/sql_1016.htm#i28403</a></p>\n'}, {'answer_id': 69865209, 'author': 'Adam', 'author_id': 2311074, 'author_profile': 'https://Stackoverflow.com/users/2311074', 'pm_score': 0, 'selected': False, 'text': '<p><strong>If you can use <code>where in</code> instead of <code>where exists</code>, then <code>where in</code> is probably faster.</strong></p>\n<p>Using <code>where in</code> or <code>where exists</code>\nwill go through all results of your parent result. The difference here is that the <code>where exists</code> will cause a lot of dependet sub-queries. If you can prevent dependet sub-queries, then <code>where in</code> will be the better choice.</p>\n<p><em>Example</em></p>\n<p>Assume we have 10,000 companies, each has 10 users (thus our users table has 100,000 entries). Now assume you want to find a user by his name or his company name.</p>\n<p>The following query using <code>were exists</code> has an execution of 141ms:</p>\n<pre><code>select * from `users` \nwhere `first_name` =\'gates\' \nor exists \n(\n  select * from `companies` \n  where `users`.`company_id` = `companies`.`id`\n  and `name` = \'gates\'\n)\n</code></pre>\n<p>This happens, because for each user a dependent sub query is executed:\n<a href="https://i.stack.imgur.com/mpcAK.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/mpcAK.png" alt="enter image description here" /></a></p>\n<p>However, if we avoid the exists query and write it using:</p>\n<pre><code>select * from `users` \nwhere `first_name` =\'gates\' \nor users.company_id in  \n(\n    select id from `companies` \n    where  `name` = \'gates\'\n)\n</code></pre>\n<p>Then depended sub queries are avoided and the query would run in 0,012 ms</p>\n<p><a href="https://i.stack.imgur.com/FHt2d.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/FHt2d.png" alt="enter image description here" /></a></p>\n'}]
['https://Stackoverflow.com/questions/24929', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2528/']