355/sql/select.html


<!DOCTYPE html>

<html>
  <head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
  <meta name="viewport" content="width=device-width,initial-scale=1">
  <meta http-equiv="x-ua-compatible" content="ie=edge">
  <meta name="lang:clipboard.copy" content="Copy to clipboard">
  <meta name="lang:clipboard.copied" content="Copied to clipboard">
  <meta name="lang:search.language" content="en">
  <meta name="lang:search.pipeline.stopwords" content="True">
  <meta name="lang:search.pipeline.trimmer" content="True">
  <meta name="lang:search.result.none" content="No matching documents">
  <meta name="lang:search.result.one" content="1 matching document">
  <meta name="lang:search.result.other" content="# matching documents">
  <meta name="lang:search.tokenizer" content="[\s\-]+">

  
    <link href="https://fonts.gstatic.com/" rel="preconnect" crossorigin>
    <link href="https://fonts.googleapis.com/css?family=Roboto+Mono:400,500,700|Roboto:300,400,400i,700&display=fallback" rel="stylesheet">

    <link href="https://fonts.googleapis.com/css2?family=Alata&family=Open+Sans:ital,wght%400,300;0,400;0,600;1,400&display=swap" rel="stylesheet">
    <style>
      body,
      input {
        font-family: "Roboto", "Helvetica Neue", Helvetica, Arial, sans-serif
      }

      code,
      kbd,
      pre {
        font-family: "Roboto Mono", "Courier New", Courier, monospace
      }
    </style>
  

  <link rel="stylesheet" href="../_static/stylesheets/application.css"/>
  <link rel="stylesheet" href="../_static/stylesheets/application-palette.css"/>
  <link rel="stylesheet" href="../_static/stylesheets/application-fixes.css"/>
  
  <link rel="stylesheet" href="../_static/fonts/material-icons.css"/>
  
  <meta name="theme-color" content="2196f3">
  <script src="../_static/javascripts/modernizr.js"></script>
  
  
    <title>SELECT &#8212; Trino 355 Documentation</title>
    <link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
    <link rel="stylesheet" href="../_static/material.css" type="text/css" />
    <link rel="stylesheet" type="text/css" href="../_static/copybutton.css" />
    <link rel="stylesheet" type="text/css" href="../_static/trino.css" />
    <script id="documentation_options" data-url_root="../" src="../_static/documentation_options.js"></script>
    <script src="../_static/jquery.js"></script>
    <script src="../_static/underscore.js"></script>
    <script src="../_static/doctools.js"></script>
    <script src="../_static/language_data.js"></script>
    <script src="../_static/clipboard.min.js"></script>
    <script src="../_static/copybutton.js"></script>
    <link rel="index" title="Index" href="../genindex.html" />
    <link rel="search" title="Search" href="../search.html" />
    <link rel="next" title="SET ROLE" href="set-role.html" />
    <link rel="prev" title="ROLLBACK" href="rollback.html" />
  
   
    <script async src="https://www.googletagmanager.com/gtag/js?id=UA-133457846-1"></script>
    <script>
      window.dataLayer = window.dataLayer || [];
      function gtag(){dataLayer.push(arguments);}
      gtag('js', new Date());
      gtag('config', 'UA-133457846-1');
      gtag('config', 'AW-1036784065');
      window._linkedin_data_partner_ids = ['2842796'];
    </script>
    <script async src="https://snap.licdn.com/li.lms-analytics/insight.min.js"></script>
  </head>
  <body dir=ltr
        data-md-color-primary= data-md-color-accent=>
  
  <svg class="md-svg">
    <defs data-children-count="0">
      
      <svg xmlns="http://www.w3.org/2000/svg" width="416" height="448" viewBox="0 0 416 448" id="__github"><path fill="currentColor" d="M160 304q0 10-3.125 20.5t-10.75 19T128 352t-18.125-8.5-10.75-19T96 304t3.125-20.5 10.75-19T128 256t18.125 8.5 10.75 19T160 304zm160 0q0 10-3.125 20.5t-10.75 19T288 352t-18.125-8.5-10.75-19T256 304t3.125-20.5 10.75-19T288 256t18.125 8.5 10.75 19T320 304zm40 0q0-30-17.25-51T296 232q-10.25 0-48.75 5.25Q229.5 240 208 240t-39.25-2.75Q130.75 232 120 232q-29.5 0-46.75 21T56 304q0 22 8 38.375t20.25 25.75 30.5 15 35 7.375 37.25 1.75h42q20.5 0 37.25-1.75t35-7.375 30.5-15 20.25-25.75T360 304zm56-44q0 51.75-15.25 82.75-9.5 19.25-26.375 33.25t-35.25 21.5-42.5 11.875-42.875 5.5T212 416q-19.5 0-35.5-.75t-36.875-3.125-38.125-7.5-34.25-12.875T37 371.5t-21.5-28.75Q0 312 0 260q0-59.25 34-99-6.75-20.5-6.75-42.5 0-29 12.75-54.5 27 0 47.5 9.875t47.25 30.875Q171.5 96 212 96q37 0 70 8 26.25-20.5 46.75-30.25T376 64q12.75 25.5 12.75 54.5 0 21.75-6.75 42 34 40 34 99.5z"/></svg>
      
    </defs>
  </svg>
  
  <input class="md-toggle" data-md-toggle="drawer" type="checkbox" id="__drawer">
  <input class="md-toggle" data-md-toggle="search" type="checkbox" id="__search">
  <label class="md-overlay" data-md-component="overlay" for="__drawer"></label>
  <a href="#sql/select" tabindex="1" class="md-skip"> Skip to content </a>
  <div id="announcement">
  <div id="announcement-content">
    Presto SQL is now Trino
    <a href="https://trino.io/blog/2020/12/27/announcing-trino.html" target="_blank">Read why &raquo;</a>
  </div>
  <header class="md-header" data-md-component="header">
  <nav class="md-header-nav md-grid">
    <div class="md-flex navheader">
      <div class="md-flex__cell md-flex__cell--shrink">
        <a href="../index.html" title="Trino 355 Documentation"
           class="md-header-nav__button md-logo">
          
              <img src="../_static/trino.svg" height="26"
                   alt="Trino 355 Documentation logo">
          
        </a>
      </div>
      <div class="md-flex__cell md-flex__cell--shrink">
        <label class="md-icon md-icon--menu md-header-nav__button" for="__drawer"></label>
      </div>
      <div class="md-flex__cell md-flex__cell--stretch">
        <div class="md-flex__ellipsis md-header-nav__title" data-md-component="title">
          <span class="md-header-nav__topic">Trino 355 Documentation</span>
          <span class="md-header-nav__topic"> SELECT </span>
        </div>
      </div>
      <div class="md-flex__cell md-flex__cell--shrink">
        <label class="md-icon md-icon--search md-header-nav__button" for="__search"></label>
        
<div class="md-search" data-md-component="search" role="dialog">
  <label class="md-search__overlay" for="__search"></label>
  <div class="md-search__inner" role="search">
    <form class="md-search__form" action="../search.html" method="GET" name="search">
      <input type="text" class="md-search__input" name="q" placeholder="Search"
             autocapitalize="off" autocomplete="off" spellcheck="false"
             data-md-component="query" data-md-state="active">
      <label class="md-icon md-search__icon" for="__search"></label>
      <button type="reset" class="md-icon md-search__icon" data-md-component="reset" tabindex="-1">
        &#xE5CD;
      </button>
    </form>
    <div class="md-search__output">
      <div class="md-search__scrollwrap" data-md-scrollfix>
        <div class="md-search-result" data-md-component="result">
          <div class="md-search-result__meta">
            Type to start searching
          </div>
          <ol class="md-search-result__list"></ol>
        </div>
      </div>
    </div>
  </div>
</div>

      </div>
      
        <div class="md-flex__cell md-flex__cell--shrink">
          <div class="md-header-nav__source">
            <a href="https://github.com/trinodb/trino" title="Go to repository" class="md-source" data-md-source="github">

    <div class="md-source__icon">
      <svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 0 24 24" width="28" height="28">
        <use xlink:href="#__github" width="24" height="24"></use>
      </svg>
    </div>
  
  <div class="md-source__repository">
    Trino
  </div>
</a>
          </div>
        </div>
      
      
  <script src="../_static/javascripts/version_dropdown.js"></script>
  <script>
    var json_loc = "../../versions.json",
        target_loc = "../../",
        text = "Versions";
    $( document ).ready( add_version_dropdown(json_loc, target_loc, text));
  </script>
  

    </div>
  </nav>
</header></div>

  
  <div class="md-container">
    
    
    <!-- empty -->
    <main class="md-main">
      <div class="md-main__inner md-grid" data-md-component="container">
        
          <div class="md-sidebar md-sidebar--primary" data-md-component="navigation">
            <div class="md-sidebar__scrollwrap">
              <div class="md-sidebar__inner">
                <nav class="md-nav md-nav--primary" data-md-level="0">
  <label class="md-nav__title md-nav__title--site" for="__drawer">
    <a href="../index.html" title="Trino 355 Documentation" class="md-nav__button md-logo">
      
        <img src="../_static/trino.svg" alt=" logo" width="48" height="48">
      
    </a>
    <a href="../index.html"
       title="Trino 355 Documentation">Trino 355 Documentation</a>
  </label>
    <div class="md-nav__source">
      <a href="https://github.com/trinodb/trino" title="Go to repository" class="md-source" data-md-source="github">

    <div class="md-source__icon">
      <svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 0 24 24" width="28" height="28">
        <use xlink:href="#__github" width="24" height="24"></use>
      </svg>
    </div>
  
  <div class="md-source__repository">
    Trino
  </div>
</a>
    </div>
  
  
  <ul class="md-nav__list">
    <li class="md-nav__item">
    
    
      <a href="../overview.html" class="md-nav__link">Overview</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../installation.html" class="md-nav__link">Installation</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../client.html" class="md-nav__link">Clients</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../security.html" class="md-nav__link">Security</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../admin.html" class="md-nav__link">Administration</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../optimizer.html" class="md-nav__link">Query optimizer</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../connector.html" class="md-nav__link">Connectors</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../functions.html" class="md-nav__link">Functions and operators</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../language.html" class="md-nav__link">SQL language</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../sql.html" class="md-nav__link">SQL statement syntax</a>
      <ul class="md-nav__list"> 
    <li class="md-nav__item">
    
    
      <a href="alter-schema.html" class="md-nav__link">ALTER SCHEMA</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="alter-table.html" class="md-nav__link">ALTER TABLE</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="alter-view.html" class="md-nav__link">ALTER VIEW</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="analyze.html" class="md-nav__link">ANALYZE</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="call.html" class="md-nav__link">CALL</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="comment.html" class="md-nav__link">COMMENT</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="commit.html" class="md-nav__link">COMMIT</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="create-role.html" class="md-nav__link">CREATE ROLE</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="create-schema.html" class="md-nav__link">CREATE SCHEMA</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="create-table.html" class="md-nav__link">CREATE TABLE</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="create-table-as.html" class="md-nav__link">CREATE TABLE AS</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="create-view.html" class="md-nav__link">CREATE VIEW</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="deallocate-prepare.html" class="md-nav__link">DEALLOCATE PREPARE</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="delete.html" class="md-nav__link">DELETE</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="describe.html" class="md-nav__link">DESCRIBE</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="describe-input.html" class="md-nav__link">DESCRIBE INPUT</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="describe-output.html" class="md-nav__link">DESCRIBE OUTPUT</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="drop-role.html" class="md-nav__link">DROP ROLE</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="drop-schema.html" class="md-nav__link">DROP SCHEMA</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="drop-table.html" class="md-nav__link">DROP TABLE</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="drop-view.html" class="md-nav__link">DROP VIEW</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="execute.html" class="md-nav__link">EXECUTE</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="explain.html" class="md-nav__link">EXPLAIN</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="explain-analyze.html" class="md-nav__link">EXPLAIN ANALYZE</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="grant.html" class="md-nav__link">GRANT</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="grant-roles.html" class="md-nav__link">GRANT ROLES</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="insert.html" class="md-nav__link">INSERT</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="prepare.html" class="md-nav__link">PREPARE</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="reset-session.html" class="md-nav__link">RESET SESSION</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="revoke.html" class="md-nav__link">REVOKE</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="revoke-roles.html" class="md-nav__link">REVOKE ROLES</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="rollback.html" class="md-nav__link">ROLLBACK</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
    <input class="md-toggle md-nav__toggle" data-md-toggle="toc" type="checkbox" id="__toc">
    <label class="md-nav__link md-nav__link--active" for="__toc"> SELECT </label>
    
      <a href="#" class="md-nav__link md-nav__link--active">SELECT</a>
      
        
