DEV Community

Cover image for How to Use SQL UNION and UNION ALL to Merge Query Results
DbVisualizer
DbVisualizer

Posted on

How to Use SQL UNION and UNION ALL to Merge Query Results

#sql #unionall #sqlserver

If you've ever needed to combine the results of multiple SQL queries into one list, UNION is your tool. It’s simple but powerful — UNION removes duplicates, while UNION ALL includes them. This post walks you through syntax, examples, and best practices.

Syntax & Function

  • UNION → Merges result sets and removes duplicates
  • UNION ALL → Merges and keeps duplicates

Example: 

SELECT name FROM employees
UNION
SELECT name FROM contractors;
Enter fullscreen mode Exit fullscreen mode

For performance:

SELECT name FROM employees
UNION ALL
SELECT name FROM contractors;
Enter fullscreen mode Exit fullscreen mode

Make sure both SELECT statements return the same number and type of columns.

Use Cases

  • Partitioned Tables 
SELECT * FROM sales_2022
UNION ALL
SELECT * FROM sales_2023;
Enter fullscreen mode Exit fullscreen mode
  • Merge Similar Data from Different Sources 
SELECT user_id, name FROM crm.users
UNION
SELECT id, full_name FROM app.users;
Enter fullscreen mode Exit fullscreen mode

Important Notes

  • Only use ORDER BY once, at the end.
  • Column names come from the first SELECT.
  • Errors happen if columns don't match in count or type.

Best Practices

  • Match column counts and data types.
  • Use UNION ALL when you’re confident duplicates don’t matter.
  • Avoid using ORDER BY inside each SELECT.
  • Label your columns with aliases for clarity.

FAQ

Why use UNION ALL instead of UNION?

It’s faster and doesn’t remove duplicates. Use when duplicates are fine.

Can I sort UNION results?

Yes, at the end:

... UNION ...
ORDER BY name;
Enter fullscreen mode Exit fullscreen mode

Can I combine more than two SELECTs?

Absolutely. Just chain UNION or UNION ALL.

Can UNION be used for filtering categories?

Yes. You can simulate OR logic across filtered subsets.

Best Practices

  • Match column counts and data types.
  • Use UNION ALL when you’re confident duplicates don’t matter.
  • Avoid using ORDER BY inside each SELECT.
  • Label your columns with aliases for clarity.

Conclusion

UNION and UNION ALL are essential SQL tools for combining query results. Whether you're aggregating records from partitions or unifying multi-system data, knowing how to use these operators effectively will make your SQL cleaner and more efficient.

Read the SQL UNION Operator: How To Combine Result Sets for more info.

Top comments (0)