A study of default rules for the mySQL UNION operator

 
/*  Create a data table  */ 
create table td_base_data( id int(10) not null auto_increment,userId int(10) default '0',primary key (`id`))ENGINE=MyISAM DEFAULT CHARSET=gbk; 
create table td_base_data_20090527( id int(10) not null auto_increment,userId int(10) default '0',primary key (`id`))ENGINE=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 = '45') + (select count(userId) from td_base_data_20090527 where userId = '45') 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 */ 
/*  In the mysql , union It has the function of eliminating duplicates  */ 

/*  Query manual definition  */ 
/* 

Check the mysql reference manual:
13.2.7.2. UNION syntax
If you do not use the keyword ALL for UNION, then all rows returned are one-only, as if you had used DISTINCT for the entire result set. If you specify ALL, you get all the matching lines from all the SELECT statements you used.
The DISTINCT keyword is a word of choice, which does not have any effect, but is allowed in the grammar according to the requirements of the SQL standard. (in MySQL, DISTINCT represents the default working nature of a common.)
*/
/* proves that in mysql, union is by default the */ of DISTINCT
/*
Check the 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 the order of columns in all queries must be the same.
2. Data types must be compatible.
Parameters:
UNION
Specifies that multiple result sets are composed and returned as a single result set.
ALL
Contains all rows in the result, including duplicate rows. If not specified, the duplicate row is deleted.
*/
/* proves that in mssql, union is also */ of DISTINCT by default

/* query standard definition */
/*
Enquiry of SQL2003 standard:
Transact - SQL reference
4.10.6.2 Operators that operate on multisets and return multisets
MULTISET UNION is an 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 is specified, then DISTINCT is implicit.
*/
You can see that the standard SQL2003 defines DISTINCT as the default value of union */

You should index the userId fields in both tables to speed up the query */
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;