Google Git
Sign in
apache / beam / refs/heads/asf-site / . / website / generated-content / documentation / dsls / sql / calcite / query-syntax / index.html
blob: bd59a22040a69f711b89b12f09144522a50cfa35 [file] [log] [blame]
<!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 Calcite SQL query syntax</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.d653ded46cd5f19a535cb20567fce9699849fe46f950d91ac6bf336db8ff8724.css as=style><link href=/scss/main.min.d653ded46cd5f19a535cb20567fce9699849fe46f950d91ac6bf336db8ff8724.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/calcite/query-syntax/ 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/calcite/query-syntax.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>
&nbsp;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/calcite/query-syntax.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>
&nbsp;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.55.1/ 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.55.1/ 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.55.1/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=#sql-syntax>SQL Syntax</a></li><li><a href=#select-list>SELECT list</a><ul><li><a href=#select->SELECT *</a></li><li><a href=#select-expression>SELECT <code>expression</code></a></li><li><a href=#select-expression_1>SELECT <code>expression.*</code></a></li><li><a href=#select-modifiers>SELECT modifiers</a><ul><li><a href=#select-distinct>SELECT DISTINCT</a></li><li><a href=#select-all>SELECT ALL</a></li></ul></li><li><a href=#aliases>Aliases</a></li></ul></li><li><a href=#from-clause>FROM clause</a><ul><li><a href=#syntax>Syntax</a><ul><li><a href=#table_name>table_name</a></li><li><a href=#join>join</a></li><li><a href=#select_1>select</a></li><li><a href=#with_query_name>with_query_name</a></li></ul></li><li><a href=#subqueries>Subqueries</a></li><li><a href=#aliases_1>Aliases</a></li></ul></li><li><a href=#join-types>JOIN types</a><ul><li><a href=#syntax_1>Syntax</a></li><li><a href=#inner-join>[INNER] JOIN</a></li><li><a href=#cross-join>CROSS JOIN</a></li><li><a href=#full-outer-join>FULL [OUTER] JOIN</a></li><li><a href=#left-outer-join>LEFT [OUTER] JOIN</a></li><li><a href=#right-outer-join>RIGHT [OUTER] JOIN</a></li><li><a href=#on-clause>ON clause</a></li><li><a href=#using-clause>USING clause</a></li><li><a href=#sequences-of-joins>Sequences of JOINs</a></li></ul></li><li><a href=#where-clause>WHERE clause</a><ul><li><a href=#syntax_2>Syntax</a></li></ul></li><li><a href=#group-by-clause>GROUP BY clause</a><ul><li><a href=#syntax_3>Syntax</a></li></ul></li><li><a href=#having-clause>HAVING clause</a><ul><li><a href=#syntax_4>Syntax</a></li></ul></li><li><a href=#set-operators>Set operators</a><ul><li><a href=#syntax_6>Syntax</a></li><li><a href=#union>UNION</a></li><li><a href=#intersect>INTERSECT</a></li><li><a href=#except>EXCEPT</a></li></ul></li><li><a href=#limit-clause-and-offset-clause>LIMIT clause and OFFSET clause</a><ul><li><a href=#syntax_7>Syntax</a></li></ul></li><li><a href=#with-clause>WITH clause</a></li><li><a href=#aliases_2>Aliases</a><ul><li><a href=#explicit-alias-syntax>Explicit alias syntax</a></li><li><a href=#explicit-alias-visibility>Explicit alias visibility</a><ul><li><a href=#from-clause-aliases>FROM clause aliases</a></li></ul></li><li><a href=#ambiguous-aliases>Ambiguous aliases</a></li><li><a href=#implicit-aliases>Implicit aliases</a></li></ul></li></ul></nav></nav><div class="body__contained body__section-nav"><h1 id=beam-calcite-sql-query-syntax>Beam Calcite SQL query syntax</h1><p>Query statements scan one or more tables or expressions and return the computed
result rows.</p><p>Generally, the semantics of queries is standard. See the following
sections to learn about extensions for supporting Beam&rsquo;s unified
batch/streaming model:</p><ul><li><a href=/documentation/dsls/sql/extensions/joins>Joins</a></li><li><a href=/documentation/dsls/sql/windowing-and-triggering/>Windowing & Triggering</a></li></ul><p>The main functionality of Beam SQL is the <code>SELECT</code> statement. This is how you
query and join data. The operations supported are a subset of
<a href=https://calcite.apache.org/docs/reference.html#grammar>Apache Calcite SQL</a>.</p><h2 id=sql-syntax>SQL Syntax</h2><pre><code>query_statement:
[ WITH with_query_name AS ( query_expr ) [, ...] ]
query_expr
query_expr:
{ select | ( query_expr ) | query_expr set_op query_expr }
[ LIMIT count [ OFFSET skip_rows ] ]
select:
SELECT [{ ALL | DISTINCT }]
{ [ expression. ]* [ EXCEPT ( column_name [, ...] ) ]
[ REPLACE ( expression [ AS ] column_name [, ...] ) ]
| expression [ [ AS ] alias ] } [, ...]
[ FROM from_item [, ...] ]
[ WHERE bool_expression ]
[ GROUP BY { expression [, ...] } ]
[ HAVING bool_expression ]
set_op:
UNION { ALL | DISTINCT } | INTERSECT DISTINCT | EXCEPT DISTINCT
from_item: {
table_name [ [ AS ] alias ] |
join |
( query_expr ) [ [ AS ] alias ]
with_query_name [ [ AS ] alias ]
}
join:
from_item [ join_type ] JOIN from_item
[ { ON bool_expression | USING ( join_column [, ...] ) } ]
join_type:
{ INNER | CROSS | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER] }
</code></pre><p>Notation:</p><ul><li>Square brackets &ldquo;[ ]&rdquo; indicate optional clauses.</li><li>Parentheses &ldquo;( )&rdquo; indicate literal parentheses.</li><li>The vertical bar &ldquo;|&rdquo; indicates a logical OR.</li><li>Curly braces &ldquo;{ }&rdquo; enclose a set of options.</li><li>A comma followed by an ellipsis within square brackets &ldquo;[, &mldr; ]&rdquo;
indicates that the preceding item can repeat in a comma-separated list.</li></ul><h2 id=select-list>SELECT list</h2><p>Syntax:</p><pre><code>SELECT [{ ALL | DISTINCT }]
{ [ expression. ]*
| expression [ [ AS ] alias ] } [, ...]
</code></pre><p>The <code>SELECT</code> list defines the columns that the query will return. Expressions in
the <code>SELECT</code> list can refer to columns in any of the <code>from_item</code>s in its
corresponding <code>FROM</code> clause.</p><p>Each item in the <code>SELECT</code> list is one of:</p><ul><li>*</li><li><code>expression</code></li><li><code>expression.*</code></li></ul><h3 id=select->SELECT *</h3><p><code>SELECT *</code>, often referred to as <em>select star</em>, produces one output column for
each column that is visible after executing the full query.</p><pre tabindex=0><code>SELECT * FROM (SELECT &#39;apple&#39; AS fruit, &#39;carrot&#39; AS vegetable);
+-------+-----------+
| fruit | vegetable |
+-------+-----------+
| apple | carrot |
+-------+-----------+
</code></pre><h3 id=select-expression>SELECT <code>expression</code></h3><p>Items in a <code>SELECT</code> list can be expressions. These expressions evaluate to a
single value and produce one output column, with an optional explicit <code>alias</code>.</p><p>If the expression does not have an explicit alias, it receives an implicit alias
according to the rules for <a href=#implicit-aliases>implicit aliases</a>, if possible.
Otherwise, the column is anonymous and you cannot refer to it by name elsewhere
in the query.</p><h3 id=select-expression_1>SELECT <code>expression.*</code></h3><p>An item in a <code>SELECT</code> list can also take the form of <code>expression.*</code>. This
produces one output column for each column or top-level field of <code>expression</code>.
The expression must be a table alias.</p><p>The following query produces one output column for each column in the table
<code>groceries</code>, aliased as <code>g</code>.</p><pre tabindex=0><code>WITH groceries AS
(SELECT &#39;milk&#39; AS dairy,
&#39;eggs&#39; AS protein,
&#39;bread&#39; AS grain)
SELECT g.*
FROM groceries AS g;
+-------+---------+-------+
| dairy | protein | grain |
+-------+---------+-------+
| milk | eggs | bread |
+-------+---------+-------+
</code></pre><h3 id=select-modifiers>SELECT modifiers</h3><p>You can modify the results returned from a <code>SELECT</code> query, as follows.</p><h4 id=select-distinct>SELECT DISTINCT</h4><p>A <code>SELECT DISTINCT</code> statement discards duplicate rows and returns only the
remaining rows. <code>SELECT DISTINCT</code> cannot return columns of the following types:</p><ul><li>STRUCT</li><li>ARRAY</li></ul><h4 id=select-all>SELECT ALL</h4><p>A <code>SELECT ALL</code> statement returns all rows, including duplicate rows. <code>SELECT ALL</code> is the default behavior of <code>SELECT</code>.</p><h3 id=aliases>Aliases</h3><p>See <a href=#aliases_2>Aliases</a> for information on syntax and visibility for
<code>SELECT</code> list aliases.</p><h2 id=from-clause>FROM clause</h2><p>The <code>FROM</code> clause indicates the table or tables from which to retrieve rows, and
specifies how to join those rows together to produce a single stream of rows for
processing in the rest of the query.</p><h3 id=syntax>Syntax</h3><pre><code>from_item: {
table_name [ [ AS ] alias ] |
join |
( query_expr ) [ [ AS ] alias ] |
with_query_name [ [ AS ] alias ]
}
</code></pre><h4 id=table_name>table_name</h4><p>The name (optionally qualified) of an existing table.</p><pre><code>SELECT * FROM Roster;
SELECT * FROM beam.Roster;
</code></pre><h4 id=join>join</h4><p>See <a href=#join-types>JOIN Types</a> below and <a href=/documentation/dsls/sql/extensions/joins>Joins</a>.</p><h4 id=select_1>select</h4><p><code>( select ) [ [ AS ] alias ]</code> is a table <a href=#subqueries>subquery</a>.</p><h4 id=with_query_name>with_query_name</h4><p>The query names in a <code>WITH</code> clause (see <a href=#with-clause>WITH Clause</a>) act like
names of temporary tables that you can reference anywhere in the <code>FROM</code> clause.
In the example below, <code>subQ1</code> and <code>subQ2</code> are <code>with_query_names</code>.</p><p>Example:</p><pre><code>WITH
subQ1 AS (SELECT * FROM Roster WHERE SchoolID = 52),
subQ2 AS (SELECT SchoolID FROM subQ1)
SELECT DISTINCT * FROM subQ2;
</code></pre><p>The <code>WITH</code> clause hides any permanent tables with the same name for the duration
of the query, unless you qualify the table name, e.g. <code>beam.Roster</code>.</p><h3 id=subqueries>Subqueries</h3><p>A subquery is a query that appears inside another statement, and is written
inside parentheses. These are also referred to as &ldquo;sub-SELECTs&rdquo; or &ldquo;nested
SELECTs&rdquo;. The full <code>SELECT</code> syntax is valid in subqueries.</p><p>There are two types of subquery:</p><ul><li>Expression Subqueries
which you can use in a query wherever expressions are valid. Expression
subqueries return a single value.</li><li>Table subqueries, which you can use only in a <code>FROM</code> clause. The outer query
treats the result of the subquery as a table.</li></ul><p>Note that there must be parentheses around both types of subqueries.</p><p>Example:</p><pre tabindex=0><code>SELECT AVG ( PointsScored )
FROM
( SELECT PointsScored
FROM Stats
WHERE SchoolID = 77 )
</code></pre><p>Optionally, a table subquery can have an alias.</p><p>Example:</p><pre tabindex=0><code>SELECT r.LastName
FROM
( SELECT * FROM Roster) AS r;
</code></pre><h3 id=aliases_1>Aliases</h3><p>See <a href=#aliases_2>Aliases</a> for information on syntax and visibility for
<code>FROM</code> clause aliases.</p><h2 id=join-types>JOIN types</h2><p>Also see <a href=/documentation/dsls/sql/extensions/joins>Joins</a>.</p><h3 id=syntax_1>Syntax</h3><pre><code>join:
from_item [ join_type ] JOIN from_item
[ ON bool_expression | USING ( join_column [, ...] ) ]
join_type:
{ INNER | CROSS | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER] }
</code></pre><p>The <code>JOIN</code> clause merges two <code>from_item</code>s so that the <code>SELECT</code> clause can query
them as one source. The <code>join_type</code> and <code>ON</code> or <code>USING</code> clause (a &ldquo;join
condition&rdquo;) specify how to combine and discard rows from the two <code>from_item</code>s to
form a single source.</p><p>All <code>JOIN</code> clauses require a <code>join_type</code>.</p><p>A <code>JOIN</code> clause requires a join condition unless one of the following conditions
is true:</p><ul><li><code>join_type</code> is <code>CROSS</code>.</li><li>One or both of the <code>from_item</code>s is not a table, e.g. an <code>array_path</code> or
<code>field_path</code>.</li></ul><h3 id=inner-join>[INNER] JOIN</h3><p>An <code>INNER JOIN</code>, or simply <code>JOIN</code>, effectively calculates the Cartesian product
of the two <code>from_item</code>s and discards all rows that do not meet the join
condition. &ldquo;Effectively&rdquo; means that it is possible to implement an <code>INNER JOIN</code>
without actually calculating the Cartesian product.</p><h3 id=cross-join>CROSS JOIN</h3><p><code>CROSS JOIN</code> is generally not yet supported.</p><h3 id=full-outer-join>FULL [OUTER] JOIN</h3><p>A <code>FULL OUTER JOIN</code> (or simply <code>FULL JOIN</code>) returns all fields for all rows in
both <code>from_item</code>s that meet the join condition.</p><p><code>FULL</code> indicates that <em>all rows</em> from both <code>from_item</code>s are returned, even if
they do not meet the join condition. For streaming jobs, all rows that are
not late according to default trigger and belonging to the same window
if there&rsquo;s non-global window applied.</p><p><code>OUTER</code> indicates that if a given row from one <code>from_item</code> does not join to any
row in the other <code>from_item</code>, the row will return with NULLs for all columns
from the other <code>from_item</code>.</p><p>Also see <a href=/documentation/dsls/sql/extensions/joins>Joins</a>.</p><h3 id=left-outer-join>LEFT [OUTER] JOIN</h3><p>The result of a <code>LEFT OUTER JOIN</code> (or simply <code>LEFT JOIN</code>) for two <code>from_item</code>s
always retains all rows of the left <code>from_item</code> in the <code>JOIN</code> clause, even if no
rows in the right <code>from_item</code> satisfy the join predicate.</p><p><code>LEFT</code> indicates that all rows from the <em>left</em> <code>from_item</code> are returned; if a
given row from the left <code>from_item</code> does not join to any row in the <em>right</em>
<code>from_item</code>, the row will return with NULLs for all columns from the right
<code>from_item</code>. Rows from the right <code>from_item</code> that do not join to any row in the
left <code>from_item</code> are discarded.</p><h3 id=right-outer-join>RIGHT [OUTER] JOIN</h3><p>The result of a <code>RIGHT OUTER JOIN</code> (or simply <code>RIGHT JOIN</code>) is similar and
symmetric to that of <code>LEFT OUTER JOIN</code>.</p><h3 id=on-clause>ON clause</h3><p>The <code>ON</code> clause contains a <code>bool_expression</code>. A combined row (the result of
joining two rows) meets the join condition if <code>bool_expression</code> returns TRUE.</p><p>Example:</p><pre tabindex=0><code>SELECT * FROM Roster INNER JOIN PlayerStats
ON Roster.LastName = PlayerStats.LastName;
</code></pre><h3 id=using-clause>USING clause</h3><p>The <code>USING</code> clause requires a <code>column_list</code> of one or more columns which occur
in both input tables. It performs an equality comparison on that column, and the
rows meet the join condition if the equality comparison returns TRUE.</p><p>In most cases, a statement with the <code>USING</code> keyword is equivalent to using the
<code>ON</code> keyword. For example, the statement:</p><pre tabindex=0><code>SELECT FirstName
FROM Roster INNER JOIN PlayerStats
USING (LastName);
</code></pre><p>is equivalent to:</p><pre tabindex=0><code>SELECT FirstName
FROM Roster INNER JOIN PlayerStats
ON Roster.LastName = PlayerStats.LastName;
</code></pre><p>The results from queries with <code>USING</code> do differ from queries that use <code>ON</code> when
you use <code>SELECT *</code>. To illustrate this, consider the query:</p><pre tabindex=0><code>SELECT * FROM Roster INNER JOIN PlayerStats
USING (LastName);
</code></pre><p>This statement returns the rows from <code>Roster</code> and <code>PlayerStats</code> where
<code>Roster.LastName</code> is the same as <code>PlayerStats.LastName</code>. The results include a
single <code>LastName</code> column.</p><p>By contrast, consider the following query:</p><pre tabindex=0><code>SELECT * FROM Roster INNER JOIN PlayerStats
ON Roster.LastName = PlayerStats.LastName;
</code></pre><p>This statement returns the rows from <code>Roster</code> and <code>PlayerStats</code> where
<code>Roster.LastName</code> is the same as <code>PlayerStats.LastName</code>. The results include two
<code>LastName</code> columns; one from <code>Roster</code> and one from <code>PlayerStats</code>.</p><h3 id=sequences-of-joins>Sequences of JOINs</h3><p>The <code>FROM</code> clause can contain multiple <code>JOIN</code> clauses in sequence.</p><p>Example:</p><pre tabindex=0><code>SELECT * FROM a LEFT JOIN b ON TRUE LEFT JOIN c ON TRUE;
</code></pre><p>where <code>a</code>, <code>b</code>, and <code>c</code> are any <code>from_item</code>s. JOINs are bound from left to
right, but you can insert parentheses to group them in a different order.</p><h2 id=where-clause>WHERE clause</h2><h3 id=syntax_2>Syntax</h3><pre tabindex=0><code>WHERE bool_expression
</code></pre><p>The <code>WHERE</code> clause filters out rows by evaluating each row against
<code>bool_expression</code>, and discards all rows that do not return TRUE (that is, rows
that return FALSE or NULL).</p><p>Example:</p><pre tabindex=0><code>SELECT * FROM Roster
WHERE SchoolID = 52;
</code></pre><p>The <code>bool_expression</code> can contain multiple sub-conditions.</p><p>Example:</p><pre tabindex=0><code>SELECT * FROM Roster
WHERE LastName LIKE &#39;Mc%&#39; OR LastName LIKE &#39;Mac%&#39;;
</code></pre><p>You cannot reference column aliases from the <code>SELECT</code> list in the <code>WHERE</code>
clause.</p><p>Expressions in an <code>INNER JOIN</code> have an equivalent expression in the <code>WHERE</code>
clause. For example, a query using <code>INNER</code> <code>JOIN</code> and <code>ON</code> has an equivalent
expression using <code>CROSS JOIN</code> and <code>WHERE</code>.</p><p>Example - this query:</p><pre tabindex=0><code>SELECT * FROM Roster INNER JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;
</code></pre><p>is equivalent to:</p><pre tabindex=0><code>SELECT * FROM Roster CROSS JOIN TeamMascot
WHERE Roster.SchoolID = TeamMascot.SchoolID;
</code></pre><h2 id=group-by-clause>GROUP BY clause</h2><p>Also see <a href=/documentation/dsls/sql/windowing-and-triggering/>Windowing & Triggering</a></p><h3 id=syntax_3>Syntax</h3><pre><code>GROUP BY { expression [, ...] }
</code></pre><p>The <code>GROUP BY</code> clause groups together rows in a table with non-distinct values
for the <code>expression</code> in the <code>GROUP BY</code> clause. For multiple rows in the source
table with non-distinct values for <code>expression</code>, the <code>GROUP BY</code> clause produces
a single combined row. <code>GROUP BY</code> is commonly used when aggregate functions are
present in the <code>SELECT</code> list, or to eliminate redundancy in the output.</p><p>Example:</p><pre tabindex=0><code>SELECT SUM(PointsScored), LastName
FROM PlayerStats
GROUP BY LastName;
</code></pre><h2 id=having-clause>HAVING clause</h2><h3 id=syntax_4>Syntax</h3><pre tabindex=0><code>HAVING bool_expression
</code></pre><p>The <code>HAVING</code> clause is similar to the <code>WHERE</code> clause: it filters out rows that
do not return TRUE when they are evaluated against the <code>bool_expression</code>.</p><p>As with the <code>WHERE</code> clause, the <code>bool_expression</code> can be any expression that
returns a boolean, and can contain multiple sub-conditions.</p><p>The <code>HAVING</code> clause differs from the <code>WHERE</code> clause in that:</p><ul><li>The <code>HAVING</code> clause requires <code>GROUP BY</code> or aggregation to be present in the
query.</li><li>The <code>HAVING</code> clause occurs after <code>GROUP BY</code> and aggregation.
This means that the <code>HAVING</code> clause is evaluated once for every
aggregated row in the result set. This differs from the <code>WHERE</code> clause,
which is evaluated before <code>GROUP BY</code> and aggregation.</li></ul><p>The <code>HAVING</code> clause can reference columns available via the <code>FROM</code> clause, as
well as <code>SELECT</code> list aliases. Expressions referenced in the <code>HAVING</code> clause
must either appear in the <code>GROUP BY</code> clause or they must be the result of an
aggregate function:</p><pre tabindex=0><code>SELECT LastName
FROM Roster
GROUP BY LastName
HAVING SUM(PointsScored) &gt; 15;
</code></pre><h2 id=set-operators>Set operators</h2><h3 id=syntax_6>Syntax</h3><pre><code>UNION { ALL | DISTINCT } | INTERSECT DISTINCT | EXCEPT DISTINCT
</code></pre><p>Set operators combine results from two or more input queries into a single
result set. You must specify <code>ALL</code> or <code>DISTINCT</code>; if you specify <code>ALL</code>, then all
rows are retained. If <code>DISTINCT</code> is specified, duplicate rows are discarded.</p><p>If a given row R appears exactly m times in the first input query and n times in
the second input query (m >= 0, n >= 0):</p><ul><li>For <code>UNION ALL</code>, R appears exactly m + n times in the result.</li><li>For <code>UNION DISTINCT</code>, the <code>DISTINCT</code> is computed after the <code>UNION</code> is
computed, so R appears exactly one time.</li><li>For <code>INTERSECT DISTINCT</code>, the <code>DISTINCT</code> is computed after the result above
is computed.</li><li>For <code>EXCEPT DISTINCT</code>, row R appears once in the output if m > 0 and
n = 0.</li><li>If there are more than two input queries, the above operations generalize
and the output is the same as if the inputs were combined incrementally from
left to right.</li></ul><p>The following rules apply:</p><ul><li>For set operations other than <code>UNION ALL</code>, all column types must support
equality comparison.</li><li>The input queries on each side of the operator must return the same number
of columns.</li><li>The operators pair the columns returned by each input query according to the
columns&rsquo; positions in their respective <code>SELECT</code> lists. That is, the first
column in the first input query is paired with the first column in the
second input query.</li><li>The result set always uses the column names from the first input query.</li><li>The result set always uses the supertypes of input types in corresponding
columns, so paired columns must also have either the same data type or a
common supertype.</li><li>You must use parentheses to separate different set operations; for this
purpose, set operations such as <code>UNION ALL</code> and <code>UNION DISTINCT</code> are
different. If the statement only repeats the same set operation, parentheses
are not necessary.</li></ul><p>Examples:</p><pre tabindex=0><code>query1 UNION ALL (query2 UNION DISTINCT query3)
query1 UNION ALL query2 UNION ALL query3
</code></pre><p>Invalid:</p><pre><code>query1 UNION ALL query2 UNION DISTINCT query3
query1 UNION ALL query2 INTERSECT ALL query3; // INVALID.
</code></pre><h3 id=union>UNION</h3><p>The <code>UNION</code> operator combines the result sets of two or more input queries by
pairing columns from the result set of each query and vertically concatenating
them.</p><h3 id=intersect>INTERSECT</h3><p>The <code>INTERSECT</code> operator returns rows that are found in the result sets of both
the left and right input queries. Unlike <code>EXCEPT</code>, the positioning of the input
queries (to the left vs. right of the <code>INTERSECT</code> operator) does not matter.</p><h3 id=except>EXCEPT</h3><p>The <code>EXCEPT</code> operator returns rows from the left input query that are not
present in the right input query.</p><h2 id=limit-clause-and-offset-clause>LIMIT clause and OFFSET clause</h2><h3 id=syntax_7>Syntax</h3><pre tabindex=0><code>LIMIT count [ OFFSET skip_rows ]
</code></pre><p><code>LIMIT</code> specifies a non-negative <code>count</code> of type INTEGER, and no more than <code>count</code>
rows will be returned. <code>LIMIT</code> <code>0</code> returns 0 rows. If there is a set operation,
<code>LIMIT</code> is applied after the set operation is evaluated.</p><p><code>OFFSET</code> specifies a non-negative <code>skip_rows</code> of type INTEGER, and only rows from
that offset in the table will be considered.</p><p>These clauses accept only literal or parameter values.</p><p>The rows that are returned by <code>LIMIT</code> and <code>OFFSET</code> is unspecified.</p><h2 id=with-clause>WITH clause</h2><p>The <code>WITH</code> clause contains one or more named subqueries which execute every time
a subsequent <code>SELECT</code> statement references them. Any clause or subquery can
reference subqueries you define in the <code>WITH</code> clause. This includes any <code>SELECT</code>
statements on either side of a set operator, such as <code>UNION</code>.</p><p>Example:</p><pre tabindex=0><code>WITH subQ1 AS (SELECT SchoolID FROM Roster),
subQ2 AS (SELECT OpponentID FROM PlayerStats)
SELECT * FROM subQ1
UNION ALL
SELECT * FROM subQ2;
</code></pre><h2 id=aliases_2>Aliases</h2><p>An alias is a temporary name given to a table, column, or expression present in
a query. You can introduce explicit aliases in the <code>SELECT</code> list or <code>FROM</code>
clause, or Beam will infer an implicit alias for some expressions.
Expressions with neither an explicit nor implicit alias are anonymous and the
query cannot reference them by name.</p><h3 id=explicit-alias-syntax>Explicit alias syntax</h3><p>You can introduce explicit aliases in either the <code>FROM</code> clause or the <code>SELECT</code>
list.</p><p>In a <code>FROM</code> clause, you can introduce explicit aliases for any item, including
tables, arrays, subqueries, and <code>UNNEST</code> clauses, using <code>[AS] alias</code>. The <code>AS</code>
keyword is optional.</p><p>Example:</p><pre tabindex=0><code>SELECT s.FirstName, s2.SongName
FROM Singers AS s JOIN Songs AS s2 ON s.SingerID = s2.SingerID;
</code></pre><p>You can introduce explicit aliases for any expression in the <code>SELECT</code> list using
<code>[AS] alias</code>. The <code>AS</code> keyword is optional.</p><p>Example:</p><pre tabindex=0><code>SELECT s.FirstName AS name, LOWER(s.FirstName) AS lname
FROM Singers s;
</code></pre><h3 id=explicit-alias-visibility>Explicit alias visibility</h3><p>After you introduce an explicit alias in a query, there are restrictions on
where else in the query you can reference that alias. These restrictions on
alias visibility are the result of Beam&rsquo;s name scoping rules.</p><h4 id=from-clause-aliases>FROM clause aliases</h4><p>Beam processes aliases in a <code>FROM</code> clause from left to right, and aliases
are visible only to subsequent <code>JOIN</code> clauses.</p><h3 id=ambiguous-aliases>Ambiguous aliases</h3><p>Beam provides an error if a name is ambiguous, meaning it can resolve to
more than one unique object.</p><p>Examples:</p><p>This query contains column names that conflict between tables, since both
<code>Singers</code> and <code>Songs</code> have a column named <code>SingerID</code>:</p><pre tabindex=0><code>SELECT SingerID
FROM Singers, Songs;
</code></pre><h3 id=implicit-aliases>Implicit aliases</h3><p>In the <code>SELECT</code> list, if there is an expression that does not have an explicit
alias, Beam assigns an implicit alias according to the following rules.
There can be multiple columns with the same alias in the <code>SELECT</code> list.</p><ul><li>For identifiers, the alias is the identifier. For example, <code>SELECT abc</code>
implies <code>AS abc</code>.</li><li>For path expressions, the alias is the last identifier in the path. For
example, <code>SELECT abc.def.ghi</code> implies <code>AS ghi</code>.</li><li>For field access using the &ldquo;dot&rdquo; member field access operator, the alias is
the field name. For example, <code>SELECT (struct_function()).fname</code> implies <code>AS fname</code>.</li></ul><p>In all other cases, there is no implicit alias, so the column is anonymous and
cannot be referenced by name. The data from that column will still be returned
and the displayed query results may have a generated label for that column, but
the label cannot be used like an alias.</p><p>In a <code>FROM</code> clause, <code>from_item</code>s are not required to have an alias. The
following rules apply:</p><p>If there is an expression that does not have an explicit alias, Beam assigns
an implicit alias in these cases:</p><ul><li>For identifiers, the alias is the identifier. For example, <code>FROM abc</code>
implies <code>AS abc</code>.</li><li>For path expressions, the alias is the last identifier in the path. For
example, <code>FROM abc.def.ghi</code> implies <code>AS ghi</code></li></ul><p>Table subqueries do not have implicit aliases.</p><p><code>FROM UNNEST(x)</code> does not have an implicit alias.</p><blockquote><p>Portions of this page are modifications based on
<a href=https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax>work</a>
created and
<a href=https://developers.google.com/terms/site-policies>shared by Google</a>
and used according to terms described in the <a href=https://creativecommons.org/licenses/by/3.0/>Creative Commons 3.0
Attribution License</a>.</p></blockquote></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>&copy;
<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>