Sunday, September 24, 2023

Explain how to use the UNION operator to combine the results of two queries.

 The UNION operator in SQL is used to combine the results of two or more SELECT queries into a single result set. It removes duplicates and returns a single result set that includes all rows from the component queries. The UNION operator is commonly used when you want to combine data from different tables or perform multiple queries and return a unified result.

Here's how to use the UNION operator:

Basic Syntax:

SELECT columns
FROM table1
UNION 
SELECT columns 
FROM table2;
  • SELECT columns: List the columns you want to retrieve in the result.
  • table1 and table2: The tables you want to select data from.
  • Each SELECT statement should return the same number of columns with compatible data types.

Example:

Suppose you have two tables, "employees" and "contractors," and you want to combine the names of employees and contractors into a single result set:

SELECT employee_name 
FROM employees 
UNION 
SELECT contractor_name
FROM contractors;

In this example:

  • We're selecting the "employee_name" column from the "employees" table and the "contractor_name" column from the "contractors" table.
  • The UNION operator combines the results from both queries into a single result set, removing duplicates and returning a list of unique names that includes both employees and contractors.

You can use UNION with more than two queries as well. For example:

SELECT column1 
FROM table1 
UNION
SELECT column2 
FROM table2
UNION 
SELECT column3 
FROM table3;

The important thing to note is that the UNION operator combines results vertically, and it eliminates duplicates by default. If you want to include duplicate rows, you can use the UNION ALL operator instead. Additionally, all component SELECT statements must have the same number of columns with compatible data types, or you may encounter errors.

No comments:

Post a Comment

If you have any doubts. Please let me know

How can you create an alias for a table in a SQL query?

In SQL, you can create an alias for a table in a query to give the table a temporary, alternative name that you can use in the query. Table ...