MySQL full join issue

[Truth1984]

MySQL doesn’t support full join, maybe as a query builder, it should automatically transform into multiple subjoins ?

[elhigu]

Please be more specific. What kind of SQL doesn't work with mysql and what would be equal mysql compatible query? Is there any pitfalls like if there are multiple full joins?

If it can be done in a compatible manner so that returned data matches the data returned for example by postgres full join I don't see any reason why it couldn't be implemented.

[Truth1984]

.fullOuterJoin() produce full outer join query with dialect set to mysql
however there is no such thing as full outer join in mysql, and it throws syntax error.
The workaround is to use multiple subjoins to replace direct query: full outer join.
If you only notify users of how full outer join is only avaliable in certain sql languages, then it might defeat the purpose of using knex.

[elhigu]

The workaround is to use multiple subjoins to replace direct query: full outer join.

Please give an example exactly how will that replacing query be?

If you only notify users of how full outer join is only avaliable in certain sql languages, then it might defeat the purpose of using knex.

There are many places in knex where this is the case. Throwing an error is just fine in that case, but ofcourse if there is a single query which returns the same results for mysql and it is possible to implement, then that would be a better solution. For example sqlite3 multi-insert is transformed query.

[Truth1984]

i see

full outer join example

basically something like knex.leftJoin().union(builder=>builder.rightJoin())

[elhigu]

If there is good integration test that it actually works the same way also with more complex full joins etc. I would happily accept pull request implementing this 👍