SQL table.column not working when I use it with full join

I have this SQL query:

SELECT main.cname, main.email, das.a 
FROM main 
FULL JOIN das ON main.email = das.cname 

And it returns the error :

“unknown column main.cname in filed list”.

while the column certainly exists:

SELECT main.cname 
FROM main

seems alright.

I tried using aliases but it just made it worse. I’m clueless

Answer

As coyeb60297 has pointed out, MySQL doesn’t do full outer joins so your query is:

SELECT main.cname, main.email, das.a 
FROM 
  main full
  JOIN das ON main.email = das.cname 

and because main has been aliased as full, you can’t say main.cname because main is not a thing

This would work:

SELECT full.cname, full.email, das.a 
FROM 
  main full
  JOIN das ON full.email = das.cname 

.. but it isn’t a full join (it just better explains the point above).

Consider instead something like:

SELECT main.cname, main.email, das.a 
FROM 
  main 
  LEFT JOIN das ON main.email = das.cname 
UNION
SELECT main.cname, das.email, das.a 
FROM 
  main 
  RIGHT JOIN das ON main.email = das.cname 

If you face performance issues with the full join consider:

1. do you really need it?
2. MatBailie’s suggestion to union on only those rows the left join lacks: (a LEFT JOIN b) UNION ALL (a RIGHT JOIN b WHERE a.id IS NULL)