Set Operations

UNION/UNION ALL/INTERSECT/EXCEPT

Syntax

1

query UNION [ ALL ] | Intersect | Except query

Description

• UNION is used to return the union set of multiple query results.
• INTERSECT is used to return the intersection of multiple query results.
• EXCEPT is used to return the difference set of multiple query results.

Precautions

• Set operation is to join tables from head to tail under certain conditions. The quantity of columns returned by each SELECT statement must be the same. Column types must be the same. Column names can be different.
• By default, the duplicate records returned by UNION are removed. The duplicate records returned by UNION ALL are not removed.

Example

Output the union set of Orders1 and Orders2 without duplicate records.

1
2

insert into temp SELECT  * FROM Orders1
  UNION SELECT  * FROM Orders2;

IN

Syntax

1
2
3

SELECT [ ALL | DISTINCT ]   { * | projectItem [, projectItem ]* }
  FROM tableExpression
  WHERE column_name IN (value (, value)* ) | query

Description

The IN operator allows multiple values to be specified in the WHERE clause. It returns true if the expression exists in the given table subquery.

Precautions

The subquery table must consist of a single column, and the data type of the column must be the same as that of the expression.

Example

Return user and amount information of the products in NewProducts of the Orders table.

1
2
3
4
5

insert into temp SELECT user, amount
FROM Orders
WHERE product IN (
    SELECT product FROM NewProducts
);