MySQL多表查询合并结果unionall，内连接查询
MySQL多表查询合并结果和内连接查询
1、使⽤union和union all合并两个查询结果：select 字段名 from tablename1 union select 字段名 from tablename2；
注意这个操作必须保证两张表字段相同，字段数据类型也相同，再针对结果统⼀排序操作等。另外，使⽤union的时候会去除重复（相同）的记录?，⽽union all则不会。
create table table_new
select * from
(
SELECT * FROM DB.table1
union all
SELECT * FROM DB.table2
) as t
group by key1,key2 collate utf8_bin
order by key1,key2;
1. 在数据库查询中，默认是不区分⼤⼩写的。那如何让查询结果区分⼤⼩写呢？collate utf8_bin放在like前后都可以。
2. select * from user where name like "A\%B%"  collate utf8_bin;
或者  select * from user where name  collate utf8_bin like "A\%B%" ;
注：下边的⼏个连接查询涉及到笛卡尔积的概念，即如果存在两张表，第⼀张记录数为n条，另⼀张表的记录数为m条，那么笛卡尔积得出的记录数就是n*m条；如果第⼀张表的字段数为a个，另⼀张的字段数为b个，则笛卡尔积得出的字段数就是a+b个。
2、使⽤natural join⾃然连接：前提是两张表有相同的字段：（这个操作会去掉重复的字段）
对于这个查询，我的理解是：保留这两张表中关联字段（例如这⾥的depart_id）都存在的数据，去掉只有⼀个表中有的:
如上述内容中，company中有四条数据，⽽emp中有7条，结果不论哪个放前边都只出来六条，因为company中depart_id为4的，emp中没有，⽽emp中depart_id为5的，company中⼜没有。
3、inner join on等值连接查询：
这个操作在我理解，和上边的⾃然连接区别就在于没有去掉重复的字段，还有更加的灵活，不需要有相同字段名的字段：
4、inner join on不等值连接查询：
对于这个查询，我的理解就是，他是“=”查询的补集，即这个查询的结果集 = 笛卡尔积结果集减去“=”查询的结果集；本例中，company有4条记录，emp有7条记录，笛卡尔积是4*7=28条记录；“=”查询的结果是6条记录，⼀次"!="查询就应该是其余的22条记录。
5、从上边的操作中可以拓展出另⼀种查询：字连接查询，即实际只有⼀张表，⽤重命名的⽅式当成两张来⽤：
可以看到，根据笛卡尔积，这⾥应该有4*4=16条结果，但是因为有了条件，就筛选出了四条
mysql 实战 or、in与union all 的查询效率
OR、in和union all 查询效率到底哪个快。
⽹上很多的声⾳都是说union all 快于 or、in，因为or、in会导致全表扫描，他们给出了很多的实例。
但真的union all真的快于or、in？本⽂就是采⽤实际的实例来探讨到底是它们之间的效率。
1：创建表，插⼊数据、数据量为1千万【要不效果不明显】。
Sql代码
1. drop table if EXISTS BT;
2. create table BT(
3.    ID int(10) NOT NUll,
4.    VName varchar(20) DEFAULT '' NOT NULL,
5.    PRIMARY key( ID )
6. )ENGINE=INNODB;
该表只有两个字段 ID为主键【索引页类似】，⼀个是普通的字段。（偷懒就⽤简单的表结构呢）mysql语句多表查询
向BT表中插⼊1千万条数据
这⾥我写了⼀个简单的存储过程【所以你的mysql版本⾄少⼤于5.0，俺的版本为5.1】，代码如下。
注意：最好
INSERT INTO BT ( ID,VNAME )  VALUES( i, CONCAT( 'M', i ) );---1
修改为
INSERT INTO BT ( ID,VNAME )  VALUES( i, CONCAT( 'M', i, 'TT' ) );---2
修改原因在
⾮索引列及VNAME使⽤了联合进⾏完全扫描请使⽤1 。
⾮索引列及VNAME使⽤了全表扫描请使⽤2 。
Sql代码
订单buffer
1. DROP PROCEDURE IF EXISTS test_proc;
2. CREATE PROCEDURE test_proc()
3. BEGIN
4. declare i int default 0;
5. set autocommit = 0;
6. while i<10000000 do
7. INSERT INTO BT ( ID,VNAME )  VALUES( i, CONCAT( 'M', i ) );
8. set i = i+1;
9. if i%2000 = 0 theninline是什么意思英语
10. commit;
11. end if;
个人简历模板免费下载电子版一张
12. end while;
13. END;
就不写注释呢，挺简单的。
存储过程是最好设置下innob的相关参数【主要和⽇志、写缓存相关这样能加快插⼊】，俺没有设置插⼊1千万条数据插了6分钟。部分数据如下：1千万数据类似
2：实战
2.1 ：分别在索引列上使⽤ or、in、union all
我们创建的表只有主键索引，所以只能⽤ID做查询呢。我们查 ID 为 98，85220，9888589的三个数据各个耗时如下：
时间都为0.00，怎么会这样呢，呵呵所有查询都是在毫秒级别。
我使⽤其他的⼯具--EMS SQL Manager  for mysql
查询显⽰时间为
93 ms， 94ms，93 ms，时间相差了多少⼏乎可以忽略。
然后我们在看看各⾃的执⾏计划
这⾥要注意的字段type 与ref字段
我们发现union all 的所⽤的 type【type为显⽰连接使⽤了何种类型】为ref ⽽or和in为range【ref连接类型优于range，相差不了多少】，⽽查询⾏数都⼀样【看rows字段都是为3】。
从整个的过程来看，在索引列使⽤常数or及in和union all查询相差不了多少。
但为什么在有的复杂查询中，再索引列使⽤or及in ⽐union all 速度慢很多呢，这可能是你的查询写的不够合理，让mysql放弃索引⽽进⾏全表扫描。
2.2：在⾮索引列中使⽤ or、in及union all。
我们查 VNAME 为 M98，M85220，M9888589的三个数据各个耗时如下：
substring函数使用方法我们发现为啥union all查询时间⼏乎为 or 和in的三倍。
这是为什么呢，我们先不说，先看看三个的查询计划。
这⾥我们发现计划⼏乎⼀样。
但我们要注意扫描的此时对于 or及in 来说只对表扫描⼀次即rows是列为9664782。
⽽对于union all 来说对表扫描了三次即rows的和为9664782*3。
这也是为什么我们看到union all 为⼏乎为三倍的原因。
备注：如果使⽤存储过程使⽤第⼆sql该执⾏计划所有的type列为 all，其实这个是我最想演⽰的，但现在已经快写完毕了才发现问题将错就错呢。
3：总结
jqueryclick3.1：不要迷信union all 就⽐ or及in 快，要结合实际情况分析到底使⽤哪种情况。
3.2：对于索引列来最好使⽤union all，因复杂的查询【包含运算等】将使or、in放弃索引⽽全表扫描，除⾮你能确定or、in会使⽤索引。
3.3：对于只有⾮索引字段来说你就⽼⽼实实的⽤or 或者in，因为⾮索引字段本来要全表扫描⽽union all 只成倍增加表扫描的次数。
3.4：对于及有索引字段【索引字段有效】⼜包含⾮索引字段来时，按理你也使⽤or 、in或者union all 都可以，
但是我推荐使⽤or、in。
如以下查询：
Sql代码
1. select * from bt where bt.VName = 'M98' or bt.id ='9888589'
2.
3. select * from bt where bt.VName = 'M98'
4. UNION ALL
5. select * from bt where  bt.id = '9888589'
该两个查询速度相差多少主要取决于索引列查询时长，如索引列查询时间太长的话，那你也⽤or或者in代替吧。
3.5：以上主要针对的是单表，⽽多表联合查询来说，考虑的地⽅就⽐较多了，⽐如连接⽅式，查询表数据量分布、索引等，再结合单表的策略选择合适的关键字。
个⼈观点仅供参考、需要结合实际数据⽤例测试选择合适的关键字.......................

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。