<nav class="md-nav md-nav--secondary">
    <label class="md-nav__title" for="__toc">Contents</label>
    <ul class="md-nav__list" data-md-scrollfix="">
        <li class="md-nav__item"><a href="#synopsis" class="md-nav__link">Synopsis</a>
        </li>
        <li class="md-nav__item"><a href="#description" class="md-nav__link">Description</a>
        </li>
        <li class="md-nav__item"><a href="#with-clause" class="md-nav__link">WITH clause</a>
        </li>
        <li class="md-nav__item"><a href="#with-recursive-clause" class="md-nav__link">WITH RECURSIVE clause</a>
        </li>
        <li class="md-nav__item"><a href="#select-clause" class="md-nav__link">SELECT clause</a><nav class="md-nav">
                <ul class="md-nav__list">
        <li class="md-nav__item"><a href="#select-expressions" class="md-nav__link">Select expressions</a>
        </li></ul>
            </nav>
        </li>
        <li class="md-nav__item"><a href="#group-by-clause" class="md-nav__link">GROUP BY clause</a><nav class="md-nav">
                <ul class="md-nav__list">
        <li class="md-nav__item"><a href="#complex-grouping-operations" class="md-nav__link">Complex grouping operations</a>
        </li>
        <li class="md-nav__item"><a href="#grouping-sets" class="md-nav__link">GROUPING SETS</a>
        </li>
        <li class="md-nav__item"><a href="#cube" class="md-nav__link">CUBE</a>
        </li>
        <li class="md-nav__item"><a href="#rollup" class="md-nav__link">ROLLUP</a>
        </li>
        <li class="md-nav__item"><a href="#combining-multiple-grouping-expressions" class="md-nav__link">Combining multiple grouping expressions</a>
        </li>
        <li class="md-nav__item"><a href="#grouping-operation" class="md-nav__link">GROUPING operation</a>
        </li></ul>
            </nav>
        </li>
        <li class="md-nav__item"><a href="#having-clause" class="md-nav__link">HAVING clause</a>
        </li>
        <li class="md-nav__item"><a href="#window-clause" class="md-nav__link">WINDOW clause</a>
        </li>
        <li class="md-nav__item"><a href="#set-operations" class="md-nav__link">Set operations</a><nav class="md-nav">
                <ul class="md-nav__list">
        <li class="md-nav__item"><a href="#union-clause" class="md-nav__link">UNION clause</a>
        </li>
        <li class="md-nav__item"><a href="#intersect-clause" class="md-nav__link">INTERSECT clause</a>
        </li>
        <li class="md-nav__item"><a href="#except-clause" class="md-nav__link">EXCEPT clause</a>
        </li></ul>
            </nav>
        </li>
        <li class="md-nav__item"><a href="#order-by-clause" class="md-nav__link">ORDER BY clause</a>
        </li>
        <li class="md-nav__item"><a href="#offset-clause" class="md-nav__link">OFFSET clause</a>
        </li>
        <li class="md-nav__item"><a href="#limit-or-fetch-first-clauses" class="md-nav__link">LIMIT or FETCH FIRST clauses</a>
        </li>
        <li class="md-nav__item"><a href="#tablesample" class="md-nav__link">TABLESAMPLE</a>
        </li>
        <li class="md-nav__item"><a href="#unnest" class="md-nav__link">UNNEST</a>
        </li>
        <li class="md-nav__item"><a href="#joins" class="md-nav__link">Joins</a><nav class="md-nav">
                <ul class="md-nav__list">
        <li class="md-nav__item"><a href="#cross-join" class="md-nav__link">CROSS JOIN</a>
        </li>
        <li class="md-nav__item"><a href="#lateral" class="md-nav__link">LATERAL</a>
        </li>
        <li class="md-nav__item"><a href="#qualifying-column-names" class="md-nav__link">Qualifying column names</a>
        </li></ul>
            </nav>
        </li>
        <li class="md-nav__item"><a href="#subqueries" class="md-nav__link">Subqueries</a><nav class="md-nav">
                <ul class="md-nav__list">
        <li class="md-nav__item"><a href="#exists" class="md-nav__link">EXISTS</a>
        </li>
        <li class="md-nav__item"><a href="#in" class="md-nav__link">IN</a>
        </li>
        <li class="md-nav__item"><a href="#scalar-subquery" class="md-nav__link">Scalar subquery</a>
        </li></ul>
            </nav>
        </li>
    </ul>
</nav>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="set-role.html" class="md-nav__link">SET ROLE</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="set-session.html" class="md-nav__link">SET SESSION</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="show-catalogs.html" class="md-nav__link">SHOW CATALOGS</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="show-columns.html" class="md-nav__link">SHOW COLUMNS</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="show-create-schema.html" class="md-nav__link">SHOW CREATE SCHEMA</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="show-create-table.html" class="md-nav__link">SHOW CREATE TABLE</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="show-create-view.html" class="md-nav__link">SHOW CREATE VIEW</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="show-functions.html" class="md-nav__link">SHOW FUNCTIONS</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="show-grants.html" class="md-nav__link">SHOW GRANTS</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="show-role-grants.html" class="md-nav__link">SHOW ROLE GRANTS</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="show-roles.html" class="md-nav__link">SHOW ROLES</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="show-schemas.html" class="md-nav__link">SHOW SCHEMAS</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="show-session.html" class="md-nav__link">SHOW SESSION</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="show-stats.html" class="md-nav__link">SHOW STATS</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="show-tables.html" class="md-nav__link">SHOW TABLES</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="start-transaction.html" class="md-nav__link">START TRANSACTION</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="update.html" class="md-nav__link">UPDATE</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="use.html" class="md-nav__link">USE</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="values.html" class="md-nav__link">VALUES</a>
      
    
    </li></ul>
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../develop.html" class="md-nav__link">Developer guide</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../appendix.html" class="md-nav__link">Appendix</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../release.html" class="md-nav__link">Release notes</a>
      
    
    </li>
  </ul>
  

</nav>
              </div>
            </div>
          </div>
          <div class="md-sidebar md-sidebar--secondary" data-md-component="toc">
            <div class="md-sidebar__scrollwrap">
              <div class="md-sidebar__inner">
                
<nav class="md-nav md-nav--secondary">
    <label class="md-nav__title" for="__toc">Contents</label>
    <ul class="md-nav__list" data-md-scrollfix="">
        <li class="md-nav__item"><a href="#synopsis" class="md-nav__link">Synopsis</a>
        </li>
        <li class="md-nav__item"><a href="#description" class="md-nav__link">Description</a>
        </li>
        <li class="md-nav__item"><a href="#with-clause" class="md-nav__link">WITH clause</a>
        </li>
        <li class="md-nav__item"><a href="#with-recursive-clause" class="md-nav__link">WITH RECURSIVE clause</a>
        </li>
        <li class="md-nav__item"><a href="#select-clause" class="md-nav__link">SELECT clause</a><nav class="md-nav">
                <ul class="md-nav__list">
        <li class="md-nav__item"><a href="#select-expressions" class="md-nav__link">Select expressions</a>
        </li></ul>
            </nav>
        </li>
        <li class="md-nav__item"><a href="#group-by-clause" class="md-nav__link">GROUP BY clause</a><nav class="md-nav">
                <ul class="md-nav__list">
        <li class="md-nav__item"><a href="#complex-grouping-operations" class="md-nav__link">Complex grouping operations</a>
        </li>
        <li class="md-nav__item"><a href="#grouping-sets" class="md-nav__link">GROUPING SETS</a>
        </li>
        <li class="md-nav__item"><a href="#cube" class="md-nav__link">CUBE</a>
        </li>
        <li class="md-nav__item"><a href="#rollup" class="md-nav__link">ROLLUP</a>
        </li>
        <li class="md-nav__item"><a href="#combining-multiple-grouping-expressions" class="md-nav__link">Combining multiple grouping expressions</a>
        </li>
        <li class="md-nav__item"><a href="#grouping-operation" class="md-nav__link">GROUPING operation</a>
        </li></ul>
            </nav>
        </li>
        <li class="md-nav__item"><a href="#having-clause" class="md-nav__link">HAVING clause</a>
        </li>
        <li class="md-nav__item"><a href="#window-clause" class="md-nav__link">WINDOW clause</a>
        </li>
        <li class="md-nav__item"><a href="#set-operations" class="md-nav__link">Set operations</a><nav class="md-nav">
                <ul class="md-nav__list">
        <li class="md-nav__item"><a href="#union-clause" class="md-nav__link">UNION clause</a>
        </li>
        <li class="md-nav__item"><a href="#intersect-clause" class="md-nav__link">INTERSECT clause</a>
        </li>
        <li class="md-nav__item"><a href="#except-clause" class="md-nav__link">EXCEPT clause</a>
        </li></ul>
            </nav>
        </li>
        <li class="md-nav__item"><a href="#order-by-clause" class="md-nav__link">ORDER BY clause</a>
        </li>
        <li class="md-nav__item"><a href="#offset-clause" class="md-nav__link">OFFSET clause</a>
        </li>
        <li class="md-nav__item"><a href="#limit-or-fetch-first-clauses" class="md-nav__link">LIMIT or FETCH FIRST clauses</a>
        </li>
        <li class="md-nav__item"><a href="#tablesample" class="md-nav__link">TABLESAMPLE</a>
        </li>
        <li class="md-nav__item"><a href="#unnest" class="md-nav__link">UNNEST</a>
        </li>
        <li class="md-nav__item"><a href="#joins" class="md-nav__link">Joins</a><nav class="md-nav">
                <ul class="md-nav__list">
        <li class="md-nav__item"><a href="#cross-join" class="md-nav__link">CROSS JOIN</a>
        </li>
        <li class="md-nav__item"><a href="#lateral" class="md-nav__link">LATERAL</a>
        </li>
        <li class="md-nav__item"><a href="#qualifying-column-names" class="md-nav__link">Qualifying column names</a>
        </li></ul>
            </nav>
        </li>
        <li class="md-nav__item"><a href="#subqueries" class="md-nav__link">Subqueries</a><nav class="md-nav">
                <ul class="md-nav__list">
        <li class="md-nav__item"><a href="#exists" class="md-nav__link">EXISTS</a>
        </li>
        <li class="md-nav__item"><a href="#in" class="md-nav__link">IN</a>
        </li>
        <li class="md-nav__item"><a href="#scalar-subquery" class="md-nav__link">Scalar subquery</a>
        </li></ul>
            </nav>
        </li>
    </ul>
</nav>
              </div>
            </div>
          </div>
        
        <div class="md-content">
          <article class="md-content__inner md-typeset" role="main">
            
  
<h1 id="sql-select--page-root">SELECT<a class="headerlink" href="#sql-select--page-root" title="Permalink to this headline">#</a></h1>

<h2 id="synopsis">Synopsis<a class="headerlink" href="#synopsis" title="Permalink to this headline">#</a></h2>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT ] select_expression [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ]
[ HAVING condition]
[ WINDOW window_definition_list]
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
[ ORDER BY expression [ ASC | DESC ] [, ...] ]
[ OFFSET count [ ROW | ROWS ] ]
[ LIMIT { count | ALL } ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES } ]
</pre></div>
</div>
<p>where <code class="docutils literal notranslate"><span class="pre">from_item</span></code> is one of</p>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>table_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>from_item join_type from_item
  [ ON join_condition | USING ( join_column [, ...] ) ]
