FULL JOIN query results in Error Code: 1054. Any workaround?

Question

I'm trying to join two tables to show all rows that match the where clause, not just the ones that match the join. It's two tables of bills and I am trying to merge them so that each row with matching year and month from both tables has a few fields from each.

SELECT
    tblhydrobill.billyear, tblhydrobill.billmonth, tblhydrobill.KWH as elecconsumption, tblhydrobill.CurrentCharges as eleccost,
    tblgasdata.cubicft as gasconsumption, tblgasdata.total as gascost
FROM tblhydrobill
FULL JOIN tblgasdata ON tblhydrobill.billyear = tblgasdata.billyear and tblhydrobill.billmonth = tblgasdata.billmonth
WHERE tblhydrobill.meteridnumber = 19 and tblgasdata.buildingid = 19
ORDER BY tblhydrobill.billyear asc, tblhydrobill.billmonth asc

LEFT JOIN executes properly, but only shows results where data exists for month/year on both tables. I am trying to get it to return all rows, it's ok if there are null fields on one side of the join if there is no match on the other side and vice-versa.

Using FULL JOIN I get the following MySQL error:

Error Code: 1054. Unknown column 'tblhydrobill.billyear' in 'field list'

Update:

FULL JOIN is not supported yet by MySQL as pointed out by @jarlh, thanks.

Also, your WHERE clause conditions make the FULL JOIN return regular INNER JOIN result. Move those conditions to the ON clause to get true FULL JOIN result. (On a dbms supporting it.) — jarlh, Jan 19 '22 at 06:31
As MySQL does't recognize `FULL JOIN` , 'FULL' is most probably interpreted as an alias of `tblhydrobill`. So `tblhydrobill.billyear` is not valid col reference anymore. — Serg, Jan 19 '22 at 07:09
@jarlh I saw FULL referenced here: https://mysqlcode.com/mysql-full-join/ but looking at mysql.com v8 ref. guide, no mention of it! The error referred to the column as a field, perhaps field is what they call a column when written in a select statement. — Steve Seeger, Jan 19 '22 at 07:24
Does this answer your question? [1054 Unknown Column in Full Join, but not other Joins](https://stackoverflow.com/questions/26614500/1054-unknown-column-in-full-join-but-not-other-joins) — philipxy, Jan 20 '22 at 08:21

LukStorms · Answer 1 · 2022-01-19T10:48:51.433

2

Since MySql doesn't support FULL JOIN, it's using FULL as the alias for tblhydrobill.

But a FULL JOIN can be emulated.

SELECT h.billyear, h.billmonth
, h.KWH as elecconsumption
, h.CurrentCharges as eleccost
, g.cubicft as gasconsumption
, g.total as gascost
FROM tblhydrobill h
LEFT JOIN tblgasdata g 
  ON g.billyear = h.billyear 
 AND g.billmonth = h.billmonth
 AND g.buildingid = 19
WHERE h.meteridnumber = 19

UNION ALL

SELECT g.billyear, g.billmonth
, h.KWH as elecconsumption
, h.CurrentCharges as eleccost
, g.cubicft as gasconsumption
, g.total as gascost
FROM tblgasdata g 
LEFT JOIN tblhydrobill h
  ON h.billyear = g.billyear 
 AND h.billmonth = g.billmonth
 AND h.meteridnumber = 19
WHERE g.buildingid = 19
  AND h.billyear IS NULL

ORDER BY billyear, billmonth

edited Jan 19 '22 at 10:48

answered Jan 19 '22 at 08:16

LukStorms

28,916
5
31
45

UNION ALL will collapse non-matching doubles in left/right table as well. Use this method with caution, it can return results a bit different from FULL JOIN. – Serg Jan 19 '22 at 09:10
`UNION ALL` doesn't make duplicate rows unique though. And the 2nd suery is merely adding what's missing in the first. I still prefere it over using the normal `UNION` for this. – LukStorms Jan 19 '22 at 09:40
Consider [the example](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8e1b631d5c620cd3638731f3ca23ccb1). Note both UNION and UNION ALL are different from FULL JOIN – Serg Jan 19 '22 at 10:05
@Serg See [this version](https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=3bafb3c4d3b101a806c15ebae0da530c) of your fiddle. Notice the `where t1.id is null`. – LukStorms Jan 19 '22 at 10:46
Good. It works as expected. – Serg Jan 19 '22 at 11:49
Btw, a postgresql version with a working full join to compare : *db<>fiddle [here](https://dbfiddle.uk/?rdbms=postgres_10&fiddle=edaa5a99aa1a03705c1fcb4bff3b7c02)* – LukStorms Jan 19 '22 at 11:52

Salman A · Accepted Answer · 2022-01-19T10:28:58.707

You can generate a list of year-month pairs that are present in one or both tables using union, then left join the two tables with that result:

select *
from (
    select billyear, billmonth from tblhydrobill where meteridnumber = 19
    union
    select billyear, billmonth from tblgasdata   where buildingid = 19
) as ym
left join tblhydrobill on tblhydrobill.billyear = ym.billyear and tblhydrobill.billmonth = ym.billmonth and tblhydrobill.meteridnumber = 19
left join tblgasdata   on tblgasdata.billyear   = ym.billyear and tblgasdata.billmonth   = ym.billmonth and tblgasdata.buildingid = 19
order by ym.billyear, ym.billmonth

Note that it is possible to build ym list manually e.g.:

from (
    select 2022,  1 union
    select 2021, 12 union
    select 2021, 11
) as ym

FULL JOIN query results in Error Code: 1054. Any workaround?

2 Answers2