full outer join?

[Chris Withers]

This feels like a newbie question, but how would I do a full outer join
in SQLAlchemy?

Chris

--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk

[Michael Bayer]

I have a similar question - why ?

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.

[Chris Withers]

I have a self join (same one I've been asking about recently) and turns
out that there should be a one to one mapping for the aggregated rows on
one side to the aggregated rows on the other side.

If that's not true, then the returned rows are rubbish. In all my
current use cases it *should* be true but I'm looking for a join that
will show me when something has changed by having nulls in either the
left or right side of the join.

If you can suggest a way that doesn't involve a full outer join, I'm all
ears :-)

[Michael Bayer]

well the alternative to full outer join is a union of two outer joins - more tedious. “full outer join” isn’t in the library but you can just subclass Join and do a @compiles to get that exact syntax.

[Jonathan Vanasco]

On Monday, December 2, 2013 2:46:20 PM UTC-5, Michael Bayer wrote:

well the alternative to full outer join is a union of two outer joins - more tedious.    “full outer join” isn’t in the library but you can just subclass Join and do a  @compiles to get that exact syntax.

you could also query the union of two outer joins, by treating it as a nested subquery.  that is not only tedious, but painful and annoying.   you could do it though!

[Michael Bayer]

if someone wants to pullreq the kw argument “full=True” on .outerjoin(), if it’s done effectively I won’t reject it.

[Stefan Urbanek]

I've added a PR for full=True in outerjoin(), join() and Join():

    https://github.com/zzzeek/sqlalchemy/pull/167

FULL OUTER JOIN, besides other uses, is very useful in ETLs for doing transformations on top of table differences. Using two outer joins, as suggested in one of the above posts, unnecessarily overcomplicates statements.

Stefan