SELECT

Function

SELECT reads data from a table.

Acting like a filter layered over a database table, SELECT uses SQL keywords to extract the data needed from the data table.

Precautions

You must have query permissions on every column used in the SELECT command.

Syntax

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

[ WITH  with_query [, ...] ]
SELECT [/*+ plan_hint */] [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
{ * | {expression [ [ AS ] output_name ]} [, ...] }
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition [, ...] ]
[ WINDOW {window_name AS ( window_definition )} [, ...] ]
[ { UNION | INTERSECT | EXCEPT | MINUS } [ ALL | DISTINCT ] select ]
[ ORDER BY {expression [ [ ASC | DESC | USING operator ] | nlssort_expression_clause ] [ NULLS { FIRST | LAST } ]} [, ...] ]
[ { [ LIMIT { count | ALL } ] [ OFFSET start [ ROW | ROWS ] ] } | { LIMIT start, { count | ALL } } ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ];

Aliases of expressions in targetlist can be used in conditions and expressions.

• Only referencing within the same level is allowed.
• Only aliases in targetlist can be referenced.
• Only subsequent expressions can reference preceding ones.
• Cannot contain volatile functions.
• Cannot include Window functions.
• Aliases cannot be referenced in JOIN ON conditions.
• An error will occur if there are multiple aliases to apply in targetlist.

• Subquery with_query is as follows:

  1 2	with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( {select | values | insert | update | delete} )

• Specified query source from_item is as follows:

  1 2 3 4 5 6

  {[ ONLY ] table_name | view_name [ * ] [ partition_clause ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ] |( select ) [ AS ] alias [ ( column_alias [, ...] ) ] |with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ] |function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ] |function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] ) |from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]}

• The group clause is as follows:

  1 2 3 4 5 6

  ( ) | expression | ( expression [, ...] ) | ROLLUP ( { expression | ( expression [, ...] ) } [, ...] ) | CUBE ( { expression | ( expression [, ...] ) } [, ...] ) | GROUPING SETS ( grouping_element [, ...] )

• Specified partition partition_clause is as follows:

  1 2	PARTITION { ( partition_name ) | FOR ( partition_value [, ...] ) }

  

  Specifying partitions is only applicable to regular tables.

• The sorting order nlssort_expression_clause is as follows:

  1	NLSSORT ( column_name, ' NLS_SORT = { SCHINESE_PINYIN_M | generic_m_ci } ' )

• Simplified query syntax, functionally equivalent to select * from table_name.

  1	TABLE { ONLY {(table_name)| table_name} | table_name [ * ]};

Parameter Description

• WITH with_query [, ...]

  Declares one or more subqueries that can be referenced by name in the main query, acting as temporary tables.

  Detailed format of with_query is: with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( {select | values | insert | update | delete} ).

  • with_query_name specifies the name of the result set generated by the subquery, which can be used to access the subquery's result set in the query.
  • By default, a with_query referenced multiple times by the main query is usually executed only once, and its result set is materialized for repeated queries by the main query. A with_query referenced once by the main query is not executed separately but replaces its subquery directly at the reference point in the main query, executing along with the main query. Explicitly specifying [ NOT ] MATERIALIZED can alter this default behavior:
    • When MATERIALIZED is specified, the subquery is executed once, and its result set is materialized.
    • When NOT MATERIALIZED is specified, the subquery is replaced at the reference point in the main query. NOT MATERIALIZED is ignored under the following circumstances:
      • The subquery contains volatile functions.
      • The subquery is an INSERT, UPDATE, or DELETE statement.
      • A with_query2 referenced more than once refers to a self-referential with_query1 from an outer layer, then with_query2 cannot be replaced at the reference point.
        For example, in the following sample, tmp2 is referenced twice. Since tmp2 references the self-referential tmp1 from an outer layer, tmp2 is materialized even if NOT MATERIALIZED is specified.

        1 2 3 4 5

        with recursive tmp1(b) as (values(1) union all (with tmp2 as not materialized (select * from tmp1) select tt1.b + tt2.b from tmp2 tt1, tmp2 tt2)) select * from tmp1;

  • column_name specifies the column names displayed in the subquery's result set.
  • Each subquery can be a SELECT, VALUES, INSERT, UPDATE, or DELETE statement.