</pre></div>
</div>
<p>and <code class="docutils literal notranslate"><span class="pre">join_type</span></code> is one of</p>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>[ INNER ] JOIN
LEFT [ OUTER ] JOIN
RIGHT [ OUTER ] JOIN
FULL [ OUTER ] JOIN
CROSS JOIN
</pre></div>
</div>
<p>and <code class="docutils literal notranslate"><span class="pre">grouping_element</span></code> is one of</p>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>()
expression
GROUPING SETS ( ( column [, ...] ) [, ...] )
CUBE ( column [, ...] )
ROLLUP ( column [, ...] )
</pre></div>
</div>


<h2 id="description">Description<a class="headerlink" href="#description" title="Permalink to this headline">#</a></h2>
<p>Retrieve rows from zero or more tables.</p>


<h2 id="with-clause">WITH clause<a class="headerlink" href="#with-clause" title="Permalink to this headline">#</a></h2>
<p>The <code class="docutils literal notranslate"><span class="pre">WITH</span></code> clause defines named relations for use within a query.
It allows flattening nested queries or simplifying subqueries.
For example, the following queries are equivalent:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">a</span><span class="p">,</span> <span class="n">b</span>
<span class="k">FROM</span> <span class="p">(</span>
  <span class="k">SELECT</span> <span class="n">a</span><span class="p">,</span> <span class="k">MAX</span><span class="p">(</span><span class="n">b</span><span class="p">)</span> <span class="k">AS</span> <span class="n">b</span> <span class="k">FROM</span> <span class="n">t</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">a</span>
<span class="p">)</span> <span class="k">AS</span> <span class="n">x</span><span class="p">;</span>

<span class="k">WITH</span> <span class="n">x</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">a</span><span class="p">,</span> <span class="k">MAX</span><span class="p">(</span><span class="n">b</span><span class="p">)</span> <span class="k">AS</span> <span class="n">b</span> <span class="k">FROM</span> <span class="n">t</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">a</span><span class="p">)</span>
<span class="k">SELECT</span> <span class="n">a</span><span class="p">,</span> <span class="n">b</span> <span class="k">FROM</span> <span class="n">x</span><span class="p">;</span>
</pre></div>
</div>
<p>This also works with multiple subqueries:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">WITH</span>
  <span class="n">t1</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">a</span><span class="p">,</span> <span class="k">MAX</span><span class="p">(</span><span class="n">b</span><span class="p">)</span> <span class="k">AS</span> <span class="n">b</span> <span class="k">FROM</span> <span class="n">x</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">a</span><span class="p">),</span>
  <span class="n">t2</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">a</span><span class="p">,</span> <span class="k">AVG</span><span class="p">(</span><span class="n">d</span><span class="p">)</span> <span class="k">AS</span> <span class="n">d</span> <span class="k">FROM</span> <span class="n">y</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">a</span><span class="p">)</span>
<span class="k">SELECT</span> <span class="n">t1</span><span class="p">.</span><span class="o">*</span><span class="p">,</span> <span class="n">t2</span><span class="p">.</span><span class="o">*</span>
<span class="k">FROM</span> <span class="n">t1</span>
<span class="k">JOIN</span> <span class="n">t2</span> <span class="k">ON</span> <span class="n">t1</span><span class="p">.</span><span class="n">a</span> <span class="o">=</span> <span class="n">t2</span><span class="p">.</span><span class="n">a</span><span class="p">;</span>
</pre></div>
</div>
<p>Additionally, the relations within a <code class="docutils literal notranslate"><span class="pre">WITH</span></code> clause can chain:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">WITH</span>
  <span class="n">x</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">a</span> <span class="k">FROM</span> <span class="n">t</span><span class="p">),</span>
  <span class="n">y</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">a</span> <span class="k">AS</span> <span class="n">b</span> <span class="k">FROM</span> <span class="n">x</span><span class="p">),</span>
  <span class="n">z</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">b</span> <span class="k">AS</span> <span class="k">c</span> <span class="k">FROM</span> <span class="n">y</span><span class="p">)</span>
<span class="k">SELECT</span> <span class="k">c</span> <span class="k">FROM</span> <span class="n">z</span><span class="p">;</span>
</pre></div>
</div>
<div class="admonition warning">
<p class="admonition-title">Warning</p>
<p>Currently, the SQL for the <code class="docutils literal notranslate"><span class="pre">WITH</span></code> clause will be inlined anywhere the named
relation is used. This means that if the relation is used more than once and the query
is non-deterministic, the results may be different each time.</p>
</div>


<h2 id="with-recursive-clause">WITH RECURSIVE clause<a class="headerlink" href="#with-recursive-clause" title="Permalink to this headline">#</a></h2>
<p>The <code class="docutils literal notranslate"><span class="pre">WITH</span> <span class="pre">RECURSIVE</span></code> clause is a variant of the <code class="docutils literal notranslate"><span class="pre">WITH</span></code> clause. It defines
a list of queries to process, including recursive processing of suitable
queries.</p>
<div class="admonition warning">
<p class="admonition-title">Warning</p>
<p>This feature is experimental only. Proceed to use it only if you understand
potential query failures and the impact of the recursion processing on your
workload.</p>
</div>
<p>A recursive <code class="docutils literal notranslate"><span class="pre">WITH</span></code>-query must be shaped as a <code class="docutils literal notranslate"><span class="pre">UNION</span></code> of two relations. The
first relation is called the <em>recursion base</em>, and the second relation is called
the <em>recursion step</em>. Trino supports recursive <code class="docutils literal notranslate"><span class="pre">WITH</span></code>-queries with a single
recursive reference to a <code class="docutils literal notranslate"><span class="pre">WITH</span></code>-query from within the query. The name <code class="docutils literal notranslate"><span class="pre">T</span></code> of
the query <code class="docutils literal notranslate"><span class="pre">T</span></code> can be mentioned once in the <code class="docutils literal notranslate"><span class="pre">FROM</span></code> clause of the recursion
step relation.</p>
<p>The following listing shows a simple example, that displays a commonly used
form of a single query in the list:</p>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>WITH RECURSIVE t(n) AS (
    VALUES (1)
    UNION ALL
    SELECT n + 1 FROM t WHERE n &lt; 4
)
SELECT sum(n) FROM t;
</pre></div>
</div>
<p>In the preceding query the simple assignment <code class="docutils literal notranslate"><span class="pre">VALUES</span> <span class="pre">(1)</span></code> defines the
recursion base relation. <code class="docutils literal notranslate"><span class="pre">SELECT</span> <span class="pre">n</span> <span class="pre">+</span> <span class="pre">1</span> <span class="pre">FROM</span> <span class="pre">t</span> <span class="pre">WHERE</span> <span class="pre">n</span> <span class="pre">&lt;</span> <span class="pre">4</span></code> defines the
recursion step relation. The recursion processing performs these steps:</p>
<ul class="simple">
<li><p>recursive base yields <code class="docutils literal notranslate"><span class="pre">1</span></code></p></li>
<li><p>first recursion yields <code class="docutils literal notranslate"><span class="pre">1</span> <span class="pre">+</span> <span class="pre">1</span> <span class="pre">=</span> <span class="pre">2</span></code></p></li>
<li><p>second recursion uses the result from the first and adds one: <code class="docutils literal notranslate"><span class="pre">2</span> <span class="pre">+</span> <span class="pre">1</span> <span class="pre">=</span> <span class="pre">3</span></code></p></li>
<li><p>third recursion uses the result from the second and adds one again:
<code class="docutils literal notranslate"><span class="pre">3</span> <span class="pre">+</span> <span class="pre">1</span> <span class="pre">=</span> <span class="pre">4</span></code></p></li>
<li><p>fourth recursion aborts since <code class="docutils literal notranslate"><span class="pre">n</span> <span class="pre">=</span> <span class="pre">4</span></code></p></li>
<li><p>this results in <code class="docutils literal notranslate"><span class="pre">t</span></code> having values <code class="docutils literal notranslate"><span class="pre">1</span></code>, <code class="docutils literal notranslate"><span class="pre">2</span></code>, <code class="docutils literal notranslate"><span class="pre">3</span></code> and <code class="docutils literal notranslate"><span class="pre">4</span></code></p></li>
<li><p>the final statement performs the sum operation of these elements with the
final result value <code class="docutils literal notranslate"><span class="pre">10</span></code></p></li>
</ul>
<p>The types of the returned columns are those of the base relation. Therefore it
is required that types in the step relation can be coerced to base relation
types.</p>
<p>The <code class="docutils literal notranslate"><span class="pre">RECURSIVE</span></code> clause applies to all queries in the <code class="docutils literal notranslate"><span class="pre">WITH</span></code> list, but not
all of them must be recursive. If a <code class="docutils literal notranslate"><span class="pre">WITH</span></code>-query is not shaped according to
the rules mentioned above or it does not contain a recursive reference, it is
processed like a regular <code class="docutils literal notranslate"><span class="pre">WITH</span></code>-query. Column aliases are mandatory for all
the queries in the recursive <code class="docutils literal notranslate"><span class="pre">WITH</span></code> list.</p>
<p>The following limitations apply as a result of following the SQL standard and
due to implementation choices, in addition to <code class="docutils literal notranslate"><span class="pre">WITH</span></code> clause limitations:</p>
<ul class="simple">
<li><p>only single-element recursive cycles are supported. Like in regular
<code class="docutils literal notranslate"><span class="pre">WITH</span></code>-queries, references to previous queries in the <code class="docutils literal notranslate"><span class="pre">WITH</span></code> list are
allowed. References to following queries are forbidden.</p></li>
<li><p>usage of outer joins, set operations, limit clause, and others is not always
allowed in the step relation</p></li>
<li><p>recursion depth is fixed, defaults to <code class="docutils literal notranslate"><span class="pre">10</span></code>, and doesn’t depend on the actual
query results</p></li>
</ul>
<p>You can adjust the recursion depth with the <a class="reference internal" href="set-session.html"><span class="doc">session property</span></a> <code class="docutils literal notranslate"><span class="pre">max_recursion_depth</span></code>. When changing the value consider
that the size of the query plan growth is quadratic with the recursion depth.</p>


<h2 id="select-clause">SELECT clause<a class="headerlink" href="#select-clause" title="Permalink to this headline">#</a></h2>
<p>The <code class="docutils literal notranslate"><span class="pre">SELECT</span></code> clause specifies the output of the query. Each <code class="docutils literal notranslate"><span class="pre">select_expression</span></code>
defines a column or columns to be included in the result.</p>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>SELECT [ ALL | DISTINCT ] select_expression [, ...]
</pre></div>
</div>
<p>The <code class="docutils literal notranslate"><span class="pre">ALL</span></code> and <code class="docutils literal notranslate"><span class="pre">DISTINCT</span></code> quantifiers determine whether duplicate rows
are included in the result set. If the argument <code class="docutils literal notranslate"><span class="pre">ALL</span></code> is specified,
all rows are included. If the argument <code class="docutils literal notranslate"><span class="pre">DISTINCT</span></code> is specified, only unique
rows are included in the result set. In this case, each output column must
be of a type that allows comparison. If neither argument is specified,
the behavior defaults to <code class="docutils literal notranslate"><span class="pre">ALL</span></code>.</p>

