SQL学习记录:集合

并集: union

select  'IND' type_cd, cust_id, lname name FROM individualUNION ALL //all保留重复项select  'BUS' type_cd,cust_id, nameFROM businessUNION ALLselect  'BUS' type_cd,cust_id, nameFROM business

select  'IND' type_cd, cust_id, lname name FROM individualUNION  //不保留重复项select  'BUS' type_cd,cust_id, nameFROM businessUNION select  'BUS' type_cd,cust_id, nameFROM business

交集: intersect

差: except 集合差操作

select emp_idfrom employeewhere assigned_branch_id  = 2    AND (title = 'Teller' OR title = 'Head Teller'exceptselect distinct open_emp_idfrom accountwhere open_branch_id = 2

注意

1. 若要对符合查询进行排序, 可以oeder by 列名字要用第一个查询的名字.最好将列明重命名一致!

select  'IND' type_cd, cust_id, lname name FROM individualUNION  select  'BUS' type_cd,cust_id as id, nameFROM businessUNION select  'BUS' type_cd,cust_id as id, nameFROM businessorder by cust_id

1. 集合操作的优先级
   
   • 在调用集合操作时, 集合操作具有最高优先级
   • 可以用圆括号对多个查询进行封装,以明确指定他们的次序