En español

SQL UNION operator tutorial with MySql

SQL UNION OPERATOR

At times, you might want to combine the results of two quite distinct queries. There may be no link between the results of each query; you just want to display them all in one results set.

You can join the results from two or more SELECT queries into one results set by using the UNION operator.

SQL UNION SYNTAX

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

The UNION operator is placed between the two queries to indicate to the database system that you want the results from each query to be presented as one results set.

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

SQL UNION EXAMPLE

We are going to join the results from our tables "publisher" and "publisher3";

TABLE PUBLISHER TABLE PUBLISHER3

 

With the following statement we join both columns "names" of our tables;

SELECT name FROM eli.publisher3 
UNION 
SELECT name FROM eli.publisher;


We use MySql Workbench to see the result;

To use the UNION operator take into account a few ground rules:

  1. Each query must produce the same number of columns;
  2. Columns' data types must be the same, or at least the database system must be able to convert the data types to be the same. The data type for each column was determined when you created the table. If you're not sure what data type a column has, you can find out by looking at the database.

UNION ALL

If you want all rows returned in the results set, regardless of whether they are unique, you need to use the ALL statement, as illustrated in the following SQL:

   SELECT name FROM eli.publisher3 
   UNION ALL
   SELECT name FROM eli.publisher;


Below we can see all the rows returned, even if "eli" and "edu4java" is repeated;

 

ORDER BY clause with UNION

The ORDER BY clause comes at the end of the UNION statements and refers to the first SELECT query.

This doesn't work on IBM's DB2, which allows the ORDER BY statement to come last only if the name of the column being ordered appears in all the queries.

Likewise, the ORDER BY syntax when using UNION is different in Oracle. Rather than specifying the name of the column to be ordered, you specify the position in which the column appears in the SELECT list.

<< Previous Next >>