<h3 id="select-expressions">Select expressions<a class="headerlink" href="#select-expressions" title="Permalink to this headline">#</a></h3>
<p>Each <code class="docutils literal notranslate"><span class="pre">select_expression</span></code> must be in one of the following forms:</p>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>expression [ [ AS ] column_alias ]
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>row_expression.* [ AS ( column_alias [, ...] ) ]
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>relation.*
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>*
</pre></div>
</div>
<p>In the case of <code class="docutils literal notranslate"><span class="pre">expression</span> <span class="pre">[</span> <span class="pre">[</span> <span class="pre">AS</span> <span class="pre">]</span> <span class="pre">column_alias</span> <span class="pre">]</span></code>, a single output column
is defined.</p>
<p>In the case of <code class="docutils literal notranslate"><span class="pre">row_expression.*</span> <span class="pre">[</span> <span class="pre">AS</span> <span class="pre">(</span> <span class="pre">column_alias</span> <span class="pre">[,</span> <span class="pre">...]</span> <span class="pre">)</span> <span class="pre">]</span></code>,
the <code class="docutils literal notranslate"><span class="pre">row_expression</span></code> is an arbitrary expression of type <code class="docutils literal notranslate"><span class="pre">ROW</span></code>.
All fields of the row define output columns to be included in the result set.</p>
<p>In the case of <code class="docutils literal notranslate"><span class="pre">relation.*</span></code>, all columns of <code class="docutils literal notranslate"><span class="pre">relation</span></code> are included
in the result set. In this case column aliases are not allowed.</p>
<p>In the case of <code class="docutils literal notranslate"><span class="pre">*</span></code>, all columns of the relation defined by the query
are included in the result set.</p>
<p>In the result set, the order of columns is the same as the order of their
specification by the select expressions. If a select expression returns multiple
columns, they are ordered the same way they were ordered in the source
relation or row type expression.</p>
<p>If column aliases are specified, they override any preexisting column
or row field names:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="p">(</span><span class="k">CAST</span><span class="p">(</span><span class="k">ROW</span><span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="k">true</span><span class="p">)</span> <span class="k">AS</span> <span class="k">ROW</span><span class="p">(</span><span class="n">field1</span> <span class="nb">bigint</span><span class="p">,</span> <span class="n">field2</span> <span class="nb">boolean</span><span class="p">))).</span><span class="o">*</span> <span class="k">AS</span> <span class="p">(</span><span class="n">alias1</span><span class="p">,</span> <span class="n">alias2</span><span class="p">);</span>
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span> alias1 | alias2
--------+--------
      1 | true
(1 row)
</pre></div>
</div>
<p>Otherwise, the existing names are used:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="p">(</span><span class="k">CAST</span><span class="p">(</span><span class="k">ROW</span><span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="k">true</span><span class="p">)</span> <span class="k">AS</span> <span class="k">ROW</span><span class="p">(</span><span class="n">field1</span> <span class="nb">bigint</span><span class="p">,</span> <span class="n">field2</span> <span class="nb">boolean</span><span class="p">))).</span><span class="o">*</span><span class="p">;</span>
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span> field1 | field2
--------+--------
      1 | true
(1 row)
</pre></div>
</div>
<p>and in their absence, anonymous columns are produced:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="p">(</span><span class="k">ROW</span><span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="k">true</span><span class="p">)).</span><span class="o">*</span><span class="p">;</span>
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span> _col0 | _col1
-------+-------
     1 | true
(1 row)
</pre></div>
</div>


<h2 id="group-by-clause">GROUP BY clause<a class="headerlink" href="#group-by-clause" title="Permalink to this headline">#</a></h2>
<p>The <code class="docutils literal notranslate"><span class="pre">GROUP</span> <span class="pre">BY</span></code> clause divides the output of a <code class="docutils literal notranslate"><span class="pre">SELECT</span></code> statement into
groups of rows containing matching values. A simple <code class="docutils literal notranslate"><span class="pre">GROUP</span> <span class="pre">BY</span></code> clause may
contain any expression composed of input columns or it may be an ordinal
number selecting an output column by position (starting at one).</p>
<p>The following queries are equivalent. They both group the output by
the <code class="docutils literal notranslate"><span class="pre">nationkey</span></code> input column with the first query using the ordinal
position of the output column and the second query using the input
column name:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="k">count</span><span class="p">(</span><span class="o">*</span><span class="p">),</span> <span class="n">nationkey</span> <span class="k">FROM</span> <span class="n">customer</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="mi">2</span><span class="p">;</span>

<span class="k">SELECT</span> <span class="k">count</span><span class="p">(</span><span class="o">*</span><span class="p">),</span> <span class="n">nationkey</span> <span class="k">FROM</span> <span class="n">customer</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">nationkey</span><span class="p">;</span>
</pre></div>
</div>
<p><code class="docutils literal notranslate"><span class="pre">GROUP</span> <span class="pre">BY</span></code> clauses can group output by input column names not appearing in
the output of a select statement. For example, the following query generates
row counts for the <code class="docutils literal notranslate"><span class="pre">customer</span></code> table using the input column <code class="docutils literal notranslate"><span class="pre">mktsegment</span></code>:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="k">count</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">customer</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">mktsegment</span><span class="p">;</span>
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span> _col0
-------
 29968
 30142
 30189
 29949
 29752
(5 rows)
</pre></div>
</div>
<p>When a <code class="docutils literal notranslate"><span class="pre">GROUP</span> <span class="pre">BY</span></code> clause is used in a <code class="docutils literal notranslate"><span class="pre">SELECT</span></code> statement all output
expressions must be either aggregate functions or columns present in
the <code class="docutils literal notranslate"><span class="pre">GROUP</span> <span class="pre">BY</span></code> clause.</p>

<span id="id1"></span><h3 id="complex-grouping-operations">Complex grouping operations<a class="headerlink" href="#complex-grouping-operations" title="Permalink to this headline">#</a></h3>
<p>Trino also supports complex aggregations using the <code class="docutils literal notranslate"><span class="pre">GROUPING</span> <span class="pre">SETS</span></code>, <code class="docutils literal notranslate"><span class="pre">CUBE</span></code>
and <code class="docutils literal notranslate"><span class="pre">ROLLUP</span></code> syntax. This syntax allows users to perform analysis that requires
aggregation on multiple sets of columns in a single query. Complex grouping
operations do not support grouping on expressions composed of input columns.
Only column names or ordinals are allowed.</p>
<p>Complex grouping operations are often equivalent to a <code class="docutils literal notranslate"><span class="pre">UNION</span> <span class="pre">ALL</span></code> of simple
<code class="docutils literal notranslate"><span class="pre">GROUP</span> <span class="pre">BY</span></code> expressions, as shown in the following examples. This equivalence
does not apply, however, when the source of data for the aggregation
is non-deterministic.</p>


<h3 id="grouping-sets">GROUPING SETS<a class="headerlink" href="#grouping-sets" title="Permalink to this headline">#</a></h3>
<p>Grouping sets allow users to specify multiple lists of columns to group on.
The columns not part of a given sublist of grouping columns are set to <code class="docutils literal notranslate"><span class="pre">NULL</span></code>.</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">shipping</span><span class="p">;</span>
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span> origin_state | origin_zip | destination_state | destination_zip | package_weight
--------------+------------+-------------------+-----------------+----------------
 California   |      94131 | New Jersey        |            8648 |             13
 California   |      94131 | New Jersey        |            8540 |             42
 New Jersey   |       7081 | Connecticut       |            6708 |            225
 California   |      90210 | Connecticut       |            6927 |           1337
 California   |      94131 | Colorado          |           80302 |              5
 New York     |      10002 | New Jersey        |            8540 |              3
(6 rows)
</pre></div>
</div>
<p><code class="docutils literal notranslate"><span class="pre">GROUPING</span> <span class="pre">SETS</span></code> semantics are demonstrated by this example query:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">origin_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">package_weight</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">shipping</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="k">GROUPING</span> <span class="k">SETS</span> <span class="p">(</span>
    <span class="p">(</span><span class="n">origin_state</span><span class="p">),</span>
    <span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">),</span>
    <span class="p">(</span><span class="n">destination_state</span><span class="p">));</span>
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span> origin_state | origin_zip | destination_state | _col0
--------------+------------+-------------------+-------
 New Jersey   | NULL       | NULL              |   225
 California   | NULL       | NULL              |  1397
 New York     | NULL       | NULL              |     3
 California   |      90210 | NULL              |  1337
 California   |      94131 | NULL              |    60
 New Jersey   |       7081 | NULL              |   225
 New York     |      10002 | NULL              |     3
 NULL         | NULL       | Colorado          |     5
 NULL         | NULL       | New Jersey        |    58
 NULL         | NULL       | Connecticut       |  1562
(10 rows)
</pre></div>
</div>
<p>The preceding query may be considered logically equivalent to a <code class="docutils literal notranslate"><span class="pre">UNION</span> <span class="pre">ALL</span></code> of
multiple <code class="docutils literal notranslate"><span class="pre">GROUP</span> <span class="pre">BY</span></code> queries:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">origin_state</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">package_weight</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">shipping</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">origin_state</span>

<span class="k">UNION</span> <span class="k">ALL</span>

<span class="k">SELECT</span> <span class="n">origin_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">package_weight</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">shipping</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">origin_state</span><span class="p">,</span> <span class="n">origin_zip</span>

<span class="k">UNION</span> <span class="k">ALL</span>

<span class="k">SELECT</span> <span class="k">NULL</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">package_weight</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">shipping</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">destination_state</span><span class="p">;</span>
</pre></div>
</div>
<p>However, the query with the complex grouping syntax (<code class="docutils literal notranslate"><span class="pre">GROUPING</span> <span class="pre">SETS</span></code>, <code class="docutils literal notranslate"><span class="pre">CUBE</span></code>
or <code class="docutils literal notranslate"><span class="pre">ROLLUP</span></code>) will only read from the underlying data source once, while the
query with the <code class="docutils literal notranslate"><span class="pre">UNION</span> <span class="pre">ALL</span></code> reads the underlying data three times. This is why
queries with a <code class="docutils literal notranslate"><span class="pre">UNION</span> <span class="pre">ALL</span></code> may produce inconsistent results when the data
source is not deterministic.</p>


<h3 id="cube">CUBE<a class="headerlink" href="#cube" title="Permalink to this headline">#</a></h3>
<p>The <code class="docutils literal notranslate"><span class="pre">CUBE</span></code> operator generates all possible grouping sets (i.e. a power set)
for a given set of columns. For example, the query:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">package_weight</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">shipping</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="k">CUBE</span> <span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">);</span>
</pre></div>
</div>
<p>is equivalent to:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">package_weight</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">shipping</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="k">GROUPING</span> <span class="k">SETS</span> <span class="p">(</span>
    <span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">),</span>
    <span class="p">(</span><span class="n">origin_state</span><span class="p">),</span>
    <span class="p">(</span><span class="n">destination_state</span><span class="p">),</span>
    <span class="p">()</span>
<span class="p">);</span>
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span> origin_state | destination_state | _col0
--------------+-------------------+-------
 California   | New Jersey        |    55
 California   | Colorado          |     5
 New York     | New Jersey        |     3
 New Jersey   | Connecticut       |   225
 California   | Connecticut       |  1337
 California   | NULL              |  1397
 New York     | NULL              |     3
 New Jersey   | NULL              |   225
 NULL         | New Jersey        |    58
 NULL         | Connecticut       |  1562
 NULL         | Colorado          |     5
 NULL         | NULL              |  1625
(12 rows)
</pre></div>
</div>


<h3 id="rollup">ROLLUP<a class="headerlink" href="#rollup" title="Permalink to this headline">#</a></h3>
<p>The <code class="docutils literal notranslate"><span class="pre">ROLLUP</span></code> operator generates all possible subtotals for a given set of
columns. For example, the query:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">origin_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">package_weight</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">shipping</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="k">ROLLUP</span> <span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">);</span>
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span> origin_state | origin_zip | _col2
--------------+------------+-------
 California   |      94131 |    60
 California   |      90210 |  1337
 New Jersey   |       7081 |   225
 New York     |      10002 |     3
 California   | NULL       |  1397
 New York     | NULL       |     3
 New Jersey   | NULL       |   225
 NULL         | NULL       |  1625
(8 rows)
</pre></div>
</div>
<p>is equivalent to:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">origin_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">package_weight</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">shipping</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="k">GROUPING</span> <span class="k">SETS</span> <span class="p">((</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">),</span> <span class="p">(</span><span class="n">origin_state</span><span class="p">),</span> <span class="p">());</span>
</pre></div>
</div>


