{"id":2578,"date":"2026-05-30T03:28:47","date_gmt":"2026-05-30T03:28:47","guid":{"rendered":"https:\/\/tucumandevelopers.com\/index.php\/2026\/05\/30\/sql-pattern-series-1-the-presence-pattern\/"},"modified":"2026-05-30T03:28:47","modified_gmt":"2026-05-30T03:28:47","slug":"sql-pattern-series-1-the-presence-pattern","status":"publish","type":"post","link":"https:\/\/tucumandevelopers.com\/index.php\/2026\/05\/30\/sql-pattern-series-1-the-presence-pattern\/","title":{"rendered":"SQL Pattern Series #1: The Presence Pattern"},"content":{"rendered":"<div>\n<div><\/div>\n<div data-article-id=\"3781492\" id=\"article-body\">\n<p><em>Thinking in terms of existence instead of lists<\/em><\/p>\n<blockquote>\n<p>SQL Pattern Series #1 of 21<\/p>\n<p>A collection of practical SQL patterns that help developers recognize common solutions to recurring database problems.<\/p>\n<\/blockquote>\n<h2> <a name=\"what-youll-learn\" href=\"#what-youll-learn\"> <\/a> What You&#8217;ll Learn <\/h2>\n<p>In this article you&#8217;ll learn:<\/p>\n<ul>\n<li>When EXISTS and IN solve the same problem<\/li>\n<li>The difference between set membership and existence<\/li>\n<li>Why the underlying mental model matters<\/li>\n<li>When I typically reach for EXISTS<\/li>\n<\/ul>\n<p>Most SQL developers write a query like this at some point: <\/p>\n<div>\n<pre><code><span>SELECT<\/span> <span>c<\/span><span>.<\/span><span>CustomerID<\/span><span>,<\/span> <span>c<\/span><span>.<\/span><span>CustomerName<\/span> <span>FROM<\/span> <span>Customers<\/span> <span>c<\/span> <span>WHERE<\/span> <span>c<\/span><span>.<\/span><span>CustomerID<\/span> <span>IN<\/span> <span>(<\/span> <span>SELECT<\/span> <span>o<\/span><span>.<\/span><span>CustomerID<\/span> <span>FROM<\/span> <span>Orders<\/span> <span>o<\/span> <span>);<\/span> <\/code><\/pre>\n<div>\n<\/p><\/div>\n<\/p><\/div>\n<p>And it works.<\/p>\n<p>But sometimes it isn&#8217;t the best way to think about the problem.<\/p>\n<hr>\n<h2> <a name=\"the-question-behind-the-query\" href=\"#the-question-behind-the-query\"> <\/a> The Question Behind the Query <\/h2>\n<p>Many SQL problems can be framed in two different ways.<\/p>\n<h3> <a name=\"set-membership\" href=\"#set-membership\"> <\/a> Set Membership <\/h3>\n<blockquote>\n<p>Is this value in a set? <\/p>\n<\/blockquote>\n<div>\n<pre><code><span>WHERE<\/span> <span>CustomerID<\/span> <span>IN<\/span> <span>(...)<\/span> <\/code><\/pre>\n<div>\n<\/p><\/div>\n<\/p><\/div>\n<h3> <a name=\"existence\" href=\"#existence\"> <\/a> Existence <\/h3>\n<blockquote>\n<p>Does at least one matching row exist? <\/p>\n<\/blockquote>\n<div>\n<pre><code><span>WHERE<\/span> <span>EXISTS<\/span> <span>(...)<\/span> <\/code><\/pre>\n<div>\n<\/p><\/div>\n<\/p><\/div>\n<p>Both approaches often return the same result.<\/p>\n<p>But they represent different mental models.<\/p>\n<hr>\n<h2> <a name=\"the-presence-pattern\" href=\"#the-presence-pattern\"> <\/a> The Presence Pattern <\/h2>\n<p>The <strong>Presence Pattern<\/strong> is useful when you do not actually care about the values being returned from a related table.<\/p>\n<p>You only care whether a matching row exists.<\/p>\n<p>For example:<\/p>\n<ul>\n<li>Customers who have placed an order<\/li>\n<li>Users who have logged in<\/li>\n<li>Employees assigned to a project<\/li>\n<li>Products that have sales<\/li>\n<\/ul>\n<p>In these cases, the question is often:<\/p>\n<blockquote>\n<p>Does a related row exist?<\/p>\n<\/blockquote>\n<p>rather than:<\/p>\n<blockquote>\n<p>What values are contained in this list?<\/p>\n<\/blockquote>\n<hr>\n<h2> <a name=\"example-using-exists\" href=\"#example-using-exists\"> <\/a> Example Using EXISTS <\/h2>\n<div>\n<pre><code><span>SELECT<\/span> <span>c<\/span><span>.<\/span><span>CustomerID<\/span><span>,<\/span> <span>c<\/span><span>.<\/span><span>CustomerName<\/span> <span>FROM<\/span> <span>Customers<\/span> <span>c<\/span> <span>WHERE<\/span> <span>EXISTS<\/span> <span>(<\/span> <span>SELECT<\/span> <span>1<\/span> <span>FROM<\/span> <span>Orders<\/span> <span>o<\/span> <span>WHERE<\/span> <span>o<\/span><span>.<\/span><span>CustomerID<\/span> <span>=<\/span> <span>c<\/span><span>.<\/span><span>CustomerID<\/span> <span>);<\/span> <\/code><\/pre>\n<div>\n<\/p><\/div>\n<\/p><\/div>\n<p>The subquery is correlated to the outer query.<\/p>\n<p>Conceptually, SQL asks:<\/p>\n<blockquote>\n<p>For this customer, does at least one matching order exist?<\/p>\n<\/blockquote>\n<p>As soon as the answer becomes true, the condition is satisfied.<\/p>\n<hr>\n<h2> <a name=\"why-this-pattern-matters\" href=\"#why-this-pattern-matters\"> <\/a> Why This Pattern Matters <\/h2>\n<p>Many SQL developers initially learn syntax.<\/p>\n<p>Over time, they discover that query writing is really about choosing the right mental model.<\/p>\n<p>The Presence Pattern encourages you to think in terms of:<\/p>\n<ul>\n<li>existence<\/li>\n<li>relationships<\/li>\n<li>matching rows<\/li>\n<\/ul>\n<p>instead of building lists unnecessarily.<\/p>\n<p>That shift often makes queries easier to reason about.<\/p>\n<hr>\n<h2> <a name=\"a-note-on-performance\" href=\"#a-note-on-performance\"> <\/a> A Note on Performance <\/h2>\n<p>Modern database optimizers are extremely sophisticated.<\/p>\n<p>In many systems, <code>IN<\/code> and <code>EXISTS<\/code> may be rewritten into similar execution plans.<\/p>\n<p>As a result:<\/p>\n<blockquote>\n<p>The same result does not necessarily mean the same execution strategy.<\/p>\n<\/blockquote>\n<p>And the same syntax does not necessarily mean different performance.<\/p>\n<p>Always validate assumptions with execution plans and real-world testing.<\/p>\n<p>The value of this pattern is primarily conceptual:<\/p>\n<ul>\n<li>existence vs. membership<\/li>\n<li>relationship vs. list<\/li>\n<li>presence vs. values<\/li>\n<\/ul>\n<hr>\n<h2> <a name=\"when-i-reach-for-this-pattern\" href=\"#when-i-reach-for-this-pattern\"> <\/a> When I Reach for This Pattern <\/h2>\n<p>I typically consider <code>EXISTS<\/code> when:<\/p>\n<ul>\n<li>I only need to know whether related data exists<\/li>\n<li>The subquery may return many rows<\/li>\n<li>The relationship itself is the focus of the query<\/li>\n<li>I want the query to communicate intent clearly<\/li>\n<\/ul>\n<p>Examples include:<\/p>\n<ul>\n<li>customers with orders<\/li>\n<li>users with activity<\/li>\n<li>products with transactions<\/li>\n<li>accounts with associated records<\/li>\n<\/ul>\n<hr>\n<h2> <a name=\"key-takeaway\" href=\"#key-takeaway\"> <\/a> Key Takeaway <\/h2>\n<p>Many SQL problems become easier when you ask:<\/p>\n<blockquote>\n<p>Do I need the values?<\/p>\n<\/blockquote>\n<p>or:<\/p>\n<blockquote>\n<p>Do I simply need to know whether they exist?<\/p>\n<\/blockquote>\n<p>That small distinction changes how you think about the query.<\/p>\n<p>And sometimes, changing how you think about the problem is more important than changing the syntax.<\/p>\n<hr>\n<h2> <a name=\"sql-pattern-series\" href=\"#sql-pattern-series\"> <\/a> SQL Pattern Series <\/h2>\n<p>This article is part of the <strong>SQL Pattern Series<\/strong>, a collection of practical SQL patterns that help developers recognize common problem-solving approaches found in reporting, analytics, and application development.<\/p>\n<hr>\n<h2> <a name=\"sql-bubble-pop\" href=\"#sql-bubble-pop\"> <\/a> SQL Bubble Pop <\/h2>\n<p>If you are learning SQL or helping others learn SQL, I created <strong><a href=\"https:\/\/apps.apple.com\/us\/app\/sql-bubble-pop-sql-coding-game\/id6744767120\" target=\"_blank\" rel=\"noopener noreferrer\">SQL Bubble Pop<\/a><\/strong>, a mobile game that teaches SQL concepts through quick, interactive challenges and pattern recognition exercises.<\/p>\n<p>The goal is simple:<\/p>\n<blockquote>\n<p>Learn SQL by recognizing patterns instead of memorizing syntax.<\/p>\n<\/blockquote><\/div>\n<\/div>\n<\/div>\n<\/div>\n<p>Fuente: <a href=\"https:\/\/dev.to\/baldwin_apps\/sql-pattern-series-1-the-presence-pattern-a5f\">Art\u00edculo original<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Thinking in terms of existence instead of lists SQL Pattern Series #1 of 21 A collection of practical SQL patterns that help developers recognize common solutions to recurring database problems. What You&#8217;ll Learn In this article you&#8217;ll learn: When EXISTS and IN solve the same problem The difference between set membership and existence Why the [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":2577,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[41],"tags":[],"class_list":["post-2578","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-devto"],"jetpack_publicize_connections":[],"_links":{"self":[{"href":"https:\/\/tucumandevelopers.com\/index.php\/wp-json\/wp\/v2\/posts\/2578","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tucumandevelopers.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tucumandevelopers.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tucumandevelopers.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/tucumandevelopers.com\/index.php\/wp-json\/wp\/v2\/comments?post=2578"}],"version-history":[{"count":0,"href":"https:\/\/tucumandevelopers.com\/index.php\/wp-json\/wp\/v2\/posts\/2578\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/tucumandevelopers.com\/index.php\/wp-json\/wp\/v2\/media\/2577"}],"wp:attachment":[{"href":"https:\/\/tucumandevelopers.com\/index.php\/wp-json\/wp\/v2\/media?parent=2578"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tucumandevelopers.com\/index.php\/wp-json\/wp\/v2\/categories?post=2578"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tucumandevelopers.com\/index.php\/wp-json\/wp\/v2\/tags?post=2578"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}