How do I concatenate two similar tables on a result

Question

I have two tables with similar columns. I would simply like to select both tables, one after another, so that if I have 'x' rows on table1 and 'y' rows on table2, I'd get 'x + y' rows.

Answer 1

You would use UNION [ALL] for this. The tables don't need to have the same column names but you do need to select the same number of columns from each and the corresponding columns need to be of compatible datatypes

SELECT col1,col2,col3 FROM table1 
UNION ALL
SELECT col1,col2,col3 FROM table2

UNION ALL is preferrable to UNION where there is a choice as it can avoid a sort operation to get rid of duplicates.

Answer 2

Just to add to what they were saying, you might want to add an Order By. Depends on the version of SQL you're using.

SELECT Col1, Col2, Col3
FROM   Table1
UNION
SELECT Col1, Col2, Col3
FROM   Table2
ORDER BY Col1

Note that ORDER and GROUP BYs have to go after the last table in the UNION.