> 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.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://qatesting.gitbook.io/qa/database-testing/sql/dml-commands/clauses/set-operations.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.