UNION Clause and IN Operator in SQL

In SQL database management, there are various functions that make it easier for us to manipulate and retrieve data efficiently. Two of them often relied upon by developers and data analysts are the UNION clause and IN operator.

The following is a complete guide on their functions, usage requirements, and syntax writing.

---

UNION Clause

The UNION clause is a clause in SQL used to combine two or more results from SELECT statements.

To use this clause, you cannot just arbitrarily combine tables. There are standard rules that must be followed to avoid errors in the query:

• Each SELECT statement being combined must have the same number of columns.
• The data types of corresponding columns must be the same or compatible.
• Each column in the SELECT statement must be in the same order.

By default, the UNION clause will only select and display distinct values. This means the system will automatically filter the results to avoid data duplication.

UNION Syntax

SELECT column_name(s) FROM table1

UNION

SELECT column_name(s) FROM table2;

---

UNION ALL Clause

Within the UNION family, there is also a syntax variation known as UNION ALL.

The main difference between the two lies in how the system handles duplicate data. While UNION removes duplicates, UNION ALL allows data duplication.

This clause will display all result rows from each combined SELECT statement, regardless of whether there are identical values or not.

UNION ALL Syntax

SELECT column_name(s) FROM table1

UNION ALL

SELECT column_name(s) FROM table2;

Note: Using UNION ALL is usually faster than regular UNION because the computing system does not need to spend extra time scanning and removing duplicate rows.

---

IN Operator

The IN operator is a logical operator added to the WHERE clause to filter data based on several possible values at once.

This operator is often referred to as a shorthand method to replace the repeated use of the OR operator when selecting multiple conditions.

Using IN makes SQL code much neater, more concise, and easier to read.

Syntax of IN Operator Using a Specific Value List

SELECT column_name(s) FROM table_name

WHERE column_name IN (value1, value2, ...);

---

Syntax of IN Operator Using a Subquery

Besides defining values manually, you can also place another SELECT statement inside the parentheses to generate a dynamic list of values.

SELECT column_name(s) FROM table_name

WHERE column_name IN (SELECT STATEMENT);