sql - mysql query to get all the unit that a student not enrolled in -

i have 2 tables: chosenunit , units. in chosenunit table there 2 columns student_id , unit_id . units table has unit_id , unit_name columns units in system. lets there 2 students ids of 1000 , 1001. enrolled in several units. chosenunit table this:

(1000,2000), (1000,2001), (1000,2006), (1001,2000), (1001,2004), 

i need units student not enrolled in. if 1000 logs in system,it need show unit_names of subjects except 2000,2001,2006.

there many possible solutions on problem.

first using left join , is null.

select  a.*    units         left join choseunits b             on a.unit_id = b.unit_id ,                 b.student_id = 1000   b.unit_id null 

• sqlfiddle demo

to further gain more knowledge joins, kindly visit link below:

• visual representation of sql joins

second using not exists

select  a.*    unit   not exists         (             select  1                choseunit b               a.unit_id = b.unit_id ,                     b.student_id = 1000         ) 

• sqlfiddle demo