Union Operator MySQL Essential Training

Union Operator MySQL Essential Training

Uncategorized

Union Operator MySQL Essential Training




Welcome to another fun and more effective lesson in the MySQL essential training. This lesson Union Operator MySQL Essential Training will gives you an opportunity to combine two results and produce a new result. Lets jump into the real time examples and understand more.
MySQL UNION operator allows you to combine two or more result sets from multiple tables into a single result set. The syntax of the MySQL UNION is as follows:

If we want to apply the union operator between any of the two operators, then we should follow some rules to get an exact output. Follow they are,
The column numbers in the both tables which uses the Select statement must be equal.
The columns appears in the position of each select statement in both table should be same data type, or at least it can convertible to the same data type.
The Union operator itself find and removes the duplicate rows even if we didn’t use the DISTINCT operator. Some times the UNION clause is known as UNION DISTINCT Operator. The UNION ALL is performs faster than the UNION DISTINCT. It doesn’t allows the duplicate rows if exists.
Union Operator MySQL Essential Training – Examples
Lets discuss it with the real time example.
Consider our previous tables products and employees and performs the Union operator on them. The query will be looks like this,

The above query is gives the following output.
Union Operator MySQL Essential Training
Union Operator MySQL Essential Training – Union without Alias
In our previous example we uses the alias which means the column name, if we combine the two tables. Think what would be the column name if we didn’t give any alias name. The Union take the column name by the order we gives.
Lets try the same scenario with out using the alias name,

The above query is gives the following output.
Union Operator MySQL Essential Training
Union Operator MySQL Essential Training – Union with Order By
The result has id and first_name as the label, which are the names of columns in the first SELECT statement.
Our previous lesson in this series is ORDER BY clause. The Union Operator can works very perfectly with the ORDER BY to sort the results in the desired orders. So far we can use the parentheses to put the select statements.
Let’s take a look at the following example:

The above query is gives the following output.
Union Operator MySQL Essential Training
If we put the ORDER BY clause in each SELECT statement, it will not affect the order of the rows in the final result produced by the UNION operator.
The ORDER BY operator not only consider the column names, but it also considers the positions of the columns. So by using the columns position also we can sort the results.

In this lesson we’ve learned how the UNION operator is combine the data from multiple table columns.




Leave a Reply