Why do we use UNION to join rows in SQL?

Question

Why use union in Sql at all when there must be same order of columns and it's names in the select statements. Couldn't we just update or alter the table to add more rows ?

Answer 1

The reason that you would not just keep adding rows to a single table is that they don't belong in that table.

For the same reason that if you're doing arithmetic between two integer variables like x + y, you don't permanently add the value of y to x. You want to preserve x's value as it's own thing, even though sometimes you also need the sum.

A book like SQL and Relational Theory: How to Write Accurate SQL Code makes clear that there's a difference between a relation and a relvar. A relvar is like a table. It's a persistent storage of a specific set of rows.

A relation is the result of a SQL expression like SELECT or VALUES. That relation may not be stored in any relvar; it is ephemeral. Perhaps it's the result of a more complex query that uses expressions, joins, and so on.

By analogy, the a number like 42 is an integer value. But int x that stores an integer value 42 is an integer variable. They can both be used as an operand for + but they're not the same kind of thing.

You can UNION two relations, if their columns are compatible in number and data type. Those relations aren't necessarily just relvars, they could be the result of other subqueries.

Just like in arithmetic, you can add x and a whole other integer expression.