| <!doctype html><html lang=en class=no-js><head><meta charset=utf-8><meta http-equiv=x-ua-compatible content="IE=edge"><meta name=viewport content="width=device-width,initial-scale=1"><title>Beam ZetaSQL operators</title><meta name=description content="Apache Beam is an open source, unified model and set of language-specific SDKs for defining and executing data processing workflows, and also data ingestion and integration flows, supporting Enterprise Integration Patterns (EIPs) and Domain Specific Languages (DSLs). Dataflow pipelines simplify the mechanics of large-scale batch and streaming data processing and can run on a number of runtimes like Apache Flink, Apache Spark, and Google Cloud Dataflow (a cloud service). Beam also brings DSL in different languages, allowing users to easily implement their data integration processes."><link href="https://fonts.googleapis.com/css?family=Roboto:100,300,400,500,700" rel=stylesheet><link rel=preload href=/scss/main.min.408fddfe3e8a45f87a5a8c9a839d77db667c1c534e5e5cd0d957ffc3dd6c14cf.css as=style><link href=/scss/main.min.408fddfe3e8a45f87a5a8c9a839d77db667c1c534e5e5cd0d957ffc3dd6c14cf.css rel=stylesheet integrity><script src=https://code.jquery.com/jquery-2.2.4.min.js></script><style>.body__contained img{max-width:100%}</style><script type=text/javascript src=/js/bootstrap.min.2979f9a6e32fc42c3e7406339ee9fe76b31d1b52059776a02b4a7fa6a4fd280a.js defer></script> |
| <script type=text/javascript src=/js/language-switch-v2.min.121952b7980b920320ab229551857669209945e39b05ba2b433a565385ca44c6.js defer></script> |
| <script type=text/javascript src=/js/fix-menu.min.039174b67107465f2090a493f91e126f7aa797f29420f9edab8a54d9dd4b3d2d.js defer></script> |
| <script type=text/javascript src=/js/section-nav.min.1405fd5e70fab5f6c54037c269b1d137487d8f3d1b3009032525f6db3fbce991.js defer></script> |
| <script type=text/javascript src=/js/page-nav.min.af231204c9c52c5089d53a4c02739eacbb7f939e3be1c6ffcc212e0ac4dbf879.js defer></script> |
| <script type=text/javascript src=/js/expandable-list.min.75a4526624a3b8898fe7fb9e3428c205b581f8b38c7926922467aef17eac69f2.js defer></script> |
| <script type=text/javascript src=/js/copy-to-clipboard.min.364c06423d7e8993fc42bb4abc38c03195bc8386db26d18774ce775d08d5b18d.js defer></script> |
| <script type=text/javascript src=/js/calendar.min.336664054fa0f52b08bbd4e3c59b5cb6d63dcfb2b4d602839746516b0817446b.js defer></script> |
| <script type=text/javascript src=/js/fix-playground-nested-scroll.min.0283f1037cb1b9d5074c6eaf041292b524a8148a7cdb803d5ccd6d1fc4eb3253.js defer></script> |
| <script type=text/javascript src=/js/anchor-content-jump-fix.min.22d3240f81632e4c11179b9d2aaf37a40da9414333c43aa97344e8b21a7df0e4.js defer></script> |
| <link rel=alternate type=application/rss+xml title="Apache Beam" href=/feed.xml><link rel=canonical href=/documentation/dsls/sql/zetasql/operators/ data-proofer-ignore><link rel="shortcut icon" type=image/x-icon href=/images/favicon.ico><link rel=stylesheet href=https://use.fontawesome.com/releases/v5.4.1/css/all.css integrity=sha384-5sAR7xN1Nv6T6+dT2mhtzEpVJvfS3NScPQTrOxhwjIuvcA67KV2R5Jz6kr4abQsz crossorigin=anonymous><link rel=stylesheet href=https://unpkg.com/swiper@8/swiper-bundle.min.css><script async src=https://platform.twitter.com/widgets.js></script> |
| <script>(function(e,t,n,s,o,i,a){e.GoogleAnalyticsObject=o,e[o]=e[o]||function(){(e[o].q=e[o].q||[]).push(arguments)},e[o].l=1*new Date,i=t.createElement(n),a=t.getElementsByTagName(n)[0],i.async=1,i.src=s,a.parentNode.insertBefore(i,a)})(window,document,"script","//www.google-analytics.com/analytics.js","ga"),ga("create","UA-73650088-1","auto"),ga("send","pageview")</script><script>(function(e,t,n,s,o,i){e.hj=e.hj||function(){(e.hj.q=e.hj.q||[]).push(arguments)},e._hjSettings={hjid:2182187,hjsv:6},o=t.getElementsByTagName("head")[0],i=t.createElement("script"),i.async=1,i.src=n+e._hjSettings.hjid+s+e._hjSettings.hjsv,o.appendChild(i)})(window,document,"https://static.hotjar.com/c/hotjar-",".js?sv=")</script></head><body class=body data-spy=scroll data-target=.page-nav data-offset=0><nav class="navigation-bar-mobile header navbar navbar-fixed-top"><div class=navbar-header><a href=/ class=navbar-brand><img alt=Brand style=height:46px;width:43px src=/images/beam_logo_navbar_mobile.png></a> |
| <a class=navbar-link href=/get-started/>Get Started</a> |
| <a class=navbar-link href=/documentation/>Documentation</a> |
| <button type=button class="navbar-toggle menu-open" aria-expanded=false aria-controls=navbar onclick=openMenu()> |
| <span class=sr-only>Toggle navigation</span> |
| <span class=icon-bar></span> |
| <span class=icon-bar></span> |
| <span class=icon-bar></span></button></div><div class="navbar-mask closed"></div><div id=navbar class="navbar-container closed"><button type=button class=navbar-toggle aria-expanded=false aria-controls=navbar id=closeMenu> |
| <span class=sr-only>Toggle navigation</span> |
| <span class=icon-bar></span> |
| <span class=icon-bar></span> |
| <span class=icon-bar></span></button><ul class="nav navbar-nav"><li><div class=searchBar-mobile><script>(function(){var t,n="012923275103528129024:4emlchv9wzi",e=document.createElement("script");e.type="text/javascript",e.async=!0,e.src="https://cse.google.com/cse.js?cx="+n,t=document.getElementsByTagName("script")[0],t.parentNode.insertBefore(e,t)})()</script><gcse:search></gcse:search></div></li><li><a class=navbar-link href=/about>About</a></li><li><a class=navbar-link href=/get-started/>Get Started</a></li><li><span class=navbar-link>Documentation</span><ul><li><a href=/documentation/>General</a></li><li><a href=/documentation/sdks/java/>Languages</a></li><li><a href=/documentation/runners/capability-matrix/>Runners</a></li><li><a href=/documentation/io/connectors/>I/O Connectors</a></li></ul></li><li><a class=navbar-link href=/roadmap/>Roadmap</a></li><li><a class=navbar-link href=/community/>Community</a></li><li><a class=navbar-link href=/contribute/>Contribute</a></li><li><a class=navbar-link href=/blog/>Blog</a></li><li><a class=navbar-link href=/case-studies/>Case Studies</a></li></ul><ul class="nav navbar-nav navbar-right"><li><a href=https://github.com/apache/beam/edit/master/website/www/site/content/en/documentation/dsls/sql/zetasql/operators.md data-proofer-ignore><svg xmlns="http://www.w3.org/2000/svg" width="25" height="24" fill="none" viewBox="0 0 25 24"><path stroke="#ff6d00" stroke-linecap="round" stroke-linejoin="round" stroke-width="2.75" d="M4.543 20h4l10.5-10.5c.53-.53.828-1.25.828-2s-.298-1.47-.828-2-1.25-.828-2-.828-1.47.298-2 .828L4.543 16v4zm9.5-13.5 4 4"/></svg></a></li><li class=dropdown><a href=# class=dropdown-toggle id=apache-dropdown data-toggle=dropdown role=button aria-haspopup=true aria-expanded=false><img src=https://www.apache.org/foundation/press/kit/feather_small.png alt="Apache Logo" style=height:20px> |
| Apache |
| <span class=arrow-icon><svg xmlns="http://www.w3.org/2000/svg" width="20" height="20" fill="none" viewBox="0 0 20 20"><circle cx="10" cy="10" r="10" fill="#ff6d00"/><path stroke="#fff" stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M8.535 5.28l4.573 4.818-4.573 4.403"/></svg></span></a><ul class="dropdown-menu dropdown-menu-right"><li><a target=_blank href=https://www.apache.org/>ASF Homepage</a></li><li><a target=_blank href=https://www.apache.org/licenses/>License</a></li><li><a target=_blank href=https://www.apache.org/security/>Security</a></li><li><a target=_blank href=https://www.apache.org/foundation/thanks.html>Thanks</a></li><li><a target=_blank href=https://www.apache.org/foundation/sponsorship.html>Sponsorship</a></li><li><a target=_blank href=https://www.apache.org/foundation/policies/conduct>Code of Conduct</a></li></ul></li></ul></div></nav><nav class=navigation-bar-desktop><a href=/ class=navbar-logo><img src=/images/beam_logo_navbar.png alt="Beam Logo"></a><div class=navbar-bar-left><div class=navbar-links><a class=navbar-link href=/about>About</a> |
| <a class=navbar-link href=/get-started/>Get Started</a><li class="dropdown navbar-dropdown navbar-dropdown-documentation"><a href=# class="dropdown-toggle navbar-link" role=button aria-haspopup=true aria-expanded=false>Documentation |
| <span><svg xmlns="http://www.w3.org/2000/svg" width="12" height="11" fill="none" viewBox="0 0 12 11"><path stroke="#ff6d00" stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M10.666 4.535 5.847 9.108 1.444 4.535"/></svg></span></a><ul class=dropdown-menu><li><a class=navbar-dropdown-menu-link href=/documentation/>General</a></li><li><a class=navbar-dropdown-menu-link href=/documentation/sdks/java/>Languages</a></li><li><a class=navbar-dropdown-menu-link href=/documentation/runners/capability-matrix/>Runners</a></li><li><a class=navbar-dropdown-menu-link href=/documentation/io/connectors/>I/O Connectors</a></li></ul></li><a class=navbar-link href=/roadmap/>Roadmap</a> |
| <a class=navbar-link href=/community/>Community</a> |
| <a class=navbar-link href=/contribute/>Contribute</a> |
| <a class=navbar-link href=/blog/>Blog</a> |
| <a class=navbar-link href=/case-studies/>Case Studies</a></div><div id=iconsBar><a type=button onclick=showSearch()><svg xmlns="http://www.w3.org/2000/svg" width="25" height="24" fill="none" viewBox="0 0 25 24"><path stroke="#ff6d00" stroke-linecap="round" stroke-linejoin="round" stroke-width="2.75" d="M10.191 17c3.866.0 7-3.134 7-7s-3.134-7-7-7-7 3.134-7 7 3.134 7 7 7zm11 4-6-6"/></svg></a><a target=_blank href=https://github.com/apache/beam/edit/master/website/www/site/content/en/documentation/dsls/sql/zetasql/operators.md data-proofer-ignore><svg xmlns="http://www.w3.org/2000/svg" width="25" height="24" fill="none" viewBox="0 0 25 24"><path stroke="#ff6d00" stroke-linecap="round" stroke-linejoin="round" stroke-width="2.75" d="M4.543 20h4l10.5-10.5c.53-.53.828-1.25.828-2s-.298-1.47-.828-2-1.25-.828-2-.828-1.47.298-2 .828L4.543 16v4zm9.5-13.5 4 4"/></svg></a><li class="dropdown navbar-dropdown navbar-dropdown-apache"><a href=# class=dropdown-toggle role=button aria-haspopup=true aria-expanded=false><img src=https://www.apache.org/foundation/press/kit/feather_small.png alt="Apache Logo" style=height:20px> |
| Apache |
| <span class=arrow-icon><svg xmlns="http://www.w3.org/2000/svg" width="20" height="20" fill="none" viewBox="0 0 20 20"><circle cx="10" cy="10" r="10" fill="#ff6d00"/><path stroke="#fff" stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M8.535 5.28l4.573 4.818-4.573 4.403"/></svg></span></a><ul class=dropdown-menu><li><a class=navbar-dropdown-menu-link target=_blank href=https://www.apache.org/>ASF Homepage</a></li><li><a class=navbar-dropdown-menu-link target=_blank href=https://www.apache.org/licenses/>License</a></li><li><a class=navbar-dropdown-menu-link target=_blank href=https://www.apache.org/security/>Security</a></li><li><a class=navbar-dropdown-menu-link target=_blank href=https://www.apache.org/foundation/thanks.html>Thanks</a></li><li><a class=navbar-dropdown-menu-link target=_blank href=https://www.apache.org/foundation/sponsorship.html>Sponsorship</a></li><li><a class=navbar-dropdown-menu-link target=_blank href=https://www.apache.org/foundation/policies/conduct>Code of Conduct</a></li></ul></li></div><div class="searchBar disappear"><script>(function(){var t,n="012923275103528129024:4emlchv9wzi",e=document.createElement("script");e.type="text/javascript",e.async=!0,e.src="https://cse.google.com/cse.js?cx="+n,t=document.getElementsByTagName("script")[0],t.parentNode.insertBefore(e,t)})()</script><gcse:search></gcse:search> |
| <a type=button onclick=endSearch()><svg xmlns="http://www.w3.org/2000/svg" width="25" height="25" fill="none" viewBox="0 0 25 25"><path stroke="#ff6d00" stroke-linecap="round" stroke-linejoin="round" stroke-width="2.75" d="M21.122 20.827 4.727 4.432M21.122 4.43 4.727 20.827"/></svg></a></div></div></nav><div class=header-push></div><div class="top-banners swiper"><div class=swiper-wrapper><div class=swiper-slide><a href=https://tour.beam.apache.org><img class=banner-img-desktop src=/images/banners/tour-of-beam/tour-of-beam-desktop.png alt="Start Tour of Beam"> |
| <img class=banner-img-mobile src=/images/banners/tour-of-beam/tour-of-beam-mobile.png alt="Start Tour of Beam"></a></div><div class=swiper-slide><a href=https://beam.apache.org/documentation/ml/overview/><img class=banner-img-desktop src=/images/banners/machine-learning/machine-learning-desktop.jpg alt="Machine Learning"> |
| <img class=banner-img-mobile src=/images/banners/machine-learning/machine-learning-mobile.jpg alt="Machine Learning"></a></div></div><div class=swiper-pagination></div><div class=swiper-button-prev></div><div class=swiper-button-next></div></div><script src=/js/swiper-bundle.min.min.e0e8f81b0b15728d35ff73c07f42ddbb17a108d6f23df4953cb3e60df7ade675.js></script> |
| <script src=/js/sliders/top-banners.min.afa7d0a19acf7a3b28ca369490b3d401a619562a2a4c9612577be2f66a4b9855.js></script> |
| <script>function showSearch(){addPlaceholder();var e,t=document.querySelector(".searchBar");t.classList.remove("disappear"),e=document.querySelector("#iconsBar"),e.classList.add("disappear")}function addPlaceholder(){$("input:text").attr("placeholder","What are you looking for?")}function endSearch(){var e,t=document.querySelector(".searchBar");t.classList.add("disappear"),e=document.querySelector("#iconsBar"),e.classList.remove("disappear")}function blockScroll(){$("body").toggleClass("fixedPosition")}function openMenu(){addPlaceholder(),blockScroll()}</script><div class="clearfix container-main-content"><div class="section-nav closed" data-offset-top=90 data-offset-bottom=500><span class="section-nav-back glyphicon glyphicon-menu-left"></span><nav><ul class=section-nav-list data-section-nav><li><span class=section-nav-list-main-title>Languages</span></li><li><span class=section-nav-list-title>Java</span><ul class=section-nav-list><li><a href=/documentation/sdks/java/>Java SDK overview</a></li><li><a href=https://beam.apache.org/releases/javadoc/2.56.0/ target=_blank>Java SDK API reference <img src=/images/external-link-icon.png width=14 height=14 alt="External link."></a></li><li><a href=/documentation/sdks/java-dependencies/>Java SDK dependencies</a></li><li><a href=/documentation/sdks/java-extensions/>Java SDK extensions</a></li><li><a href=/documentation/sdks/java-thirdparty/>Java 3rd party extensions</a></li><li><a href=/documentation/sdks/java/testing/nexmark/>Nexmark benchmark suite</a></li><li><a href=/documentation/sdks/java/testing/tpcds/>TPC-DS benchmark suite</a></li><li><a href=/documentation/sdks/java-multi-language-pipelines/>Java multi-language pipelines quickstart</a></li></ul></li><li><span class=section-nav-list-title>Python</span><ul class=section-nav-list><li><a href=/documentation/sdks/python/>Python SDK overview</a></li><li><a href=https://beam.apache.org/releases/pydoc/2.56.0/ target=_blank>Python SDK API reference <img src=/images/external-link-icon.png width=14 height=14 alt="External link."></a></li><li><a href=/documentation/sdks/python-dependencies/>Python SDK dependencies</a></li><li><a href=/documentation/sdks/python-streaming/>Python streaming pipelines</a></li><li><a href=/documentation/sdks/python-type-safety/>Ensuring Python type safety</a></li><li><a href=/documentation/sdks/python-machine-learning/>Machine Learning</a></li><li><a href=/documentation/sdks/python-pipeline-dependencies/>Managing pipeline dependencies</a></li><li><a href=/documentation/sdks/python-multi-language-pipelines/>Python multi-language pipelines quickstart</a></li><li><a href=/documentation/sdks/python-unrecoverable-errors/>Python Unrecoverable Errors</a></li></ul></li><li><span class=section-nav-list-title>Go</span><ul class=section-nav-list><li><a href=/documentation/sdks/go/>Go SDK overview</a></li><li><a href=https://pkg.go.dev/github.com/apache/beam/sdks/v2/go/pkg/beam target=_blank>Go SDK API reference <img src=/images/external-link-icon.png width=14 height=14 alt="External link."></a><li><a href=/documentation/sdks/go-dependencies/>Go SDK dependencies</a></li><li><a href=/documentation/sdks/go-cross-compilation/>Cross compilation</a></li></li></ul></li><li><span class=section-nav-list-title>Typescript</span><ul class=section-nav-list><li><a href=/documentation/sdks/typescript/>Typescript SDK overview</a></li><li><a href=https://beam.apache.org/releases/typedoc/current/ target=_blank>Typescript SDK API reference <img src=/images/external-link-icon.png width=14 height=14 alt="External link."></a></li></ul></li><li><span class=section-nav-list-title>Scala</span><ul class=section-nav-list><li><a href=/documentation/sdks/scala/>Scio</a></li><li><a href=https://spotify.github.io/scio/api/com/spotify/scio/index.html target=_blank>Scio SDK API reference <img src=/images/external-link-icon.png width=14 height=14 alt="External link."></a></li></ul></li><li><span class=section-nav-list-title>Yaml</span><ul class=section-nav-list><li><a href=/documentation/sdks/yaml/>Yaml overview</a></li><li><a href=/documentation/sdks/yaml-udf/>Yaml User Defined Functions</a></li><li><a href=/documentation/sdks/yaml-combine/>Yaml Aggregation</a></li><li><a href=/documentation/sdks/yaml-errors/>Error handling</a></li><li><a href=/documentation/sdks/yaml-inline-python/>Inlining Python</a></li><li><a href=https://beam.apache.org/releases/yamldoc/current/ target=_blank>YAML API reference <img src=/images/external-link-icon.png width=14 height=14 alt="External link."></a></ul></li><li><span class=section-nav-list-title>SQL</span><ul class=section-nav-list><li><a href=/documentation/dsls/sql/overview/>Overview</a></li><li><a href=/documentation/dsls/sql/walkthrough/>Walkthrough</a></li><li><a href=/documentation/dsls/sql/shell/>Shell</a></li><li class=section-nav-item--collapsible><span class=section-nav-list-title>Apache Calcite dialect</span><ul class=section-nav-list><li><a href=/documentation/dsls/sql/calcite/overview/>Calcite support overview</a></li><li><a href=/documentation/dsls/sql/calcite/query-syntax/>Query syntax</a></li><li><a href=/documentation/dsls/sql/calcite/lexical/>Lexical structure</a></li><li><a href=/documentation/dsls/sql/calcite/data-types/>Data types</a></li><li><a href=/documentation/dsls/sql/calcite/scalar-functions/>Scalar functions</a></li><li><a href=/documentation/dsls/sql/calcite/aggregate-functions/>Aggregate functions</a></li></ul></li><li class=section-nav-item--collapsible><span class=section-nav-list-title>ZetaSQL dialect</span><ul class=section-nav-list><li><a href=/documentation/dsls/sql/zetasql/overview/>ZetaSQL support overview</a></li><li><a href=/documentation/dsls/sql/zetasql/syntax/>Function call rules</a></li><li><a href=/documentation/dsls/sql/zetasql/conversion-rules/>Conversion rules</a></li><li><a href=/documentation/dsls/sql/zetasql/query-syntax/>Query syntax</a></li><li><a href=/documentation/dsls/sql/zetasql/lexical/>Lexical structure</a></li><li><a href=/documentation/dsls/sql/zetasql/data-types/>Data types</a></li><li><a href=/documentation/dsls/sql/zetasql/operators/>Operators</a></li><li class=section-nav-item--collapsible><span class=section-nav-list-title>Scalar functions</span><ul class=section-nav-list><li><a href=/documentation/dsls/sql/zetasql/string-functions/>String functions</a></li><li><a href=/documentation/dsls/sql/zetasql/math-functions/>Mathematical functions</a></li><li><a href=/documentation/dsls/sql/zetasql/conditional-expressions/>Conditional expressions</a></li></ul></li><li><a href=/documentation/dsls/sql/zetasql/aggregate-functions/>Aggregate functions</a></li></ul></li><li class=section-nav-item--collapsible><span class=section-nav-list-title>Beam SQL extensions</span><ul class=section-nav-list><li><a href=/documentation/dsls/sql/extensions/create-external-table/>CREATE EXTERNAL TABLE</a></li><li><a href=/documentation/dsls/sql/extensions/windowing-and-triggering/>Windowing & triggering</a></li><li><a href=/documentation/dsls/sql/extensions/joins/>Joins</a></li><li><a href=/documentation/dsls/sql/extensions/user-defined-functions/>User-defined functions</a></li><li><a href=/documentation/dsls/sql/extensions/set/>SET pipeline options</a></li></ul></li></ul></li><li><span class=section-nav-list-title>DataFrames</span><ul class=section-nav-list><li><a href=/documentation/dsls/dataframes/overview/>Overview</a></li><li><a href=/documentation/dsls/dataframes/differences-from-pandas/>Differences from pandas</a></li><li><a href=https://github.com/apache/beam/tree/master/sdks/python/apache_beam/examples/dataframe target=_blank>Example pipelines <img src=/images/external-link-icon.png width=14 height=14 alt="External link."></a></li><li><a href=https://beam.apache.org/releases/pydoc/2.56.0/apache_beam.dataframe.html target=_blank>DataFrame API reference <img src=/images/external-link-icon.png width=14 height=14 alt="External link."></a></li></ul></li></ul></nav></div><nav class="page-nav clearfix" data-offset-top=90 data-offset-bottom=500><nav id=TableOfContents><ul><li><a href=#element-access-operators>Element access operators</a></li><li><a href=#arithmetic-operators>Arithmetic operators</a></li><li><a href=#logical-operators>Logical operators</a></li><li><a href=#comparison-operators>Comparison operators</a></li><li><a href=#is-operators>IS operators</a></li></ul></nav></nav><div class="body__contained body__section-nav"><h1 id=beam-zetasql-operators>Beam ZetaSQL operators</h1><p>Operators are represented by special characters or keywords; they do not use |
| function call syntax. An operator manipulates any number of data inputs, also |
| called operands, and returns a result.</p><p>Common conventions:</p><ul><li>Unless otherwise specified, all operators return <code>NULL</code> when one of the |
| operands is <code>NULL</code>.</li></ul><p>The following table lists all supported operators from highest to |
| lowest precedence. Precedence determines the order in which operators will be evaluated within a statement.</p><div class=table-container-wrapper><div class=table-wrapper><table class=table-wrapper--equal-p><thead><tr><th>Order of Precedence</th><th>Operator</th><th>Input Data Types</th><th>Name</th><th>Operator Arity</th></tr></thead><tbody><tr><td>1</td><td>.</td><td><span>STRUCT</span><br></td><td>Member field access operator</td><td>Binary</td></tr><tr><td> </td><td>[ ]</td><td>ARRAY</td><td>Array position. Must be used with OFFSET or ORDINAL&mdash.</td><td>Binary</td></tr><tr><td>2</td><td>-</td><td>All numeric types</td><td>Unary minus</td><td>Unary</td></tr><tr><td>3</td><td>*</td><td>All numeric types</td><td>Multiplication</td><td>Binary</td></tr><tr><td> </td><td>/</td><td>All numeric types</td><td>Division</td><td>Binary</td></tr><tr><td>4</td><td>+</td><td>All numeric types</td><td>Addition</td><td>Binary</td></tr><tr><td> </td><td>-</td><td>All numeric types</td><td>Subtraction</td><td>Binary</td></tr><tr><td>5 (Comparison Operators)</td><td>=</td><td>Any comparable type. See |
| <a href=/documentation/dsls/sql/zetasql/data-types>Data Types</a> for |
| a complete list.</td><td>Equal</td><td>Binary</td></tr><tr><td> </td><td><</td><td>Any comparable type. See |
| <a href=/documentation/dsls/sql/zetasql/data-types>Data Types</a> for |
| a complete list.</td><td>Less than</td><td>Binary</td></tr><tr><td> </td><td>></td><td>Any comparable type. See |
| <a href=/documentation/dsls/sql/zetasql/data-types>Data Types</a> for |
| a complete list.</td><td>Greater than</td><td>Binary</td></tr><tr><td> </td><td><=</td><td>Any comparable type. See |
| <a href=/documentation/dsls/sql/zetasql/data-types>Data Types</a> for |
| a complete list.</td><td>Less than or equal to</td><td>Binary</td></tr><tr><td> </td><td>>=</td><td>Any comparable type. See |
| <a href=/documentation/dsls/sql/zetasql/data-types>Data Types</a> for |
| a complete list.</td><td>Greater than or equal to</td><td>Binary</td></tr><tr><td> </td><td>!=, <></td><td>Any comparable type. See |
| <a href=/documentation/dsls/sql/zetasql/data-types>Data Types</a> for |
| a complete list.</td><td>Not equal</td><td>Binary</td></tr><tr><td> </td><td>[NOT] LIKE</td><td>STRING and byte</td><td>Value does [not] match the pattern specified</td><td>Binary</td></tr><tr><td> </td><td>[NOT] BETWEEN</td><td>Any comparable types. See Data Types for list.</td><td>Value is [not] within the range specified</td><td>Binary</td></tr><tr><td> </td><td>[NOT] IN</td><td>Any comparable types. See Data Types for list.</td><td>Value is [not] in the set of values specified</td><td>Binary</td></tr><tr><td> </td><td>IS [NOT] <code>NULL</code></td><td>All</td><td>Value is [not] <code>NULL</code></td><td>Unary</td></tr><tr><td> </td><td>IS [NOT] TRUE</td><td>BOOL</td><td>Value is [not] TRUE.</td><td>Unary</td></tr><tr><td> </td><td>IS [NOT] FALSE</td><td>BOOL</td><td>Value is [not] FALSE.</td><td>Unary</td></tr><tr><td>6</td><td>NOT</td><td>BOOL</td><td>Logical NOT</td><td>Unary</td></tr><tr><td>7</td><td>AND</td><td>BOOL</td><td>Logical AND</td><td>Binary</td></tr><tr><td>8</td><td>OR</td><td>BOOL</td><td>Logical OR</td><td>Binary</td></tr></tbody></table></div></div><p>Operators with the same precedence are left associative. This means that those |
| operators are grouped together starting from the left and moving right. For |
| example, the expression:</p><p><code>x AND y AND z</code></p><p>is interpreted as</p><p><code>( ( x AND y ) AND z )</code></p><p>The expression:</p><pre tabindex=0><code>x * y / z |
| </code></pre><p>is interpreted as:</p><pre tabindex=0><code>( ( x * y ) / z ) |
| </code></pre><p>All comparison operators have the same priority and are grouped using left |
| associativity. However, comparison operators are not associative. As a result, |
| it is recommended that you use parentheses to improve readability and ensure |
| expressions are resolved as desired. For example:</p><p><code>(x < y) IS FALSE</code></p><p>is recommended over:</p><p><code>x < y IS FALSE</code></p><h2 id=element-access-operators>Element access operators</h2><div class=table-container-wrapper><div class=table-wrapper><table class=table-wrapper--equal-p><thead><tr><th>Operator</th><th>Syntax</th><th>Input Data Types</th><th>Result Data Type</th><th>Description</th></tr></thead><tbody><tr><td>.</td><td>expression.fieldname1...</td><td><span>STRUCT</span><br></td><td>Type T stored in fieldname1</td><td>Dot operator. Can be used to access nested fields, |
| e.g.expression.fieldname1.fieldname2...</td></tr><tr><td>[ ]</td><td>array_expression [position_keyword (int_expression ) ]</td><td>See ARRAY Functions.</td><td>Type T stored in ARRAY</td><td>position_keyword is either OFFSET or ORDINAL.</td></tr></tbody></table></div></div><h2 id=arithmetic-operators>Arithmetic operators</h2><p>All arithmetic operators accept input of numeric type T, and the result type |
| has type T unless otherwise indicated in the description below:</p><div class=table-wrapper><table><thead><tr><th>Name</th><th>Syntax</th></tr></thead><tbody><tr><td>Addition</td><td>X + Y</td></tr><tr><td>Subtraction</td><td>X - Y</td></tr><tr><td>Multiplication</td><td>X * Y</td></tr><tr><td>Division</td><td>X / Y</td></tr><tr><td>Unary Minus</td><td>- X</td></tr></tbody></table></div><p>Result types for Addition and Multiplication:</p><div class=table-wrapper><table class=table-wrapper--equal-p><thead><tr><th> </th><th>INT64</th><th>FLOAT64</th></tr></thead><tbody><tr><td>INT64</td><td>INT64</td><td>FLOAT64</td></tr><tr><td>FLOAT64</td><td>FLOAT64</td><td>FLOAT64</td></tr></tbody></table></div><p>Result types for Subtraction:</p><div class=table-wrapper><table class=table-wrapper--equal-p><thead><tr><th> </th><th>INT64</th><th>FLOAT64</th></tr></thead><tbody><tr><td>INT64</td><td>INT64</td><td>FLOAT64</td></tr><tr><td>FLOAT64</td><td>FLOAT64</td><td>FLOAT64</td></tr></tbody></table></div><p>Result types for Division:</p><div class=table-wrapper><table class=table-wrapper--equal-p><thead><tr><th> </th><th>INT64</th><th>FLOAT64</th></tr></thead><tbody><tr><td>INT64</td><td>FLOAT64</td><td>FLOAT64</td></tr><tr><td>FLOAT64</td><td>FLOAT64</td><td>FLOAT64</td></tr></tbody></table></div><p>Result types for Unary Minus:</p><div class=table-wrapper><table><thead><tr><th>Input Data Type</th><th>Result Data Type</th></tr></thead><tbody><tr><td>INT64</td><td>INT64</td></tr><tr><td>FLOAT64</td><td>FLOAT64</td></tr></tbody></table></div><h2 id=logical-operators>Logical operators</h2><p>All logical operators allow only BOOL input.</p><div class=table-container-wrapper><div class=table-wrapper><table class=table-wrapper--equal-p><thead><tr><th>Name</th><th>Syntax</th><th>Description</th></tr></thead><tbody><tr><td>Logical NOT</td><td nowrap>NOT X</td><td>Returns FALSE if input is TRUE. Returns TRUE if input is FALSE. Returns <code>NULL</code> |
| otherwise.</td></tr><tr><td>Logical AND</td><td nowrap>X AND Y</td><td>Returns FALSE if at least one input is FALSE. Returns TRUE if both X and Y |
| are TRUE. Returns <code>NULL</code> otherwise.</td></tr><tr><td>Logical OR</td><td nowrap>X OR Y</td><td>Returns FALSE if both X and Y are FALSE. Returns TRUE if at least one input |
| is TRUE. Returns <code>NULL</code> otherwise.</td></tr></tbody></table></div></div><h2 id=comparison-operators>Comparison operators</h2><p>Comparisons always return BOOL. Comparisons generally |
| require both operands to be of the same type. If operands are of different |
| types, and if Cloud Dataflow SQL can convert the values of those types to a |
| common type without loss of precision, Cloud Dataflow SQL will generally coerce |
| them to that common type for the comparison; Cloud Dataflow SQL will generally |
| <a href=/documentation/dsls/sql/zetasql/conversion-rules/#coercion>coerce literals to the type of non-literals</a>, where |
| present. Comparable data types are defined in |
| <a href=/documentation/dsls/sql/zetasql/data-types>Data Types</a>.</p><p>STRUCTs support only 4 comparison operators: equal |
| (=), not equal (!= and <>), and IN.</p><p>The following rules apply when comparing these data types:</p><ul><li><dl><dt>FLOAT64</dt><dd>All comparisons with NaN return FALSE, |
| except for <code>!=</code> and <code><></code>, which return TRUE.</dd></dl></li><li>BOOL: FALSE is less than TRUE.</li><li>STRING: Strings are |
| compared codepoint-by-codepoint, which means that canonically equivalent |
| strings are only guaranteed to compare as equal if |
| they have been normalized first.</li><li><code>NULL</code>: The convention holds here: any operation with a <code>NULL</code> input returns |
| <code>NULL</code>.</li></ul><div class=table-container-wrapper><div class=table-wrapper><table class=table-wrapper--equal-p><thead><tr><th>Name</th><th>Syntax</th><th>Description</th></tr></thead><tbody><tr><td>Less Than</td><td>X < Y</td><td>Returns TRUE if X is less than Y.</td></tr><tr><td>Less Than or Equal To</td><td>X <= Y</td><td>Returns TRUE if X is less than or equal to Y.</td></tr><tr><td>Greater Than</td><td>X > Y</td><td>Returns TRUE if X is greater than Y.</td></tr><tr><td>Greater Than or Equal To</td><td>X >= Y</td><td>Returns TRUE if X is greater than or equal to Y.</td></tr><tr><td>Equal</td><td>X = Y</td><td>Returns TRUE if X is equal to Y.</td></tr><tr><td>Not Equal</td><td>X != Y<br>X <> Y</td><td>Returns TRUE if X is not equal to Y.</td></tr><tr><td>BETWEEN</td><td>X [NOT] BETWEEN Y AND Z</td><td>Returns TRUE if X is [not] within the range specified. The result of "X |
| BETWEEN Y AND Z" is equivalent to "Y <= X AND X <= Z" but X is evaluated |
| only once in the former.</td></tr><tr><td>LIKE</td><td>X [NOT] LIKE Y</td><td>Checks if the STRING in the first operand X |
| matches a pattern specified by the second operand Y. Expressions can contain |
| these characters:<ul><li>A percent sign "%" matches any number of characters or bytes</li><li>An underscore "_" matches a single character or byte</li><li>You can escape "\", "_", or "%" using two backslashes. For example, <code>"\\%"</code>. If you are using raw strings, only a single backslash is |
| required. For example, <code>r"\%"</code>.</li></ul></td></tr><tr><td>IN</td><td>Multiple - see below</td><td>Returns FALSE if the right operand is empty. Returns <code>NULL</code> if the left |
| operand is <code>NULL</code>. Returns TRUE or <code>NULL</code>, never FALSE, if the right operand |
| contains <code>NULL</code>. Arguments on either side of IN are general expressions. Neither |
| operand is required to be a literal, although using a literal on the right is |
| most common. X is evaluated only once.</td></tr></tbody></table></div></div><p>When testing values that have a STRUCT data type for |
| equality, it’s possible that one or more fields are <code>NULL</code>. In such cases:</p><ul><li>If all non-NULL field values are equal, the comparison returns NULL.</li><li>If any non-NULL field values are not equal, the comparison returns false.</li></ul><p>The following table demonstrates how STRUCT data |
| types are compared when they have fields that are <code>NULL</code> valued.</p><div class=table-wrapper><table class=table-wrapper--equal-p><thead><tr><th>Struct1</th><th>Struct2</th><th>Struct1 = Struct2</th></tr></thead><tbody><tr><td><code>STRUCT(1, NULL)</code></td><td><code>STRUCT(1, NULL)</code></td><td><code>NULL</code></td></tr><tr><td><code>STRUCT(1, NULL)</code></td><td><code>STRUCT(2, NULL)</code></td><td><code>FALSE</code></td></tr><tr><td><code>STRUCT(1,2)</code></td><td><code>STRUCT(1, NULL)</code></td><td><code>NULL</code></td></tr></tbody></table></div><h2 id=is-operators>IS operators</h2><p>IS operators return TRUE or FALSE for the condition they are testing. They never |
| return <code>NULL</code>, even for <code>NULL</code> inputs. If NOT is present, the output BOOL value |
| is inverted.</p><div class=table-container-wrapper><div class=table-wrapper><table class=table-wrapper--equal-p><thead><tr><th>Function Syntax</th><th>Input Data Type</th><th>Result Data Type</th><th>Description</th></tr></thead><tbody><tr><td><pre>X IS [NOT] NULL</pre></td><td>Any value type</td><td>BOOL</td><td>Returns TRUE if the operand X evaluates to <code>NULL</code>, and returns FALSE |
| otherwise.</td></tr><tr><td><pre>X IS [NOT] TRUE</pre></td><td>BOOL</td><td>BOOL</td><td>Returns TRUE if the BOOL operand evaluates to TRUE. Returns FALSE |
| otherwise.</td></tr><tr><td><pre>X IS [NOT] FALSE</pre></td><td>BOOL</td><td>BOOL</td><td>Returns TRUE if the BOOL operand evaluates to FALSE. Returns FALSE |
| otherwise.</td></tr></tbody></table></div></div></div></div><footer class=footer><div class=footer__contained><div class=footer__cols><div class="footer__cols__col footer__cols__col__logos"><div class=footer__cols__col__logo><img src=/images/beam_logo_circle.svg class=footer__logo alt="Beam logo"></div><div class=footer__cols__col__logo><img src=/images/apache_logo_circle.svg class=footer__logo alt="Apache logo"></div></div><div class=footer-wrapper><div class=wrapper-grid><div class=footer__cols__col><div class=footer__cols__col__title>Start</div><div class=footer__cols__col__link><a href=/get-started/beam-overview/>Overview</a></div><div class=footer__cols__col__link><a href=/get-started/quickstart-java/>Quickstart (Java)</a></div><div class=footer__cols__col__link><a href=/get-started/quickstart-py/>Quickstart (Python)</a></div><div class=footer__cols__col__link><a href=/get-started/quickstart-go/>Quickstart (Go)</a></div><div class=footer__cols__col__link><a href=/get-started/downloads/>Downloads</a></div></div><div class=footer__cols__col><div class=footer__cols__col__title>Docs</div><div class=footer__cols__col__link><a href=/documentation/programming-guide/>Concepts</a></div><div class=footer__cols__col__link><a href=/documentation/pipelines/design-your-pipeline/>Pipelines</a></div><div class=footer__cols__col__link><a href=/documentation/runners/capability-matrix/>Runners</a></div></div><div class=footer__cols__col><div class=footer__cols__col__title>Community</div><div class=footer__cols__col__link><a href=/contribute/>Contribute</a></div><div class=footer__cols__col__link><a href=https://projects.apache.org/committee.html?beam target=_blank>Team<img src=/images/external-link-icon.png width=14 height=14 alt="External link."></a></div><div class=footer__cols__col__link><a href=/community/presentation-materials/>Media</a></div><div class=footer__cols__col__link><a href=/community/in-person/>Events/Meetups</a></div><div class=footer__cols__col__link><a href=/community/contact-us/>Contact Us</a></div></div><div class=footer__cols__col><div class=footer__cols__col__title>Resources</div><div class=footer__cols__col__link><a href=/blog/>Blog</a></div><div class=footer__cols__col__link><a href=https://github.com/apache/beam>GitHub</a></div></div></div><div class=footer__bottom>© |
| <a href=https://www.apache.org>The Apache Software Foundation</a> |
| | <a href=/privacy_policy>Privacy Policy</a> |
| | <a href=/feed.xml>RSS Feed</a><br><br>Apache Beam, Apache, Beam, the Beam logo, and the Apache feather logo are either registered trademarks or trademarks of The Apache Software Foundation. All other products or name brands are trademarks of their respective holders, including The Apache Software Foundation.</div></div><div class="footer__cols__col footer__cols__col__logos"><div class=footer__cols__col--group><div class=footer__cols__col__logo><a href=https://github.com/apache/beam><img src=/images/logos/social-icons/github-logo-150.png class=footer__logo alt="Github logo"></a></div><div class=footer__cols__col__logo><a href=https://www.linkedin.com/company/apache-beam/><img src=/images/logos/social-icons/linkedin-logo-150.png class=footer__logo alt="Linkedin logo"></a></div></div><div class=footer__cols__col--group><div class=footer__cols__col__logo><a href=https://twitter.com/apachebeam><img src=/images/logos/social-icons/twitter-logo-150.png class=footer__logo alt="Twitter logo"></a></div><div class=footer__cols__col__logo><a href=https://www.youtube.com/channel/UChNnb_YO_7B0HlW6FhAXZZQ><img src=/images/logos/social-icons/youtube-logo-150.png class=footer__logo alt="Youtube logo"></a></div></div></div></div></div></footer></body></html> |
