The Amazing World of SQL Unions

In this article, we’ll discuss how to merge numerous SELECT queries into a single result set using the UNION operator, as well as the many forms of UNION and the distinction between a UNION and a JOIN.

Understanding Combined Queries

A single SELECT statement is present in the majority of SQL queries, and it retrieves data from one or more tables. A single query result set can be returned from several queries (many SELECT statements) when using SQL. Unions or compound inquiries are the traditional names for these merged queries.

Combining two queries against the same table typically achieves the same results as a single query with various WHERE clause constraints. In other words, a combined query can be given for any SELECT statement that has multiple WHERE clauses.

Creating Combined Queries

SQL queries are combined using the UNION operator. Using UNION, you can specify multiple SELECT statements, and their results can be combined into a single result set.

Why Combined queries

1. To return similarly structured data from different tables in a single query.
2. To perform multiple queries against a single table returning the data as one query.

Let’s jump right into it!

The UNION Operator

The Union operator returns rows from both tables. If used by itself, UNION returns a distinct list of rows. Using UNION ALL, returns all rows from both tables. A UNION is useful when you want to sort results from two separate queries as one combined result.

The INTERSECT Operator

Use an intersect operator to return rows that are in common between two tables; it returns unique rows from both the left and right query. This query is useful when you want to find results that are in common between two queries.

The Except Operator

Use the EXCEPT Operator to return only rows found in the left query. It returns unique rows from the left query that aren’t in the right query’s results. This query is useful when you’re looking to find rows that are in one set but not another. For example, to create a list of all vendors that are not customers.

UNION Rules

1. A UNION must be made up of two or more SELECT statements separated by the keyword UNION (for example, if you’re combining four SELECT statements, you’d use three UNION keywords).
2. Each UNION query must use the same columns, expressions, or aggregate functions (and some DBMSs even require that columns be listed in the same order).
3. The datatypes of the columns must be compatible. They do not have to be the same name or type, but they must be of a type that the DBMS can implicitly convert (for example, different numeric types or different date types).

Including or Eliminating Duplicate Rows

Any duplicate rows in the query result set are automatically removed by the UNION (in other words, it behaves just as multiple WHERE clause conditions in a single SELECT would).

This is UNION’s default behavior, but you can change it if you want. If you would, in fact, want all occurrences of all matches returned, you could use UNION ALL instead of UNION.

Note: Other UNION Types

Two additional types of UNION are supported by some DBMSs. EXCEPT (also known as MINUS) can be used to retrieve only the rows that exist in the first table but not the second, whereas INTERSECT can retrieve only the rows that exist in both tables. In practice, however, these UNION types are rarely used because joins can achieve the same results.

Note:

In practice, UNION is really useful when you need to combine data from multiple tables, even tables with mismatched column names, in which case you can combine UNION with aliases to retrieve a single set of results.

The Difference Between Join and Union

Joins

1. JOIN combines data from many tables based on a matched condition between them.
2. It combines data into new columns.
3. Number of columns selected from each table may not be same.
4. Datatypes of corresponding columns selected from each table can be different.
5. It may not return distinct columns.

Union

1. SQL combines the result-set of two or more SELECT statements.
2. It combines data into new rows
3. Number of columns selected from each table should be same.
4. Datatypes of corresponding columns selected from each table should be same.
5. It returns distinct rows.

I hope you found this article fun and interesting!