Subqueries

use ReportBuilderTraining

---
--- find students without an address
---

select *
	from Student
	where StudentId not in (select StudentId from Address)
	-- 133 students


---
--- Retrieve all students, with permanent address when available
---

select s.*, a.*
	from Student s
	left join (
	
		select *
			from Address
			where AddressTypeCode='P'

	) a
	on s.StudentId=a.StudentId
	order by a.AddressId
	-- 5,000 students



---
--- and, if you still want to compare performance, compare to our last version to solve this problem
---

select s.*, a.*
	from Student s
	left join Address a
	on s.StudentId=a.StudentId and AddressTypeCode='P'
	order by a.AddressId
	-- 5,000 students