Null和not NULL值

Question

我想通过比较u.fld_id = ct.fld_teacher_id来获取教师的信息，如果ct.fld_teacher_id为null，我将显示not assigned yet。但是ct.fld_teacher_id为空的行在执行时不会显示。

这是我的问题。

  SELECT ct.fld_id as id, ct.fld_serial, ct.fld_city, ct.fld_venue_name, 
        ct.fld_start_time, ct.fld_end_time, 
        ct.fld_duration, ct.fld_available_seats, 
        ct.fld_course_price, ct.fld_status,
        c.fld_name, u.fld_first_name, u.fld_last_name,
        ct.fld_status, ct.fld_teacher_id, ct.fld_training_name,
        ct.fld_class, ct.fld_level, ct.fld_is_fixed, tt.fld_name as training_type_name
   FROM tbl_course_training as ct, tbl_users as u,
        tbl_courses as c, tbl_training_types as tt
   WHERE c.fld_id = ct.fld_course_id AND u.fld_id = ct.fld_teacher_id AND 
       ct.fld_teacher_id = 'NULL' AND tt.fld_id = ct.fld_training_type_id 

Answer 1

如果不知道你的查询的结构，我就不能对你的查询进行排序。然而，我注意到的事情很少。

• 不能将空值与ct.fld_teacher_id = 'NULL'进行比较(null不是字符串)

ct.fld_teacher_id is null --如果您需要选择even when

• ，则正确的方式为ct.fld_teacher_id is null

u.fld_id = isnull( ct.fld_teacher_id，u.fld_id) --或( sql服务器) u.fld_id = ct.fld_teacher_id或ct.fld_teacher_id is null --(my sql或sql服务器) u.fld_id = COALESCE(ct.fld_teacher_id，u.fld_id) --(my Sql)

• 也避免使用像From Table1 t1, Table2 t2这样的旧方法连接表

从Table1 t1加入Table2 t2 On t1.Key = t2.Key --Correct

• 我认为这里需要一些Left Join。请使用check this link了解Sql Joins

的工作原理