• plan_hint clause

  Follows the SELECT keyword in the /*+ */ format. This hint is used to optimize the plan generated by the statement block corresponding to SELECT. For detailed usage, refer to Plan Hint Optimization.

• ALL

  Declares that all rows meeting the conditions are returned. This is the default behavior, and the keyword can be omitted.

• DISTINCT [ ON ( expression [, ...] ) ]

  Removes all duplicate rows from the result set of SELECT, ensuring each row in the result set is unique.

  ON ( expression [, ...] ) retains only the first row in the set of rows that yield the same result when evaluated against the given expressions.

  

  The DISTINCT ON expression is interpreted using the same rules as ORDER BY. Unless ORDER BY is used to ensure that the required row appears first, the first row is unpredictable.

• SELECT list

  Specifies the column names in the table to be queried, which can be partial columns or all columns (using the wildcard *).

  An alias can be assigned to an output column using the AS output_name clause, typically used for displaying the output column.

  Column names can be expressed in the following forms:

  • Manual input of column names, separating multiple columns with commas (,).
  • Can be columns computed within the FROM clause.
• FROM clause

  Declares one or more source tables for SELECT.

  Elements involved in the FROM clause include:

  • table_name

    Table or view name, optionally prefixed with a schema name, for example, schema_name.table_name.

  • alias

    Assigns a temporary table alias to a table or complex table reference for subsequent query references.

    Aliases serve for abbreviation or disambiguation in self-joins. Providing an alias entirely conceals the actual table name.

  • column_alias

    Column alias.

  • PARTITION

    Queries data from a specific partition of a partitioned table.

  • partition_name

    Partition name.

  • partition_value

    Specifies the partition key value. When creating a partitioned table with multiple partition keys, the PARTITION FOR clause can specify these key values to uniquely identify a partition.

  • subquery

    Subqueries may appear in the FROM clause, creating a temporary table to hold their outputs.

  • with_query_name

    WITH clauses can also serve as sources for the FROM clause, referenced by their WITH query names.

  • function_name

    Function name. Function calls can also appear in the FROM clause.

  • join_type

    The options are:

    • [ INNER ] JOIN

      Combines two FROM items using a JOIN clause. Parentheses can dictate nesting order. Absent them, JOIN nests left-to-right.

      Regardless, JOIN binds tighter than comma-separated FROM items.

    • LEFT [ OUTER ] JOIN

      Returns all rows satisfying the join condition from the Cartesian product, plus those rows from the left table that fail to match any row in the right table under the join condition. Left rows extend to the full width of the resulting table by padding NULLs in corresponding right table column positions. Note that the JOIN clause's condition applies only during matching computation. Outer conditions apply post-computation.

    • RIGHT [ OUTER ] JOIN

      Yields all results of the inner join, augmented by each unmatched right row (left-padded with NULLs).

      Symbolic convenience since conversion to LEFT OUTER JOIN is possible by swapping left and right inputs.

    • FULL [ OUTER ] JOIN

      Delivers all inner join results, supplemented by every unmatched left row (right-padded with NULLs) and every unmatched right row (left-padded with NULLs).

    • CROSS JOIN

      Equivalent to INNER JOIN ON (TRUE), which means no rows are removed by qualification. Such joins offer mere syntactic convenience, mirroring effects achievable via straightforward FROM and WHERE constructs.

      

      A join condition must be specified for the INNER and OUTER join types, namely one of NATURAL ON, join_condition, or USING (join_column [, ...]). However, they cannot appear in CROSS JOIN.

    Among them, CROSS JOIN and INNER JOIN generate a simple Cartesian product, which is the same as the result of listing two items at the top level of FROM.

  • ON join_condition

    Specifies the join condition, used to define which rows are matched in a join. Example: ON left_table.a = right_table.a

  • USING(join_column[, ...])

    An abbreviation for ON left_table.a = right_table.a AND left_table.b = right_table.b .... Corresponding columns must share the same name.

  • NATURAL

    An abbreviation for a USING list containing all columns with identical names across the two tables.

  • from item

    Name of the query source object used for joining.

