Which operator is used to combine the results of two or more than two queries?

---

The SQL AND & OR operators are used to combine multiple conditions to narrow data in an SQL statement. These two operators are called as the conjunctive operators.

These operators provide a means to make multiple comparisons with different operators in the same SQL statement.

The AND Operator

The AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause.

Syntax

The basic syntax of the AND operator with a WHERE clause is as follows −

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];

You can combine N number of conditions using the AND operator. For an action to be taken by the SQL statement, whether it be a transaction or a query, all conditions separated by the AND must be TRUE.

Example

Consider the CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Following is an example, which would fetch the ID, Name and Salary fields from the CUSTOMERS table, where the salary is greater than 2000 and the age is less than 25 years −

SQL> SELECT ID, NAME, SALARY 
FROM CUSTOMERS
WHERE SALARY > 2000 AND age < 25;

This would produce the following result −

+----+-------+----------+
| ID | NAME  | SALARY   |
+----+-------+----------+
|  6 | Komal |  4500.00 |
|  7 | Muffy | 10000.00 |
+----+-------+----------+

The OR Operator

The OR operator is used to combine multiple conditions in an SQL statement's WHERE clause.

Syntax

The basic syntax of the OR operator with a WHERE clause is as follows −

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]

You can combine N number of conditions using the OR operator. For an action to be taken by the SQL statement, whether it be a transaction or query, the only any ONE of the conditions separated by the OR must be TRUE.

Example

Consider the CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

The following code block hasa query, which would fetch the ID, Name and Salary fields from the CUSTOMERS table, where the salary is greater than 2000 OR the age is less than 25 years.

Set operators are used to join the results of two (or more) SELECT statements. They are basically keywords that tell SQL how to merge the results from the different SELECT statements. There are 4 set operators that can be used to combine data – each has a different focus:

OperatorDescriptionUNIONCombines the results of two or more queries into a distinct single result, with any duplicates being removedUNION ALLCombines the results of two or more queries into a distinct single result, with any duplicates being retainedINTERSECTKeeps the results that are common to all queriesEXCEPTReturns the results that are in the first query and not in the second

The basic UNION statement is the most commonly used, so we’ll look at this first.

How to use the UNION operator

The UNION operator is used to combine data from two or more queries. Here is a simple example that shows how it works.

SELECT A.ID,
A.Name
FROM [UnionDemo].[dbo].[GroupA] AS A
UNION
SELECT B.ID,
B.Name
FROM [UnionDemo].[dbo].[GroupB] AS B

Some things to note about the UNION operator:

• the number and the order of the columns must be the same in all queries
• the data types must be compatible
• any duplicates that may exist in the two tables are excluded
• duplicates occur when records are the same across ALL the columns (e.g. in the above, if the name in Group B for ID 1005 had been Clare1, it would have been included in the result set)

If we do want to include any duplicates in the records that are returned by the select statements then we can use the UNION ALL operator:

SELECT A.ID,
A.Name
FROM [UnionDemo].[dbo].[GroupA] AS A
UNION ALL
SELECT B.ID,
B.Name
FROM [UnionDemo].[dbo].[GroupB] AS B

As you can see, the UNION operator is a simple, but powerful addition to your SQL skill set. The most common use cases for needing it are when you have multiple tables of the ‘same’ data – e.g. sales data from different regions. As long as the basic rules are adhered to, then the UNION statement is a good option.

When would you need to use UNION ALL?

As we’ve seen in the example above, UNION ALL will retain any duplicates in the result data set. What is a use case for this behavior? There are not many times when we can accommodate duplicates, although I’m sure there are some situations when the presence of duplicates doesn’t affect the analysis.

The main reason that you would use UNION ALL instead of UNION is performance-related. UNION ALL is much quicker than UNION as it does not have to check for duplicates, so it should be used when you know that there are no duplicates in the source tables (for example, sales data from region A can’t appear in the table for region B).

How to use the INTERSECT and EXCEPT operators

The INTERSECT operator

As the name implies, the INTERSECT operator will combine the results of two queries and return only rows that appear in both result sets. This is a useful way of finding duplicates in tables.

SELECT A.ID,
A.Name
FROM [UnionDemo].[dbo].[GroupA] AS A
INTERSECT
SELECT B.ID,
B.Name
FROM [UnionDemo].[dbo].[GroupB] AS B

The EXCEPT operator

The EXCEPT operator will return records from a select statement that don’t appear in a second select statement. The syntax is exactly the same as our UNION and INTERSECT examples, with the EXCEPT operator simply used instead.

SELECT A.ID,
A.Name
FROM [UnionDemo].[dbo].[GroupA] AS A
EXCEPT
SELECT B.ID,
B.Name
FROM [UnionDemo].[dbo].[GroupB] AS B

An example use case for the EXCEPT operator is when you want to find out which customers have no related sales recorded for them in a sales order table. In the tables below, you can see that there are no sales in the orders table for Clare.

The query to return the person with no orders recorded (i.e. Clare) is:

SELECT
A.ID, A.Name, O.Date, O.Amount
FROM [UnionDemo].[dbo].[GroupA] AS A
LEFT OUTER JOIN [UnionDemo].[dbo].[Orders] AS O on A.ID = O.ID
EXCEPT
SELECT
A.ID, A.Name, O.Date, O.Amount
FROM [UnionDemo].[dbo].[GroupA] AS A
RIGHT OUTER JOIN [UnionDemo].[dbo].[Orders] AS O on A.ID = O.ID

UNION vs JOIN

Both UNION and JOIN combine data from different tables. There is, however, a fundamental difference between the two.

The key difference is that in the JOIN statement, we are combining COLUMNS from different tables (based on a related column), whereas with UNION we are combining ROWS from tables with identical columns.

Summary

The SQL UNION operator is used to combine the results of two or more queries into a distinct single result set. A typical use case for this is when we have data split up across multiple tables, and we want to merge it into one single set of results.

The INTERSECT and EXCEPT operators are other types of set operators which allow us to find duplicates in the results returned by two queries (INTERSECT) or results from one query that don’t appear in a second (EXCEPT).

If your organization uses both SQL Server and Excel, then check out the SQL Spreads Add-In for Excel; it will greatly simplify how you manage your data environment. 

Article by

Andy McDonald

Andy has worked 20+ years in the Engineering, Financial, and IT sectors with data analysis and presentation using tools such as SQL Server, Excel, Power Query and Power BI.

Which operator is used to combine two or more queries?

What would be used to combine the results from two queries?

Which set operator is used to combine the result sets of two or more queries into a single result set?

Which of the following query is used to combine records from two or more tables?