Each of the preceding two statements will return an integer first column, which results in type conversion of the dates to integers. Note: Before MySQL 4.1.1, the data type for each column of the UNION result is based only on the type of the column in the first SELECT, and values from corresponding column of the following SELECT statements are converted to that type. In both cases, the result is a string column. In the second statement, integers and dates are selected for the first column, strings and integers for the second column. In the first statement, strings and dates are selected for the second column. In each statement, the data type for each column of the result is determined from the selected values.
(Normally, you write UNION such that corresponding columns do have the same types, but MySQL performs type conversion as necessary if they do not.) Columns are matched by position rather than by name, which is why the following two statements return different results, even though they select the same values from the two tables: The second and subsequent SELECT statements in the UNION must select the same number of columns, but corresponding columns need not have the same names or data types. The names for the columns of the UNION result come from the names of the columns in the first SELECT. For example, to select the integer column from each table, do this: mysql> SELECT i FROM t1 UNION SELECT i FROM t2 UNION SELECT i FROM t3 +-+| i |+-+| 1 || 2 || 3 || -1 || 100 || 200 |+-+ To write a UNION statement that combines multiple retrievals, just write several SELECT statements and put the keyword UNION between them. Tables t1 and t2 have integer and character columns, and t3 has date and integer columns. If you want to create a result set that combines the results from several queries, you can do so by using a UNION statement. | | 213B Commerce Park.Learn More Buy Performing Multiple-Table Retrievals with UNION If you want to select all records, including duplicates, follow the first UNION keyword with ALL − > SELECT company, '', street FROM vendor > SELECT first_name, last_name, address FROM customer Mysql> SELECT fname, lname, addr FROM prospect
#Mysql union different column types how to#
The following query illustrates how to select names and addresses from the three tables all at once − It does not matter if all the three tables have different column names.
Assume the three tables have the following contents − Suppose you have two tables that list prospective and actual customers, a third that lists vendors from whom you purchase supplies, and you want to create a single mailing list by merging names and addresses from all three tables. You can use UNION if you want to select rows one after the other from several tables or several sets of rows from a single table all as a single result set.
0 kommentar(er)
