Default rule research for the MySQL Union operator _mysql

Copy Code code as follows:

/* Set up the data table * *
CREATE TABLE Td_base_data (ID int () NOT null Auto_increment,userid int () default ' 0 ', primary key (' ID ')) engine=myisam DEFAULT CHARSET=GBK;
CREATE TABLE td_base_data_20090527 (ID int () NOT null Auto_increment,userid int () default ' 0 ', primary key (' ID ')) engin E=myisam DEFAULT CHARSET=GBK;
/* INSERT Analog record * *
Insert into Td_base_data (USERID) values (1);
Insert into Td_base_data (userId) values (45);
Insert into Td_base_data (userId) values (45);
Insert into Td_base_data (USERID) values (1);
Insert into Td_base_data (userId) values (45);
Insert into td_base_data_20090527 (USERID) values (1);
Insert into td_base_data_20090527 (userId) values (45);
Insert into td_base_data_20090527 (userId) values (45);
Insert into td_base_data_20090527 (USERID) values (1);
Insert into td_base_data_20090527 (userId) values (45);
Insert into td_base_data_20090527 (userId) values (45);
* * Query Test/*
Select COUNT (userId) as Cnumber from td_base_data where userId = ' 45 ';
/* 3 * *
Select COUNT (userId) as Cnumber from td_base_data_20090527 where userId = ' 45 ';
/* 4 * *
Select (select count (userId) from td_base_data where userId = ' the ') + (select COUNT (userId) from td_base_data_20090527 whe Re userId = ' ') as Cnumber;
/* 7 * *
Select COUNT (*) from
(
Select ID from td_base_data where userId = ' 45 '
Union
Select ID from td_base_data_20090527 where userId = ' 45 '
) as TX;
/* 4 * *
Select COUNT (*) from
(
SELECT * from td_base_data where userId = ' 45 '
Union
SELECT * from td_base_data_20090527 where userId = ' 45 '
) as TX;
/* 4 * *
/* proof that in MySQL, the Union itself has the function of excluding duplicates.

/* Query Manual definition * *
/*

Query MySQL reference manual:
13.2.7.2. Union Grammar
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 you specify all, you get all the matching rows from all the used SELECT statements.
Distinct keyword is an optional word, does not play any role, but according to the requirements of the SQL standard, in the syntax to allow the adoption. (in MySQL, distinct represents the default working nature of a common body.) ）
*/
/* Proof in MySQL, union default is DISTINCT's * *
/*
Query MSSQL Reference Manual:
Transact-SQL Reference
UNION operator:
The two basic rules for combining the result sets of two queries using UNION are:
1. The number of columns and columns in all queries must be the same.
2. Data types must be compatible.
Parameters:
UNION
Specifies that multiple result sets are grouped and returned as a single result set.
All
Include all rows in the result, including duplicate rows. If not specified, the duplicate row is deleted.
*/
/* Proof in MSSQL, union default is also distinct * *

/* Query Standard definition * *
/*
Query SQL2003 Standard:
Transact-SQL Reference
4.10.6.2 Operators that operate in multisets and return multisets
Multiset Union is a operator that computes the union of two multisets. There are two variants, specified using all or DISTINCT, to either retain duplicates or remove duplicates.
7.13 <query expression>
Syntax Rules
6) If UNION, EXCEPT, or INTERSECT is specified and neither all nor DISTINCT are specified, then DISTINCT is implicit.
*/
/* Visible SQL2003 standard defines the default value of the Union for distinct.

/* Correct query, and should be indexed in the UserID field of the two tables to speed up the query speed * *
Select COUNT (userId) as Cnumber from
(
Select UserId from td_base_data where userId = ' 45 '
UNION ALL
Select UserId from td_base_data_20090527 where userId = ' 45 '
) as TX;