• WHERE clause

  The WHERE clause forms a row selection expression to narrow the scope of the SELECT query. condition is any expression that returns a Boolean value. Rows that do not satisfy this condition will not be retrieved.

  In the WHERE clause, the (+) operator can be specified to convert a join into an outer join. However, this usage is not recommended as it is non-standard SQL syntax and may lead to compatibility issues during platform migration. Additionally, there are numerous limitations when using (+):

  1. It can only appear in the WHERE clause.
  2. If the FROM clause already specifies a table join relationship, (+) cannot be used again in the WHERE clause.
  3. (+) can only apply to columns of tables or views, not to expressions.
  4. If table A and table B have multiple join conditions, (+) must be specified in all join conditions. Otherwise, (+) will not take effect, and the join will default to an inner join without any notification.
  5. Tables involved in the join condition with (+) cannot span queries or subqueries. If the table affected by (+) is not in the FROM clause of the current query or subquery, an error will occur. If the counterpart table affected by (+) does not exist, no error is raised, and the join defaults to an inner join.
  6. Expressions involving (+) cannot be directly connected by OR.
  7. If the column affected by (+) is compared with a constant, the expression becomes part of the join condition.
  8. A single table cannot correspond to multiple external tables.
  9. (+) can only appear in comparison, NOT, ANY, ALL, IN, NULLIF, IS DISTINCT FROM, and IS OF expressions. It cannot appear in other types of expressions, nor can these expressions include those connected by AND or OR.
  10. (+) can only convert to a left outer join or right outer join, not a full join—that is, (+) cannot be specified on both tables in an expression.
      

      For the LIKE operator in the WHERE clause, if special characters %, _, and \ need to be queried in LIKE, use backslashes (\) for escape.

      Example:

      1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

      CREATE TABLE tt01 (id int,content varchar(50)) store AS orc; INSERT INTO tt01 values (1,'Jack say ''hello'''); INSERT INTO tt01 values (2,'Rose do 50%'); INSERT INTO tt01 values (3,'Lilei say ''world'''); INSERT INTO tt01 values (4,'Hanmei do 100%'); SELECT * FROM tt01 order by id; id | content ----+------------------- 1 | Jack say 'hello' 2 | Rose do 50% 3 | Lilei say 'world' 4 | Hanmei do 100% (4 rows) SELECT * FROM tt01 WHERE content like '%''he%'; id | content ----+------------------ 1 | Jack say 'hello' (1 row) SELECT * FROM tt01 WHERE content like '%50\%%'; id | content ----+------------- 2 | Rose do 50% (1 row)

• GROUP BY clause

  Groups query results by one or more columns. The results with the same value form a group.

  • ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )
    ROLLUP is used to calculate the standard aggregate value specified in GROUP BY for an ordered group column, create a high-level partial sum from right to left, and finally create a cumulative sum. A group can be considered as a set of groups. For example:

    1	GROUP BY ROLLUP (a,b,c)

    Equivalent to:

    1	GROUP BY GROUPING SETS((a,b,c), (a,b), (a), ( ))

    The elements in a ROLLUP clause can be separate columns or expressions, or lists enclosed in parentheses. If the lists are in parentheses, they must be considered as a whole when a grouping set is generated. For example:

    1	GROUP BY ROLLUP ((a,b), (c,d))

    Equivalent to:

    1	GROUPING SETS ((a,b,c,d), (a,b), (c,d ), ( ))

  • CUBE ( { expression | ( expression [, ...] ) } [, ...] )

    CUBE automatically groups and summarizes the fields listed in the GROUP BY clause. The result set contains all possible combinations of values in the dimension column and the aggregation values in the basic rows that match the combinations of these dimension values. It returns a row of summary information for each group. You can use CUBE to generate crosstab values. For example, if three expressions (n = 3) are provided in the CUBE clause, the operation result is a 2ⁿ = 2³ = 8 groups. The rows grouped by the values of n expressions are called regular rows, and the remaining rows are called super-aggregated rows. For example:

    1	GROUP BY CUBE (a,b,c)

    Equivalent to:

    1	GROUP BY GROUPING SETS((a,b,c), (a,b), (a,c), (b,c), (a), (b), (c), ( ))

    The elements in the CUBE clause can be separate columns or expressions, or lists enclosed in parentheses. If the lists are in parentheses, they must be considered as a whole when a grouping set is generated. For example:

    1	GROUP BY CUBE (a, (b, c), d)

    Equivalent to:

    GROUP BY GROUPING SETS ((a,b,c,d), (a,b,c), (a), ( ))

  • GROUPING SETS ( grouping_element [, ...] )

    The GROUPING SETS clause is an extension of the GROUP BY clause. It allows you to specify multiple GROUP BY options. The option is used to define a group set. Each group set needs to be included in a separate parenthesis. A blank parenthesis (()) indicates that all data is processed as a group. This improves efficiency by trimming data groups that are not required. You can specify a data group for query as needed.

    

    If the expression of the SELECT list references ungrouped columns, an error is reported unless an aggregate function is used because multiple values may be returned for ungrouped columns.

