{"id":7451,"date":"2015-11-13T17:28:09","date_gmt":"2015-11-13T15:28:09","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=7451"},"modified":"2015-11-13T17:28:09","modified_gmt":"2015-11-13T15:28:09","slug":"sql-mini-hack-of-the-day-inverted-in-clause","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/sql-mini-hack-of-the-day-inverted-in-clause","title":{"rendered":"SQL mini hack of the day, inverted IN clause"},"content":{"rendered":"

We are used to issue queries with an IN<\/strong> clause of the form:<\/p>\n

\n
... where state in ('started', 'completed', 'failed') ...<\/pre>\n<\/blockquote>\n
However I’ve had a few cases where I used an inverted format. Here’s one use case followed by an inverted IN<\/strong> clause.<\/p>\n
Dynamic query building<\/h3>\n
Say we have this function:<\/p>\n
\n
GetLaggingSlaves(clusterName string)<\/pre>\n<\/blockquote>\n
Which, based on whether given clusterName<\/strong> is empty or not, would return list of all lagging slaves, or only those in the given cluster, respectively:<\/p>\n
\n
SELECT hostname FROM database_instance WHERE slave_lag_seconds > 60<\/pre>\n<\/blockquote>\n
or<\/p>\n
\n
SELECT hostname FROM database_instance WHERE cluster_name = 'mycluster:3306' AND slave_lag_seconds > 60<\/pre>\n<\/blockquote>\n
To avoid SQL injection you would create a prepared statement, but you don’t want to copy+paste everything, and so you build your query dynamically based on the value of clusterName<\/strong>. You want to end up with either:<\/p>\n
\n
SELECT hostname FROM database_instance WHERE slave_lag_seconds > ?<\/pre>\n
, acceptableLag<\/strong><\/p><\/blockquote>\n
or<\/p>\n
\n
SELECT hostname FROM database_instance WHERE cluster_name = ? AND slave_lag_seconds > ?<\/pre>\n
, clusterName, acceptableLag<\/strong><\/p><\/blockquote>\n
Dynamic query building is good practice, but a little pain (BTW I’m designing a new, simple & non intrusive query builder for golang). Is there a way to just get away with one query that has it all?<\/p>\n
This is one option:<\/p>\n
\n
SELECT hostname FROM database_instance WHERE (cluster_name = ? OR ? = '') AND slave_lag_seconds > ?<\/pre>\n
,clusterName, clusterName, acceptableLag<\/strong><\/p><\/blockquote>\n
and it is somewhat painful to list clusterName twice in the arguments list. This is where the inverted IN<\/strong> clause kicks in. It will negate usage of an index, and may look strange at first glance, but as you get used to it it just becomes\u00a0another pattern:<\/p>\n
\n
SELECT hostname FROM database_instance WHERE ? IN (cluster_name, '') AND slave_lag_seconds > ?<\/pre>\n
, clusterName, acceptableLag<\/strong><\/p><\/blockquote>\n
So when clusterName<\/strong> is empty, all rows where slave_lag_seconds > acceptableLag<\/strong> are fetched; when non empty, only those where cluster_name<\/strong> equals our value.<\/p>\n","protected":false},"excerpt":{"rendered":"
We are used to issue queries with an IN clause of the form: … where state in (‘started’, ‘completed’, ‘failed’) … However I’ve had a few cases where I used an inverted format. Here’s one use case followed by an inverted IN clause. Dynamic query building Say we have this function: GetLaggingSlaves(clusterName string) Which, based […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"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,"enabled":false},"version":2}},"categories":[5],"tags":[21],"class_list":["post-7451","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1Wb","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/7451","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=7451"}],"version-history":[{"count":11,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/7451\/revisions"}],"predecessor-version":[{"id":7463,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/7451\/revisions\/7463"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=7451"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=7451"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=7451"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}