Mysql multi-Table merge Query

Hi. baidu. commagicwengitem094ffd1808ee945bf0090e44blog. jhonse. comindex. phparchives2076.jhonse is quite useful. It records a UNION syntax SELECT... UNION [ALL | DISTINCT] SELECT... [UNION [ALL | DISTINCT] SELECT...] UNION is used

The http://hi.baidu.com/magicweng/item/094ffd1808ee945bf0090e44 http://blog.jhonse.com/index.php/archives/2076.jhonse is useful to record a UNION syntax SELECT... UNION [ALL | DISTINCT] SELECT... [UNION [ALL | DISTINCT] SELECT...] UNION is used

Http://hi.baidu.com/magicweng/item/094ffd1808ee945bf0090e44

Http://blog.jhonse.com/index.php/archives/2076.jhonseis available.

1. UNION syntax

SELECT...

UNION [ALL | DISTINCT]

SELECT...

[UNION [ALL | DISTINCT]

SELECT...]

UNION is used to combine the results from many SELECT statements into a result set.

Columns selected at the corresponding position of each SELECT statement should have the same type. (For example, the first column selected by the first statement should be of the same type as the first column selected by other statements .) (Note: I tested it in MySQL. If the column attributes of the two tables are of different types, the statement can be executed. You can also verify it) the column names used in the first SELECT statement are also used for the result column names (note that the number of select columns in each query statement must be the same, for example, you can use select * from... unino all select * from... union all ..., of course, you need to ensure that the number of select columns must be consistent. If you do not use select *, you don't have to worry about it. Hahaha ).

The SELECT statement is a regular selection statement, but is subject to the following restrictions:

· Only the last SELECT statement can use into outfile.

· HIGH _ PRIORITY cannot be used with SELECT statements that are part of the UNION operation. If you specify HIGH_PRIORITY for the first SELECT statement, it does not work. If you specify HIGH_PRIORITY for other subsequent SELECT statements, a syntax error occurs.

If you do not use the keyword ALL for UNION, ALL returned rows are unique, as if you have used DISTINCT for the entire result set. If ALL is specified, ALL matched rows are obtained from ALL used SELECT statements. For example, select value fromcode union all (select name from neeq_code) does not duplicate; remove All

The DISTINCT keyword is a self-selected word and does not play any role, but can be used in the syntax according to the requirements of the SQL standard. (In MySQL, DISTINCT represents the default working nature of a shared body .)

You can mix union all and union distinct in the same query. The mixed UNION type is treated in this way, that is, the DISTINCT shared body overwrites ALL the shared bodies located on the left. The DISTINCT shared body can be explicitly generated using union distinct, or implicitly generated using UNION (without the DISTINCT or ALL keyword.

If you want to use the order by or LIMIT clauseAll UNION results are classified or restricted.To enclose a single SELECT statement with parentheses and place order by or LIMIT behind the last statement. The following example uses both clauses:

(SELECT a FROM tbl_name WHERE a = 10 ANDB = 1) UNION (SELECT a FROM tbl_name WHERE a = 11 and B = 2) ORDER BY a LIMIT 10;

(SELECT a FROM tbl_name WHERE a = 10 and B = 1) UNION (SELECTa FROM tbl_name WHERE a = 11 and B = 2) ORDER BY a LIMIT 10;

Such order by statements cannot be referenced BY columns including table names (that is, names in tbl_name.col_name format ). You can provide a column alias in the first SELECT statement (the second select statement cannot provide the column alias), and refer to the alias in order, or use the column position to refer to the column in order. (Aliases are preferred because column locations are not recommended .)

In addition, if a column with a category has an alias, the order by clause must reference the alias instead of the column name. The first statement in the following statement must be run, but the second statement will fail. An error occurs when the 'A' column is unknown in 'order clause:

(SELECT a AS B FROM t) UNION (SELECT...) order by B;

(SELECT a AS B FROM t) UNION (SELECT...) ORDER BY;

To apply order by or LIMIT to aninpidual SELECT, place the clause inside the parentheses that enclose theSELECT.

To use ORDERBY or LIMIT for a single SELECT statement, the clause should be placed in parentheses. Parentheses contain SELECT.

(SELECT a FROM tbl_name WHERE a = 10 ANDB = 1 order by a LIMIT 10) UNION (SELECT a FROM tbl_name WHERE a = 11 and B = 2 ORDERBY a LIMIT 10 );

In this way, a total of 20 data records are obtained.

(Select value from code order by VALUEdesc LIMIT 4) union all (select value from neeq_code order by VALUE desc LIMIT4) order by value desc LIMIT 4; 4 pieces of data are obtained, the last orderby value desc LIMIT 4 is to sort the entire result and take four

2. instance Expansion

Union can combine two queries of the same table. the benefits of doing so are also very obvious. For example, in the blog application, you can use an SQL statement to display the top blog and ordinary blog pages.

(

SELECT * FROM 'blog'

WHERE top = 1

Order by created DESC

)

UNION

(

SELECT *

FROM 'blog'

WHERE top = 0

Order by created DESC

) LIMIT 2, 3

Note: union requires that the two tables to be joined have the same number of data columns (select * is used here). If one table does not have the fields of the other table, it can be replaced by NULL.

// Total number of data entries in the table after union

Example: SELECT count (*) from (SELECT * from code union all select * from neeq_code) as t; if t is removed, an error is returned...

// Conditional query of tables after union

SELECT t. * from (SELECT * from code union all select * from neeq_code) as t where t. Field name = '';

// Virtual Column

SELECT value, name, '000000' from code union all select value, name, '000000' from neeq_code;

The value is the name of a virtual column.