<h3 id="combining-multiple-grouping-expressions">Combining multiple grouping expressions<a class="headerlink" href="#combining-multiple-grouping-expressions" title="Permalink to this headline">#</a></h3>
<p>Multiple grouping expressions in the same query are interpreted as having
cross-product semantics. For example, the following query:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">package_weight</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">shipping</span>
<span class="k">GROUP</span> <span class="k">BY</span>
    <span class="k">GROUPING</span> <span class="k">SETS</span> <span class="p">((</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">)),</span>
    <span class="k">ROLLUP</span> <span class="p">(</span><span class="n">origin_zip</span><span class="p">);</span>
</pre></div>
</div>
<p>which can be rewritten as:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">package_weight</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">shipping</span>
<span class="k">GROUP</span> <span class="k">BY</span>
    <span class="k">GROUPING</span> <span class="k">SETS</span> <span class="p">((</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">)),</span>
    <span class="k">GROUPING</span> <span class="k">SETS</span> <span class="p">((</span><span class="n">origin_zip</span><span class="p">),</span> <span class="p">());</span>
</pre></div>
</div>
<p>is logically equivalent to:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">package_weight</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">shipping</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="k">GROUPING</span> <span class="k">SETS</span> <span class="p">(</span>
    <span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">),</span>
    <span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">)</span>
<span class="p">);</span>
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span> origin_state | destination_state | origin_zip | _col3
--------------+-------------------+------------+-------
 New York     | New Jersey        |      10002 |     3
 California   | New Jersey        |      94131 |    55
 New Jersey   | Connecticut       |       7081 |   225
 California   | Connecticut       |      90210 |  1337
 California   | Colorado          |      94131 |     5
 New York     | New Jersey        | NULL       |     3
 New Jersey   | Connecticut       | NULL       |   225
 California   | Colorado          | NULL       |     5
 California   | Connecticut       | NULL       |  1337
 California   | New Jersey        | NULL       |    55
(10 rows)
</pre></div>
</div>
<p>The <code class="docutils literal notranslate"><span class="pre">ALL</span></code> and <code class="docutils literal notranslate"><span class="pre">DISTINCT</span></code> quantifiers determine whether duplicate grouping
sets each produce distinct output rows. This is particularly useful when
multiple complex grouping sets are combined in the same query. For example, the
following query:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">package_weight</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">shipping</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="k">ALL</span>
    <span class="k">CUBE</span> <span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">),</span>
    <span class="k">ROLLUP</span> <span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">);</span>
</pre></div>
</div>
<p>is equivalent to:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">package_weight</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">shipping</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="k">GROUPING</span> <span class="k">SETS</span> <span class="p">(</span>
    <span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">),</span>
    <span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">),</span>
    <span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">),</span>
    <span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">),</span>
    <span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">),</span>
    <span class="p">(</span><span class="n">origin_state</span><span class="p">),</span>
    <span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">),</span>
    <span class="p">(</span><span class="n">origin_state</span><span class="p">),</span>
    <span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">),</span>
    <span class="p">(</span><span class="n">origin_state</span><span class="p">),</span>
    <span class="p">(</span><span class="n">destination_state</span><span class="p">),</span>
    <span class="p">()</span>
<span class="p">);</span>
</pre></div>
</div>
<p>However, if the query uses the <code class="docutils literal notranslate"><span class="pre">DISTINCT</span></code> quantifier for the <code class="docutils literal notranslate"><span class="pre">GROUP</span> <span class="pre">BY</span></code>:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">package_weight</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">shipping</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="k">DISTINCT</span>
    <span class="k">CUBE</span> <span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">),</span>
    <span class="k">ROLLUP</span> <span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">);</span>
</pre></div>
</div>
<p>only unique grouping sets are generated:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">package_weight</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">shipping</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="k">GROUPING</span> <span class="k">SETS</span> <span class="p">(</span>
    <span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">),</span>
    <span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">),</span>
    <span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">),</span>
    <span class="p">(</span><span class="n">origin_state</span><span class="p">),</span>
    <span class="p">(</span><span class="n">destination_state</span><span class="p">),</span>
    <span class="p">()</span>
<span class="p">);</span>
</pre></div>
</div>
<p>The default set quantifier is <code class="docutils literal notranslate"><span class="pre">ALL</span></code>.</p>


<h3 id="grouping-operation">GROUPING operation<a class="headerlink" href="#grouping-operation" title="Permalink to this headline">#</a></h3>
<p><code class="docutils literal notranslate"><span class="pre">grouping(col1,</span> <span class="pre">...,</span> <span class="pre">colN)</span> <span class="pre">-&gt;</span> <span class="pre">bigint</span></code></p>
<p>The grouping operation returns a bit set converted to decimal, indicating which columns are present in a
grouping. It must be used in conjunction with <code class="docutils literal notranslate"><span class="pre">GROUPING</span> <span class="pre">SETS</span></code>, <code class="docutils literal notranslate"><span class="pre">ROLLUP</span></code>, <code class="docutils literal notranslate"><span class="pre">CUBE</span></code>  or <code class="docutils literal notranslate"><span class="pre">GROUP</span> <span class="pre">BY</span></code>
and its arguments must match exactly the columns referenced in the corresponding <code class="docutils literal notranslate"><span class="pre">GROUPING</span> <span class="pre">SETS</span></code>,
<code class="docutils literal notranslate"><span class="pre">ROLLUP</span></code>, <code class="docutils literal notranslate"><span class="pre">CUBE</span></code> or <code class="docutils literal notranslate"><span class="pre">GROUP</span> <span class="pre">BY</span></code> clause.</p>
<p>To compute the resulting bit set for a particular row, bits are assigned to the argument columns with
the rightmost column being the least significant bit. For a given grouping, a bit is set to 0 if the
corresponding column is included in the grouping and to 1 otherwise. For example, consider the query
below:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">origin_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">package_weight</span><span class="p">),</span>
       <span class="k">grouping</span><span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">shipping</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="k">GROUPING</span> <span class="k">SETS</span> <span class="p">(</span>
    <span class="p">(</span><span class="n">origin_state</span><span class="p">),</span>
    <span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">),</span>
    <span class="p">(</span><span class="n">destination_state</span><span class="p">)</span>
<span class="p">);</span>
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>origin_state | origin_zip | destination_state | _col3 | _col4
--------------+------------+-------------------+-------+-------
California   | NULL       | NULL              |  1397 |     3
New Jersey   | NULL       | NULL              |   225 |     3
New York     | NULL       | NULL              |     3 |     3
California   |      94131 | NULL              |    60 |     1
New Jersey   |       7081 | NULL              |   225 |     1
California   |      90210 | NULL              |  1337 |     1
New York     |      10002 | NULL              |     3 |     1
NULL         | NULL       | New Jersey        |    58 |     6
NULL         | NULL       | Connecticut       |  1562 |     6
NULL         | NULL       | Colorado          |     5 |     6
(10 rows)
</pre></div>
</div>
<p>The first grouping in the above result only includes the <code class="docutils literal notranslate"><span class="pre">origin_state</span></code> column and excludes
the <code class="docutils literal notranslate"><span class="pre">origin_zip</span></code> and <code class="docutils literal notranslate"><span class="pre">destination_state</span></code> columns. The bit set constructed for that grouping
is <code class="docutils literal notranslate"><span class="pre">011</span></code> where the most significant bit represents <code class="docutils literal notranslate"><span class="pre">origin_state</span></code>.</p>


<h2 id="having-clause">HAVING clause<a class="headerlink" href="#having-clause" title="Permalink to this headline">#</a></h2>
<p>The <code class="docutils literal notranslate"><span class="pre">HAVING</span></code> clause is used in conjunction with aggregate functions and
the <code class="docutils literal notranslate"><span class="pre">GROUP</span> <span class="pre">BY</span></code> clause to control which groups are selected. A <code class="docutils literal notranslate"><span class="pre">HAVING</span></code>
clause eliminates groups that do not satisfy the given conditions.
<code class="docutils literal notranslate"><span class="pre">HAVING</span></code> filters groups after groups and aggregates are computed.</p>
<p>The following example queries the <code class="docutils literal notranslate"><span class="pre">customer</span></code> table and selects groups
with an account balance greater than the specified value:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="k">count</span><span class="p">(</span><span class="o">*</span><span class="p">),</span> <span class="n">mktsegment</span><span class="p">,</span> <span class="n">nationkey</span><span class="p">,</span>
       <span class="k">CAST</span><span class="p">(</span><span class="k">sum</span><span class="p">(</span><span class="n">acctbal</span><span class="p">)</span> <span class="k">AS</span> <span class="nb">bigint</span><span class="p">)</span> <span class="k">AS</span> <span class="n">totalbal</span>
<span class="k">FROM</span> <span class="n">customer</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="n">mktsegment</span><span class="p">,</span> <span class="n">nationkey</span>
<span class="k">HAVING</span> <span class="k">sum</span><span class="p">(</span><span class="n">acctbal</span><span class="p">)</span> <span class="o">&gt;</span> <span class="mi">5700000</span>
<span class="k">ORDER</span> <span class="k">BY</span> <span class="n">totalbal</span> <span class="k">DESC</span><span class="p">;</span>
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span> _col0 | mktsegment | nationkey | totalbal
-------+------------+-----------+----------
  1272 | AUTOMOBILE |        19 |  5856939
  1253 | FURNITURE  |        14 |  5794887
  1248 | FURNITURE  |         9 |  5784628
  1243 | FURNITURE  |        12 |  5757371
  1231 | HOUSEHOLD  |         3 |  5753216
  1251 | MACHINERY  |         2 |  5719140
  1247 | FURNITURE  |         8 |  5701952
(7 rows)
</pre></div>
</div>


<span id="id2"></span><h2 id="window-clause">WINDOW clause<a class="headerlink" href="#window-clause" title="Permalink to this headline">#</a></h2>
<p>The <code class="docutils literal notranslate"><span class="pre">WINDOW</span></code> clause is used to define named window specifications. The defined named
window specifications can be referred to in the <code class="docutils literal notranslate"><span class="pre">SELECT</span></code> and <code class="docutils literal notranslate"><span class="pre">ORDER</span> <span class="pre">BY</span></code> clauses
of the enclosing query:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">orderkey</span><span class="p">,</span> <span class="n">clerk</span><span class="p">,</span> <span class="n">totalprice</span><span class="p">,</span>
      <span class="n">rank</span><span class="p">()</span> <span class="n">OVER</span> <span class="n">w</span> <span class="k">AS</span> <span class="n">rnk</span>
<span class="k">FROM</span> <span class="n">orders</span>
<span class="n">WINDOW</span> <span class="n">w</span> <span class="k">AS</span> <span class="p">(</span><span class="n">PARTITION</span> <span class="k">BY</span> <span class="n">clerk</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">totalprice</span> <span class="k">DESC</span><span class="p">)</span>
<span class="k">ORDER</span> <span class="k">BY</span> <span class="k">count</span><span class="p">()</span> <span class="n">OVER</span> <span class="n">w</span><span class="p">,</span> <span class="n">clerk</span><span class="p">,</span> <span class="n">rnk</span>
</pre></div>
</div>
<p>The window definition list of <code class="docutils literal notranslate"><span class="pre">WINDOW</span></code> clause can contain one or multiple named window
specifications of the form</p>
<div class="highlight-none notranslate"><div class="highlight"><pre><span></span>window_name AS (window_specification)
</pre></div>
</div>
<p>A window specification has the following components:</p>
<ul class="simple">
<li><p>The existing window name, which refers to a named window specification in the
<code class="docutils literal notranslate"><span class="pre">WINDOW</span></code> clause. The window specification associated with the referenced name
is the basis of the current specification.</p></li>
<li><p>The partition specification, which separates the input rows into different
partitions. This is analogous to how the <code class="docutils literal notranslate"><span class="pre">GROUP</span> <span class="pre">BY</span></code> clause separates rows
into different groups for aggregate functions.</p></li>
<li><p>The ordering specification, which determines the order in which input rows
will be processed by the window function.</p></li>
<li><p>The window frame, which specifies a sliding window of rows to be processed
by the function for a given row. If the frame is not specified, it defaults
to <code class="docutils literal notranslate"><span class="pre">RANGE</span> <span class="pre">UNBOUNDED</span> <span class="pre">PRECEDING</span></code>, which is the same as
<code class="docutils literal notranslate"><span class="pre">RANGE</span> <span class="pre">BETWEEN</span> <span class="pre">UNBOUNDED</span> <span class="pre">PRECEDING</span> <span class="pre">AND</span> <span class="pre">CURRENT</span> <span class="pre">ROW</span></code>. This frame contains all
rows from the start of the partition up to the last peer of the current row.
In the absence of <code class="docutils literal notranslate"><span class="pre">ORDER</span> <span class="pre">BY</span></code>, all rows are considered peers, so <code class="docutils literal notranslate"><span class="pre">RANGE</span>
<span class="pre">BETWEEN</span> <span class="pre">UNBOUNDED</span> <span class="pre">PRECEDING</span> <span class="pre">AND</span> <span class="pre">CURRENT</span> <span class="pre">ROW</span></code> is equivalent to <code class="docutils literal notranslate"><span class="pre">BETWEEN</span>
<span class="pre">UNBOUNDED</span> <span class="pre">PRECEDING</span> <span class="pre">AND</span> <span class="pre">UNBOUNDED</span> <span class="pre">FOLLOWING</span></code></p></li>
</ul>
<p>Each component is optional. In the case when window partitioning, ordering or frame
is not specified and existing window name is absent or the referenced window
specification does not provide particular components, defaults are used.</p>


<h2 id="set-operations">Set operations<a class="headerlink" href="#set-operations" title="Permalink to this headline">#</a></h2>
<p><code class="docutils literal notranslate"><span class="pre">UNION</span></code> <code class="docutils literal notranslate"><span class="pre">INTERSECT</span></code> and <code class="docutils literal notranslate"><span class="pre">EXCEPT</span></code> are all set operations.  These clauses are used
to combine the results of more than one select statement into a single result set:</p>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>query UNION [ALL | DISTINCT] query
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>query INTERSECT [DISTINCT] query
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>query EXCEPT [DISTINCT] query
</pre></div>
</div>
<p>The argument <code class="docutils literal notranslate"><span class="pre">ALL</span></code> or <code class="docutils literal notranslate"><span class="pre">DISTINCT</span></code> controls which rows are included in
the final result set. If the argument <code class="docutils literal notranslate"><span class="pre">ALL</span></code> is specified all rows are
included even if the rows are identical.  If the argument <code class="docutils literal notranslate"><span class="pre">DISTINCT</span></code>
is specified only unique rows are included in the combined result set.
If neither is specified, the behavior defaults to <code class="docutils literal notranslate"><span class="pre">DISTINCT</span></code>.  The <code class="docutils literal notranslate"><span class="pre">ALL</span></code>
argument is not supported for <code class="docutils literal notranslate"><span class="pre">INTERSECT</span></code> or <code class="docutils literal notranslate"><span class="pre">EXCEPT</span></code>.</p>
<p>Multiple set operations are processed left to right, unless the order is explicitly
specified via parentheses. Additionally, <code class="docutils literal notranslate"><span class="pre">INTERSECT</span></code> binds more tightly
than <code class="docutils literal notranslate"><span class="pre">EXCEPT</span></code> and <code class="docutils literal notranslate"><span class="pre">UNION</span></code>. That means <code class="docutils literal notranslate"><span class="pre">A</span> <span class="pre">UNION</span> <span class="pre">B</span> <span class="pre">INTERSECT</span> <span class="pre">C</span> <span class="pre">EXCEPT</span> <span class="pre">D</span></code>
is the same as <code class="docutils literal notranslate"><span class="pre">A</span> <span class="pre">UNION</span> <span class="pre">(B</span> <span class="pre">INTERSECT</span> <span class="pre">C)</span> <span class="pre">EXCEPT</span> <span class="pre">D</span></code>.</p>

<h3 id="union-clause">UNION clause<a class="headerlink" href="#union-clause" title="Permalink to this headline">#</a></h3>
<p><code class="docutils literal notranslate"><span class="pre">UNION</span></code> combines all the rows that are in the result set from the
first query with those that are in the result set for the second query.
The following is an example of one of the simplest possible <code class="docutils literal notranslate"><span class="pre">UNION</span></code> clauses.
It selects the value <code class="docutils literal notranslate"><span class="pre">13</span></code> and combines this result set with a second query
that selects the value <code class="docutils literal notranslate"><span class="pre">42</span></code>:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="mi">13</span>
<span class="k">UNION</span>
<span class="k">SELECT</span> <span class="mi">42</span><span class="p">;</span>
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span> _col0
-------
    13
    42
(2 rows)
</pre></div>
</div>
<p>The following query demonstrates the difference between <code class="docutils literal notranslate"><span class="pre">UNION</span></code> and <code class="docutils literal notranslate"><span class="pre">UNION</span> <span class="pre">ALL</span></code>.
It selects the value <code class="docutils literal notranslate"><span class="pre">13</span></code> and combines this result set with a second query that
selects the values <code class="docutils literal notranslate"><span class="pre">42</span></code> and <code class="docutils literal notranslate"><span class="pre">13</span></code>:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="mi">13</span>
<span class="k">UNION</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="p">(</span><span class="k">VALUES</span> <span class="mi">42</span><span class="p">,</span> <span class="mi">13</span><span class="p">);</span>
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span> _col0
-------
    13
    42
(2 rows)
</pre></div>
</div>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="mi">13</span>
<span class="k">UNION</span> <span class="k">ALL</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="p">(</span><span class="k">VALUES</span> <span class="mi">42</span><span class="p">,</span> <span class="mi">13</span><span class="p">);</span>
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span> _col0
-------
    13
    42
    13
(2 rows)
</pre></div>
</div>


<h3 id="intersect-clause">INTERSECT clause<a class="headerlink" href="#intersect-clause" title="Permalink to this headline">#</a></h3>
<p><code class="docutils literal notranslate"><span class="pre">INTERSECT</span></code> returns only the rows that are in the result sets of both the first and
the second queries. The following is an example of one of the simplest
possible <code class="docutils literal notranslate"><span class="pre">INTERSECT</span></code> clauses. It selects the values <code class="docutils literal notranslate"><span class="pre">13</span></code> and <code class="docutils literal notranslate"><span class="pre">42</span></code> and combines
this result set with a second query that selects the value <code class="docutils literal notranslate"><span class="pre">13</span></code>.  Since <code class="docutils literal notranslate"><span class="pre">42</span></code>
is only in the result set of the first query, it is not included in the final results.:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="p">(</span><span class="k">VALUES</span> <span class="mi">13</span><span class="p">,</span> <span class="mi">42</span><span class="p">)</span>
<span class="k">INTERSECT</span>
<span class="k">SELECT</span> <span class="mi">13</span><span class="p">;</span>
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span> _col0
-------
    13
(2 rows)
</pre></div>
</div>


<h3 id="except-clause">EXCEPT clause<a class="headerlink" href="#except-clause" title="Permalink to this headline">#</a></h3>
<p><code class="docutils literal notranslate"><span class="pre">EXCEPT</span></code> returns the rows that are in the result set of the first query,
but not the second. The following is an example of one of the simplest
possible <code class="docutils literal notranslate"><span class="pre">EXCEPT</span></code> clauses. It selects the values <code class="docutils literal notranslate"><span class="pre">13</span></code> and <code class="docutils literal notranslate"><span class="pre">42</span></code> and combines
this result set with a second query that selects the value <code class="docutils literal notranslate"><span class="pre">13</span></code>.  Since <code class="docutils literal notranslate"><span class="pre">13</span></code>
is also in the result set of the second query, it is not included in the final result.:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="p">(</span><span class="k">VALUES</span> <span class="mi">13</span><span class="p">,</span> <span class="mi">42</span><span class="p">)</span>
<span class="k">EXCEPT</span>
<span class="k">SELECT</span> <span class="mi">13</span><span class="p">;</span>
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span> _col0
-------
   42
(2 rows)
</pre></div>
</div>


<span id="id3"></span><h2 id="order-by-clause">ORDER BY clause<a class="headerlink" href="#order-by-clause" title="Permalink to this headline">#</a></h2>
<p>The <code class="docutils literal notranslate"><span class="pre">ORDER</span> <span class="pre">BY</span></code> clause is used to sort a result set by one or more
output expressions:</p>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...]
</pre></div>
</div>
<p>Each expression may be composed of output columns, or it may be an ordinal
number selecting an output column by position, starting at one. The
<code class="docutils literal notranslate"><span class="pre">ORDER</span> <span class="pre">BY</span></code> clause is evaluated after any <code class="docutils literal notranslate"><span class="pre">GROUP</span> <span class="pre">BY</span></code> or <code class="docutils literal notranslate"><span class="pre">HAVING</span></code> clause,
and before any <code class="docutils literal notranslate"><span class="pre">OFFSET</span></code>, <code class="docutils literal notranslate"><span class="pre">LIMIT</span></code> or <code class="docutils literal notranslate"><span class="pre">FETCH</span> <span class="pre">FIRST</span></code> clause.
The default null ordering is <code class="docutils literal notranslate"><span class="pre">NULLS</span> <span class="pre">LAST</span></code>, regardless of the ordering direction.</p>
<p>Note that, following the SQL specification, an <code class="docutils literal notranslate"><span class="pre">ORDER</span> <span class="pre">BY</span></code> clause only
affects the order of rows for queries that immediately contain the clause.
Trino follows that specification, and drops redundant usage of the clause to
avoid negative performance impacts.</p>
<p>In the following example, the clause only applies to the select statement.</p>
<div class="highlight-SQL notranslate"><div class="highlight"><pre><span></span><span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">some_table</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">another_table</span>
<span class="k">ORDER</span> <span class="k">BY</span> <span class="n">field</span><span class="p">;</span>
</pre></div>
</div>
<p>Since tables in SQL are inherently unordered, and the <code class="docutils literal notranslate"><span class="pre">ORDER</span> <span class="pre">BY</span></code> clause in
this case does not result in any difference, but negatively impacts performance
of running the overall insert statement, Trino skips the sort operation.</p>
<p>Another example where the <code class="docutils literal notranslate"><span class="pre">ORDER</span> <span class="pre">BY</span></code> clause is redundant, and does not affect
the outcome of the overall statement, is a nested query:</p>
<div class="highlight-SQL notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span>
<span class="k">FROM</span> <span class="n">some_table</span>
    <span class="k">JOIN</span> <span class="p">(</span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">another_table</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">field</span><span class="p">)</span> <span class="n">u</span>
    <span class="k">ON</span> <span class="n">some_table</span><span class="p">.</span><span class="k">key</span> <span class="o">=</span> <span class="n">u</span><span class="p">.</span><span class="k">key</span><span class="p">;</span>
</pre></div>
</div>
<p>More background information and details can be found in
<a class="reference external" href="https://trino.io/blog/2019/06/03/redundant-order-by.html">a blog post about this optimization</a>.</p>


<span id="id4"></span><h2 id="offset-clause">OFFSET clause<a class="headerlink" href="#offset-clause" title="Permalink to this headline">#</a></h2>
<p>The <code class="docutils literal notranslate"><span class="pre">OFFSET</span></code> clause is used to discard a number of leading rows
from the result set:</p>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>OFFSET count [ ROW | ROWS ]
</pre></div>
</div>
<p>If the <code class="docutils literal notranslate"><span class="pre">ORDER</span> <span class="pre">BY</span></code> clause is present, the <code class="docutils literal notranslate"><span class="pre">OFFSET</span></code> clause is evaluated
over a sorted result set, and the set remains sorted after the
leading rows are discarded:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">name</span> <span class="k">FROM</span> <span class="n">nation</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">name</span> <span class="k">OFFSET</span> <span class="mi">22</span><span class="p">;</span>
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>      name
----------------
 UNITED KINGDOM
 UNITED STATES
 VIETNAM
(3 rows)
</pre></div>
</div>
<p>Otherwise, it is arbitrary which rows are discarded.
If the count specified in the <code class="docutils literal notranslate"><span class="pre">OFFSET</span></code> clause equals or exceeds the size
of the result set, the final result is empty.</p>


<h2 id="limit-or-fetch-first-clauses">LIMIT or FETCH FIRST clauses<a class="headerlink" href="#limit-or-fetch-first-clauses" title="Permalink to this headline">#</a></h2>
<p>The <code class="docutils literal notranslate"><span class="pre">LIMIT</span></code> or <code class="docutils literal notranslate"><span class="pre">FETCH</span> <span class="pre">FIRST</span></code> clause restricts the number of rows
in the result set.</p>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>LIMIT { count | ALL }
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }
</pre></div>
</div>
<p>The following example queries a large table, but the <code class="docutils literal notranslate"><span class="pre">LIMIT</span></code> clause
restricts the output to only have five rows (because the query lacks an <code class="docutils literal notranslate"><span class="pre">ORDER</span> <span class="pre">BY</span></code>,
exactly which rows are returned is arbitrary):</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">orderdate</span> <span class="k">FROM</span> <span class="n">orders</span> <span class="k">LIMIT</span> <span class="mi">5</span><span class="p">;</span>
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span> orderdate
------------
 1994-07-25
 1993-11-12
 1992-10-06
 1994-01-04
 1997-12-28
(5 rows)
</pre></div>
</div>
<p><code class="docutils literal notranslate"><span class="pre">LIMIT</span> <span class="pre">ALL</span></code> is the same as omitting the <code class="docutils literal notranslate"><span class="pre">LIMIT</span></code> clause.</p>
<p>The <code class="docutils literal notranslate"><span class="pre">FETCH</span> <span class="pre">FIRST</span></code> clause supports either the <code class="docutils literal notranslate"><span class="pre">FIRST</span></code> or <code class="docutils literal notranslate"><span class="pre">NEXT</span></code> keywords
and the <code class="docutils literal notranslate"><span class="pre">ROW</span></code> or <code class="docutils literal notranslate"><span class="pre">ROWS</span></code> keywords. These keywords are equivalent and
the choice of keyword has no effect on query execution.</p>
<p>If the count is not specified in the <code class="docutils literal notranslate"><span class="pre">FETCH</span> <span class="pre">FIRST</span></code> clause, it defaults to <code class="docutils literal notranslate"><span class="pre">1</span></code>:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">orderdate</span> <span class="k">FROM</span> <span class="n">orders</span> <span class="k">FETCH</span> <span class="k">FIRST</span> <span class="k">ROW</span> <span class="k">ONLY</span><span class="p">;</span>
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span> orderdate
------------
 1994-02-12
(1 row)
</pre></div>
</div>
<p>If the <code class="docutils literal notranslate"><span class="pre">OFFSET</span></code> clause is present, the <code class="docutils literal notranslate"><span class="pre">LIMIT</span></code> or <code class="docutils literal notranslate"><span class="pre">FETCH</span> <span class="pre">FIRST</span></code> clause
is evaluated after the <code class="docutils literal notranslate"><span class="pre">OFFSET</span></code> clause:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="p">(</span><span class="k">VALUES</span> <span class="mi">5</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="mi">4</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="mi">3</span><span class="p">)</span> <span class="n">t</span><span class="p">(</span><span class="n">x</span><span class="p">)</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">x</span> <span class="k">OFFSET</span> <span class="mi">2</span> <span class="k">LIMIT</span> <span class="mi">2</span><span class="p">;</span>
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span> x
---
 3
 4
(2 rows)
</pre></div>
</div>
<p>For the <code class="docutils literal notranslate"><span class="pre">FETCH</span> <span class="pre">FIRST</span></code> clause, the argument <code class="docutils literal notranslate"><span class="pre">ONLY</span></code> or <code class="docutils literal notranslate"><span class="pre">WITH</span> <span class="pre">TIES</span></code>
controls which rows are included in the result set.</p>
<p>If the argument <code class="docutils literal notranslate"><span class="pre">ONLY</span></code> is specified, the result set is limited to the exact
number of leading rows determined by the count.</p>
<p>If the argument <code class="docutils literal notranslate"><span class="pre">WITH</span> <span class="pre">TIES</span></code> is specified, it is required that the <code class="docutils literal notranslate"><span class="pre">ORDER</span> <span class="pre">BY</span></code>
clause be present. The result set consists of the same set of leading rows
and all of the rows in the same peer group as the last of them (‘ties’)
as established by the ordering in the <code class="docutils literal notranslate"><span class="pre">ORDER</span> <span class="pre">BY</span></code> clause. The result set is sorted:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">name</span><span class="p">,</span> <span class="n">regionkey</span>
<span class="k">FROM</span> <span class="n">nation</span>
<span class="k">ORDER</span> <span class="k">BY</span> <span class="n">regionkey</span> <span class="k">FETCH</span> <span class="k">FIRST</span> <span class="k">ROW</span> <span class="k">WITH</span> <span class="n">TIES</span><span class="p">;</span>
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>    name    | regionkey
------------+-----------
 ETHIOPIA   |         0
 MOROCCO    |         0
 KENYA      |         0
 ALGERIA    |         0
 MOZAMBIQUE |         0
(5 rows)
</pre></div>
</div>


<h2 id="tablesample">TABLESAMPLE<a class="headerlink" href="#tablesample" title="Permalink to this headline">#</a></h2>
<p>There are multiple sample methods:</p>
<dl>
<dt><code class="docutils literal notranslate"><span class="pre">BERNOULLI</span></code></dt><dd><p>Each row is selected to be in the table sample with a probability of
the sample percentage. When a table is sampled using the Bernoulli
method, all physical blocks of the table are scanned and certain
rows are skipped (based on a comparison between the sample percentage
and a random value calculated at runtime).</p>
<p>The probability of a row being included in the result is independent
from any other row. This does not reduce the time required to read
the sampled table from disk. It may have an impact on the total
query time if the sampled output is processed further.</p>
</dd>
<dt><code class="docutils literal notranslate"><span class="pre">SYSTEM</span></code></dt><dd><p>This sampling method divides the table into logical segments of data
and samples the table at this granularity. This sampling method either
selects all the rows from a particular segment of data or skips it
(based on a comparison between the sample percentage and a random
value calculated at runtime).</p>
<p>The rows selected in a system sampling will be dependent on which
connector is used. For example, when used with Hive, it is dependent
on how the data is laid out on HDFS. This method does not guarantee
independent sampling probabilities.</p>
</dd>
</dl>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>Neither of the two methods allow deterministic bounds on the number of rows returned.</p>
</div>
<p>Examples:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span>
<span class="k">FROM</span> <span class="n">users</span> <span class="n">TABLESAMPLE</span> <span class="n">BERNOULLI</span> <span class="p">(</span><span class="mi">50</span><span class="p">);</span>

<span class="k">SELECT</span> <span class="o">*</span>
<span class="k">FROM</span> <span class="n">users</span> <span class="n">TABLESAMPLE</span> <span class="k">SYSTEM</span> <span class="p">(</span><span class="mi">75</span><span class="p">);</span>
</pre></div>
</div>
<p>Using sampling with joins:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">o</span><span class="p">.</span><span class="o">*</span><span class="p">,</span> <span class="n">i</span><span class="p">.</span><span class="o">*</span>
<span class="k">FROM</span> <span class="n">orders</span> <span class="n">o</span> <span class="n">TABLESAMPLE</span> <span class="k">SYSTEM</span> <span class="p">(</span><span class="mi">10</span><span class="p">)</span>
<span class="k">JOIN</span> <span class="n">lineitem</span> <span class="n">i</span> <span class="n">TABLESAMPLE</span> <span class="n">BERNOULLI</span> <span class="p">(</span><span class="mi">40</span><span class="p">)</span>
  <span class="k">ON</span> <span class="n">o</span><span class="p">.</span><span class="n">orderkey</span> <span class="o">=</span> <span class="n">i</span><span class="p">.</span><span class="n">orderkey</span><span class="p">;</span>
</pre></div>
</div>


<span id="id5"></span><h2 id="unnest">UNNEST<a class="headerlink" href="#unnest" title="Permalink to this headline">#</a></h2>
<p><code class="docutils literal notranslate"><span class="pre">UNNEST</span></code> can be used to expand an <a class="reference internal" href="../language/types.html#array-type"><span class="std std-ref">ARRAY</span></a> or <a class="reference internal" href="../language/types.html#map-type"><span class="std std-ref">MAP</span></a> into a relation.
Arrays are expanded into a single column, and maps are expanded into two columns (key, value).
<code class="docutils literal notranslate"><span class="pre">UNNEST</span></code> can also be used with multiple arguments, in which case they are expanded into multiple columns,
with as many rows as the highest cardinality argument (the other columns are padded with nulls).
<code class="docutils literal notranslate"><span class="pre">UNNEST</span></code> can optionally have a <code class="docutils literal notranslate"><span class="pre">WITH</span> <span class="pre">ORDINALITY</span></code> clause, in which case an additional ordinality column
is added to the end.
<code class="docutils literal notranslate"><span class="pre">UNNEST</span></code> is normally used with a <code class="docutils literal notranslate"><span class="pre">JOIN</span></code> and can reference columns
from relations on the left side of the join.</p>
<p>Using a single column:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">student</span><span class="p">,</span> <span class="n">score</span>
<span class="k">FROM</span> <span class="n">tests</span>
<span class="k">CROSS</span> <span class="k">JOIN</span> <span class="k">UNNEST</span><span class="p">(</span><span class="n">scores</span><span class="p">)</span> <span class="k">AS</span> <span class="n">t</span> <span class="p">(</span><span class="n">score</span><span class="p">);</span>
</pre></div>
</div>
<p>Using multiple columns:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">numbers</span><span class="p">,</span> <span class="n">animals</span><span class="p">,</span> <span class="n">n</span><span class="p">,</span> <span class="n">a</span>
<span class="k">FROM</span> <span class="p">(</span>
  <span class="k">VALUES</span>
    <span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="mi">2</span><span class="p">,</span> <span class="mi">5</span><span class="p">],</span> <span class="nb">ARRAY</span><span class="p">[</span><span class="s1">'dog'</span><span class="p">,</span> <span class="s1">'cat'</span><span class="p">,</span> <span class="s1">'bird'</span><span class="p">]),</span>
    <span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="mi">7</span><span class="p">,</span> <span class="mi">8</span><span class="p">,</span> <span class="mi">9</span><span class="p">],</span> <span class="nb">ARRAY</span><span class="p">[</span><span class="s1">'cow'</span><span class="p">,</span> <span class="s1">'pig'</span><span class="p">])</span>
