SQL for combining results from multiple queries

Combining Results from Multiple Queries Using UNION and UNION ALL

UNION:

  • Combines the results of multiple queries vertically, removing any duplicate rows.
  • The columns in each query must match in number and data type.

UNION ALL:

  • Similar to UNION, but does not remove duplicate rows.
  • Can be used to combine results that have different column names or data types.

Code Solution:

-- Query 1: Select columns from table1
SELECT column1, column2
FROM table1;

UNION

-- Query 2: Select columns from table2
SELECT column3, column4
FROM table2;

Example:

SELECT name, age
FROM students;

UNION

SELECT name, age
FROM employees;

This query will combine the results of two queries: one selecting the name and age columns from the students table, and another selecting the same columns from the employees table. The results will be displayed in a single table, with duplicate rows removed.

UNION ALL Example:

SELECT name, age
FROM students;

UNION ALL

SELECT name, salary
FROM employees;

This query will combine the results of the same two queries, but will not remove duplicate rows. The results will be displayed in a single table, with duplicate rows included.

Implementation Tips:

  • Ensure that the columns in each query match in number and data type.
  • Use parentheses around each query to improve readability and maintainability.
  • Consider using aliases for columns to avoid name conflicts.
  • Limit the number of queries being combined to avoid performance issues.
  • Use ORDER BY to sort the combined results.