How to use union in sql

The UNION operator in SQL is used to merge the result sets of SELECT statements with the same column structure, delete duplicate rows (default) or retain duplicate rows (using the ALL keyword) to obtain a new deduplicated Result set.

Usage of UNION in SQL

In SQL, UNION is a method used to combine two or Operator for the result sets of multiple SELECT statements with the same column structure. It merges the result sets into a new result set containing unique rows from all input result sets.

Grammar

The general syntax of the UNION statement is as follows:

<code>SELECT_STATEMENT1 UNION [ALL] SELECT_STATEMENT2 [UNION ... SELECT_STATEMENTn]</code>

Among them:

• SELECT_STATEMENT1 is the first SELECT statement.
• UNION is the UNION operator.
• [ALL] is an optional keyword that indicates whether to keep duplicate rows. If omitted, duplicate rows will be removed.
• UNION There can be multiple SELECT statements.

Usage

The UNION operator is used in the following situations:

• Merge result sets with the same column structure :UNION can combine result sets from different tables, views, or subqueries as long as they have the same column order and data type.
• Eliminate duplicate rows: By default, UNION will delete duplicate rows. However, if the ALL keyword is used, it will retain duplicate rows.
• Create a deduplicated result set: If the input result set may contain duplicate rows, you can create a deduplicated result set containing all rows by using UNION ALL.

Example

The following example uses UNION to merge employee information from two tables:

<code>SELECT *
FROM employees
UNION
SELECT *
FROM new_hires;</code>

The result set will contain data from the employees table and The unique employee row of the new_hires table.

The following example uses UNION ALL to retain duplicate rows:

<code>SELECT *
FROM employees
UNION ALL
SELECT *
FROM new_hires;</code>

The result set will contain all employee rows from the employees table and the new_hires table, including duplicate rows.

The above is the detailed content of How to use union in sql. For more information, please follow other related articles on the PHP Chinese website!