<span class="p">)</span> <span class="k">AS</span> <span class="n">x</span> <span class="p">(</span><span class="n">numbers</span><span class="p">,</span> <span class="n">animals</span><span class="p">)</span>
<span class="k">CROSS</span> <span class="k">JOIN</span> <span class="k">UNNEST</span><span class="p">(</span><span class="n">numbers</span><span class="p">,</span> <span class="n">animals</span><span class="p">)</span> <span class="k">AS</span> <span class="n">t</span> <span class="p">(</span><span class="n">n</span><span class="p">,</span> <span class="n">a</span><span class="p">);</span>
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>  numbers  |     animals      |  n   |  a
-----------+------------------+------+------
 [2, 5]    | [dog, cat, bird] |    2 | dog
 [2, 5]    | [dog, cat, bird] |    5 | cat
 [2, 5]    | [dog, cat, bird] | NULL | bird
 [7, 8, 9] | [cow, pig]       |    7 | cow
 [7, 8, 9] | [cow, pig]       |    8 | pig
 [7, 8, 9] | [cow, pig]       |    9 | NULL
(6 rows)
</pre></div>
</div>
<p><code class="docutils literal notranslate"><span class="pre">WITH</span> <span class="pre">ORDINALITY</span></code> clause:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">numbers</span><span class="p">,</span> <span class="n">n</span><span class="p">,</span> <span class="n">a</span>
<span class="k">FROM</span> <span class="p">(</span>
  <span class="k">VALUES</span>
    <span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="mi">2</span><span class="p">,</span> <span class="mi">5</span><span class="p">]),</span>
    <span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="mi">7</span><span class="p">,</span> <span class="mi">8</span><span class="p">,</span> <span class="mi">9</span><span class="p">])</span>
