> For the complete documentation index, see [llms.txt](https://qatesting.gitbook.io/qa/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://qatesting.gitbook.io/qa/database-testing/sql/dml-commands/clauses/set-operations.md).

# Set Operations

## UNION and UNION ALL keywords

### **UNION**

The UNION keyword combines the result sets of two or more SELECT statements, removing duplicate rows.

```sql
SELECT columns FROM table1 
UNION 
SELECT columns FROM table2;
```

* The column names and data types must match in each SELECT statement.
* Duplicate rows are removed.
* Default ORDER BY sorting is applied unless overridden.

### **UNION ALL**

The UNION ALL keyword also combines multiple SELECT statements but does NOT remove duplicate rows.

```sql
SELECT columns FROM table1
UNION ALL 
SELECT columns FROM table2; 
```

* Duplicate rows are included in the result set.
* Useful when you want to retain duplicates, like combining totals from multiple tables.

**Key Differences**

* UNION removes duplicates, UNION ALL keeps duplicates.
* UNION sorts by default, UNION ALL keeps original ordering.
* UNION is slower as it performs additional processing to filter duplicates.

**Uses**

* Combine similar data from related tables.
* Append totals or aggregates from various tables.
* Merge results from stored procedures or queries into one.

So in summary, UNION and UNION ALL combine multiple SELECT statements but control duplicate rows and sorting differently. They allow you to unite results into one output.

**INTERSECT**

The INTERSECT operator returns only the common rows between two SELECT statements.

```sql
SELECT column1 FROM table1
INTERSECT
SELECT column1 FROM table2; 
```

This returns only rows found in both result sets.

**EXCEPT**

The EXCEPT operator returns distinct rows from the first SELECT statement that are not in the second.

```sql
SELECT column1 FROM table1
EXCEPT
SELECT column1 FROM table2;
```

This returns only rows from table1 and not table2.