Full outer joins

[Paul Sanderson]

I have two tables from two versions of the same database

each table has an integer id primary key

I want to find the rows that dont appear in both tables. So I think I
need a full outer join which I understand is not supported by SQLite

I have seen this which gives an alternative, but it seems very complex

http://en.wikipedia.org/wiki/Join_%28SQL%29#Full_outer_join

Is there an easier way?

and, I am sure this has been asked before, but out of interest why are
full out joins not supported?

[David King]

Post by Paul Sanderson
I want to find the rows that dont appear in both tables. So I think I
need a full outer join which I understand is not supported by SQLite
I have seen this which gives an alternative, but it seems very complex
http://en.wikipedia.org/wiki/Join_%28SQL%29#Full_outer_join
Is there an easier way?

It looks hairy but here's what it's doing. Given tables A,B:

1. Do the regular join (all rows with matches in both A and B)
2. Find rows in A that aren't in B
3. Find rows in B that aren't in A
4. Concatenate those 3 queries together with UNION ALL

Part of the reason the wikipedia one looks complex is that it is worried about types (all of the CAST stuff). But you don't have to worry about types in sqlite, because each cell has a type rather than each column.

Post by Paul Sanderson
and, I am sure this has been asked before, but out of interest why are
full out joins not supported?

I can't speak for *why* it's not supported directly, but you can emulate it with the above type of query

[Keith Medcalf]

Post by David King
1. Do the regular join (all rows with matches in both A and B)
2. Find rows in A that aren't in B
3. Find rows in B that aren't in A
4. Concatenate those 3 queries together with UNION ALL

will be the same as A UNION ALL B, which is much simpler.

I think what the OP wanted was

WITH C AS (select rowid from A INTERSECT select rowid from B)
select rowid from a where rowid not in c
union all
select rowid from b where rowid not in c;

Assuming the rowid's are the same between the tables.

[Keith Medcalf]

Ooops. This can be further simplified as:

select rowid from a where rowid not in b
union all
select rowid from b where rowid not in a;

Post by Keith Medcalf

Post by David King
1. Do the regular join (all rows with matches in both A and B)
2. Find rows in A that aren't in B
3. Find rows in B that aren't in A
4. Concatenate those 3 queries together with UNION ALL

will be the same as A UNION ALL B, which is much simpler.
I think what the OP wanted was
WITH C AS (select rowid from A INTERSECT select rowid from B)
select rowid from a where rowid not in c
union all
select rowid from b where rowid not in c;
Assuming the rowid's are the same between the tables.
_______________________________________________
sqlite-users mailing list
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Richard Hipp]

On Tue, Oct 21, 2014 at 7:12 PM, Paul Sanderson <

Post by Paul Sanderson
out of interest why are
full out joins not supported?

In 14 years, you are the first person to ask for them. That tells me that
probably not many people would use them even if we did put them in.

[dave]

-----Original Message-----
Sent: Tuesday, October 21, 2014 6:59 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Full outer joins
On Tue, Oct 21, 2014 at 7:12 PM, Paul Sanderson <

Post by Paul Sanderson
out of interest why are
full out joins not supported?

In 14 years, you are the first person to ask for them. That
tells me that
probably not many people would use them even if we did put them in.

...

I'll be the second; I needed it last week.

I was able to get by for my particular problem, because the full range of
keys is well-known, so I could make a table of those to drive a series of
outer joins.

The work around referenced in other posts is useful in a pinch, though it
would be messy in my case because I am joining 4 tables.

-dave

[Paul Sanderson]

Really!!

I can accept that it would not be needed as often as other joins but I
can imagine that anyone who wrtes software that populates databases
and who subsequently changes their software or needs to benchmark
against some other datasource would find this useful.


Cheers
Paul

Post by Richard Hipp
On Tue, Oct 21, 2014 at 7:12 PM, Paul Sanderson <

Post by Paul Sanderson
out of interest why are
full out joins not supported?

In 14 years, you are the first person to ask for them. That tells me that
probably not many people would use them even if we did put them in.
--
D. Richard Hipp
_______________________________________________
sqlite-users mailing list
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Stephen Chrzanowski]

Saying I 'would like this type of join' is something I say very lightly.
By that I mean it'd be good to see them, but, really, I'm not going to put
any pressure on anyone to get it implemented.

I'd been using these joins in SQL2K for years before I found out about
SQLite and a lot of what I was doing in 2K couldn't be done in SQLite
because of the outer joins. Fortunately, the apps for 2K are specifically
for 2K and will never be converted to SQLite, but with the lack of
capability, I just learned a new way to get my results, which I think was
basically make a few queries. This was before I REALLY figured out the
unions (Even though it was part of my training back then)

On Wed, Oct 22, 2014 at 3:46 AM, Paul Sanderson <

Post by Paul Sanderson
Really!!
I can accept that it would not be needed as often as other joins but I
can imagine that anyone who wrtes software that populates databases
and who subsequently changes their software or needs to benchmark
against some other datasource would find this useful.
Cheers
Paul

Post by Richard Hipp
On Tue, Oct 21, 2014 at 7:12 PM, Paul Sanderson <

Post by Paul Sanderson
out of interest why are
full out joins not supported?

In 14 years, you are the first person to ask for them. That tells me

that

Post by Richard Hipp
probably not many people would use them even if we did put them in.
--
D. Richard Hipp
_______________________________________________
sqlite-users mailing list
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Steinar Midtskogen]

Post by Richard Hipp
In 14 years, you are the first person to ask for them. That tells me that
probably not many people would use them even if we did put them in.

I've only written one program using sqlite a few years ago, and I had to
make an ugly workaround using UNIONs and LEFT OUTER JOIN. I left a
comment in the code that this should be replaced when sqlite supports
FULL OUTER JOIN because the workaround can become extremely slow.
Perhaps native support wont help speed, but sqlite might be in a better
position to optimise than me. I would certainly try it out if it gets
supported.

I sorely missed that join back then, but I didn't ask for it since it's
on the top of the list at https://www.sqlite.org/omitted.html

At that time the page said "Those features near the top of the list are
likely to be added in the near future". So I was thinking it would be a
bit impolite or impatient to ask...