<span class="p">)</span> <span class="k">AS</span> <span class="n">x</span> <span class="p">(</span><span class="n">numbers</span><span class="p">)</span>
<span class="k">CROSS</span> <span class="k">JOIN</span> <span class="k">UNNEST</span><span class="p">(</span><span class="n">numbers</span><span class="p">)</span> <span class="k">WITH</span> <span class="k">ORDINALITY</span> <span class="k">AS</span> <span class="n">t</span> <span class="p">(</span><span class="n">n</span><span class="p">,</span> <span class="n">a</span><span class="p">);</span>
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>  numbers  | n | a
-----------+---+---
 [2, 5]    | 2 | 1
 [2, 5]    | 5 | 2
 [7, 8, 9] | 7 | 1
 [7, 8, 9] | 8 | 2
 [7, 8, 9] | 9 | 3
(5 rows)
</pre></div>
</div>


<h2 id="joins">Joins<a class="headerlink" href="#joins" title="Permalink to this headline">#</a></h2>
<p>Joins allow you to combine data from multiple relations.</p>

<h3 id="cross-join">CROSS JOIN<a class="headerlink" href="#cross-join" title="Permalink to this headline">#</a></h3>
<p>A cross join returns the Cartesian product (all combinations) of two
relations. Cross joins can either be specified using the explit
<code class="docutils literal notranslate"><span class="pre">CROSS</span> <span class="pre">JOIN</span></code> syntax or by specifying multiple relations in the
<code class="docutils literal notranslate"><span class="pre">FROM</span></code> clause.</p>
<p>Both of the following queries are equivalent:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span>
<span class="k">FROM</span> <span class="n">nation</span>
<span class="k">CROSS</span> <span class="k">JOIN</span> <span class="n">region</span><span class="p">;</span>