• HAVING clause

  Used together with the GROUP BY clause to select specific groups. The HAVING clause compares some attributes of a group with a constant value. Only groups that satisfy the logical expression in the HAVING clause are extracted.

• WINDOW clause

  Typically formatted as WINDOW window_name AS ( window_definition ) [, ...], where window_name is the name that can be referenced by subsequent window definitions. window_definition can take the following forms:

  [ existing_window_name ]

  [ PARTITION BY expression [, ...] ]

  [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]

  [ frame_clause ]

  frame_clause defines a window frame for the window function. Not all window functions depend on this frame, which consists of a set of related rows relative to the current query row. frame_clause can be structured as follows:

  [ RANGE | ROWS ] frame_start

  [ RANGE | ROWS ] BETWEEN frame_start AND frame_end

  Options for frame_start and frame_end:

  UNBOUNDED PRECEDING

  value PRECEDING (unsupported by RANGE)

  CURRENT ROW

  value FOLLOWING (unsupported by RANGE)

  UNBOUNDED FOLLOWING

• UNION clause

  UNION computes the union of the sets of rows returned by multiple SELECT statements.

  Constraints for the UNION clause:

  • By default, UNION excludes duplicate rows unless the ALL clause is explicitly stated.
  • Multiple UNION operators within the same SELECT statement are evaluated from left to right unless overridden by parentheses.

  General syntax:

  select_statement UNION [ALL] select_statement

  • Here, select_statement can be any SELECT statement without ORDER BY or LIMIT clauses.
  • If enclosed in parentheses, ORDER BY and LIMIT can be applied to sub-expressions.
• INTERSECT clause

  INTERSECT computes the intersection of the sets of rows returned by multiple SELECT statements, excluding duplicate records.

  Constraints for the INTERSECT clause:

  • Multiple INTERSECT operators within the same SELECT statement are evaluated from left to right unless overridden by parentheses.
  • When performing both UNION and INTERSECT operations on the results of multiple SELECT statements, INTERSECT operations are prioritized.

  General syntax:

  select_statement INTERSECT select_statement

• EXCEPT clause

  The EXCEPT clause has the following general form:

  select_statement EXCEPT [ ALL ] select_statement

  The EXCEPT operator computes the rows that exist in the output of the left SELECT statement but not in the output of the right SELECT statement.

  The result of EXCEPT does not contain any duplicate rows unless the ALL option is specified. When using ALL, a row that has m duplicates in the left table and n duplicates in the right table will appear max(m–n, 0) times in the result. Unless specified otherwise with parentheses, multiple EXCEPT operators within the same SELECT statement are evaluated from left to right. EXCEPT binds at the same level as UNION.

• MINUS clause

  Has the same functionality and usage as the EXCEPT clause.

• ORDER BY clause

  Sorts the data retrieved by the SELECT statement in ascending or descending order. For cases where the ORDER BY expression includes multiple columns:

  • Sorting is performed first based on the leftmost column. If the values in this column are identical, the next expression is used for comparison, and so forth.
  • If all declared expressions are identical, the results are returned in a random order.
  • Columns being sorted in ORDER BY must be included among the columns of the result set retrieved by the SELECT statement.
  

  • If ORDER BY is not specified, the data is returned in the order most quickly generated by the database system.
  • You may choose to append the keywords ASC (for ascending order) or DESC (for descending order) after any expression in the ORDER BY clause. If unspecified, ASC is used by default.
  • To support Chinese Pinyin sorting and case-insensitive sorting, you need to specify the encoding format as UTF-8 or GBK during database initialization. The commands are as follows:

    initdb –E UTF8 –D ../data –locale=zh_CN.UTF-8 or initdb –E GBK –D ../data –locale=zh_CN.GBK

