| "use strict";(self.webpackChunk=self.webpackChunk||[]).push([[4609],{15680:(e,t,a)=>{a.d(t,{xA:()=>d,yg:()=>s});var n=a(96540);function r(e,t,a){return t in e?Object.defineProperty(e,t,{value:a,enumerable:!0,configurable:!0,writable:!0}):e[t]=a,e}function l(e,t){var a=Object.keys(e);if(Object.getOwnPropertySymbols){var n=Object.getOwnPropertySymbols(e);t&&(n=n.filter((function(t){return Object.getOwnPropertyDescriptor(e,t).enumerable}))),a.push.apply(a,n)}return a}function i(e){for(var t=1;t<arguments.length;t++){var a=null!=arguments[t]?arguments[t]:{};t%2?l(Object(a),!0).forEach((function(t){r(e,t,a[t])})):Object.getOwnPropertyDescriptors?Object.defineProperties(e,Object.getOwnPropertyDescriptors(a)):l(Object(a)).forEach((function(t){Object.defineProperty(e,t,Object.getOwnPropertyDescriptor(a,t))}))}return e}function o(e,t){if(null==e)return{};var a,n,r=function(e,t){if(null==e)return{};var a,n,r={},l=Object.keys(e);for(n=0;n<l.length;n++)a=l[n],t.indexOf(a)>=0||(r[a]=e[a]);return r}(e,t);if(Object.getOwnPropertySymbols){var l=Object.getOwnPropertySymbols(e);for(n=0;n<l.length;n++)a=l[n],t.indexOf(a)>=0||Object.prototype.propertyIsEnumerable.call(e,a)&&(r[a]=e[a])}return r}var p=n.createContext({}),g=function(e){var t=n.useContext(p),a=t;return e&&(a="function"==typeof e?e(t):i(i({},t),e)),a},d=function(e){var t=g(e.components);return n.createElement(p.Provider,{value:t},e.children)},y="mdxType",m={inlineCode:"code",wrapper:function(e){var t=e.children;return n.createElement(n.Fragment,{},t)}},u=n.forwardRef((function(e,t){var a=e.components,r=e.mdxType,l=e.originalType,p=e.parentName,d=o(e,["components","mdxType","originalType","parentName"]),y=g(a),u=r,s=y["".concat(p,".").concat(u)]||y[u]||m[u]||l;return a?n.createElement(s,i(i({ref:t},d),{},{components:a})):n.createElement(s,i({ref:t},d))}));function s(e,t){var a=arguments,r=t&&t.mdxType;if("string"==typeof e||r){var l=a.length,i=new Array(l);i[0]=u;var o={};for(var p in t)hasOwnProperty.call(t,p)&&(o[p]=t[p]);o.originalType=e,o[y]="string"==typeof e?e:r,i[1]=o;for(var g=2;g<l;g++)i[g]=a[g];return n.createElement.apply(null,i)}return n.createElement.apply(null,a)}u.displayName="MDXCreateElement"},21321:(e,t,a)=>{a.r(t),a.d(t,{assets:()=>d,contentTitle:()=>p,default:()=>s,frontMatter:()=>o,metadata:()=>g,toc:()=>y});var n=a(58168),r=a(98587),l=(a(96540),a(15680)),i=["components"],o={id:"sql-operators",title:"Druid SQL Operators",sidebar_label:"Operators"},p=void 0,g={unversionedId:"querying/sql-operators",id:"querying/sql-operators",title:"Druid SQL Operators",description:"\x3c!--",source:"@site/docs/latest/querying/sql-operators.md",sourceDirName:"querying",slug:"/querying/sql-operators",permalink:"/docs/latest/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/latest/querying/sql-data-types"},next:{title:"Scalar functions",permalink:"/docs/latest/querying/sql-scalar"}},d={},y=[{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},{value:"Other operators",id:"other-operators",level:2}],m={toc:y},u="wrapper";function s(e){var t=e.components,a=(0,r.A)(e,i);return(0,l.yg)(u,(0,n.A)({},m,a,{components:t,mdxType:"MDXLayout"}),(0,l.yg)("admonition",{type:"info"},(0,l.yg)("p",{parentName:"admonition"}," Apache Druid supports two query languages: Druid SQL and ",(0,l.yg)("a",{parentName:"p",href:"/docs/latest/querying/"},"native queries"),".\nThis document describes the SQL language.")),(0,l.yg)("p",null,"Operators in ",(0,l.yg)("a",{parentName:"p",href:"/docs/latest/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.yg)("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.yg)("a",{parentName:"p",href:"https://docs.oracle.com/javase/specs/jls/se8/html/jls-5.html"},"Java")," and ",(0,l.yg)("a",{parentName:"p",href:"https://en.wikipedia.org/wiki/IEEE_754"},"the IEEE standard"),"."),(0,l.yg)("p",null,"Keep the following guidelines in mind to help you manage precision issues:"),(0,l.yg)("ul",null,(0,l.yg)("li",{parentName:"ul"},"Long values can store up to 2^63 accurately with an additional bit used for the sign."),(0,l.yg)("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.yg)("h2",{id:"arithmetic-operators"},"Arithmetic operators"),(0,l.yg)("table",null,(0,l.yg)("thead",{parentName:"table"},(0,l.yg)("tr",{parentName:"thead"},(0,l.yg)("th",{parentName:"tr",align:null},"Operator"),(0,l.yg)("th",{parentName:"tr",align:null},"Description"))),(0,l.yg)("tbody",{parentName:"table"},(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"x + y")),(0,l.yg)("td",{parentName:"tr",align:null},"Add")),(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"x - y")),(0,l.yg)("td",{parentName:"tr",align:null},"Subtract")),(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"x * y")),(0,l.yg)("td",{parentName:"tr",align:null},"Multiply")),(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"x / y")),(0,l.yg)("td",{parentName:"tr",align:null},"Divide")))),(0,l.yg)("h2",{id:"datetime-arithmetic-operators"},"Datetime arithmetic operators"),(0,l.yg)("p",null,"For the datetime arithmetic operators, ",(0,l.yg)("inlineCode",{parentName:"p"},"interval_expr")," can include interval literals like ",(0,l.yg)("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.yg)("a",{parentName:"p",href:"/docs/latest/querying/sql-scalar#date-and-time-functions"},(0,l.yg)("inlineCode",{parentName:"a"},"TIME_SHIFT")," function"),".\nAlso see ",(0,l.yg)("a",{parentName:"p",href:"/docs/latest/querying/sql-scalar#date-and-time-functions"},(0,l.yg)("inlineCode",{parentName:"a"},"TIMESTAMPADD"))," for datetime arithmetic."),(0,l.yg)("table",null,(0,l.yg)("thead",{parentName:"table"},(0,l.yg)("tr",{parentName:"thead"},(0,l.yg)("th",{parentName:"tr",align:null},"Operator"),(0,l.yg)("th",{parentName:"tr",align:null},"Description"))),(0,l.yg)("tbody",{parentName:"table"},(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"timestamp_expr + interval_expr")),(0,l.yg)("td",{parentName:"tr",align:null},"Add an amount of time to a timestamp.")),(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"timestamp_expr - interval_expr")),(0,l.yg)("td",{parentName:"tr",align:null},"Subtract an amount of time from a timestamp.")))),(0,l.yg)("h2",{id:"concatenation-operator"},"Concatenation operator"),(0,l.yg)("p",null,"Also see the ",(0,l.yg)("a",{parentName:"p",href:"/docs/latest/querying/sql-scalar#string-functions"},"CONCAT function"),"."),(0,l.yg)("table",null,(0,l.yg)("thead",{parentName:"table"},(0,l.yg)("tr",{parentName:"thead"},(0,l.yg)("th",{parentName:"tr",align:null},"Operator"),(0,l.yg)("th",{parentName:"tr",align:null},"Description"))),(0,l.yg)("tbody",{parentName:"table"},(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("code",null,"x ","|","|"," y")),(0,l.yg)("td",{parentName:"tr",align:null},"Concatenate strings ",(0,l.yg)("inlineCode",{parentName:"td"},"x")," and ",(0,l.yg)("inlineCode",{parentName:"td"},"y"),".")))),(0,l.yg)("h2",{id:"comparison-operators"},"Comparison operators"),(0,l.yg)("table",null,(0,l.yg)("thead",{parentName:"table"},(0,l.yg)("tr",{parentName:"thead"},(0,l.yg)("th",{parentName:"tr",align:null},"Operator"),(0,l.yg)("th",{parentName:"tr",align:null},"Description"))),(0,l.yg)("tbody",{parentName:"table"},(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"x = y")),(0,l.yg)("td",{parentName:"tr",align:null},"Equal to")),(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"x IS NOT DISTINCT FROM y")),(0,l.yg)("td",{parentName:"tr",align:null},"Equal to, considering ",(0,l.yg)("inlineCode",{parentName:"td"},"NULL")," as a value. Never returns ",(0,l.yg)("inlineCode",{parentName:"td"},"NULL"),".")),(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"x <> y")),(0,l.yg)("td",{parentName:"tr",align:null},"Not equal to")),(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"x IS DISTINCT FROM y")),(0,l.yg)("td",{parentName:"tr",align:null},"Not equal to, considering ",(0,l.yg)("inlineCode",{parentName:"td"},"NULL")," as a value. Never returns ",(0,l.yg)("inlineCode",{parentName:"td"},"NULL"),".")),(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"x > y")),(0,l.yg)("td",{parentName:"tr",align:null},"Greater than")),(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"x >= y")),(0,l.yg)("td",{parentName:"tr",align:null},"Greater than or equal to")),(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"x < y")),(0,l.yg)("td",{parentName:"tr",align:null},"Less than")),(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"x <= y")),(0,l.yg)("td",{parentName:"tr",align:null},"Less than or equal to")))),(0,l.yg)("h2",{id:"logical-operators"},"Logical operators"),(0,l.yg)("table",null,(0,l.yg)("thead",{parentName:"table"},(0,l.yg)("tr",{parentName:"thead"},(0,l.yg)("th",{parentName:"tr",align:null},"Operator"),(0,l.yg)("th",{parentName:"tr",align:null},"Description"))),(0,l.yg)("tbody",{parentName:"table"},(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"x AND y")),(0,l.yg)("td",{parentName:"tr",align:null},"Boolean AND")),(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"x OR y")),(0,l.yg)("td",{parentName:"tr",align:null},"Boolean OR")),(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"NOT x")),(0,l.yg)("td",{parentName:"tr",align:null},"Boolean NOT")),(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"x IS NULL")),(0,l.yg)("td",{parentName:"tr",align:null},"True if ",(0,l.yg)("em",{parentName:"td"},"x")," is NULL or empty string")),(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"x IS NOT NULL")),(0,l.yg)("td",{parentName:"tr",align:null},"True if ",(0,l.yg)("em",{parentName:"td"},"x")," is neither NULL nor empty string")),(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"x IS TRUE")),(0,l.yg)("td",{parentName:"tr",align:null},"True if ",(0,l.yg)("em",{parentName:"td"},"x")," is true")),(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"x IS NOT TRUE")),(0,l.yg)("td",{parentName:"tr",align:null},"True if ",(0,l.yg)("em",{parentName:"td"},"x")," is not true")),(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"x IS FALSE")),(0,l.yg)("td",{parentName:"tr",align:null},"True if ",(0,l.yg)("em",{parentName:"td"},"x")," is false")),(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"x IS NOT FALSE")),(0,l.yg)("td",{parentName:"tr",align:null},"True if ",(0,l.yg)("em",{parentName:"td"},"x")," is not false")),(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"x BETWEEN y AND z")),(0,l.yg)("td",{parentName:"tr",align:null},"Equivalent to ",(0,l.yg)("inlineCode",{parentName:"td"},"x >= y AND x <= z"))),(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"x NOT BETWEEN y AND z")),(0,l.yg)("td",{parentName:"tr",align:null},"Equivalent to ",(0,l.yg)("inlineCode",{parentName:"td"},"x < y OR x > z"))),(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"x LIKE pattern [ESCAPE esc]")),(0,l.yg)("td",{parentName:"tr",align:null},"True if ",(0,l.yg)("em",{parentName:"td"},"x")," matches a SQL LIKE pattern (with an optional escape)")),(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"x NOT LIKE pattern [ESCAPE esc]")),(0,l.yg)("td",{parentName:"tr",align:null},"True if ",(0,l.yg)("em",{parentName:"td"},"x")," does not match a SQL LIKE pattern (with an optional escape)")),(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"x IN (values)")),(0,l.yg)("td",{parentName:"tr",align:null},"True if ",(0,l.yg)("em",{parentName:"td"},"x")," is one of the listed values")),(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"x NOT IN (values)")),(0,l.yg)("td",{parentName:"tr",align:null},"True if ",(0,l.yg)("em",{parentName:"td"},"x")," is not one of the listed values")),(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"x IN (subquery)")),(0,l.yg)("td",{parentName:"tr",align:null},"True if ",(0,l.yg)("em",{parentName:"td"},"x")," is returned by the subquery. This will be translated into a join; see ",(0,l.yg)("a",{parentName:"td",href:"/docs/latest/querying/sql-translation"},"Query translation")," for details.")),(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"x NOT IN (subquery)")),(0,l.yg)("td",{parentName:"tr",align:null},"True if ",(0,l.yg)("em",{parentName:"td"},"x")," is not returned by the subquery. This will be translated into a join; see ",(0,l.yg)("a",{parentName:"td",href:"/docs/latest/querying/sql-translation"},"Query translation")," for details.")))),(0,l.yg)("h2",{id:"other-operators"},"Other operators"),(0,l.yg)("table",null,(0,l.yg)("thead",{parentName:"table"},(0,l.yg)("tr",{parentName:"thead"},(0,l.yg)("th",{parentName:"tr",align:null},"Operator"),(0,l.yg)("th",{parentName:"tr",align:null},"Description"))),(0,l.yg)("tbody",{parentName:"table"},(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"PIVOT (aggregation_function(column_to_aggregate) FOR column_with_values_to_pivot IN (pivoted_column1 [, pivoted_column2 ...]))")),(0,l.yg)("td",{parentName:"tr",align:null},"Carries out an aggregation and transforms rows into columns in the output.")),(0,l.yg)("tr",{parentName:"tbody"},(0,l.yg)("td",{parentName:"tr",align:null},(0,l.yg)("inlineCode",{parentName:"td"},"UNPIVOT (values_column FOR names_column IN (unpivoted_column1 [, unpivoted_column2 ... ]))")),(0,l.yg)("td",{parentName:"tr",align:null},"Transforms existing column values into rows.")))))}s.isMDXComponent=!0}}]); |