MariaDB Tutorial

MariaDB Tutorial MariaDB Drop Database MariaDB Alter Database MariaDB Create a Database MariaDB Aggregate Functions MariaDB Group By Clause MariaDB Drop And Truncate Table MariaDB Like Clause MariaDB Order By Clause MariaDB Intersect Operator MariaDB Having Clause MariaDB Union MariaDB String Functions MariaDB Numieric Funcitons Difference Between MariaDB & MySQL

MariaDB - UNION
2022-04-22 09:08:36

MariaDB - UNION

MariaDB- UNION Operator

In MariaDB UNION operator is used to merging the results of 2 or more SELECT statements. From the result sets of a select statement, the duplicate rows are eliminated.

Syntax:

SELECT col_1, col_2, ..col_n FROM able_name [WHERE Conditions] UNION [DISTINCT] SELECT col_name FROM table_name [WHERE Condition];
  • col_1, col_2, ..col_n are the columns you wish to retrieve
  • Table_name is the table from which you wish to retrieve rows. There should be a minimum of one table name listed in the FROM clause.
  • Table_name can also be mentioned as databseName.table_name.This allows us for involving data from different databases with the UNION operator
  • WHERE clause is optional. Which can be used to mention a condition.
  • DISTINCT keyword eliminates the duplicate records from the result set, but use with UNION is useless as UNION does the same thing DISTINCT does.
  • There must be the same number of columns in both the SELECT statements.
  • We cannot use the UNION operator with the aggregate functions.
  • The operation precedence of UNION AND EXCEPT is the same, whereas INTERSECT has higher precedence. But if we are using oracle mode, all three operators have the same precedence
  • Individual SELECT statements can use ORDER BY and LIMIT clauses. Though these clauses cannot affect the order of result of the union, they are used to limit the records returned by a single select statement. In this instant, the single select statement should be mentioned in the parenthesis.
  • We can use the global ORDER BY and LIMIT clause, which would affect the result of UNION.

Lets see an example:

     We will be using two tables named 'Student' and 'Courses'.

     The structure and data of the tables look as follows:

MariaDB- UNION Operator MariaDB- UNION Operator

Data:

MariaDB [student]> select * from student;
MariaDB- UNION Operator 
MariaDB [student]> select * from course;
MariaDB- UNION Operator

Lets perform UNION operation.

MariaDB [student]> SELECT name FROM student
    -> UNION
    -> SELECT name FROM course;
MariaDB- UNION Operator

In the above example, we are retrieving names from both the tables. UNION operators eliminate the duplicate rows. If two rows with same data appear then it is returned only once.

Example with ORDER BY

MariaDB [student]> SELECT student_id, name FROM student UNION SELECT id, name FROM course ORDER BY 2;
MariaDB- UNION Operator

In the above example, we have to use the ORDER BY clause. Initially, we have retrieved id and names from both the table using UNION. After that, we have applied to sort on names column, which is sorted alphabetically.

In the next example we have used ORDER BY and LIMIT

MariaDB [student]> SELECT student_id, name FROM student UNION SELECT id, name FROM course ORDER BY 1 LIMIT 7;

We can see rows are ordered in ascending order based on student_id; we have mentioned ORDER BY 1, so it is ordered based on the first column, which is student_id, and we have used the LIMIT clause, so the rows returned are the first 7 rows.

UNION-ALL Operator

UNION-ALL operator is used to merging the result sets of two or more SELECT statements. All the rows are returned for query passed, and This operator does not eliminate the duplicate rows returned by multiple SELECT statements.

Syntax:

SELECT col_1, col_2, col_n FROM table_name
[WHERE condition]
UNION ALL SELECT col_1, col_2, col_n FROM table_name [WHERE condition]
  • col_1, col_2, ..col_n are the columns you wish to retrieve
  • Table_name is the table from which you wish to retrieve rows. There should be a minimum of one table name listed in the FROM clause.
  • Table_name can also be mentioned as databseName.table_name.This allows us for involving data from different databases with the UNION operator
  • WHERE clause is optional. Which can be used to mention a condition.
  • Note that there should be the same number of columns in both the SELECT statements.

Let's see an example:

We will be using UNION ALL below:

MariaDB [student]> SELECT name FROM student UNION ALL SELECT name FROM course;
MariaDB- UNION Operator

In the above example we can see all row from both the table are retrieved and we can see rows with same data are also appearing.

Example with ORDER BY

MariaDB [student]> SELECT student_id, name FROM student UNION ALL SELECT id, name FROM course ORDER BY 1 ;
MariaDB- UNION Operator

Here we can see the rows are ordered based on student_id, the result of both the select queries is merged and duplicate rows are present./