求带外键约束的查询语句

Question

classes_def的字段manager_id与teacher_def的id相关联，现在想根据manager_id查询出classes_def.name,state,teacher.name
//教师
CREATE TABLE teacher_def(
id INT PRIMARY KEY IDENTITY(1,1),
name VARCHAR(20),
password VARCHAR(20),
UNIQUE(name,password))//班级
CREATE TABLE classes_def(
id INT PRIMARY KEY IDENTITY(1,1),
name VARCHAR(10) NOT NULL UNIQUE,
state VARCHAR(10) DEFAULT '未开课' NOT NULL,
manager_id int,
FOREIGN KEY(manager_id) REFERENCES teacher_def(id))

Answer 1

classes_def的字段manager_id与teacher_def的id相关联，现在想根据manager_id查询出select classes_def.name,state,teacher.name from classes_def left join teacher_def on classes_def.manager_id=teacher_def.id and classes_def.manager_id=想查询的id

Answer 2

如果不写and classes_def.manager_id=想查询的id
他将列出所有班级的对应老师,如果老师没有则为null