how to use full outer join by removing inner join

Question

I am new to MySQL I have one query which works perfectly fine with inner join but with inner join some records got missing I want all the data from both the table but when i use full outer join or full join it gives error unknown column classroom.id in field list

here is the query

SELECT 
  classroom.id as id, 
  classroom.grade as grade,
  classroom.status as status, 
  teacher.id as tid, 
  teacher.name as tname 
FROM classroom
FULL JOIN teacher on classroom.teacherId = teacher.id 
ORDER BY grade ASC

these are my two tables you can see in the picture enter image description here

and also I mention in column

classroom 
id,grade,teacherid,status
teacher 
id,email,password,name,status,role

Answer 1

MySQL does not support a FULL OUTER JOIN or FULL JOIN, you have to emulate it using UNION with LEFT JOIN and RIGHT JOIN.

Read more about it here: Why does MySQL report a syntax error on FULL OUTER JOIN?

So your syntax should look like this:

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

UPDATE: Per your comments below, I've include a WHERE clause to remove NULL values AND empty '' values. You could also write a WHERE clause in each of the UNION queries above but I find it easier to put it in a subquery and write the WHERE clause once in the outer query. I've also added aliases a, b, c so its easier to read vs. using the table names.

Demo here.