UNION & UNION ALL

The UNION statement combines the output of two or more SELECT commands. A union works best when using two tables with similar columns because each cloumn must have the same data type. Take for example, you have two tables, one of employees in software department and the other of employees in hardware department. If, you want to list the names of all the employees in both the departments, you need to use UNION statement.
Syntax:

SELECT column1, column2,....
FROM table1
UNION 
SELECT column1, column2,....
FROM table2;

Consider the tables, stud11 and stud12.

stud11

roll	fname	lname	percent
1	Anthony	Scott	70
2	Kari	Pettersen	65
3	Stephen	Pettersen	60
4	Joel	Harris	62
5	David	Johnson	79

stud12

roll	fname	lname	percent
1	Brad	Maines	72
2	Kevin	Carlson	83
3	David	Johnson	68
4	Joel	Harris	76
5	Freddy	Hicks	58

The query below will demonstrate the operation of UNION statement.

SELECT fname, lname FROM stud11
UNION
SELECT fname, lname FROM stud12;

The output will be:

fname	lname
Anthony	Scott
Kari	Pettersen
Stephen	Pettersen
Joel	Harris
David	Johnson
Brad	Maines
Kevin	Carlson
Freddy	Hicks

Notice that the two students, "Joel Harris" and "David Johnson" are there in both the tables but they will be listed only once. The UNION statement lists only the distinct values. If you need to display all the values, you need to use the UNION ALL statement. The syntax for a UNION ALL query is same as UNION statement.
Syntax:

SELECT column1, column2,....
FROM table1
UNION ALL
SELECT column1, column2,....
FROM table2;

Example:

SELECT fname, lname FROM stud11
UNION ALL
SELECT fname, lname FROM stud12;

The output will be:

fname	lname
Anthony	Scott
Kari	Pettersen
Stephen	Pettersen
Joel	Harris
David	Johnson
Brad	Maines
Kevin	Carlson
David	Johnson
Joel	Harris
Freddy	Hicks