"use strict";(self.webpackChunk=self.webpackChunk||[]).push([[7182],{3905:(t,e,a)=>{a.d(e,{Zo:()=>m,kt:()=>k});var n=a(67294);function r(t,e,a){return e in t?Object.defineProperty(t,e,{value:a,enumerable:!0,configurable:!0,writable:!0}):t[e]=a,t}function l(t,e){var a=Object.keys(t);if(Object.getOwnPropertySymbols){var n=Object.getOwnPropertySymbols(t);e&&(n=n.filter((function(e){return Object.getOwnPropertyDescriptor(t,e).enumerable}))),a.push.apply(a,n)}return a}function i(t){for(var e=1;e<arguments.length;e++){var a=null!=arguments[e]?arguments[e]:{};e%2?l(Object(a),!0).forEach((function(e){r(t,e,a[e])})):Object.getOwnPropertyDescriptors?Object.defineProperties(t,Object.getOwnPropertyDescriptors(a)):l(Object(a)).forEach((function(e){Object.defineProperty(t,e,Object.getOwnPropertyDescriptor(a,e))}))}return t}function o(t,e){if(null==t)return{};var a,n,r=function(t,e){if(null==t)return{};var a,n,r={},l=Object.keys(t);for(n=0;n<l.length;n++)a=l[n],e.indexOf(a)>=0||(r[a]=t[a]);return r}(t,e);if(Object.getOwnPropertySymbols){var l=Object.getOwnPropertySymbols(t);for(n=0;n<l.length;n++)a=l[n],e.indexOf(a)>=0||Object.prototype.propertyIsEnumerable.call(t,a)&&(r[a]=t[a])}return r}var p=n.createContext({}),d=function(t){var e=n.useContext(p),a=e;return t&&(a="function"==typeof t?t(e):i(i({},e),t)),a},m=function(t){var e=d(t.components);return n.createElement(p.Provider,{value:e},t.children)},u="mdxType",s={inlineCode:"code",wrapper:function(t){var e=t.children;return n.createElement(n.Fragment,{},e)}},N=n.forwardRef((function(t,e){var a=t.components,r=t.mdxType,l=t.originalType,p=t.parentName,m=o(t,["components","mdxType","originalType","parentName"]),u=d(a),N=r,k=u["".concat(p,".").concat(N)]||u[N]||s[N]||l;return a?n.createElement(k,i(i({ref:e},m),{},{components:a})):n.createElement(k,i({ref:e},m))}));function k(t,e){var a=arguments,r=e&&e.mdxType;if("string"==typeof t||r){var l=a.length,i=new Array(l);i[0]=N;var o={};for(var p in e)hasOwnProperty.call(e,p)&&(o[p]=e[p]);o.originalType=t,o[u]="string"==typeof t?t:r,i[1]=o;for(var d=2;d<l;d++)i[d]=a[d];return n.createElement.apply(null,i)}return n.createElement.apply(null,a)}N.displayName="MDXCreateElement"},94612:(t,e,a)=>{a.r(e),a.d(e,{assets:()=>m,contentTitle:()=>p,default:()=>k,frontMatter:()=>o,metadata:()=>d,toc:()=>u});var n=a(87462),r=a(63366),l=(a(67294),a(3905)),i=["components"],o={id:"sql-operators",title:"Druid SQL Operators",sidebar_label:"Operators"},p=void 0,d={unversionedId:"querying/sql-operators",id:"querying/sql-operators",title:"Druid SQL Operators",description:"\x3c!--",source:"@site/docs/28.0.1/querying/sql-operators.md",sourceDirName:"querying",slug:"/querying/sql-operators",permalink:"/docs/28.0.1/querying/sql-operators",draft:!1,tags:[],version:"current",frontMatter:{id:"sql-operators",title:"Druid SQL Operators",sidebar_label:"Operators"},sidebar:"docs",previous:{title:"SQL data types",permalink:"/docs/28.0.1/querying/sql-data-types"},next:{title:"Scalar functions",permalink:"/docs/28.0.1/querying/sql-scalar"}},m={},u=[{value:"Arithmetic operators",id:"arithmetic-operators",level:2},{value:"Datetime arithmetic operators",id:"datetime-arithmetic-operators",level:2},{value:"Concatenation operator",id:"concatenation-operator",level:2},{value:"Comparison operators",id:"comparison-operators",level:2},{value:"Logical operators",id:"logical-operators",level:2}],s={toc:u},N="wrapper";function k(t){var e=t.components,a=(0,r.Z)(t,i);return(0,l.kt)(N,(0,n.Z)({},s,a,{components:e,mdxType:"MDXLayout"}),(0,l.kt)("admonition",{type:"info"},(0,l.kt)("p",{parentName:"admonition"}," Apache Druid supports two query languages: Druid SQL and ",(0,l.kt)("a",{parentName:"p",href:"/docs/28.0.1/querying/"},"native queries"),".\nThis document describes the SQL language.")),(0,l.kt)("p",null,"Operators in ",(0,l.kt)("a",{parentName:"p",href:"/docs/28.0.1/querying/sql"},"Druid SQL")," typically operate on one or two values and return a result based on the values. Types of operators in Druid SQL include arithmetic, comparison, logical, and more, as described here. "),(0,l.kt)("p",null,"When performing math operations, Druid uses 64-bit integer (long) data type unless there are double or float values. If an operation uses float or double values, then the result is a double, which is a 64-bit float. The precision of float and double values is defined by ",(0,l.kt)("a",{parentName:"p",href:"https://docs.oracle.com/javase/specs/jls/se8/html/jls-5.html"},"Java")," and ",(0,l.kt)("a",{parentName:"p",href:"https://en.wikipedia.org/wiki/IEEE_754"},"the IEEE standard"),"."),(0,l.kt)("p",null,"Keep the following guidelines in mind to help you manage precision issues:"),(0,l.kt)("ul",null,(0,l.kt)("li",{parentName:"ul"},"Long values can store up to 2^63 accurately with an additional bit used for the sign."),(0,l.kt)("li",{parentName:"ul"},"Float values use 32 bits, and doubles use 64 bits. Both types are impacted by floating point precision. If you need exact decimal values, consider storing the number in a non-decimal format as a long value (up to the limit for longs). For example, if you need three decimal places, store the number multiplied by 1000 and then divide by 1000 when querying.")),(0,l.kt)("h2",{id:"arithmetic-operators"},"Arithmetic operators"),(0,l.kt)("table",null,(0,l.kt)("thead",{parentName:"table"},(0,l.kt)("tr",{parentName:"thead"},(0,l.kt)("th",{parentName:"tr",align:null},"Operator"),(0,l.kt)("th",{parentName:"tr",align:null},"Description"))),(0,l.kt)("tbody",{parentName:"table"},(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("inlineCode",{parentName:"td"},"x + y")),(0,l.kt)("td",{parentName:"tr",align:null},"Add")),(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("inlineCode",{parentName:"td"},"x - y")),(0,l.kt)("td",{parentName:"tr",align:null},"Subtract")),(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("inlineCode",{parentName:"td"},"x * y")),(0,l.kt)("td",{parentName:"tr",align:null},"Multiply")),(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("inlineCode",{parentName:"td"},"x / y")),(0,l.kt)("td",{parentName:"tr",align:null},"Divide")))),(0,l.kt)("h2",{id:"datetime-arithmetic-operators"},"Datetime arithmetic operators"),(0,l.kt)("p",null,"For the datetime arithmetic operators, ",(0,l.kt)("inlineCode",{parentName:"p"},"interval_expr")," can include interval literals like ",(0,l.kt)("inlineCode",{parentName:"p"},"INTERVAL '2' HOUR"),".\nThis operator treats days as uniformly 86400 seconds long, and does not take into account daylight savings time.\nTo account for daylight savings time, use the ",(0,l.kt)("a",{parentName:"p",href:"/docs/28.0.1/querying/sql-scalar#date-and-time-functions"},(0,l.kt)("inlineCode",{parentName:"a"},"TIME_SHIFT")," function"),".\nAlso see ",(0,l.kt)("a",{parentName:"p",href:"/docs/28.0.1/querying/sql-scalar#date-and-time-functions"},(0,l.kt)("inlineCode",{parentName:"a"},"TIMESTAMPADD"))," for datetime arithmetic."),(0,l.kt)("table",null,(0,l.kt)("thead",{parentName:"table"},(0,l.kt)("tr",{parentName:"thead"},(0,l.kt)("th",{parentName:"tr",align:null},"Operator"),(0,l.kt)("th",{parentName:"tr",align:null},"Description"))),(0,l.kt)("tbody",{parentName:"table"},(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("inlineCode",{parentName:"td"},"timestamp_expr + interval_expr")),(0,l.kt)("td",{parentName:"tr",align:null},"Add an amount of time to a timestamp.")),(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("inlineCode",{parentName:"td"},"timestamp_expr - interval_expr")),(0,l.kt)("td",{parentName:"tr",align:null},"Subtract an amount of time from a timestamp.")))),(0,l.kt)("h2",{id:"concatenation-operator"},"Concatenation operator"),(0,l.kt)("p",null,"Also see the ",(0,l.kt)("a",{parentName:"p",href:"/docs/28.0.1/querying/sql-scalar#string-functions"},"CONCAT function"),"."),(0,l.kt)("table",null,(0,l.kt)("thead",{parentName:"table"},(0,l.kt)("tr",{parentName:"thead"},(0,l.kt)("th",{parentName:"tr",align:null},"Operator"),(0,l.kt)("th",{parentName:"tr",align:null},"Description"))),(0,l.kt)("tbody",{parentName:"table"},(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("code",null,"x ","|","|"," y")),(0,l.kt)("td",{parentName:"tr",align:null},"Concatenate strings ",(0,l.kt)("inlineCode",{parentName:"td"},"x")," and ",(0,l.kt)("inlineCode",{parentName:"td"},"y"),".")))),(0,l.kt)("h2",{id:"comparison-operators"},"Comparison operators"),(0,l.kt)("table",null,(0,l.kt)("thead",{parentName:"table"},(0,l.kt)("tr",{parentName:"thead"},(0,l.kt)("th",{parentName:"tr",align:null},"Operator"),(0,l.kt)("th",{parentName:"tr",align:null},"Description"))),(0,l.kt)("tbody",{parentName:"table"},(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("inlineCode",{parentName:"td"},"x = y")),(0,l.kt)("td",{parentName:"tr",align:null},"Equal to")),(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("inlineCode",{parentName:"td"},"x IS NOT DISTINCT FROM y")),(0,l.kt)("td",{parentName:"tr",align:null},"Equal to, considering ",(0,l.kt)("inlineCode",{parentName:"td"},"NULL")," as a value. Never returns ",(0,l.kt)("inlineCode",{parentName:"td"},"NULL"),".")),(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("inlineCode",{parentName:"td"},"x <> y")),(0,l.kt)("td",{parentName:"tr",align:null},"Not equal to")),(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("inlineCode",{parentName:"td"},"x IS DISTINCT FROM y")),(0,l.kt)("td",{parentName:"tr",align:null},"Not equal to, considering ",(0,l.kt)("inlineCode",{parentName:"td"},"NULL")," as a value. Never returns ",(0,l.kt)("inlineCode",{parentName:"td"},"NULL"),".")),(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("inlineCode",{parentName:"td"},"x > y")),(0,l.kt)("td",{parentName:"tr",align:null},"Greater than")),(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("inlineCode",{parentName:"td"},"x >= y")),(0,l.kt)("td",{parentName:"tr",align:null},"Greater than or equal to")),(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("inlineCode",{parentName:"td"},"x < y")),(0,l.kt)("td",{parentName:"tr",align:null},"Less than")),(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("inlineCode",{parentName:"td"},"x <= y")),(0,l.kt)("td",{parentName:"tr",align:null},"Less than or equal to")))),(0,l.kt)("h2",{id:"logical-operators"},"Logical operators"),(0,l.kt)("table",null,(0,l.kt)("thead",{parentName:"table"},(0,l.kt)("tr",{parentName:"thead"},(0,l.kt)("th",{parentName:"tr",align:null},"Operator"),(0,l.kt)("th",{parentName:"tr",align:null},"Description"))),(0,l.kt)("tbody",{parentName:"table"},(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("inlineCode",{parentName:"td"},"x AND y")),(0,l.kt)("td",{parentName:"tr",align:null},"Boolean AND")),(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("inlineCode",{parentName:"td"},"x OR y")),(0,l.kt)("td",{parentName:"tr",align:null},"Boolean OR")),(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("inlineCode",{parentName:"td"},"NOT x")),(0,l.kt)("td",{parentName:"tr",align:null},"Boolean NOT")),(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("inlineCode",{parentName:"td"},"x IS NULL")),(0,l.kt)("td",{parentName:"tr",align:null},"True if ",(0,l.kt)("em",{parentName:"td"},"x")," is NULL or empty string")),(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("inlineCode",{parentName:"td"},"x IS NOT NULL")),(0,l.kt)("td",{parentName:"tr",align:null},"True if ",(0,l.kt)("em",{parentName:"td"},"x")," is neither NULL nor empty string")),(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("inlineCode",{parentName:"td"},"x IS TRUE")),(0,l.kt)("td",{parentName:"tr",align:null},"True if ",(0,l.kt)("em",{parentName:"td"},"x")," is true")),(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("inlineCode",{parentName:"td"},"x IS NOT TRUE")),(0,l.kt)("td",{parentName:"tr",align:null},"True if ",(0,l.kt)("em",{parentName:"td"},"x")," is not true")),(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("inlineCode",{parentName:"td"},"x IS FALSE")),(0,l.kt)("td",{parentName:"tr",align:null},"True if ",(0,l.kt)("em",{parentName:"td"},"x")," is false")),(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("inlineCode",{parentName:"td"},"x IS NOT FALSE")),(0,l.kt)("td",{parentName:"tr",align:null},"True if ",(0,l.kt)("em",{parentName:"td"},"x")," is not false")),(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("inlineCode",{parentName:"td"},"x BETWEEN y AND z")),(0,l.kt)("td",{parentName:"tr",align:null},"Equivalent to ",(0,l.kt)("inlineCode",{parentName:"td"},"x >= y AND x <= z"))),(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("inlineCode",{parentName:"td"},"x NOT BETWEEN y AND z")),(0,l.kt)("td",{parentName:"tr",align:null},"Equivalent to ",(0,l.kt)("inlineCode",{parentName:"td"},"x < y OR x > z"))),(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("inlineCode",{parentName:"td"},"x LIKE pattern [ESCAPE esc]")),(0,l.kt)("td",{parentName:"tr",align:null},"True if ",(0,l.kt)("em",{parentName:"td"},"x")," matches a SQL LIKE pattern (with an optional escape)")),(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("inlineCode",{parentName:"td"},"x NOT LIKE pattern [ESCAPE esc]")),(0,l.kt)("td",{parentName:"tr",align:null},"True if ",(0,l.kt)("em",{parentName:"td"},"x")," does not match a SQL LIKE pattern (with an optional escape)")),(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("inlineCode",{parentName:"td"},"x IN (values)")),(0,l.kt)("td",{parentName:"tr",align:null},"True if ",(0,l.kt)("em",{parentName:"td"},"x")," is one of the listed values")),(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("inlineCode",{parentName:"td"},"x NOT IN (values)")),(0,l.kt)("td",{parentName:"tr",align:null},"True if ",(0,l.kt)("em",{parentName:"td"},"x")," is not one of the listed values")),(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("inlineCode",{parentName:"td"},"x IN (subquery)")),(0,l.kt)("td",{parentName:"tr",align:null},"True if ",(0,l.kt)("em",{parentName:"td"},"x")," is returned by the subquery. This will be translated into a join; see ",(0,l.kt)("a",{parentName:"td",href:"/docs/28.0.1/querying/sql-translation"},"Query translation")," for details.")),(0,l.kt)("tr",{parentName:"tbody"},(0,l.kt)("td",{parentName:"tr",align:null},(0,l.kt)("inlineCode",{parentName:"td"},"x NOT IN (subquery)")),(0,l.kt)("td",{parentName:"tr",align:null},"True if ",(0,l.kt)("em",{parentName:"td"},"x")," is not returned by the subquery. This will be translated into a join; see ",(0,l.kt)("a",{parentName:"td",href:"/docs/28.0.1/querying/sql-translation"},"Query translation")," for details.")))))}k.isMDXComponent=!0}}]);