Set Functions

use ReportBuilderTraining
go

create schema tmp
go

-- need to delete this first because of the foreign keys
if object_id('tmp.TableA') is not null
   	drop table tmp.TableA
	
if object_id('tmp.TableB') is not null
   	drop table tmp.TableB
 
go

create table tmp.TableA (Name varchar(50))
create table tmp.TableB (Name varchar(50))

go

insert into tmp.TableA (Name)
	values 
		 ('Ed')
		,('Kim')
		,('Juhl')
		,(NULL)

insert into tmp.TableB (Name)
	values 
		 ('Ed')
		,('Kim')
		,('Jake')
		,('Sam')

go

select *
	from tmp.TableA

select *
	from tmp.TableB

go


---
--- Union and Union All
---

select * from tmp.TableA
union
select * from tmp.TableB

select * from tmp.TableA
union all
select * from tmp.TableB


---
--- normalize phone table
---

select * from PHone

select *
	from (

		select StudentId, Permanent Phone, 'P' PhoneType from Phone
		union
		select StudentId, Local Phone,     'L' PhoneType from Phone
		union
		select StudentId, Cell Phone,      'C' PhoneType from Phone

	) t
	where Phone is not null


---
--- Except
---

-- notice how NULLS are handled

select * from tmp.TableA
except
select * from tmp.TableB

select * from tmp.TableB
except
select * from tmp.TableA


---
--- Intersect
---

select * from tmp.TableA
intersect
select * from tmp.TableB