Using UNION With Different Tables, Fields And Filtering

All fields must be at the same position and the same data type, you can make all kind of selects, joins, where etc the UNION ALL comand is best then UNION if you want select all rows if they are the same**, this is not the case in this sample, you can make it a View.

The command "UNION" is the perfect way to join two tables with the same data context. Whether they have or do not have the same fields, you need to classify the data.

Look at this selected query.

Select t.* From (
(Select 1 as typePerson, tenName as namePerson, tenSalary as payMoney From Teachers Where tenAge>30)
UNION
(Select 0 as typePerson, stdName as namePerson, 0 as payMoney From Students Where stdYearFinish=2016)
) t
Where t.namePerson like ‘Maria%’
Order By t.tenName , t.typePerson;*

• It creates a temporary alias “t”;
• It classifies each data row “typePerson”, 1 (true) for teachers and 0 (false) to Students;
• It filters the age of the teachers;
• It filters the end year on the school to the year 2016;
• After the UNION, it filters by the field name Person that begins with Maria.

Observations

All fields must be at the same position and the same data type, you can make all kinds of selects, joins, where etc. The "UNION ALL" command is better than UNION if you want to select all rows. If they are the same**, this is not the case. In this sample, you can make it a View.

CONCLUSION

You must be careful of the position of the fields and the type, and you can use cast too.

• This selection is just an example.
• The SQL UNION ALL operator is used to combine the result sets of 2 or more SELECT statements. It does not remove duplicate rows between the various SELECT statements (all rows are returned).