UNION

Updated on 2024-12-20 GMT+08:00

View PDF

Union/Union ALL/Intersect/Except

Syntax Format

      
         query UNION [ ALL ] | Intersect | Except query

Syntax 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 operations 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, UNION takes only distinct records while UNION ALL does not remove duplicates from the result.
Example
Output distinct records found in either Orders1 and Orders2 tables.
1 2

SELECT * FROM Orders1 UNION SELECT * FROM Orders2;

IN

Syntax Format

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

Syntax 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.

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

Parent topic: Data Query Language (DQL) Syntax

Previous topic: SELECT

Next topic: Window

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot