In database management using SQL, there are various functions that make it easy for us to manipulate and retrieve data efficiently. Two of them that are often relied upon by developers and data analysts are the UNION clause and the IN operator. The following is a complete guide regarding their functions, usage requirements, and how to write their syntax.
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 haphazardly combine tables. There are standard rules that must be met so that the query does not produce an error:
- Each combined SELECT statement must have the same number of columns.
- The data types of the corresponding columns must be the same or compatible.
- The columns in each SELECT statement must be in the same order.
By default, the UNION clause only selects and displays distinct values. This means the system will automatically filter the results so that no data duplication occurs.
UNION Syntax:
SQL
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. If UNION removes duplicates, UNION ALL allows data duplication to occur. This clause will display all rows from each combined SELECT statement, regardless of whether there are exactly the same values or not.
UNION ALL Syntax:
SQL
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
Note: The use of 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 considered a shorthand method to replace the repeated use of the OR operator when selecting on multiple conditions. Using IN makes SQL code much neater, more concise, and easier to read.
IN Operator Syntax (using a specific value list):
SQL
SELECT column_name(s) FROM table_name
WHERE column_name IN (value1, value2, …);
IN Operator Syntax (using Subquery / SELECT statement):
In addition to defining values manually, you can also put another SELECT statement inside the parentheses to generate a list of values dynamically.
SQL
SELECT column_name(s) FROM table_name
WHERE column_name IN (SELECT STATEMENT);