• [ { [ LIMIT { count | ALL } ] [ OFFSET start [ ROW | ROWS ] ] } | { LIMIT start, { count | ALL } } ]

  The LIMIT clause comprises two independent LIMIT clauses, an OFFSET clause, and a multi-parameter LIMIT clause:

  LIMIT { count | ALL }

  OFFSET start [ ROW | ROWS ]

  LIMIT start, { count | ALL }

  Here, count declares the maximum number of rows to return, while start indicates how many rows to skip before beginning to return rows. If both parameters are provided, start rows are skipped prior to returning up to count rows. A multi-parameter LIMIT clause cannot coexist with either a single-parameter LIMIT clause or an OFFSET clause.

• FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY

  If count is not specified, its default value is 1. The FETCH clause restricts the total number of rows returned, starting from the first row of the query result.

• NLS_SORT

  Specifies a particular method for sorting a column. Only Chinese Pinyin format sorting and case-insensitive sorting are currently supported.

  Range:

  • SCHINESE_PINYIN_M: sorts according to Chinese Pinyin (currently supporting only Level 1 Chinese characters within the GBK charset). To support this sort, you need to set the encoding format to GBK at database creation. Otherwise, sorting will not take effect.
  • generic_m_ci: performs case-insensitive sorting.
• PARTITION clause

  Retrieves data from the corresponding partition in a partitioned table.

Examples

First, obtain a temporary table temp_t through a subquery, and then query all data in the temp_t table.

1

WITH temp_t(name,isdba) AS (SELECT usename,usesuper FROM pg_user) SELECT * FROM temp_t;

Explicitly specify MATERIALIZED for the with_query named temp_t, and then query all data in the temp_t table.

1

WITH temp_t(name,isdba) AS MATERIALIZED (SELECT usename,usesuper FROM pg_user) SELECT * FROM temp_t;

Explicitly specify NOT MATERIALIZED for the with_query named temp_t, and then query all data in the temp_t table.

1
2
3
4

WITH temp_t(name,isdba) AS NOT MATERIALIZED (SELECT usename,usesuper FROM pg_user)
 SELECT * FROM temp_t t1 WHERE name LIKE 'A%'
 UNION ALL
 SELECT * FROM temp_t t2 WHERE name LIKE 'B%';

Query all r_reason_sk records in the schemareason table and remove duplicates.

1

SELECT DISTINCT(r_reason_sk) FROM schemareason;

Example of a LIMIT clause: Retrieve one record from the table.

1

SELECT * FROM schemareason LIMIT 1;

Example of a LIMIT clause: Retrieve the third record from the table.

1

SELECT * FROM schemareason LIMIT 1 OFFSET 2;

Example of a LIMIT clause: Retrieve the first two records from the table.

1

SELECT * FROM schemareason LIMIT 2;

Query all records and sort them in ascending alphabetical order.

1

SELECT r_reason_desc FROM schemareason ORDER BY r_reason_desc;

Use table aliases to retrieve data from the pg_user and pg_user_status tables.

1

SELECT a.usename,b.locktime FROM pg_user a,pg_user_status b WHERE a.usesysid=b.roloid;

Example of a FULL JOIN clause: Perform a full join on the data from the pg_user and pg_user_status tables, displaying the union of the data.

1

postgres=#SELECT a.usename,b.locktime,a.usesuper FROM pg_user a FULL JOIN pg_user_status b on a.usesysid=b.roloid;

Example of a GROUP BY clause: Filter data based on query conditions and group the results.

1

SELECT r_reason_id, AVG(r_reason_sk) FROM schemareason GROUP BY r_reason_id HAVING AVG(r_reason_sk) > 25;

Example of a GROUP BY clause: Use a group by alias to group the results.

1

SELECT r_reason_id AS id FROM schemareason GROUP BY id;

Example of a GROUP BY CUBE clause: Filter data based on query conditions and summarize the grouped results.

