SQL理论知识第二部分

Hive sql中left semi join用法

left semi join叫做左半连接，是in/exists子查询的一种高效实现方式。

select * from tab1 where size in (select size from tab2)
等价于
select * from tab1 left semi join tab2 on tab1.size = tab2.size

特点：

1. left semi join 是只传递表的 join key 给 map 阶段，因此left semi join 中最后 select 的结果只会出现左表中的记录。
2. 由于连接的右表不会出现，因此不能通过where条件过滤右表记录，只能在on后面加上条件对右表进行过滤。
3. 连接时，右表有重复的连接key值，left semi join后产生结果不重复。
   

in和exists用法和区别

• in关键字：先执行内表查询，再将内表结果与外表匹配，查询出匹配成功的记录。由于内表无论多大，查询都不可避免，所以一般来说in适用于内表较小的，外表较大的查询场景。

select * from A where id in (select id from B)
等价于
① 先执行select id from B
② 再执行select id from A where A.id = B.id

• exists关键字：先执行外表查询，将外表查询结果放入子查询中验证，为true则输出，为false则不输出。

select * from A where exists (select 1 from B where B.id = A.id)
等价于
① 先执行外层查询select id from A
② 再执行select id from B where B.id = A.id

一般来说exists适用外表小，内表大的查询场景。代码中的select 1 还是select *都一样，执行时会忽略select.

• 总结：
  1、exists 会针对子查询的表使用索引，not exists 会对主子查询都会使用索引。in 与子查询一起使用的时候，只能针对主查询使用索引，not in 则不会使用任何索引。
  2、如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。
  3、表中有null记录，in/exists查询不执行，in (select id from B)查询出的id不为null，exists (select 1 from B where B.id = A.id)判断null= null时不为true，因此也不会输出null记录。

drop、truncate和delete的用法和区别

1. 语法
   drop table 表名
   truncate table 表名
   delete from 表名 where 列名 = 值
   eg:drop table A、truncate table B、delete from C where id = 1

2. 三者区别与联系
   1、drop是删除表数据、表结构定义，同时删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index)；依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。执行drop操作后，无法再执行插入数据操作，只有重新创建。
   truncate只是清除表的所有数据，保留表结构，但其无法删除某行数据，只能全部清楚，执行truncate之后，可以再执行插入数据操作使表再次有数据。
   delete结合where可以删除指定行记录，并且同时将该行的删除操作作为事务记录在日志中保存，以便进行回滚操作。不带where等同truncate，只删除表所有数据，但保留表结构。
   2、对于外键约束引用的表，不能使用truncate，而使用delete不带where的语句，由于truncate table记录在日志中，所以不能激活触发器。
   3、delete是数据库操作语言（dml），执行操作会记录在rollback segement上，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。而truncate和drop是数据库定义语言（ddl），它们的操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。
   4、执行速度，一般来说: drop> truncate > delete。
   5、在 InnoDB 引擎中，truncate 可以重置自增列，而 delete 不能重置自增列。
   6、truncate只能对table，delete可以是table，view，synonym。