MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.36


CREATE TABLE classroom (
 id INT,
 grade VARCHAR(25),
 status VARCHAR(25),
 teacherId INT
);

CREATE TABLE teacher (
 id INT,
 name VARCHAR(25)
);

INSERT INTO classroom 
VALUES
(1, 'A', 'Complete', 1),
(2, 'B', 'Complete', 2),
(3, NULL, 'In Progress', 1),
(4, '', 'In Progress', 1);

INSERT INTO teacher 
VALUES
(1, 'Bob'),
(2, 'Sara'),
(3, 'Mike');

Records: 4  Duplicates: 0  Warnings: 0



Records: 3  Duplicates: 0  Warnings: 0


SELECT * FROM
(SELECT 
   a.id as id, 
   a.grade as grade,
   a.status as status, 
   b.id as tid, 
   b.name as tname 
 FROM classroom a
 LEFT JOIN teacher b ON a.teacherId = b.id 
   UNION
 SELECT 
   a.id as id, 
   a.grade as grade,
   a.status as status, 
   b.id as tid, 
   b.name as tname 
 FROM classroom a
 RIGHT JOIN teacher b ON a.teacherId = b.id) c
WHERE c.grade != '' AND c.grade IS NOT NULL
ORDER BY c.grade ASC


id
grade
status
tid
tname


1
A
Complete
1
Bob

2
B
Complete
2
Sara