0

I have two tables. One is students and another is studentPerformance

students table contains id, name and email_id

studentPerformance table contains id, student_id, marks

My question is, how I can delete all the students from students table who doesn't have any record in studentPerformance table?

I did Google but didn't land any proper place.

Thanks.

abbas
  • 238
  • 2
  • 18

3 Answers3

1

Left join:

 DELETE s 
 FROM Students AS s 
 LEFT JOIN StudentPerformance AS sp
  ON sp.student_id = s.id 
 WHERE sp.student_id IS NULL; -- where not match was found (no sp-row)

or not exists:

 DELETE s 
 FROM Students AS s 
 WHERE NOT EXISTS (SELECT 1 FROM StudentPerformance AS sp WHERE sp.student_id = s.id);
SAS
  • 3,943
  • 2
  • 27
  • 48
  • Just for the sake of curiosity, which method is faster? – abbas Jan 02 '18 at 14:17
  • The main difference is in the returned columns (in a select). In general, if you don't need the data, go for not exists, which is also usually somewhat faster, but this is a delete so again, not exists is probably best. – SAS Jan 02 '18 at 14:19
  • @abbas NOT EXISTS is faster – Ace Amr Jan 02 '18 at 14:24
  • @AceAmr Do you have *any* evidence for such an assertion? – MatBailie Jan 02 '18 at 19:46
  • If you set up a SELECT test, the query plan should be different (I think it's filter a step that differs). – SAS Jan 03 '18 at 08:28
  • @MatBailie This is been discussed many times before. See -https://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/ – Ace Amr Jan 03 '18 at 10:47
  • Also discussed on SO here - https://stackoverflow.com/questions/2246772/whats-the-difference-between-not-exists-vs-not-in-vs-left-join-where-is-null – Ace Amr Jan 03 '18 at 10:52
0

You can use not exists, not in, or left join/where:

delete s from students s
   where not exists (select 1 from studentperformance sp where sp.student_id = s.id);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

--run this script and watch the result

create table #students ( id int, name varchar(20), email_id varchar(30) )

insert into #students ( id, name, email_id ) values ( 1, 'jan', 'jan@gmail.com' ), ( 2, 'Peter', 'peter@gmail.com' ), ( 7, 'Pierre', 'pierre@gmail.com' ), ( 12, 'Peter', 'peter@gmail.com' )

create table #studentPerformance ( id int, student_id int, marks int )

insert into #studentPerformance ( id, student_id, marks ) values ( 1, 2, 6 ), ( 1, 7, 8 )

delete #students from #students where id not in (select student_id from #studentPerformance)

--student 1 and 12 don't have performed so they where deleted

--student 2 and 7 won't be deleted

select * from #students

drop table #students

drop table #studentPerformance

ying lam
  • 1
  • 3