![]() ArgumentsĪn expression of any character type ( char, nchar, nvarchar, or varchar).Īn expression of any type. ![]() ![]() In this tutorial, you have learned how to use MySQL UNION statement to combine data from multiple queries into a single result set.To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation. However, it is not a good practice to sort the result set by column position. ORDER BY 1 Code language: SQL (Structured Query Language) ( sql ) MySQL also provides you with an alternative option to sort a result set based on column position using ORDER BY clause as follows: SELECT CONCAT(firstName, ' ',lastName) fullname UNION SELECT CONCAT(contactFirstName, ' ', contactLastName),įullname Code language: SQL (Structured Query Language) ( sql ) To differentiate between employees and customers, you can add a column as shown in the following query: SELECT CONCAT(firstName, ' ', lastName) fullname, Notice that if you place the ORDER BY clause in each SELECT statement, it will not affect the order of the rows in the final result set. ORDER BY fullname Code language: SQL (Structured Query Language) ( sql ) UNION SELECT concat(contactFirstName, ' ',contactLastName) If you want to sort the result set of a union, you use an ORDER BY clause in the last SELECT statement as shown in the following example: SELECT concat(firstName, ' ',lastName) fullname It uses the CONCAT() function to concatenate first name, space, and last name into a full name. This example uses the column heading of the first query for the output. UNION SELECT CONCAT(contactFirstName, ' ',contactLastName) If you want to use other column headings, you need to use column aliases explicitly in the first SELECT statement as shown in the following example: SELECT CONCAT(firstName, ' ',lastName) fullname Suppose that you want to combine the first name and last name of employees and customers into a single result set, you can use the UNION operator as follows: SELECTĬustomers Code language: SQL (Structured Query Language) ( sql )Īs you can see from the output, the MySQL UNION uses the column names of the first SELECT statement for the column headings of the output. We’ll use the customers and employees tables in the sample database for the demonstration: The following picture illustrates the difference between UNION and JOIN: MySQL UNION and column alias examples JOINĪ JOIN combines result sets horizontally, a UNION appends result set vertically. UNION ALL SELECT id FROM t2 Code language: SQL (Structured Query Language) ( sql ) + -+Ħ rows in set ( 0.00 sec) Code language: SQL (Structured Query Language) ( sql )Īs you can see, the duplicates appear in the combined result set because of the UNION ALL operation. Because UNION ALL does not need to handle duplicates, it performs faster than UNION DISTINCT . If you use the UNION ALL explicitly, the duplicate rows, if available, remain in the result. The following Venn diagram illustrates the union of two result sets that come from t1 and t2 tables: The final result set contains the distinct values from separate result sets returned by the queries: + -+Ĥ rows in set ( 0.00 sec) Code language: SQL (Structured Query Language) ( sql )īecause the rows with value 2 and 3 are duplicates, the UNION removed them and kept only unique values. UNION SELECT id FROM t2 Code language: SQL (Structured Query Language) ( sql ) The following statement combines result sets returned from t1 and t2 tables: SELECT id FROM t1 INSERT INTO t2 VALUES ( 2),( 3),( 4) Code language: SQL (Structured Query Language) ( sql ) Let’s see the following sample tables: t1 and t2: DROP TABLE IF EXISTS t1
0 Comments
Leave a Reply. |