How to order by with union in SQL?

When using UNION in SQL to combine the results of multiple queries, the ORDER BY clause applies to the final combined result set. Here’s how you can use ORDER BY with UNION:

Basic Syntax

SELECT column1, column2
FROM table1
UNION
SELECT column1, column2
FROM table2
ORDER BY column1, column2;

Key Points:

1. Placement: The ORDER BY clause must come after the final UNION query, not after individual SELECT statements.
2. Columns in ORDER BY:
   • You can order by a column name, column alias, or position (e.g., 1, 2).
   • The columns referenced in ORDER BY must appear in the combined result set.

Examples

1. Simple Example

Combine two queries and sort the final result by a column:

SELECT name, age
FROM employees
UNION
SELECT name, age
FROM managers
ORDER BY age;

• Combines the employees and managers tables, removing duplicates.
• Orders the combined result by the age column.

2. Using ORDER BY with Column Positions

Instead of specifying column names, you can order by the column positions in the result set:

SELECT name, age
FROM employees
UNION
SELECT name, age
FROM managers
ORDER BY 2; -- Orders by the second column (age)

3. UNION ALL with ORDER BY

If duplicates are acceptable, you can use UNION ALL (faster than UNION since it skips duplicate removal):

SELECT name, age
FROM employees
UNION ALL
SELECT name, age
FROM managers
ORDER BY name;

4. Ordering by a Column Alias

If you use a column alias in the SELECT statement, you can reference it in the ORDER BY clause:

SELECT name AS full_name, age
FROM employees
UNION
SELECT name AS full_name, age
FROM managers
ORDER BY full_name;

5. Ordering by a Derived Column

You can also use calculated fields in the result set:

SELECT name, age, age + 10 AS future_age
FROM employees
UNION
SELECT name, age, age + 10 AS future_age
FROM managers
ORDER BY future_age;

Common Errors

1. ORDER BY in Individual Queries:
   • Adding ORDER BY inside the individual queries will result in an error. For example:

     SELECT name, age FROM employees ORDER BY name  -- Not allowed
     UNION
     SELECT name, age FROM managers;
     

     Fix: Move the ORDER BY clause to the end of the final query.

2. Column Not in Result Set:
   • You cannot order by a column that does not appear in the combined result set:

     SELECT name, age
     FROM employees
     UNION
     SELECT name, salary
     FROM managers
     ORDER BY salary;  -- Error if salary is not in the result set
     

Conclusion

To ORDER BY with a UNION in SQL:

1. Place the ORDER BY clause at the end of the combined query.
2. Ensure the columns used in the ORDER BY clause are part of the final result set.

Let me know if you need further clarification or specific use cases!