# SELECT The SELECT command returns rows from tables and user-defined functions. The following SELECT SQL commands, clauses, and set operators are supported in AWS Clean Rooms Spark SQL: **Topics** + [SELECT list](sql-function-select-list-spark.md) + [WITH clause](WITH_clause.md) + [FROM clause](FROM_clause30.md) + [JOIN clause](join-clause.md) + [WHERE clause](WHERE_clause.md) + [VALUES clause](VALUES.md) + [GROUP BY clause](GROUP_BY_clause.md) + [HAVING clause](HAVING_clause.md) + [Set operators](UNION.md) + [ORDER BY clause](ORDER_BY_clause.md) + [Subquery examples](Subquery_examples.md) + [Correlated subqueries](correlated_subqueries.md) The syntax, arguments, and some examples come from the [Apache Spark SQL Reference](https://spark.apache.org/docs/latest/api/sql/). # SELECT list The SELECT list names the columns, functions, and expressions that you want the query to return. The list represents the output of the query. ## Syntax ``` SELECT [ DISTINCT ] | expression [ AS column_alias ] [, ...] ``` ## Parameters DISTINCT Option that eliminates duplicate rows from the result set, based on matching values in one or more columns. *expression* An expression formed from one or more columns that exist in the tables referenced by the query. An expression can contain SQL functions. For example: ``` coalesce(dimension, 'stringifnull') AS column_alias ``` AS column\$1alias A temporary name for the column that is used in the final result set. The AS keyword is optional. For example: ``` coalesce(dimension, 'stringifnull') AS dimensioncomplete ``` If you don't specify an alias for an expression that isn't a simple column name, the result set applies a default name to that column. **Note** The alias is recognized right after it is defined in the target list. You can't use an alias in other expressions defined after it in the same target list. # WITH clause A WITH clause is an optional clause that precedes the SELECT list in a query. The WITH clause defines one or more *common\$1table\$1expressions*. Each common table expression (CTE) defines a temporary table, which is similar to a view definition. You can reference these temporary tables in the FROM clause. They're used only while the query they belong to runs. Each CTE in the WITH clause specifies a table name, an optional list of column names, and a query expression that evaluates to a table (a SELECT statement). WITH clause subqueries are an efficient way of defining tables that can be used throughout the execution of a single query. In all cases, the same results can be achieved by using subqueries in the main body of the SELECT statement, but WITH clause subqueries may be simpler to write and read. Where possible, WITH clause subqueries that are referenced multiple times are optimized as common subexpressions; that is, it may be possible to evaluate a WITH subquery once and reuse its results. (Note that common subexpressions aren't limited to those defined in the WITH clause.) ## Syntax ``` [ WITH common_table_expression [, common_table_expression , ...] ] ``` where *common\$1table\$1expression* can be non-recursive. Following is the non-recursive form: ``` CTE_table_name AS ( query ) ``` ## Parameters *common\$1table\$1expression* Defines a temporary table that you can reference in the [FROM clause](FROM_clause30.md) and is used only during the execution of the query to which it belongs. *CTE\$1table\$1name* A unique name for a temporary table that defines the results of a WITH clause subquery. You can't use duplicate names within a single WITH clause. Each subquery must be given a table name that can be referenced in the [FROM clause](FROM_clause30.md). *query* Any SELECT query that AWS Clean Rooms supports. See [SELECT](sql-commands-select-spark.md). ## Usage notes You can use a WITH clause in the following SQL statement: + SELECT, WITH, UNION, UNION ALL, INTERSECT, INTERSECT ALL, EXCEPT, or EXCEPT ALL If the FROM clause of a query that contains a WITH clause doesn't reference any of the tables defined by the WITH clause, the WITH clause is ignored and the query runs as normal. A table defined by a WITH clause subquery can be referenced only in the scope of the SELECT query that the WITH clause begins. For example, you can reference such a table in the FROM clause of a subquery in the SELECT list, WHERE clause, or HAVING clause. You can't use a WITH clause in a subquery and reference its table in the FROM clause of the main query or another subquery. This query pattern results in an error message of the form `relation table_name doesn't exist` for the WITH clause table. You can't specify another WITH clause inside a WITH clause subquery. You can't make forward references to tables defined by WITH clause subqueries. For example, the following query returns an error because of the forward reference to table W2 in the definition of table W1: ``` with w1 as (select * from w2), w2 as (select * from w1) select * from sales; ERROR: relation "w2" does not exist ``` ## Examples The following example shows the simplest possible case of a query that contains a WITH clause. The WITH query named VENUECOPY selects all of the rows from the VENUE table. The main query in turn selects all of the rows from VENUECOPY. The VENUECOPY table exists only for the duration of this query. ``` with venuecopy as (select * from venue) select * from venuecopy order by 1 limit 10; ``` ``` venueid | venuename | venuecity | venuestate | venueseats ---------+----------------------------+-----------------+------------+------------ 1 | Toyota Park | Bridgeview | IL | 0 2 | Columbus Crew Stadium | Columbus | OH | 0 3 | RFK Stadium | Washington | DC | 0 4 | CommunityAmerica Ballpark | Kansas City | KS | 0 5 | Gillette Stadium | Foxborough | MA | 68756 6 | New York Giants Stadium | East Rutherford | NJ | 80242 7 | BMO Field | Toronto | ON | 0 8 | The Home Depot Center | Carson | CA | 0 9 | Dick's Sporting Goods Park | Commerce City | CO | 0 v 10 | Pizza Hut Park | Frisco | TX | 0 (10 rows) ``` The following example shows a WITH clause that produces two tables, named VENUE\$1SALES and TOP\$1VENUES. The second WITH query table selects from the first. In turn, the WHERE clause of the main query block contains a subquery that constrains the TOP\$1VENUES table. ``` with venue_sales as (select venuename, venuecity, sum(pricepaid) as venuename_sales from sales, venue, event where venue.venueid=event.venueid and event.eventid=sales.eventid group by venuename, venuecity), top_venues as (select venuename from venue_sales where venuename_sales > 800000) select venuename, venuecity, venuestate, sum(qtysold) as venue_qty, sum(pricepaid) as venue_sales from sales, venue, event where venue.venueid=event.venueid and event.eventid=sales.eventid and venuename in(select venuename from top_venues) group by venuename, venuecity, venuestate order by venuename; ``` ``` venuename | venuecity | venuestate | venue_qty | venue_sales ------------------------+---------------+------------+-----------+------------- August Wilson Theatre | New York City | NY | 3187 | 1032156.00 Biltmore Theatre | New York City | NY | 2629 | 828981.00 Charles Playhouse | Boston | MA | 2502 | 857031.00 Ethel Barrymore Theatre | New York City | NY | 2828 | 891172.00 Eugene O'Neill Theatre | New York City | NY | 2488 | 828950.00 Greek Theatre | Los Angeles | CA | 2445 | 838918.00 Helen Hayes Theatre | New York City | NY | 2948 | 978765.00 Hilton Theatre | New York City | NY | 2999 | 885686.00 Imperial Theatre | New York City | NY | 2702 | 877993.00 Lunt-Fontanne Theatre | New York City | NY | 3326 | 1115182.00 Majestic Theatre | New York City | NY | 2549 | 894275.00 Nederlander Theatre | New York City | NY | 2934 | 936312.00 Pasadena Playhouse | Pasadena | CA | 2739 | 820435.00 Winter Garden Theatre | New York City | NY | 2838 | 939257.00 (14 rows) ``` The following two examples demonstrate the rules for the scope of table references based on WITH clause subqueries. The first query runs, but the second fails with an expected error. The first query has WITH clause subquery inside the SELECT list of the main query. The table defined by the WITH clause (HOLIDAYS) is referenced in the FROM clause of the subquery in the SELECT list: ``` select caldate, sum(pricepaid) as daysales, (with holidays as (select * from date where holiday ='t') select sum(pricepaid) from sales join holidays on sales.dateid=holidays.dateid where caldate='2008-12-25') as dec25sales from sales join date on sales.dateid=date.dateid where caldate in('2008-12-25','2008-12-31') group by caldate order by caldate; caldate | daysales | dec25sales -----------+----------+------------ 2008-12-25 | 70402.00 | 70402.00 2008-12-31 | 12678.00 | 70402.00 (2 rows) ``` The second query fails because it attempts to reference the HOLIDAYS table in the main query as well as in the SELECT list subquery. The main query references are out of scope. ``` select caldate, sum(pricepaid) as daysales, (with holidays as (select * from date where holiday ='t') select sum(pricepaid) from sales join holidays on sales.dateid=holidays.dateid where caldate='2008-12-25') as dec25sales from sales join holidays on sales.dateid=holidays.dateid where caldate in('2008-12-25','2008-12-31') group by caldate order by caldate; ERROR: relation "holidays" does not exist ``` # FROM clause The FROM clause in a query lists the table references (tables, views, and subqueries) that data is selected from. If multiple table references are listed, the tables must be joined, using appropriate syntax in either the FROM clause or the WHERE clause. If no join criteria are specified, the system processes the query as a cross-join (Cartesian product). **Topics** + [Syntax](#FROM_clause30-synopsis) + [Parameters](#FROM_clause30-parameters) + [Usage notes](#FROM_clause_usage_notes) ## Syntax ``` FROM table_reference [, ...] ``` where *table\$1reference* is one of the following: ``` with_subquery_table_name | table_name | ( subquery ) [ [ AS ] alias ] table_reference [ NATURAL ] join_type table_reference [ USING ( join_column [, ...] ) ] table_reference [ INNER ] join_type table_reference ON expr ``` ## Parameters *with\$1subquery\$1table\$1name* A table defined by a subquery in the [WITH clause](WITH_clause.md). *table\$1name* Name of a table or view. *alias* Temporary alternative name for a table or view. An alias must be supplied for a table derived from a subquery. In other table references, aliases are optional. The AS keyword is always optional. Table aliases provide a convenient shortcut for identifying tables in other parts of a query, such as the WHERE clause. For example: ``` select * from sales s, listing l where s.listid=l.listid ``` If you define a table alias is defined, then the alias must be used to reference that table in the query. For example, if the query is `SELECT "tbl"."col" FROM "tbl" AS "t"`, the query would fail because the table name is essentially overridden now. A valid query in this case would be `SELECT "t"."col" FROM "tbl" AS "t"`. *column\$1alias* Temporary alternative name for a column in a table or view. *subquery* A query expression that evaluates to a table. The table exists only for the duration of the query and is typically given a name or *alias*. However, an alias isn't required. You can also define column names for tables that derive from subqueries. Naming column aliases is important when you want to join the results of subqueries to other tables and when you want to select or constrain those columns elsewhere in the query. A subquery may contain an ORDER BY clause, but this clause may have no effect if a LIMIT or OFFSET clause isn't also specified. NATURAL Defines a join that automatically uses all pairs of identically named columns in the two tables as the joining columns. No explicit join condition is required. For example, if the CATEGORY and EVENT tables both have columns named CATID, a natural join of those tables is a join over their CATID columns. If a NATURAL join is specified but no identically named pairs of columns exist in the tables to be joined, the query defaults to a cross-join. *join\$1type* Specify one of the following types of join: + [INNER] JOIN + LEFT [OUTER] JOIN + RIGHT [OUTER] JOIN + FULL [OUTER] JOIN + CROSS JOIN Cross-joins are unqualified joins; they return the Cartesian product of the two tables. Inner and outer joins are qualified joins. They are qualified either implicitly (in natural joins); with the ON or USING syntax in the FROM clause; or with a WHERE clause condition. An inner join returns matching rows only, based on the join condition or list of joining columns. An outer join returns all of the rows that the equivalent inner join would return plus non-matching rows from the "left" table, "right" table, or both tables. The left table is the first-listed table, and the right table is the second-listed table. The non-matching rows contain NULL values to fill the gaps in the output columns. ON *join\$1condition* Type of join specification where the joining columns are stated as a condition that follows the ON keyword. For example: ``` sales join listing on sales.listid=listing.listid and sales.eventid=listing.eventid ``` USING ( *join\$1column* [, ...] ) Type of join specification where the joining columns are listed in parentheses. If multiple joining columns are specified, they are delimited by commas. The USING keyword must precede the list. For example: ``` sales join listing using (listid,eventid) ``` ## Usage notes Joining columns must have comparable data types. A NATURAL or USING join retains only one of each pair of joining columns in the intermediate result set. A join with the ON syntax retains both joining columns in its intermediate result set. See also [WITH clause](WITH_clause.md). # JOIN clause A SQL JOIN clause is used to combine the data from two or more tables based on common fields. The results might or might not change depending on the join method specified. Left and right outer joins retain values from one of the joined tables when no match is found in the other table. The combination of the JOIN type and the join condition determines which rows are included in the final result set. The SELECT and WHERE clauses then control which columns are returned and how the rows are filtered. Understanding the different JOIN types and how to use them effectively is a crucial skill in SQL, because it allows you to combine data from multiple tables in a flexible and powerful way. ## Syntax ``` SELECT column1, column2, ..., columnn FROM table1 join_type table2 ON table1.column = table2.column; ``` ## Parameters *SELECT column1, column2, ..., columnN* The columns you want to include in the result set. You can select columns from either or both of the tables involved in the JOIN. *FROM table1* The first (left) table in the JOIN operation. *[JOIN \$1 INNER JOIN \$1 LEFT [OUTER] JOIN \$1 RIGHT [OUTER] JOIN \$1 FULL [OUTER] JOIN] table2: * The type of JOIN to be performed. JOIN or INNER JOIN returns only the rows with matching values in both tables. LEFT [OUTER] JOIN returns all rows from the left table, with matching rows from the right table. RIGHT [OUTER] JOIN returns all rows from the right table, with matching rows from the left table. FULL [OUTER] JOIN returns all rows from both tables, regardless of whether there is a match or not. CROSS JOIN creates a Cartesian product of the rows from the two tables. *ON table1.column = table2.column* The join condition, which specifies how the rows in the two tables are matched. The join condition can be based on one or more columns. *WHERE condition: * An optional clause that can be used to filter the result set further, based on a specified condition. ## Example The following example is a join between two tables with the USING clause. In this case, the columns listid and eventid are used as the join columns. The results are limited to five rows. ``` select listid, listing.sellerid, eventid, listing.dateid, numtickets from listing join sales using (listid, eventid) order by 1 limit 5; listid | sellerid | eventid | dateid | numtickets -------+----------+---------+--------+----------- 1 | 36861 | 7872 | 1850 | 10 4 | 8117 | 4337 | 1970 | 8 5 | 1616 | 8647 | 1963 | 4 5 | 1616 | 8647 | 1963 | 4 6 | 47402 | 8240 | 2053 | 18 ``` # Join types ## INNER This is the default join type. Returns the rows that have matching values in both table references. The INNER JOIN is the most common type of join used in SQL. It's a powerful way to combine data from multiple tables based on a common column or set of columns. **Syntax:** ``` SELECT column1, column2, ..., columnn FROM table1 INNER JOIN table2 ON table1.column = table2.column; ``` The following query will return all the rows where there is a matching customer\$1id value between the customers and orders tables. The result set will contain the customer\$1id, name, order\$1id, and order\$1date columns. ``` SELECT customers.customer_id, customers.name, orders.order_id, orders.order_date FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id; ``` The following query is an inner join (without the JOIN keyword) between the LISTING table and SALES table, where the LISTID from the LISTING table is between 1 and 5. This query matches LISTID column values in the LISTING table (the left table) and SALES table (the right table). The results show that LISTID 1, 4, and 5 match the criteria. ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from listing, sales where listing.listid = sales.listid and listing.listid between 1 and 5 group by 1 order by 1; listid | price | comm -------+--------+-------- 1 | 728.00 | 109.20 4 | 76.00 | 11.40 5 | 525.00 | 78.75 ``` The following example is an inner join with the ON clause. In this case, NULL rows are not returned. ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from sales join listing on sales.listid=listing.listid and sales.eventid=listing.eventid where listing.listid between 1 and 5 group by 1 order by 1; listid | price | comm -------+--------+-------- 1 | 728.00 | 109.20 4 | 76.00 | 11.40 5 | 525.00 | 78.75 ``` The following query is an inner join of two subqueries in the FROM clause. The query finds the number of sold and unsold tickets for different categories of events (concerts and shows). The FROM clause subqueries are *table* subqueries; they can return multiple columns and rows. ``` select catgroup1, sold, unsold from (select catgroup, sum(qtysold) as sold from category c, event e, sales s where c.catid = e.catid and e.eventid = s.eventid group by catgroup) as a(catgroup1, sold) join (select catgroup, sum(numtickets)-sum(qtysold) as unsold from category c, event e, sales s, listing l where c.catid = e.catid and e.eventid = s.eventid and s.listid = l.listid group by catgroup) as b(catgroup2, unsold) on a.catgroup1 = b.catgroup2 order by 1; catgroup1 | sold | unsold ----------+--------+-------- Concerts | 195444 |1067199 Shows | 149905 | 817736 ``` ## LEFT [ OUTER ] Returns all values from the left table reference and the matched values from the right table reference, or appends NULL if there is no match. It's also referred to as a *left outer join*. It returns all the rows from the left (first) table, and the matching rows from the right (second) table. If there is no match in the right table, the result set will contain NULL values for the columns from the right table. The OUTER keyword can be omitted, and the join can be written as simply LEFT JOIN. The opposite of a LEFT OUTER JOIN is a RIGHT OUTER JOIN, which returns all the rows from the right table and the matching rows from the left table. **Syntax:** ``` SELECT column1, column2, ..., columnn FROM table1 LEFT [OUTER] JOIN table2 ON table1.column = table2.column; ``` The following query will return all the rows from the customers table, along with the matching rows from the orders table. If a customer has no orders, the result set will still include that customer's information, with NULL values for the order\$1id and order\$1date columns. ``` SELECT customers.customer_id, customers.name, orders.order_id, orders.order_date FROM customers LEFT OUTER JOIN orders ON customers.customer_id = orders.customer_id; ``` The following query is a left outer join. Left and right outer joins retain values from one of the joined tables when no match is found in the other table. The left and right tables are the first and second tables listed in the syntax. NULL values are used to fill the "gaps" in the result set. This query matches LISTID column values in the LISTING table (the left table) and the SALES table (the right table). The results show that LISTIDs 2 and 3 didn't result in any sales. ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from listing left outer join sales on sales.listid = listing.listid where listing.listid between 1 and 5 group by 1 order by 1; listid | price | comm -------+--------+-------- 1 | 728.00 | 109.20 2 | NULL | NULL 3 | NULL | NULL 4 | 76.00 | 11.40 5 | 525.00 | 78.75 ``` ## RIGHT [ OUTER ] Returns all values from the right table reference and the matched values from the left table reference, or appends NULL if there is no match. It's also referred to as a *right outer join*. It returns all the rows from the right (second) table, and the matching rows from the left (first) table. If there is no match in the left table, the result set will contain NULL values for the columns from the left table. The OUTER keyword can be omitted, and the join can be written as simply RIGHT JOIN. The opposite of a RIGHT OUTER JOIN is a LEFT OUTER JOIN, which returns all the rows from the left table and the matching rows from the right table. **Syntax:** ``` SELECT column1, column2, ..., columnn FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column = table2.column; ``` The following query will return all the rows from the customers table, along with the matching rows from the orders table. If a customer has no orders, the result set will still include that customer's information, with NULL values for the order\$1id and order\$1date columns. ``` SELECT orders.order_id, orders.order_date, customers.customer_id, customers.name FROM orders RIGHT OUTER JOIN customers ON orders.customer_id = customers.customer_id; ``` The following query is a right outer join. This query matches LISTID column values in the LISTING table (the left table) and the SALES table (the right table). The results show that LISTIDs 1, 4, and 5 match the criteria. ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from listing right outer join sales on sales.listid = listing.listid where listing.listid between 1 and 5 group by 1 order by 1; listid | price | comm -------+--------+-------- 1 | 728.00 | 109.20 4 | 76.00 | 11.40 5 | 525.00 | 78.75 ``` ## FULL [OUTER] Returns all values from both relations, appending NULL values on the side that doesn't have a match. It's also referred to as a *full outer join*. It returns all the rows from both the left and right tables, regardless of whether there is a match or not. If there is no match, the result set will contain NULL values for the columns from the table that doesn't have a matching row. The OUTER keyword can be omitted, and the join can be written as simply FULL JOIN. The FULL OUTER JOIN is less commonly used than the LEFT OUTER JOIN or RIGHT OUTER JOIN, but it can be useful in certain scenarios where you need to see all the data from both tables, even if there are no matches. **Syntax:** ``` SELECT column1, column2, ..., columnn FROM table1 FULL [OUTER] JOIN table2 ON table1.column = table2.column; ``` The following query will return all the rows from both the customers and orders tables. If a customer has no orders, the result set will still include that customer's information, with NULL values for the order\$1id and order\$1date columns. If an order has no associated customer, the result set will include that order, with NULL values for the customer\$1id and name columns. ``` SELECT customers.customer_id, customers.name, orders.order_id, orders.order_date FROM customers FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id; ``` The following query is a full join. Full joins retain values from the joined tables when no match is found in the other table. The left and right tables are the first and second tables listed in the syntax. NULL values are used to fill the "gaps" in the result set. This query matches LISTID column values in the LISTING table (the left table) and the SALES table (the right table). The results show that LISTIDs 2 and 3 didn't result in any sales. ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from listing full join sales on sales.listid = listing.listid where listing.listid between 1 and 5 group by 1 order by 1; listid | price | comm -------+--------+-------- 1 | 728.00 | 109.20 2 | NULL | NULL 3 | NULL | NULL 4 | 76.00 | 11.40 5 | 525.00 | 78.75 ``` The following query is a full join. This query matches LISTID column values in the LISTING table (the left table) and the SALES table (the right table). Only rows that do not result in any sales (LISTIDs 2 and 3) are in the results. ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from listing full join sales on sales.listid = listing.listid where listing.listid between 1 and 5 and (listing.listid IS NULL or sales.listid IS NULL) group by 1 order by 1; listid | price | comm -------+--------+-------- 2 | NULL | NULL 3 | NULL | NULL ``` ## [ LEFT ] SEMI Returns values from the left side of the table reference that has a match with the right. It's also referred to as a *left semi join*. It returns only the rows from the left (first) table that have a matching row in the right (second) table. It does not return any columns from the right table - only the columns from the left table. The LEFT SEMI JOIN is useful when you want to find the rows in one table that have a match in another table, without needing to return any data from the second table. The LEFT SEMI JOIN is a more efficient alternative to using a subquery with an IN or EXISTS clause. **Syntax:** ``` SELECT column1, column2, ..., columnn FROM table1 LEFT SEMI JOIN table2 ON table1.column = table2.column; ``` The following query will return only the customer\$1id and name columns from the customers table, for the customers who have at least one order in the orders table. The result set won't include any columns from the orders table. ``` SELECT customers.customer_id, customers.name FROM customers LEFT SEMI JOIN orders ON customers.customer_id = orders.customer_id; ``` ## CROSS JOIN Returns the Cartesian product of two relations. This means that the result set will contain all possible combinations of rows from the two tables, without any condition or filter applied. The CROSS JOIN is useful when you need to generate all possible combinations of data from two tables, such as in the case of creating a report that displays all possible combinations of customer and product information. The CROSS JOIN is different from other join types (INNER JOIN, LEFT JOIN, etc.) because it doesn't have a join condition in the ON clause. The join condition isn't required for a CROSS JOIN. **Syntax:** ``` SELECT column1, column2, ..., columnn FROM table1 CROSS JOIN table2; ``` The following query will return a result set that contains all possible combinations of customer\$1id, customer\$1name, product\$1id, and product\$1name from the customers and products tables. If the customers table has 10 rows and the products table has 20 rows, the result set of the CROSS JOIN will contain 10 x 20 = 200 rows. ``` SELECT customers.customer_id, customers.name, products.product_id, products.product_name FROM customers CROSS JOIN products; ``` The following query is a cross join or Cartesian join of the LISTING table and the SALES table with a predicate to limit the results. This query matches LISTID column values in the SALES table and the LISTING table for LISTIDs 1, 2, 3, 4, and 5 in both tables. The results show that 20 rows match the criteria. ``` select sales.listid as sales_listid, listing.listid as listing_listid from sales cross join listing where sales.listid between 1 and 5 and listing.listid between 1 and 5 order by 1,2; sales_listid | listing_listid -------------+--------------- 1 | 1 1 | 2 1 | 3 1 | 4 1 | 5 4 | 1 4 | 2 4 | 3 4 | 4 4 | 5 5 | 1 5 | 1 5 | 2 5 | 2 5 | 3 5 | 3 5 | 4 5 | 4 5 | 5 5 | 5 ``` ## ANTI JOIN Returns the values from the left table reference that have no match with the right table reference. It's also referred to as a *left anti join*. The ANTI JOIN is a useful operation when you want to find the rows in one table that don't have a match in another table. **Syntax:** ``` SELECT column1, column2, ..., columnn FROM table1 LEFT ANTI JOIN table2 ON table1.column = table2.column; ``` The following query will return all the customers who haven't placed any orders. ``` SELECT customers.customer_id, customers.name FROM customers LEFT ANTI JOIN orders ON customers.customer_id = orders.customer_id WHERE orders.order_id IS NULL; ``` ## NATURAL Specifies that the rows from the two relations will implicitly be matched on equality for all columns with matching names. It automatically matches columns with the same name and data type between the two tables. It doesn't require you to explicitly specify the join condition in the ON clause. It combines all the matching columns between the two tables into the result set. The NATURAL JOIN is a convenient shorthand when the tables you're joining have columns with the same names and data types. However, it's generally recommended to use the more explicit INNER JOIN ... ON syntax to make the join conditions more explicit and easier to understand. **Syntax:** ``` SELECT column1, column2, ..., columnn FROM table1 NATURAL JOIN table2; ``` The following example is a natural join between two tables, `employees` and `departments`, with the following columns: + `employees` table: `employee_id`, `first_name`, `last_name`, `department_id ` + `departments` table: `department_id`, `department_name ` The following query will return a result set that includes the first name, last name, and department name for all matching rows between the two tables, based on the `department_id` column. ``` SELECT e.first_name, e.last_name, d.department_name FROM employees e NATURAL JOIN departments d; ``` The following example is a natural join between two tables. In this case, the columns listid, sellerid, eventid, and dateid have identical names and data types in both tables and so are used as the join columns. The results are limited to five rows. ``` select listid, sellerid, eventid, dateid, numtickets from listing natural join sales order by 1 limit 5; listid | sellerid | eventid | dateid | numtickets -------+-----------+---------+--------+----------- 113 | 29704 | 4699 | 2075 | 22 115 | 39115 | 3513 | 2062 | 14 116 | 43314 | 8675 | 1910 | 28 118 | 6079 | 1611 | 1862 | 9 163 | 24880 | 8253 | 1888 | 14 ``` # WHERE clause The WHERE clause contains conditions that either join tables or apply predicates to columns in tables. Tables can be inner-joined by using appropriate syntax in either the WHERE clause or the FROM clause. Outer join criteria must be specified in the FROM clause. ## Syntax ``` [ WHERE condition ] ``` ## *condition* Any search condition with a Boolean result, such as a join condition or a predicate on a table column. The following examples are valid join conditions: ``` sales.listid=listing.listid sales.listid<>listing.listid ``` The following examples are valid conditions on columns in tables: ``` catgroup like 'S%' venueseats between 20000 and 50000 eventname in('Jersey Boys','Spamalot') year=2008 length(catdesc)>25 date_part(month, caldate)=6 ``` Conditions can be simple or complex; for complex conditions, you can use parentheses to isolate logical units. In the following example, the join condition is enclosed by parentheses. ``` where (category.catid=event.catid) and category.catid in(6,7,8) ``` ## Usage notes You can use aliases in the WHERE clause to reference select list expressions. You can't restrict the results of aggregate functions in the WHERE clause; use the HAVING clause for this purpose. Columns that are restricted in the WHERE clause must derive from table references in the FROM clause. ## Example The following query uses a combination of different WHERE clause restrictions, including a join condition for the SALES and EVENT tables, a predicate on the EVENTNAME column, and two predicates on the STARTTIME column. ``` select eventname, starttime, pricepaid/qtysold as costperticket, qtysold from sales, event where sales.eventid = event.eventid and eventname='Hannah Montana' and date_part(quarter, starttime) in(1,2) and date_part(year, starttime) = 2008 order by 3 desc, 4, 2, 1 limit 10; eventname | starttime | costperticket | qtysold ----------------+---------------------+-------------------+--------- Hannah Montana | 2008-06-07 14:00:00 | 1706.00000000 | 2 Hannah Montana | 2008-05-01 19:00:00 | 1658.00000000 | 2 Hannah Montana | 2008-06-07 14:00:00 | 1479.00000000 | 1 Hannah Montana | 2008-06-07 14:00:00 | 1479.00000000 | 3 Hannah Montana | 2008-06-07 14:00:00 | 1163.00000000 | 1 Hannah Montana | 2008-06-07 14:00:00 | 1163.00000000 | 2 Hannah Montana | 2008-06-07 14:00:00 | 1163.00000000 | 4 Hannah Montana | 2008-05-01 19:00:00 | 497.00000000 | 1 Hannah Montana | 2008-05-01 19:00:00 | 497.00000000 | 2 Hannah Montana | 2008-05-01 19:00:00 | 497.00000000 | 4 (10 rows) ``` # VALUES clause The VALUES clause is used to provide a set of row values directly in the query, without the need to reference a table. The VALUES clause can be used in the following scenarios: + You can use the VALUES clause in an INSERT INTO statement to specify the values for the new rows being inserted into a table. + You can use the VALUES clause on its own to create a temporary result set, or inline table, without the need to reference a table. + You can combine the VALUES clause with other SQL clauses, such as WHERE, ORDER BY, or LIMIT, to filter, sort, or limit the rows in the result set. This clause is particularly useful when you need to insert, query, or manipulate a small set of data directly in your SQL statement, without the need to create or reference a permanent table. It allows you to define the column names and the corresponding values for each row, giving you the flexibility to create temporary result sets or insert data on the fly, without the overhead of managing a separate table. ## Syntax ``` VALUES ( expression [ , ... ] ) [ table_alias ] ``` ## Parameters *expression* An expression that specifies a combination of one or more values, operators and SQL functions that results in a value. *table\$1alias* An alias that specifies a temporary name with an optional column name list. ## Example The following example creates an inline table, temporary table-like result set with two columns, `col1` and `col2`. The single row in the result set contains the values `"one"` and `1`, respectively. The `SELECT * FROM` part of the query simply retrieves all the columns and rows from this temporary result set. The column names (`col1` and `col2`) are automatically generated by the database system, because the VALUES clause doesn't explicitly specify the column names. ``` SELECT * FROM VALUES ("one", 1); +----+----+ |col1|col2| +----+----+ | one| 1| +----+----+ ``` If you want to define custom column names, you can do so by using an AS clause after the VALUES clause, like this: ``` SELECT * FROM (VALUES ("one", 1)) AS my_table (name, id); +------+----+ | name | id | +------+----+ | one | 1 | +------+----+ ``` This would create a temporary result set with the column names `name` and `id`, instead of the default `col1` and `col2`. # GROUP BY clause The GROUP BY clause identifies the grouping columns for the query. Grouping columns must be declared when the query computes aggregates with standard functions such as SUM, AVG, and COUNT. If an aggregate function is present in the SELECT expression, any column in the SELECT expression that is not in an aggregate function must be in the GROUP BY clause. For more information, see [AWS Clean Rooms Spark SQL functions](sql-functions-topic-spark.md). ## Syntax ``` GROUP BY group_by_clause [, ...] group_by_clause := { expr | ROLLUP ( expr [, ...] ) | } ``` ## *Parameters* *expr* The list of columns or expressions must match the list of non-aggregate expressions in the select list of the query. For example, consider the following simple query. ``` select listid, eventid, sum(pricepaid) as revenue, count(qtysold) as numtix from sales group by listid, eventid order by 3, 4, 2, 1 limit 5; listid | eventid | revenue | numtix -------+---------+---------+-------- 89397 | 47 | 20.00 | 1 106590 | 76 | 20.00 | 1 124683 | 393 | 20.00 | 1 103037 | 403 | 20.00 | 1 147685 | 429 | 20.00 | 1 (5 rows) ``` In this query, the select list consists of two aggregate expressions. The first uses the SUM function and the second uses the COUNT function. The remaining two columns, LISTID and EVENTID, must be declared as grouping columns. Expressions in the GROUP BY clause can also reference the select list by using ordinal numbers. For example, the previous example could be abbreviated as follows. ``` select listid, eventid, sum(pricepaid) as revenue, count(qtysold) as numtix from sales group by 1,2 order by 3, 4, 2, 1 limit 5; listid | eventid | revenue | numtix -------+---------+---------+-------- 89397 | 47 | 20.00 | 1 106590 | 76 | 20.00 | 1 124683 | 393 | 20.00 | 1 103037 | 403 | 20.00 | 1 147685 | 429 | 20.00 | 1 (5 rows) ``` *ROLLUP* You can use the aggregation extension ROLLUP to perform the work of multiple GROUP BY operations in a single statement. For more information on aggregation extensions and related functions, see [Aggregation extensions](GROUP_BY_aggregation-extensions.md). # Aggregation extensions AWS Clean Rooms supports aggregation extensions to do the work of multiple GROUP BY operations in a single statement. ## *GROUPING SETS* Computes one or more grouping sets in a single statement. A grouping set is the set of a single GROUP BY clause, a set of 0 or more columns by which you can group a query's result set. GROUP BY GROUPING SETS is equivalent to running a UNION ALL query on one result set grouped by different columns. For example, GROUP BY GROUPING SETS((a), (b)) is equivalent to GROUP BY a UNION ALL GROUP BY b. The following example returns the cost of the order table's products grouped according to both the products' categories and the kind of products sold. ``` SELECT category, product, sum(cost) as total FROM orders GROUP BY GROUPING SETS(category, product); category | product | total ----------------------+----------------------+------- computers | | 2100 cellphones | | 1610 | laptop | 2050 | smartphone | 1610 | mouse | 50 (5 rows) ``` ## *ROLLUP* Assumes a hierarchy where preceding columns are considered the parents of subsequent columns. ROLLUP groups data by the provided columns, returning extra subtotal rows representing the totals throughout all levels of grouping columns, in addition to the grouped rows. For example, you can use GROUP BY ROLLUP((a), (b)) to return a result set grouped first by a, then by b while assuming that b is a subsection of a. ROLLUP also returns a row with the whole result set without grouping columns. GROUP BY ROLLUP((a), (b)) is equivalent to GROUP BY GROUPING SETS((a,b), (a), ()). The following example returns the cost of the order table's products grouped first by category and then product, with product as a subdivision of category. ``` SELECT category, product, sum(cost) as total FROM orders GROUP BY ROLLUP(category, product) ORDER BY 1,2; category | product | total ----------------------+----------------------+------- cellphones | smartphone | 1610 cellphones | | 1610 computers | laptop | 2050 computers | mouse | 50 computers | | 2100 | | 3710 (6 rows) ``` ## *CUBE* Groups data by the provided columns, returning extra subtotal rows representing the totals throughout all levels of grouping columns, in addition to the grouped rows. CUBE returns the same rows as ROLLUP, while adding additional subtotal rows for every combination of grouping column not covered by ROLLUP. For example, you can use GROUP BY CUBE ((a), (b)) to return a result set grouped first by a, then by b while assuming that b is a subsection of a, then by b alone. CUBE also returns a row with the whole result set without grouping columns. GROUP BY CUBE((a), (b)) is equivalent to GROUP BY GROUPING SETS((a, b), (a), (b), ()). The following example returns the cost of the order table's products grouped first by category and then product, with product as a subdivision of category. Unlike the preceding example for ROLLUP, the statement returns results for every combination of grouping column. ``` SELECT category, product, sum(cost) as total FROM orders GROUP BY CUBE(category, product) ORDER BY 1,2; category | product | total ----------------------+----------------------+------- cellphones | smartphone | 1610 cellphones | | 1610 computers | laptop | 2050 computers | mouse | 50 computers | | 2100 | laptop | 2050 | mouse | 50 | smartphone | 1610 | | 3710 (9 rows) ``` # HAVING clause The HAVING clause applies a condition to the intermediate grouped result set that a query returns. ## Syntax ``` [ HAVING condition ] ``` For example, you can restrict the results of a SUM function: ``` having sum(pricepaid) >10000 ``` The HAVING condition is applied after all WHERE clause conditions are applied and GROUP BY operations are completed. The condition itself takes the same form as any WHERE clause condition. ## Usage notes + Any column that is referenced in a HAVING clause condition must be either a grouping column or a column that refers to the result of an aggregate function. + In a HAVING clause, you can't specify: + An ordinal number that refers to a select list item. Only the GROUP BY and ORDER BY clauses accept ordinal numbers. ## Examples The following query calculates total ticket sales for all events by name, then eliminates events where the total sales were less than \$1800,000. The HAVING condition is applied to the results of the aggregate function in the select list: `sum(pricepaid)`. ``` select eventname, sum(pricepaid) from sales join event on sales.eventid = event.eventid group by 1 having sum(pricepaid) > 800000 order by 2 desc, 1; eventname | sum ------------------+----------- Mamma Mia! | 1135454.00 Spring Awakening | 972855.00 The Country Girl | 910563.00 Macbeth | 862580.00 Jersey Boys | 811877.00 Legally Blonde | 804583.00 (6 rows) ``` The following query calculates a similar result set. In this case, however, the HAVING condition is applied to an aggregate that isn't specified in the select list: `sum(qtysold)`. Events that did not sell more than 2,000 tickets are eliminated from the final result. ``` select eventname, sum(pricepaid) from sales join event on sales.eventid = event.eventid group by 1 having sum(qtysold) >2000 order by 2 desc, 1; eventname | sum ------------------+----------- Mamma Mia! | 1135454.00 Spring Awakening | 972855.00 The Country Girl | 910563.00 Macbeth | 862580.00 Jersey Boys | 811877.00 Legally Blonde | 804583.00 Chicago | 790993.00 Spamalot | 714307.00 (8 rows) ``` # Set operators The *set operators* are used to compare and merge the results of two separate query expressions. AWS Clean Rooms Spark SQL supports the following set operators listed in the following table. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/clean-rooms/latest/sql-reference/UNION.html) For example, if you want to know which users of a website are both buyers and sellers but their user names are stored in separate columns or tables, you can find the *intersection* of these two types of users. If you want to know which website users are buyers but not sellers, you can use the EXCEPT operator to find the *difference* between the two lists of users. If you want to build a list of all users, regardless of role, you can use the UNION operator. **Note** The ORDER BY, LIMIT, SELECT TOP, and OFFSET clauses can't be used in the query expressions merged by the UNION, UNION ALL, INTERSECT, and EXCEPT set operators. **Topics** + [Syntax](#UNION-synopsis) + [Parameters](#UNION-parameters) + [Order of evaluation for set operators](#UNION-order-of-evaluation-for-set-operators) + [Usage notes](#UNION-usage-notes) + [Example UNION queries](example_union_query.md) + [Example UNION ALL query](example_unionall_query.md) + [Example INTERSECT queries](example_intersect_query.md) + [Example EXCEPT query](Example_EXCEPT_query.md) ## Syntax ``` subquery1 { { UNION [ ALL | DISTINCT ] | INTERSECT [ ALL | DISTINCT ] | EXCEPT [ ALL | DISTINCT ] } subquery2 } [...] } ``` ## Parameters *subquery1, subquery2* A query expression that corresponds, in the form of its select list, to a second query expression that follows the UNION, UNION ALL, INTERSECT, INTERSECT ALL, EXCEPT, or EXCEPT ALL operator. The two expressions must contain the same number of output columns with compatible data types; otherwise, the two result sets can't be compared and merged. Set operations don't allow implicit conversion between different categories of data types. For more information, see [Type compatibility and conversion](s_Type_conversion.md). You can build queries that contain an unlimited number of query expressions and link them with UNION, INTERSECT, and EXCEPT operators in any combination. For example, the following query structure is valid, assuming that the tables T1, T2, and T3 contain compatible sets of columns: ``` select * from t1 union select * from t2 except select * from t3 ``` UNION [ALL \$1 DISTINCT] Set operation that returns rows from two query expressions, regardless of whether the rows derive from one or both expressions. INTERSECT [ALL \$1 DISTINCT] Set operation that returns rows that derive from two query expressions. Rows that aren't returned by both expressions are discarded. EXCEPT [ALL \$1 DISTINCT] Set operation that returns rows that derive from one of two query expressions. To qualify for the result, rows must exist in the first result table but not the second. EXCEPT ALL doesn't remove duplicates from the result rows. MINUS and EXCEPT are exact synonyms. ## Order of evaluation for set operators The UNION and EXCEPT set operators are left-associative. If parentheses aren't specified to influence the order of precedence, a combination of these set operators is evaluated from left to right. For example, in the following query, the UNION of T1 and T2 is evaluated first, then the EXCEPT operation is performed on the UNION result: ``` select * from t1 union select * from t2 except select * from t3 ``` The INTERSECT operator takes precedence over the UNION and EXCEPT operators when a combination of operators is used in the same query. For example, the following query evaluates the intersection of T2 and T3, then union the result with T1: ``` select * from t1 union select * from t2 intersect select * from t3 ``` By adding parentheses, you can enforce a different order of evaluation. In the following case, the result of the union of T1 and T2 is intersected with T3, and the query is likely to produce a different result. ``` (select * from t1 union select * from t2) intersect (select * from t3) ``` ## Usage notes + The column names returned in the result of a set operation query are the column names (or aliases) from the tables in the first query expression. Because these column names are potentially misleading, in that the values in the column derive from tables on either side of the set operator, you might want to provide meaningful aliases for the result set. + When set operator queries return decimal results, the corresponding result columns are promoted to return the same precision and scale. For example, in the following query, where T1.REVENUE is a DECIMAL(10,2) column and T2.REVENUE is a DECIMAL(8,4) column, the decimal result is promoted to DECIMAL(12,4): ``` select t1.revenue union select t2.revenue; ``` The scale is `4` because that is the maximum scale of the two columns. The precision is `12` because T1.REVENUE requires 8 digits to the left of the decimal point (12 - 4 = 8). This type promotion ensures that all values from both sides of the UNION fit in the result. For 64-bit values, the maximum result precision is 19 and the maximum result scale is 18. For 128-bit values, the maximum result precision is 38 and the maximum result scale is 37. If the resulting data type exceeds AWS Clean Rooms precision and scale limits, the query returns an error. + For set operations, two rows are treated as identical if, for each corresponding pair of columns, the two data values are either *equal* or *both NULL*. For example, if tables T1 and T2 both contain one column and one row, and that row is NULL in both tables, an INTERSECT operation over those tables returns that row. # Example UNION queries In the following UNION query, rows in the SALES table are merged with rows in the LISTING table. Three compatible columns are selected from each table; in this case, the corresponding columns have the same names and data types. ``` select listid, sellerid, eventid from listing union select listid, sellerid, eventid from sales listid | sellerid | eventid --------+----------+--------- 1 | 36861 | 7872 2 | 16002 | 4806 3 | 21461 | 4256 4 | 8117 | 4337 5 | 1616 | 8647 ``` The following example shows how you can add a literal value to the output of a UNION query so you can see which query expression produced each row in the result set. The query identifies rows from the first query expression as "B" (for buyers) and rows from the second query expression as "S" (for sellers). The query identifies buyers and sellers for ticket transactions that cost \$110,000 or more. The only difference between the two query expressions on either side of the UNION operator is the joining column for the SALES table. ``` select listid, lastname, firstname, username, pricepaid as price, 'S' as buyorsell from sales, users where sales.sellerid=users.userid and pricepaid >=10000 union select listid, lastname, firstname, username, pricepaid, 'B' as buyorsell from sales, users where sales.buyerid=users.userid and pricepaid >=10000 listid | lastname | firstname | username | price | buyorsell --------+----------+-----------+----------+-----------+----------- 209658 | Lamb | Colette | VOR15LYI | 10000.00 | B 209658 | West | Kato | ELU81XAA | 10000.00 | S 212395 | Greer | Harlan | GXO71KOC | 12624.00 | S 212395 | Perry | Cora | YWR73YNZ | 12624.00 | B 215156 | Banks | Patrick | ZNQ69CLT | 10000.00 | S 215156 | Hayden | Malachi | BBG56AKU | 10000.00 | B ``` The following example uses a UNION ALL operator because duplicate rows, if found, need to be retained in the result. For a specific series of event IDs, the query returns 0 or more rows for each sale associated with each event, and 0 or 1 row for each listing of that event. Event IDs are unique to each row in the LISTING and EVENT tables, but there might be multiple sales for the same combination of event and listing IDs in the SALES table. The third column in the result set identifies the source of the row. If it comes from the SALES table, it is marked "Yes" in the SALESROW column. (SALESROW is an alias for SALES.LISTID.) If the row comes from the LISTING table, it is marked "No" in the SALESROW column. In this case, the result set consists of three sales rows for listing 500, event 7787. In other words, three different transactions took place for this listing and event combination. The other two listings, 501 and 502, did not produce any sales, so the only row that the query produces for these list IDs comes from the LISTING table (SALESROW = 'No'). ``` select eventid, listid, 'Yes' as salesrow from sales where listid in(500,501,502) union all select eventid, listid, 'No' from listing where listid in(500,501,502) eventid | listid | salesrow ---------+--------+---------- 7787 | 500 | No 7787 | 500 | Yes 7787 | 500 | Yes 7787 | 500 | Yes 6473 | 501 | No 5108 | 502 | No ``` If you run the same query without the ALL keyword, the result retains only one of the sales transactions. ``` select eventid, listid, 'Yes' as salesrow from sales where listid in(500,501,502) union select eventid, listid, 'No' from listing where listid in(500,501,502) eventid | listid | salesrow ---------+--------+---------- 7787 | 500 | No 7787 | 500 | Yes 6473 | 501 | No 5108 | 502 | No ``` # Example UNION ALL query The following example uses a UNION ALL operator because duplicate rows, if found, need to be retained in the result. For a specific series of event IDs, the query returns 0 or more rows for each sale associated with each event, and 0 or 1 row for each listing of that event. Event IDs are unique to each row in the LISTING and EVENT tables, but there might be multiple sales for the same combination of event and listing IDs in the SALES table. The third column in the result set identifies the source of the row. If it comes from the SALES table, it is marked "Yes" in the SALESROW column. (SALESROW is an alias for SALES.LISTID.) If the row comes from the LISTING table, it is marked "No" in the SALESROW column. In this case, the result set consists of three sales rows for listing 500, event 7787. In other words, three different transactions took place for this listing and event combination. The other two listings, 501 and 502, did not produce any sales, so the only row that the query produces for these list IDs comes from the LISTING table (SALESROW = 'No'). ``` select eventid, listid, 'Yes' as salesrow from sales where listid in(500,501,502) union all select eventid, listid, 'No' from listing where listid in(500,501,502) eventid | listid | salesrow ---------+--------+---------- 7787 | 500 | No 7787 | 500 | Yes 7787 | 500 | Yes 7787 | 500 | Yes 6473 | 501 | No 5108 | 502 | No ``` If you run the same query without the ALL keyword, the result retains only one of the sales transactions. ``` select eventid, listid, 'Yes' as salesrow from sales where listid in(500,501,502) union select eventid, listid, 'No' from listing where listid in(500,501,502) eventid | listid | salesrow ---------+--------+---------- 7787 | 500 | No 7787 | 500 | Yes 6473 | 501 | No 5108 | 502 | No ``` # Example INTERSECT queries Compare the following example with the first UNION example. The only difference between the two examples is the set operator that is used, but the results are very different. Only one of the rows is the same: ``` 235494 | 23875 | 8771 ``` This is the only row in the limited result of 5 rows that was found in both tables. ``` select listid, sellerid, eventid from listing intersect select listid, sellerid, eventid from sales listid | sellerid | eventid --------+----------+--------- 235494 | 23875 | 8771 235482 | 1067 | 2667 235479 | 1589 | 7303 235476 | 15550 | 793 235475 | 22306 | 7848 ``` The following query finds events (for which tickets were sold) that occurred at venues in both New York City and Los Angeles in March. The difference between the two query expressions is the constraint on the VENUECITY column. ``` select distinct eventname from event, sales, venue where event.eventid=sales.eventid and event.venueid=venue.venueid and date_part(month,starttime)=3 and venuecity='Los Angeles' intersect select distinct eventname from event, sales, venue where event.eventid=sales.eventid and event.venueid=venue.venueid and date_part(month,starttime)=3 and venuecity='New York City'; eventname ---------------------------- A Streetcar Named Desire Dirty Dancing Electra Running with Annalise Hairspray Mary Poppins November Oliver! Return To Forever Rhinoceros South Pacific The 39 Steps The Bacchae The Caucasian Chalk Circle The Country Girl Wicked Woyzeck ``` # Example EXCEPT query The CATEGORY table in the database contains the following 11 rows: ``` catid | catgroup | catname | catdesc -------+----------+-----------+-------------------------------------------- 1 | Sports | MLB | Major League Baseball 2 | Sports | NHL | National Hockey League 3 | Sports | NFL | National Football League 4 | Sports | NBA | National Basketball Association 5 | Sports | MLS | Major League Soccer 6 | Shows | Musicals | Musical theatre 7 | Shows | Plays | All non-musical theatre 8 | Shows | Opera | All opera and light opera 9 | Concerts | Pop | All rock and pop music concerts 10 | Concerts | Jazz | All jazz singers and bands 11 | Concerts | Classical | All symphony, concerto, and choir concerts (11 rows) ``` Assume that a CATEGORY\$1STAGE table (a staging table) contains one additional row: ``` catid | catgroup | catname | catdesc -------+----------+-----------+-------------------------------------------- 1 | Sports | MLB | Major League Baseball 2 | Sports | NHL | National Hockey League 3 | Sports | NFL | National Football League 4 | Sports | NBA | National Basketball Association 5 | Sports | MLS | Major League Soccer 6 | Shows | Musicals | Musical theatre 7 | Shows | Plays | All non-musical theatre 8 | Shows | Opera | All opera and light opera 9 | Concerts | Pop | All rock and pop music concerts 10 | Concerts | Jazz | All jazz singers and bands 11 | Concerts | Classical | All symphony, concerto, and choir concerts 12 | Concerts | Comedy | All stand up comedy performances (12 rows) ``` Return the difference between the two tables. In other words, return rows that are in the CATEGORY\$1STAGE table but not in the CATEGORY table: ``` select * from category_stage except select * from category; catid | catgroup | catname | catdesc -------+----------+---------+---------------------------------- 12 | Concerts | Comedy | All stand up comedy performances (1 row) ``` The following equivalent query uses the synonym MINUS. ``` select * from category_stage minus select * from category; catid | catgroup | catname | catdesc -------+----------+---------+---------------------------------- 12 | Concerts | Comedy | All stand up comedy performances (1 row) ``` If you reverse the order of the SELECT expressions, the query returns no rows. # ORDER BY clause The ORDER BY clause sorts the result set of a query. **Note** The outermost ORDER BY expression must only have columns that are in the select list. **Topics** + [Syntax](#ORDER_BY_clause-synopsis) + [Parameters](#ORDER_BY_clause-parameters) + [Usage notes](#ORDER_BY_usage_notes) + [Examples with ORDER BY](Examples_with_ORDER_BY.md) ## Syntax ``` [ ORDER BY expression [ ASC | DESC ] ] [ NULLS FIRST | NULLS LAST ] [ LIMIT { count | ALL } ] [ OFFSET start ] ``` ## Parameters *expression* Expression that defines the sort order of the query result. It consists of one or more columns in the select list. Results are returned based on binary UTF-8 ordering. You can also specify the following: + Ordinal numbers that represent the position of select list entries (or the position of columns in the table if no select list exists) + Aliases that define select list entries When the ORDER BY clause contains multiple expressions, the result set is sorted according to the first expression, then the second expression is applied to rows that have matching values from the first expression, and so on. ASC \$1 DESC Option that defines the sort order for the expression, as follows: + ASC: ascending (for example, low to high for numeric values and 'A' to 'Z' for character strings). If no option is specified, data is sorted in ascending order by default. + DESC: descending (high to low for numeric values; 'Z' to 'A' for strings). NULLS FIRST \$1 NULLS LAST Option that specifies whether NULL values should be ordered first, before non-null values, or last, after non-null values. By default, NULL values are sorted and ranked last in ASC ordering, and sorted and ranked first in DESC ordering. LIMIT *number* \$1 ALL Option that controls the number of sorted rows that the query returns. The LIMIT number must be a positive integer; the maximum value is `2147483647`. LIMIT 0 returns no rows. You can use this syntax for testing purposes: to check that a query runs (without displaying any rows) or to return a column list from a table. An ORDER BY clause is redundant if you are using LIMIT 0 to return a column list. The default is LIMIT ALL. OFFSET *start* Option that specifies to skip the number of rows before *start* before beginning to return rows. The OFFSET number must be a positive integer; the maximum value is `2147483647`. When used with the LIMIT option, OFFSET rows are skipped before starting to count the LIMIT rows that are returned. If the LIMIT option isn't used, the number of rows in the result set is reduced by the number of rows that are skipped. The rows skipped by an OFFSET clause still have to be scanned, so it might be inefficient to use a large OFFSET value. ## Usage notes Note the following expected behavior with ORDER BY clauses: + NULL values are considered "higher" than all other values. With the default ascending sort order, NULL values sort at the end. To change this behavior, use the NULLS FIRST option. + When a query doesn't contain an ORDER BY clause, the system returns result sets with no predictable ordering of the rows. The same query run twice might return the result set in a different order. + The LIMIT and OFFSET options can be used without an ORDER BY clause; however, to return a consistent set of rows, use these options in conjunction with ORDER BY. + In any parallel system like AWS Clean Rooms, when ORDER BY doesn't produce a unique ordering, the order of the rows is nondeterministic. That is, if the ORDER BY expression produces duplicate values, the return order of those rows might vary from other systems or from one run of AWS Clean Rooms to the next. + AWS Clean Rooms doesn't support string literals in ORDER BY clauses. # Examples with ORDER BY Return all 11 rows from the CATEGORY table, ordered by the second column, CATGROUP. For results that have the same CATGROUP value, order the CATDESC column values by the length of the character string. Then order by columns CATID and CATNAME. ``` select * from category order by 2, 1, 3; catid | catgroup | catname | catdesc -------+----------+-----------+---------------------------------------- 10 | Concerts | Jazz | All jazz singers and bands 9 | Concerts | Pop | All rock and pop music concerts 11 | Concerts | Classical | All symphony, concerto, and choir conce 6 | Shows | Musicals | Musical theatre 7 | Shows | Plays | All non-musical theatre 8 | Shows | Opera | All opera and light opera 5 | Sports | MLS | Major League Soccer 1 | Sports | MLB | Major League Baseball 2 | Sports | NHL | National Hockey League 3 | Sports | NFL | National Football League 4 | Sports | NBA | National Basketball Association (11 rows) ``` Return selected columns from the SALES table, ordered by the highest QTYSOLD values. Limit the result to the top 10 rows: ``` select salesid, qtysold, pricepaid, commission, saletime from sales order by qtysold, pricepaid, commission, salesid, saletime desc salesid | qtysold | pricepaid | commission | saletime ---------+---------+-----------+------------+--------------------- 15401 | 8 | 272.00 | 40.80 | 2008-03-18 06:54:56 61683 | 8 | 296.00 | 44.40 | 2008-11-26 04:00:23 90528 | 8 | 328.00 | 49.20 | 2008-06-11 02:38:09 74549 | 8 | 336.00 | 50.40 | 2008-01-19 12:01:21 130232 | 8 | 352.00 | 52.80 | 2008-05-02 05:52:31 55243 | 8 | 384.00 | 57.60 | 2008-07-12 02:19:53 16004 | 8 | 440.00 | 66.00 | 2008-11-04 07:22:31 489 | 8 | 496.00 | 74.40 | 2008-08-03 05:48:55 4197 | 8 | 512.00 | 76.80 | 2008-03-23 11:35:33 16929 | 8 | 568.00 | 85.20 | 2008-12-19 02:59:33 ``` Return a column list and no rows by using LIMIT 0 syntax: ``` select * from venue limit 0; venueid | venuename | venuecity | venuestate | venueseats ---------+-----------+-----------+------------+------------ (0 rows) ``` # Subquery examples The following examples show different ways in which subqueries fit into SELECT queries. See [Example](join-clause.md#Join_examples) for another example of the use of subqueries. ## SELECT list subquery The following example contains a subquery in the SELECT list. This subquery is *scalar*: it returns only one column and one value, which is repeated in the result for each row that is returned from the outer query. The query compares the Q1SALES value that the subquery computes with sales values for two other quarters (2 and 3) in 2008, as defined by the outer query. ``` select qtr, sum(pricepaid) as qtrsales, (select sum(pricepaid) from sales join date on sales.dateid=date.dateid where qtr='1' and year=2008) as q1sales from sales join date on sales.dateid=date.dateid where qtr in('2','3') and year=2008 group by qtr order by qtr; qtr | qtrsales | q1sales -------+-------------+------------- 2 | 30560050.00 | 24742065.00 3 | 31170237.00 | 24742065.00 (2 rows) ``` ## WHERE clause subquery The following example contains a table subquery in the WHERE clause. This subquery produces multiple rows. In this case, the rows contain only one column, but table subqueries can contain multiple columns and rows, just like any other table. The query finds the top 10 sellers in terms of maximum tickets sold. The top 10 list is restricted by the subquery, which removes users who live in cities where there are ticket venues. This query can be written in different ways; for example, the subquery could be rewritten as a join within the main query. ``` select firstname, lastname, city, max(qtysold) as maxsold from users join sales on users.userid=sales.sellerid where users.city not in(select venuecity from venue) group by firstname, lastname, city order by maxsold desc, city desc limit 10; firstname | lastname | city | maxsold -----------+-----------+----------------+--------- Noah | Guerrero | Worcester | 8 Isadora | Moss | Winooski | 8 Kieran | Harrison | Westminster | 8 Heidi | Davis | Warwick | 8 Sara | Anthony | Waco | 8 Bree | Buck | Valdez | 8 Evangeline | Sampson | Trenton | 8 Kendall | Keith | Stillwater | 8 Bertha | Bishop | Stevens Point | 8 Patricia | Anderson | South Portland | 8 (10 rows) ``` ## WITH clause subqueries See [WITH clause](WITH_clause.md). # Correlated subqueries The following example contains a *correlated subquery* in the WHERE clause; this kind of subquery contains one or more correlations between its columns and the columns produced by the outer query. In this case, the correlation is `where s.listid=l.listid`. For each row that the outer query produces, the subquery is run to qualify or disqualify the row. ``` select salesid, listid, sum(pricepaid) from sales s where qtysold= (select max(numtickets) from listing l where s.listid=l.listid) group by 1,2 order by 1,2 limit 5; salesid | listid | sum --------+--------+---------- 27 | 28 | 111.00 81 | 103 | 181.00 142 | 149 | 240.00 146 | 152 | 231.00 194 | 210 | 144.00 (5 rows) ``` ## Correlated subquery patterns that are not supported The query planner uses a query rewrite method called subquery decorrelation to optimize several patterns of correlated subqueries for execution in an MPP environment. A few types of correlated subqueries follow patterns that AWS Clean Rooms can't decorrelate and doesn't support. Queries that contain the following correlation references return errors: + Correlation references that skip a query block, also known as "skip-level correlation references." For example, in the following query, the block containing the correlation reference and the skipped block are connected by a NOT EXISTS predicate: ``` select event.eventname from event where not exists (select * from listing where not exists (select * from sales where event.eventid=sales.eventid)); ``` The skipped block in this case is the subquery against the LISTING table. The correlation reference correlates the EVENT and SALES tables. + Correlation references from a subquery that is part of an ON clause in an outer query: ``` select * from category left join event on category.catid=event.catid and eventid = (select max(eventid) from sales where sales.eventid=event.eventid); ``` The ON clause contains a correlation reference from SALES in the subquery to EVENT in the outer query. + Null-sensitive correlation references to an AWS Clean Rooms system table. For example: ``` select attrelid from my_locks sl, my_attribute where sl.table_id=my_attribute.attrelid and 1 not in (select 1 from my_opclass where sl.lock_owner = opcowner); ``` + Correlation references from within a subquery that contains a window function. ``` select listid, qtysold from sales s where qtysold not in (select sum(numtickets) over() from listing l where s.listid=l.listid); ``` + References in a GROUP BY column to the results of a correlated subquery. For example: ``` select listing.listid, (select count (sales.listid) from sales where sales.listid=listing.listid) as list from listing group by list, listing.listid; ``` + Correlation references from a subquery with an aggregate function and a GROUP BY clause, connected to the outer query by an IN predicate. (This restriction doesn't apply to MIN and MAX aggregate functions.) For example: ``` select * from listing where listid in (select sum(qtysold) from sales where numtickets>4 group by salesid); ```