{"id":29061,"date":"2024-04-28T01:48:47","date_gmt":"2024-04-28T00:48:47","guid":{"rendered":"https:\/\/nelkodev.com\/blog\/descubre-el-poder-de-mysql-usando-not-in-para-excluir-datos\/"},"modified":"2024-06-03T17:44:11","modified_gmt":"2024-06-03T16:44:11","slug":"descubre-el-poder-de-mysql-usando-not-in-para-excluir-datos","status":"publish","type":"post","link":"https:\/\/nelkodev.com\/en\/blog\/discover-the-power-of-mysql-using-not-in-to-exclude-data\/","title":{"rendered":"Discover the Power of MySQL: Using NOT IN to Exclude Data"},"content":{"rendered":"<p>When working with databases, especially MySQL, we often need to filter data sets based on specific criteria. One of the most useful tools for this task is the operator <code>IN<\/code>, which allows us to specify multiple values in one clause <code>WHERE<\/code>. However, what happens when we need to exclude, rather than select, a specific set of values? This is where the operator comes into play. <code>NOT IN<\/code>. This article explores how to use <code>NOT IN<\/code> in MySQL to effectively handle data sets by excluding specific values.<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_80 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #ffffff;color:#ffffff\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewbox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #ffffff;color:#ffffff\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewbox=\"0 0 24 24\" version=\"1.2\" baseprofile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/nelkodev.com\/en\/blog\/discover-the-power-of-mysql-using-not-in-to-exclude-data\/#%C2%BFQue_es_NOT_IN_en_MySQL\" >What is NOT IN in MySQL?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/nelkodev.com\/en\/blog\/discover-the-power-of-mysql-using-not-in-to-exclude-data\/#Sintaxis_Basica_de_NOT_IN\" >Basic NOT IN Syntax<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/nelkodev.com\/en\/blog\/discover-the-power-of-mysql-using-not-in-to-exclude-data\/#Ejemplo_Practico_Excluyendo_Valores_Especificos\" >Practical Example: Excluding Specific Values<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/nelkodev.com\/en\/blog\/discover-the-power-of-mysql-using-not-in-to-exclude-data\/#Ventajas_de_Usar_NOT_IN\" >Advantages of Using NOT IN<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/nelkodev.com\/en\/blog\/discover-the-power-of-mysql-using-not-in-to-exclude-data\/#Consideraciones_al_Usar_NOT_IN\" >Considerations When Using NOT IN<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/nelkodev.com\/en\/blog\/discover-the-power-of-mysql-using-not-in-to-exclude-data\/#Alternativas_a_NOT_IN\" >Alternatives to NOT IN<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/nelkodev.com\/en\/blog\/discover-the-power-of-mysql-using-not-in-to-exclude-data\/#Conclusiones\" >Conclusions<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"%C2%BFQue_es_NOT_IN_en_MySQL\"><\/span>What is NOT IN in MySQL?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><code>NOT IN<\/code> is an operator in MySQL that is used to negate a condition specified in <code>IN<\/code>. Basically it helps to select all the records that do not match the values listed in the operator <code>IN<\/code>. For example, if we want to find all customers that are not in certain cities, <code>NOT IN<\/code> It would be our tool of choice.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Sintaxis_Basica_de_NOT_IN\"><\/span>Basic NOT IN Syntax<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The syntax of <code>NOT IN<\/code> It is simple and direct. Here&#039;s a basic example:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">SELECT column1, column2 FROM table WHERE column1 NOT IN (value1, value2, value3);<\/code><\/pre>\n<p>In this example, MySQL will select and return rows of <code>board<\/code> where the value of <code>column1<\/code> not even <code>value1<\/code>, neither <code>value2<\/code>, neither <code>value3<\/code>.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Ejemplo_Practico_Excluyendo_Valores_Especificos\"><\/span>Practical Example: Excluding Specific Values<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Let&#039;s say we have a table called <code>Customers<\/code> with a column <code>City<\/code>. If we want to obtain a list of clients that are not located in &#039;Madrid&#039;, &#039;Barcelona&#039; or &#039;Valencia&#039;, we could use the following query:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">SELECT * FROM Clients WHERE City NOT IN (&#039;Madrid&#039;, &#039;Barcelona&#039;, &#039;Valencia&#039;);<\/code><\/pre>\n<p>This query will filter the list of clients and exclude those who reside in Madrid, Barcelona or Valencia, showing clients from all other cities.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Ventajas_de_Usar_NOT_IN\"><\/span>Advantages of Using NOT IN<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>One of the main advantages of using <code>NOT IN<\/code> It is its clarity and ease of understanding. It allows you to explicitly specify which values should be excluded, which makes the code easy to read and maintain. Besides, <code>NOT IN<\/code> It is extremely useful in situations where the list of values to exclude is relatively small compared to the entire data.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Consideraciones_al_Usar_NOT_IN\"><\/span>Considerations When Using NOT IN<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Although <code>NOT IN<\/code> is very useful, it is important to use it correctly to avoid performance problems, especially with large volumes of data. Some points to consider include:<\/p>\n<ul>\n<li><strong>Indices<\/strong>: Make sure the columns used with <code>NOT IN<\/code> are correctly indexed. This can help significantly improve query speed.<\/li>\n<li><strong>Subqueries<\/strong>: When you use subqueries with <code>NOT IN<\/code>, performance may suffer if subqueries return large sets of data. It is crucial to evaluate and optimize these queries.<\/li>\n<li><strong>NULL values<\/strong>: It is important to remember that <code>NOT IN<\/code> will not select rows where the column has a NULL value unless explicitly specified.<\/li>\n<\/ul>\n<h2><span class=\"ez-toc-section\" id=\"Alternativas_a_NOT_IN\"><\/span>Alternatives to NOT IN<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>In some cases, you might consider using <code>NOT EXISTS<\/code> or a combination of <code>LEFT JOIN<\/code> y <code>IS NULL<\/code> as alternatives to <code>NOT IN<\/code>. These alternatives can often provide better performance with large data sets or complex subqueries.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Conclusiones\"><\/span>Conclusions<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The operator <code>NOT IN<\/code> is a powerful tool in SQL that allows developers to effectively exclude specific values in their queries. Its simplicity and clarity make it easy to implement and understand, although it is essential to handle it carefully so as not to compromise database performance.<\/p>\n<p>If you are interested in exploring more about MySQL or need help, feel free to visit <a href=\"https:\/\/nelkodev.com\/en\/\">NelkoDev<\/a> or contact me directly at <a href=\"https:\/\/nelkodev.com\/en\/contact\/\">Contact NelkoDev<\/a> for more information. I&#039;m here to help you master your database management skills and beyond!<\/p>","protected":false},"excerpt":{"rendered":"<p>When working with databases, especially in MySQL, we often need to filter data sets based on specific criteria. One of the most useful tools for this task is the IN operator, which allows us to specify multiple values in a WHERE clause. However, what happens when we need to exclude, rather than select, a specific set of data?<\/p>","protected":false},"author":1,"featured_media":29062,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[420,2185,2044],"tags":[2195,205,2189,353,140,2122,352,2123,60,1359,504,1273],"class_list":["post-29061","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","category-curso-mysql","category-sql-basics","tag-basics","tag-blog","tag-curso","tag-datos","tag-descubre","tag-excluir","tag-mysql","tag-not","tag-para","tag-poder","tag-sql","tag-usando"],"_links":{"self":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/29061","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/comments?post=29061"}],"version-history":[{"count":0,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/29061\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media\/29062"}],"wp:attachment":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media?parent=29061"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/categories?post=29061"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/tags?post=29061"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}