1

SELECT r_reason_id,AVG(r_reason_sk) FROM schemareason GROUP BY CUBE(r_reason_id,r_reason_sk);

Example of a GROUP BY GROUPING SETS clause: Filter data based on query conditions and summarize the grouped results.

1

SELECT r_reason_id,AVG(r_reason_sk) FROM schemareason GROUP BY GROUPING SETS((r_reason_id,r_reason_sk),r_reason_sk);

Example of a UNION clause: Merge contents starting with W and N in the r_reason_desc column in the schemareason table.

1
2
3
4
5
6
7

SELECT r_reason_sk, schemareason.r_reason_desc
    FROM schemareason
    WHERE schemareason.r_reason_desc LIKE 'W%'
UNION
SELECT r_reason_sk, schemareason.r_reason_desc
    FROM schemareason
    WHERE schemareason.r_reason_desc LIKE 'N%';

1
2
3
4
5
6
7
8

CREATE TABLE stu_icase_info (id bigint, name text) store AS orc;
INSERT INTO stu_icase_info VALUES (1, 'aaaa'),(2, 'AAAA');
SELECT * FROM stu_icase_info ORDER BY NLSSORT (name, 'NLS_SORT = generic_m_ci');
 id | name
----+------
  1 | aaaa
  2 | AAAA
(2 rows)

Create a partitioned table schemareason_p, insert data, and then retrieve data from the P_05_BEFORE partition of the schemareason_p table.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

CREATE TABLE schemareason_p
(
  r_reason_id character(16),
  r_reason_desc character(100)
)
PARTITION BY (r_reason_sk integer) store AS orc;


INSERT INTO schemareason_p values('AAAAAAAABAAAAAAA','reason 1',3),('AAAAAAAABAAAAAAA','reason 2',10),('AAAAAAAABAAAAAAA','reason 3',4),('AAAAAAAABAAAAAAA','reason 4',10),('AAAAAAAABAAAAAAA','reason 5',10),('AAAAAAAACAAAAAAA','reason 6',20),('AAAAAAAACAAAAAAA','reason 7',30);

SELECT * FROM schemareason_p WHERE r_reason_sk=4;
   r_reason_id    |                                            r_reason_desc                                             | r_reason_sk 
------------------+------------------------------------------------------------------------------------------------------+-------------
 AAAAAAAABAAAAAAA | reason 3                                                                                             |           4
(1 row)
-- Query the number of rows where the partition column equals 10:
SELECT count(*) FROM schemareason_p WHERE r_reason_sk=10;
 count  
--------
     3
(1 row)

Example of a GROUP BY clause: Group records in the schemareason_p table by r_reason_id and count the number of records in each group.

1
2
3
4
5
6

SELECT COUNT(*),r_reason_id FROM schemareason_p GROUP BY r_reason_id;
 count |   r_reason_id    
-------+------------------
     2 | AAAAAAAACAAAAAAA
     5 | AAAAAAAABAAAAAAA
(2 rows)

Example of a GROUP BY CUBE clause: Filter data based on query conditions and summarize the grouped query results.

1

SELECT * FROM schemareason GROUP BY CUBE (r_reason_id,r_reason_sk,r_reason_desc);

Example of a GROUP BY GROUPING SETS clause: Filter data based on query conditions and summarize the grouped query results.

1

SELECT * FROM schemareason GROUP BY GROUPING SETS ((r_reason_id,r_reason_sk),r_reason_desc);

Example of a HAVING clause: Group records in the schemareason_p table by r_reason_id, count the number of records in each group, and display only values whose number of r_reason_id is greater than 2.

1
2
3
4
5

SELECT COUNT(*) c,r_reason_id FROM schemareason_p GROUP BY r_reason_id HAVING c>2;
 c |   r_reason_id    
---+------------------
 5 | AAAAAAAABAAAAAAA
(1 row)

Example of an IN clause: Group records in the schemareason_p table by r_reason_id, count the number of records in each group, and display only counts where r_reason_id is either AAAAAAAABAAAAAAA or AAAAAAAADAAAAAAA.

1
2
3
4
5

