SQL
`UNION`
The `UNION` clause can be used to combine rows from multiple queries. The queries are required to return the same number of columns. [Implicit casting](https://duckdb.org/docs/sql/data_types/typecasting#implicit-casting) to one of the returned types is performed to combine columns of different types where necessary. If this is not possible, the `UNION` clause throws an error.  
#### Vanilla `UNION` (Set Semantics) {#docs:sql:query_syntax:setops::vanilla-union-set-semantics}  
The vanilla `UNION` clause follows set semantics, therefore it performs duplicate elimination, i.e., only unique rows will be included in the result.  
```sql
SELECT * FROM range(2) t1(x)
UNION
SELECT * FROM range(3) t2(x);
```  
| x |
|--:|
| 2 |
| 1 |
| 0 |  
#### `UNION ALL` (Bag Semantics) {#docs:sql:query_syntax:setops::union-all-bag-semantics}  
`UNION ALL` returns all rows of both queries following bag semantics, i.e., *without* duplicate elimination.  
```sql
SELECT * FROM range(2) t1(x)
UNION ALL
SELECT * FROM range(3) t2(x);
```  
| x |
|--:|
| 0 |
| 1 |
| 0 |
| 1 |
| 2 |  
#### `UNION [ALL] BY NAME` {#docs:sql:query_syntax:setops::union-all-by-name}  
The `UNION [ALL] BY NAME` clause can be used to combine rows from different tables by name, instead of by position. `UNION BY NAME` does not require both queries to have the same number of columns. Any columns that are only found in one of the queries are filled with `NULL` values for the other query.  
Take the following tables for example:  
```sql
CREATE TABLE capitals (city VARCHAR, country VARCHAR);
INSERT INTO capitals VALUES
('Amsterdam', 'NL'),
('Berlin', 'Germany');
CREATE TABLE weather (city VARCHAR, degrees INTEGER, date DATE);
INSERT INTO weather VALUES
('Amsterdam', 10, '2022-10-14'),
('Seattle', 8, '2022-10-12');
```  
```sql
SELECT * FROM capitals
UNION BY NAME
SELECT * FROM weather;
```  
|   city    | country | degrees |    date    |
|-----------|---------|--------:|------------|
| Seattle   | NULL    | 8       | 2022-10-12 |
| Amsterdam | NL      | NULL    | NULL       |
| Berlin    | Germany | NULL    | NULL       |
| Amsterdam | NULL    | 10      | 2022-10-14 |  
`UNION BY NAME` follows set semantics (therefore it performs duplicate elimination), whereas `UNION ALL BY NAME` follows bag semantics.