<span class="k">SELECT</span> <span class="o">*</span>
<span class="k">FROM</span> <span class="n">nation</span><span class="p">,</span> <span class="n">region</span><span class="p">;</span>
</pre></div>
</div>
<p>The <code class="docutils literal notranslate"><span class="pre">nation</span></code> table contains 25 rows and the <code class="docutils literal notranslate"><span class="pre">region</span></code> table contains 5 rows,
so a cross join between the two tables produces 125 rows:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">n</span><span class="p">.</span><span class="n">name</span> <span class="k">AS</span> <span class="n">nation</span><span class="p">,</span> <span class="n">r</span><span class="p">.</span><span class="n">name</span> <span class="k">AS</span> <span class="n">region</span>
<span class="k">FROM</span> <span class="n">nation</span> <span class="k">AS</span> <span class="n">n</span>
<span class="k">CROSS</span> <span class="k">JOIN</span> <span class="n">region</span> <span class="k">AS</span> <span class="n">r</span>
<span class="k">ORDER</span> <span class="k">BY</span> <span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">;</span>
</pre></div>
</div>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>     nation     |   region
----------------+-------------
 ALGERIA        | AFRICA
 ALGERIA        | AMERICA
 ALGERIA        | ASIA
 ALGERIA        | EUROPE
 ALGERIA        | MIDDLE EAST
 ARGENTINA      | AFRICA
 ARGENTINA      | AMERICA
...
(125 rows)
</pre></div>
</div>


<h3 id="lateral">LATERAL<a class="headerlink" href="#lateral" title="Permalink to this headline">#</a></h3>
<p>Subqueries appearing in the <code class="docutils literal notranslate"><span class="pre">FROM</span></code> clause can be preceded by the keyword <code class="docutils literal notranslate"><span class="pre">LATERAL</span></code>.
This allows them to reference columns provided by preceding <code class="docutils literal notranslate"><span class="pre">FROM</span></code> items.</p>
<p>A <code class="docutils literal notranslate"><span class="pre">LATERAL</span></code> join can appear at the top level in the <code class="docutils literal notranslate"><span class="pre">FROM</span></code> list, or anywhere
within a parenthesized join tree. In the latter case, it can also refer to any items
that are on the left-hand side of a <code class="docutils literal notranslate"><span class="pre">JOIN</span></code> for which it is on the right-hand side.</p>
<p>When a <code class="docutils literal notranslate"><span class="pre">FROM</span></code> item contains <code class="docutils literal notranslate"><span class="pre">LATERAL</span></code> cross-references, evaluation proceeds as follows:
for each row of the <code class="docutils literal notranslate"><span class="pre">FROM</span></code> item providing the cross-referenced columns,
the <code class="docutils literal notranslate"><span class="pre">LATERAL</span></code> item is evaluated using that row set’s values of the columns.
The resulting rows are joined as usual with the rows they were computed from.
This is repeated for set of rows from the column source tables.</p>
<p><code class="docutils literal notranslate"><span class="pre">LATERAL</span></code> is primarily useful when the cross-referenced column is necessary for
computing the rows to be joined:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">name</span><span class="p">,</span> <span class="n">x</span><span class="p">,</span> <span class="n">y</span>
<span class="k">FROM</span> <span class="n">nation</span>
<span class="k">CROSS</span> <span class="k">JOIN</span> <span class="k">LATERAL</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">name</span> <span class="o">||</span> <span class="s1">' :-'</span> <span class="k">AS</span> <span class="n">x</span><span class="p">)</span>
<span class="k">CROSS</span> <span class="k">JOIN</span> <span class="k">LATERAL</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">x</span> <span class="o">||</span> <span class="s1">')'</span> <span class="k">AS</span> <span class="n">y</span><span class="p">);</span>
</pre></div>
</div>


<h3 id="qualifying-column-names">Qualifying column names<a class="headerlink" href="#qualifying-column-names" title="Permalink to this headline">#</a></h3>
<p>When two relations in a join have columns with the same name, the column
references must be qualified using the relation alias (if the relation
has an alias), or with the relation name:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">nation</span><span class="p">.</span><span class="n">name</span><span class="p">,</span> <span class="n">region</span><span class="p">.</span><span class="n">name</span>
<span class="k">FROM</span> <span class="n">nation</span>
<span class="k">CROSS</span> <span class="k">JOIN</span> <span class="n">region</span><span class="p">;</span>

<span class="k">SELECT</span> <span class="n">n</span><span class="p">.</span><span class="n">name</span><span class="p">,</span> <span class="n">r</span><span class="p">.</span><span class="n">name</span>
<span class="k">FROM</span> <span class="n">nation</span> <span class="k">AS</span> <span class="n">n</span>
<span class="k">CROSS</span> <span class="k">JOIN</span> <span class="n">region</span> <span class="k">AS</span> <span class="n">r</span><span class="p">;</span>

<span class="k">SELECT</span> <span class="n">n</span><span class="p">.</span><span class="n">name</span><span class="p">,</span> <span class="n">r</span><span class="p">.</span><span class="n">name</span>
<span class="k">FROM</span> <span class="n">nation</span> <span class="n">n</span>
<span class="k">CROSS</span> <span class="k">JOIN</span> <span class="n">region</span> <span class="n">r</span><span class="p">;</span>
</pre></div>
</div>
<p>The following query will fail with the error <code class="docutils literal notranslate"><span class="pre">Column</span> <span class="pre">'name'</span> <span class="pre">is</span> <span class="pre">ambiguous</span></code>:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">name</span>
<span class="k">FROM</span> <span class="n">nation</span>
<span class="k">CROSS</span> <span class="k">JOIN</span> <span class="n">region</span><span class="p">;</span>
</pre></div>
</div>


<h2 id="subqueries">Subqueries<a class="headerlink" href="#subqueries" title="Permalink to this headline">#</a></h2>
<p>A subquery is an expression which is composed of a query. The subquery
is correlated when it refers to columns outside of the subquery.
Logically, the subquery will be evaluated for each row in the surrounding
query. The referenced columns will thus be constant during any single
evaluation of the subquery.</p>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>Support for correlated subqueries is limited. Not every standard form is supported.</p>
</div>

<h3 id="exists">EXISTS<a class="headerlink" href="#exists" title="Permalink to this headline">#</a></h3>
<p>The <code class="docutils literal notranslate"><span class="pre">EXISTS</span></code> predicate determines if a subquery returns any rows:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">name</span>
<span class="k">FROM</span> <span class="n">nation</span>
<span class="k">WHERE</span> <span class="k">EXISTS</span> <span class="p">(</span>
     <span class="k">SELECT</span> <span class="o">*</span>
     <span class="k">FROM</span> <span class="n">region</span>
     <span class="k">WHERE</span> <span class="n">region</span><span class="p">.</span><span class="n">regionkey</span> <span class="o">=</span> <span class="n">nation</span><span class="p">.</span><span class="n">regionkey</span>
<span class="p">);</span>
</pre></div>
</div>


<h3 id="in">IN<a class="headerlink" href="#in" title="Permalink to this headline">#</a></h3>
<p>The <code class="docutils literal notranslate"><span class="pre">IN</span></code> predicate determines if any values produced by the subquery
are equal to the provided expression. The result of <code class="docutils literal notranslate"><span class="pre">IN</span></code> follows the
standard rules for nulls. The subquery must produce exactly one column:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">name</span>
<span class="k">FROM</span> <span class="n">nation</span>
<span class="k">WHERE</span> <span class="n">regionkey</span> <span class="k">IN</span> <span class="p">(</span>
     <span class="k">SELECT</span> <span class="n">regionkey</span>
     <span class="k">FROM</span> <span class="n">region</span>
     <span class="k">WHERE</span> <span class="n">name</span> <span class="o">=</span> <span class="s1">'AMERICA'</span> <span class="k">OR</span> <span class="n">name</span> <span class="o">=</span> <span class="s1">'AFRICA'</span>
<span class="p">);</span>
</pre></div>
</div>


<h3 id="scalar-subquery">Scalar subquery<a class="headerlink" href="#scalar-subquery" title="Permalink to this headline">#</a></h3>
<p>A scalar subquery is a non-correlated subquery that returns zero or
one row. It is an error for the subquery to produce more than one
row. The returned value is <code class="docutils literal notranslate"><span class="pre">NULL</span></code> if the subquery produces no rows:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">name</span>
<span class="k">FROM</span> <span class="n">nation</span>
<span class="k">WHERE</span> <span class="n">regionkey</span> <span class="o">=</span> <span class="p">(</span><span class="k">SELECT</span> <span class="k">max</span><span class="p">(</span><span class="n">regionkey</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">region</span><span class="p">);</span>
</pre></div>
</div>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>Currently only single column can be returned from the scalar subquery.</p>
</div>


          </article>
        </div>
      </div>
    </main>
  </div>
  <footer class="md-footer">
    <div class="md-footer-nav">
      <nav class="md-footer-nav__inner md-grid">
          
            <a href="rollback.html" title="ROLLBACK"
               class="md-flex md-footer-nav__link md-footer-nav__link--prev"
               rel="prev">
              <div class="md-flex__cell md-flex__cell--shrink">
                <i class="md-icon md-icon--arrow-back md-footer-nav__button"></i>
              </div>
              <div class="md-flex__cell md-flex__cell--stretch md-footer-nav__title">
                <span class="md-flex__ellipsis">
                  <span
                      class="md-footer-nav__direction"> Previous </span> ROLLBACK </span>
              </div>
            </a>
          
          
            <a href="set-role.html" title="SET ROLE"
               class="md-flex md-footer-nav__link md-footer-nav__link--next"
               rel="next">
            <div class="md-flex__cell md-flex__cell--stretch md-footer-nav__title"><span
                class="md-flex__ellipsis"> <span
                class="md-footer-nav__direction"> Next </span> SET ROLE </span>
            </div>
            <div class="md-flex__cell md-flex__cell--shrink"><i
                class="md-icon md-icon--arrow-forward md-footer-nav__button"></i>
            </div>
          
        </a>
        
      </nav>
    </div>
    <div class="md-footer-meta md-typeset">
      <div class="md-footer-meta__inner md-grid">
        <div class="md-footer-copyright">
        </div>
      </div>
    </div>
  </footer>
  <script src="../_static/javascripts/application.js"></script>
  <script>app.initialize({version: "1.0.4", url: {base: ".."}})</script>
  </body>
</html>