SELECT COUNT(*),r_reason_id FROM schemareason_p GROUP BY r_reason_id HAVING r_reason_id IN('AAAAAAAABAAAAAAA','AAAAAAAADAAAAAAA'); 
count |   r_reason_id    
-------+------------------
     5 | AAAAAAAABAAAAAAA
(1 row)

Example of an INTERSECT clause: Query information where r_reason_id equals AAAAAAAABAAAAAAA and r_reason_sk is smaller than 5.

1
2
3
4
5
6

SELECT * FROM schemareason_p WHERE r_reason_id='AAAAAAAABAAAAAAA' INTERSECT SELECT * FROM schemareason_p WHERE r_reason_sk<5;
   r_reason_id    |                                            r_reason_desc                                             | r_reason_sk 
------------------+------------------------------------------------------------------------------------------------------+-------------
 AAAAAAAABAAAAAAA | reason 1                                                                                             |           3
 AAAAAAAABAAAAAAA | reason 3                                                                                             |           4
(2 rows)

Example of an EXCEPT clause: Query r_reason_id equals AAAAAAAABAAAAAAA and exclude information where r_reason_sk is smaller than 4.

1
2
3
4
5
6
7
8

SELECT * FROM schemareason_p WHERE r_reason_id='AAAAAAAABAAAAAAA' EXCEPT SELECT * FROM schemareason_p WHERE r_reason_sk<4;
   r_reason_id    |                                            r_reason_desc                                             | r_reason_sk 
------------------+------------------------------------------------------------------------------------------------------+-------------
 AAAAAAAABAAAAAAA | reason 5                                                                                             |          10
 AAAAAAAABAAAAAAA | reason 3                                                                                             |           4
 AAAAAAAABAAAAAAA | reason 2                                                                                             |          10
 AAAAAAAABAAAAAAA | reason 4                                                                                             |          10
(4 rows)

Implement a left join by specifying (+) in a WHERE clause.

1
2
3
4
5
6

select t1.sr_item_sk ,t2.c_customer_id from store_returns t1, customer t2 where t1.sr_customer_sk  = t2.c_customer_sk(+) 
order by 1 desc limit 1;
 sr_item_sk | c_customer_id
------------+---------------
      18000 |
(1 row)

Implement a right join by specifying (+) in a WHERE clause.

1
2
3
4
5
6

select t1.sr_item_sk ,t2.c_customer_id from store_returns t1, customer t2 where t1.sr_customer_sk(+)  = t2.c_customer_sk 
order by 1 desc limit 1;
 sr_item_sk |  c_customer_id
------------+------------------
            | AAAAAAAAJNGEBAAA
(1 row)

Implement a left join by specifying (+) in a WHERE clause and adding join conditions.

1
2
3
4
5

select t1.sr_item_sk ,t2.c_customer_id from store_returns t1, customer t2 where t1.sr_customer_sk  = t2.c_customer_sk(+) and t2.c_customer_sk(+) < 1 order by 1  limit 1;
 sr_item_sk | c_customer_id
------------+---------------
          1 |
(1 row)

Using inner nested AND/OR expressions while specifying (+) in a WHERE clause is not supported.

1
2
3

select t1.sr_item_sk ,t2.c_customer_id from store_returns t1, customer t2 where not(t1.sr_customer_sk  = t2.c_customer_sk(+) and t2.c_customer_sk(+) < 1);
ERROR:  Operator "(+)" can not be used in nesting expression.
LINE 1: ...tomer_id from store_returns t1, customer t2 where not(t1.sr_...                                                           

An error will be reported if a WHERE clause does not support the expression macro specifying (+).

1
2

select t1.sr_item_sk ,t2.c_customer_id from store_returns t1, customer t2 where (t1.sr_customer_sk  = t2.c_customer_sk(+))::bool;
ERROR:  Operator "(+)" can only be used in common expression.

An error will be reported if (+) is specified on both sides of the expression in a WHERE clause.

1
2
3

select t1.sr_item_sk ,t2.c_customer_id from store_returns t1, customer t2 where t1.sr_customer_sk(+)  = t2.c_customer_sk(+);
ERROR:  Operator "(+)" can't be specified on more than one relation in one join condition
HINT:  "t1", "t2"...are specified Operator "(+)" in one condition.