myITSchools

SQL UNION Operator

In SQL Server, the UNION operator is used to combine the result sets of two or more SELECT statements. The UNION operator requires that each SELECT statement have the same number of columns, with corresponding columns having compatible data types. The resulting output will have only distinct values by default. If you want to include duplicate values, you can use the UNION ALL operator instead.

  • Every SELECT statement within UNION must have the same number of columns
  • The columns must also have similar data types
  • The columns in every SELECT statement must also be in the same order

Syntax: –

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

How to use WHERE clause with UNION?

We can use a WHERE clause with UNION. The WHERE clause is used to filter the rows returned by the UNION operator. It is applied to the result set after the UNION operation is performed. Here is an example:

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2
WHERE column1 = 'value';

This query will return all the rows from table1 and table2 where column1 is equal to ‘value’.

It’s important to note that the WHERE clause must be applied to the result set after the UNION operation is performed. If you want to apply the WHERE clause to each individual SELECT statement, you can use parentheses to group the statements and apply the WHERE clause to each one separately. Here is an example:

(SELECT column1, column2 FROM table1 WHERE column1 = 'value1')
UNION
(SELECT column1, column2 FROM table2 WHERE column1 = 'value2');

This query will return all the rows from table1 where column1 is equal to ‘value1’ and all the rows from table2 where column1 is equal to ‘value2’.

What is the difference between INTERSECT and UNION?

In SQL Server, UNION and INTERSECT are both set operators used to combine the results of two or more SELECT statements. The main difference between them is that UNION returns all distinct rows from both queries, while INTERSECT returns only the rows that are common to both queries.

Here is an example to illustrate the difference:

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

This query will return all the distinct rows from both table1 and table2.

SELECT column1, column2 FROM table1
INTERSECT
SELECT column1, column2 FROM table2;

This query will return only the rows that are common to both table1 and table2.

You can use a WHERE clause with both UNION and INTERSECT to filter the results. The WHERE clause is applied to the result set after the UNION or INTERSECT operation is performed.

Can I use a GROUP BY clause with UNION?

you can use a GROUP BY clause with UNION. The GROUP BY clause is used to group the result set by one or more columns. It is applied to the result set after the UNION operation is performed. Here is an example:

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2
GROUP BY column1;

This query will return all the distinct rows from both table1 and table2 and group them by column1.

It’s important to note that the GROUP BY clause must be applied to the result set after the UNION operation is performed. If you want to apply the GROUP BY clause to each individual SELECT statement, you can use parentheses to group the statements and apply the GROUP BY clause to each one separately. Here is an example:

(SELECT column1, column2 FROM table1 WHERE column1 = 'value1')
UNION
(SELECT column1, column2 FROM table2 WHERE column1 = 'value2')
GROUP BY column1;

This query will return all the rows from table1 where column1 is equal to ‘value1’ and all the rows from table2 where column1 is equal to ‘value2’, and group them by column1.

Can I use a HAVING clause with UNION?

you can use a HAVING clause with UNION. The HAVING clause is used to filter the result set based on a condition that involves an aggregate function. It is applied to the result set after the UNION operation is performed. Here is an example:

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2
HAVING COUNT (column1) > 1;

This query will return all the distinct rows from both table1 and table2 where the count of column1 is greater than 1.

It’s important to note that the HAVING clause must be applied to the result set after the UNION operation is performed. If you want to apply the HAVING clause to each individual SELECT statement, you can use parentheses to group the statements and apply the HAVING clause to each one separately. Here is an example:

(SELECT column1, column2 FROM table1 WHERE column1 = 'value1')
UNION
(SELECT column1, column2 FROM table2 WHERE column1 = 'value2')
HAVING COUNT (column1) > 1;

This query will return all the rows from table1 where column1 is equal to ‘value1’ and all the rows from table2 where column1 is equal to ‘value2’, and then filter the result set where the count of column1 is greater than 1.

Scroll to Top