UNION & UNION ALL

1.) UNION

Union combines two or more tables and creates vertically stacked tables. The columns of both the tables must have similar data types in the same order and must have the same number of columns.

Query:

SELECT Stud_name, Age

FROM Student

UNION

SELECT Studentname, Marks

FROM Marks;

Note: The column names in the result-set are usually equal to the column names in the first SELECT statement in the UNION.

Have you noticed :

2.) UNION ALL

The UNION ALL operator is used to combine the result sets of 2 or more tables. It is different from UNION operator in a way that it does not remove duplicate rows between the various tables. It returns all of the rows.

Query:

SELECT Stud_name, Age

FROM Student

UNION ALL

SELECT Studentname, Marks

FROM Marks;

Here you can see :