How To Add Id To Query Result Mysql
Summary : in this tutorial, you will learn how to use MySQL UNION operator to combine two or more result sets from multiple SELECT statements into a single result set.
MySQL Union operator
MySQL Spousal relationship operator allows you to combine two or more than result sets of queries into a unmarried result prepare. The following illustrates the syntax of the Matrimony operator:
SELECT column_list Matrimony [DISTINCT | ALL] SELECT column_list UNION [DISTINCT | ALL] SELECT column_list ...
Code language: SQL (Structured Query Language) ( sql ) To combine result set of two or more queries using the Marriage operator, these are the basic rules that you must follow:
- Outset, the number and the orders of columns that announced in all
SELECTstatements must exist the same. - 2nd, the information types of columns must be the aforementioned or compatible.
By default, the Wedlock operator removes duplicate rows fifty-fifty if you don't specify the Distinct operator explicitly.
Let's run into the following sample tables: t1 and t2:
DROP Tabular array IF EXISTS t1; Drib TABLE IF EXISTS t2; CREATE Tabular array t1 ( id INT PRIMARY Fundamental ); CREATE Table t2 ( id INT Main Primal ); INSERT INTO t1 VALUES (1),(2),(3); INSERT INTO t2 VALUES (2),(three),(4);
Code linguistic communication: SQL (Structured Query Language) ( sql ) The post-obit statement combines effect sets returned from t1 and t2 tables:
SELECT id FROM t1 Marriage SELECT id FROM t2;
Code linguistic communication: SQL (Structured Query Linguistic communication) ( sql ) The final result ready contains the distinct values from dissever result sets returned by the queries:
+----+ | id | +----+ | 1 | | 2 | | 3 | | four | +----+ 4 rows in ready (0.00 sec)
Lawmaking linguistic communication: SQL (Structured Query Linguistic communication) ( sql ) Because the rows with value 2 and 3 are duplicates, the UNION removed them and kept merely unique values.
The following Venn diagram illustrates the union of two result sets that come from t1 and t2 tables:
If y'all use the Matrimony ALL explicitly, the duplicate rows, if available, remain in the consequence. Because UNION ALL does not need to handle duplicates, it performs faster thanMatrimony Distinct .
SELECT id FROM t1 Marriage ALL SELECT id FROM t2;
Code language: SQL (Structured Query Language) ( sql ) +----+ | id | +----+ | 1 | | 2 | | iii | | ii | | 3 | | 4 | +----+ 6 rows in fix (0.00 sec)
Lawmaking language: SQL (Structured Query Language) ( sql ) As you lot can see, the duplicates announced in the combined consequence set because of theUNION ALL operation.
Marriage vs. JOIN
AJOIN combines effect sets horizontally, aUNION appends consequence set vertically. The following picture illustrates the difference between UNION and Join:
MySQL Spousal relationship and column alias examples
Nosotros'll apply the customers and employees tables in the sample database for the demonstration:
Suppose that y'all desire to combine the start name and terminal name of employees and customers into a single outcome set, yous can use the UNION operator every bit follows:
SELECT firstName, lastName FROM employees UNION SELECT contactFirstName, contactLastName FROM customers;
Lawmaking language: SQL (Structured Query Language) ( sql ) 
As y'all tin can see from the output, the MySQL UNION uses the column names of the beginning SELECT statement for the column headings of the output.
If yous want to use other column headings, you need to use cavalcade aliases explicitly in the commencement SELECT statement as shown in the following example:
SELECT CONCAT(firstName,' ',lastName) fullname FROM employees Wedlock SELECT CONCAT(contactFirstName,' ',contactLastName) FROM customers;
Code linguistic communication: SQL (Structured Query Language) ( sql ) 
This example uses the cavalcade heading of the first query for the output. It uses the CONCAT() function to concatenate first name, space, and last name into a full name.
MySQL Matrimony and ORDER By
If you want to sort the result set of a union, you lot utilize an Order By clause in the finalSELECT argument as shown in the following example:
SELECT concat(firstName,' ',lastName) fullname FROM employees UNION SELECT concat(contactFirstName,' ',contactLastName) FROM customers ORDER Past fullname;
Code language: SQL (Structured Query Linguistic communication) ( sql ) 
Notice that if y'all identify the Lodge BY clause in each SELECT statement, information technology will not bear on the society of the rows in the terminal consequence prepare.
To differentiate betwixt employees and customers, you lot tin add a column as shown in the following query:
SELECT CONCAT(firstName, ' ', lastName) fullname, 'Employee' every bit contactType FROM employees Spousal relationship SELECT CONCAT(contactFirstName, ' ', contactLastName), 'Customer' equally contactType FROM customers ORDER By fullname
Code language: SQL (Structured Query Linguistic communication) ( sql ) 
MySQL also provides you with an alternative option to sort a event gear up based on column position using Guild By clause as follows:
SELECT CONCAT(firstName,' ',lastName) fullname FROM employees UNION SELECT CONCAT(contactFirstName,' ',contactLastName) FROM customers ORDER BY i;
Lawmaking language: SQL (Structured Query Language) ( sql ) However, it is non a good practise to sort the result set by column position.
In this tutorial, you have learned how to use MySQL UNION statement to combine data from multiple queries into a single result set up.
Was this tutorial helpful?
How To Add Id To Query Result Mysql,
Source: https://www.mysqltutorial.org/sql-union-mysql.aspx
Posted by: irvintionot.blogspot.com
0 Response to "How To Add Id To Query Result Mysql"
Post a Comment