T-sql 2012 get 'NOT In' or 'NOT EXISTS' to work

Transact-SQL
1

In t-sql 2012, I have the following sql:

declare @gradYear int = 2017

SELECT distinct graduation.personID
into #gradPersonID
FROM [TEST].[dbo].[Graduation] graduation
where graduation.gradyear = @gradYear

SELECT distinct personID
FROM #gradPersonID graduation
where personID NOT in
(select distinct TranscriptCourse.personID from #gradPersonID graduation
join [TEST].[dbo].[TranscriptCourse] TranscriptCourse
on TranscriptCourse.personID = graduation.personID
where TranscriptCourse.grade between '09' and '12'
and @gradYear - 4 = TranscriptCourse.endYear)
DROP table #gradPersonID

I am joining to the graduation table so I can find students that are scheduled to graduate in a certain year. In this case the year is 2017.

What I am looking for is from the TranscriptCourse table by personID if there are no records where the endYear = 2014. Somehow I canot
seem to get the 'NOT IN' or 'NOT EXISTS' to work.

Thus would you show me how to change this sql so I canaccomplish my goal?

2

Not exists:

select g.personid
  from test.dbo.graduation as g
 where not exists (select 1
                     from test.dbo.transcriptcourse as tc
                    where tc.personid=g.personid
                      and tc.grade between '09' and '12'
                      and tc.endyear=@gradYear-4
                  )
 group by g.personid
;

Left joining:

select g.personid
  from test.dbo.graduation as g
       left outer join test.dbo.transcriptcourse as tc
                    on tc.personid=g.personid
                   and tc.grade between '09' and '12'
                   and tc.endyear=@gradYear-4
 where tc.personid is null
 group by g.personid
;
1 Like
3

what is the better method to use? The left join or the not exists? Which is more efficient and why?

4

In this case, propably the not exists.

1 Like