Union使用

	select  subjectRecordNo   as  subjectRecordNo                                       
		, CIP_subjectName     as      subjectName                                          
		, CIP_subjectRemark   as      subjectRemark                                        
		, CIP_englishName     as      englishName                                          
		, CIP_sortNo          as      sortNo                                               
		, CIP_isSubject       as      isSubject                                               
	    from cip_subjectWord 
	    where  CIP_subjectName like '丰收瓜'
		or subjectRecordNo in (
		select subjectRecordNo  from cip_subjectWord_contact
		where CIP_contactSign='D'
		and CIP_contactWord='丰收瓜'
		)  

 上面的程序不能正常执行,占内存,TOAD会死掉.改为下面的语句会好很多,

		select  subjectRecordNo   as  subjectRecordNo                                       
		, CIP_subjectName     as      subjectName                                          
		, CIP_subjectRemark   as      subjectRemark                                        
		, CIP_englishName     as      englishName                                          
		, CIP_sortNo          as      sortNo                                               
		, CIP_isSubject       as      isSubject                                               
	    from cip_subjectWord 
	    where  CIP_subjectName like '菜青梨'
		union all
		select subjectRecordNo   as  subjectRecordNo                                       
		, CIP_subjectName     as      subjectName                                          
		, CIP_subjectRemark   as      subjectRemark                                        
		, CIP_englishName     as      englishName                                          
		, CIP_sortNo          as      sortNo                                               
		, CIP_isSubject       as      isSubject                                               
	    from cip_subjectWord 
	    where  subjectRecordNo in (
		select subjectRecordNo  from cip_subjectWord_contact
		where CIP_contactSign='D'
		and CIP_contactWord='菜青梨'
		) 

 

UNION 指令的目的是将两个 SQL 语句的结果合并起来,可以查看你要的查询结果.

union  会排除重复的记录  
union  all  不会排除重复的记录  
union all 比union 速度快很多，建议在相同的情况下用 union all

注意:union用法中,两个select语句的字段类型匹配,而且字段个数要相同。