Unknown column error in full join syntax

Question

A friend and I were playing around with the JOIN clauses available in MySQL. Here is the table structure we have:

create table abc 
(
  c1 int,
  c2 char(1)
);
create table xyz
(
  c2 char(1),
  c3 varchar(5)
);

The values inserted into them are:

insert into abc
values
  (1,"a"),
  (2,"b");
insert into xyz
values
  ("a","apple"),
  ("c","car");

Now, I have the following two SQL statements (sqlfiddle link):

select
  *,
  'full'
from abc
full join xyz
  using (c2);

and

select
  *,
  'full'
from abc
full join xyz
  on abc.c2 = xyz.c2;

As can be seen in the fiddle, the first query returns:

| C2 | C1 |    C3 | FULL |
|----|----|-------|------|
|  a |  1 | apple | full |

while, the second query raises and error:

Unknown column 'abc.c2' in 'on clause':

Why is the column abc.c2 not recognised when using the ON clause of JOIN syntax?

Answer 1

FULL JOIN is not supported by MySQL. It's because full is recognized as alias to abc table. Try this query, it runs without errors:

select
  *,
  'full'
from abc
full join xyz
  on full.c2 = xyz.c2;