SELECT list

The SELECT list (located between the keywords SELECT and FROM) specifies the expressions that form the output records of the SELECT command. The expression can refer to the column calculated in the FROM clause. By using the clause AS output_name, an additional name can be specified for the output column. This name is mainly used to identify the column to be displayed. It can also be used to refer to the value of the column in the ORDER BY and GROUP BY clauses, rather than in the WHERE or HAVING clauses; where the expression must be explicitly written.

Instead of the expression name, we can write * in the output list as shorthand to indicate all columns in the selected record.

UNION clause

The usage method of UNION clause is as follows：

select_statement UNION [ ALL ] select_statement

select_statement is a SELECT statement without an ORDER BY or FOR UPDATE clause. (If ORDER BY is in parentheses, then this clause is added to the subexpression, and if there are no parentheses, these clauses will be applied to the result of the UNION clause instead of the input expression on the right side of UNION.)

The UNION operator is used to compute the union result set of the rows returned by the SELECT statement. If a row appears in at least one result set, then that row appears in the union of the two result sets. The two SELECT statements that appear in the UNION operator must produce the same number of columns, and the corresponding columns must be of compatible data types.

The result of the UNION clause does not contain any duplicate rows unless the ALL option is used. The ALL option retains all duplicate rows.

Multiple UNION operators in a SELECT statement are computed in left-to-right order unless the order of computation is specified using parentheses.

Currently, neither the FOR UPDATE clause can be specified for the UNION result set, nor can this clause be specified for the UNION input.

Example:

create table test1(id int,sal int);
Insert into test1 values(1, 20000);
Insert into test1 values(2, 30000);
create table test2(id int, sal int);
Insert into test2 values(1, 30000);
Insert into test2 values(2, 40000);
select * from test1 union select * from test2;
ID  |  SAL  
----+----------
  1 | 30000
  2 | 40000
  1 | 20000
  2 | 30000
(4 rows)

MINUS clause

The usage method of MINUS clause is as follows:

select_statement MINUS select_statement

select_statement is a SELECT statement without an ORDER BY or FOR UPDATE clause.

The set of rows calculated by the MINUS operator belongs to the SELECT statement on the left side of the operator, not to the result set of the statement on the right side of the operator. It refers to the result set that is present on the left and not on the right.

The result set of the operator MINUS does not contain any duplicate rows.

The order of computation for multiple MINUS operations in the same SELECT statement is left-to-right, unless the order of computation is specified in parentheses.

The binding level of MINUS is the same as that of UNION.

For example:

create table test1(id int, sal int);
Insert into test1 values(1, 20000);
Insert into test1 values(2, 30000);
create table test2(id int, sal int);
Insert into test2 values(1, 30000);
Insert into test2 values(2, 40000);
select sal from test1 minus select sal from test2;

       SAL
---------------------
     20000