On 2017/03/22 9:53 AM, Eric Grange wrote:
For the sake of curiosity, is anyone (as in any human) using RIGHT JOIN?
Personally I never had a need for a RIGHT JOIN, not because of theoretical
or design considerations, but it just never came into my flow of thought
when writing SQL...
I guess some automated SQL query generators could use it though, because
they do not have a "flow of thought".
I had a need of it the other day (something that can easily be overcome
with a CTE or subquery, but still may have made the sql read more natural):
We have one system which contains (among other things) clients,
suppliers and stock tables.
A stock item will mostly have a supplier and mostly will have an
intended client (the production is bespoke) except for a few common
materials.
So we needed a list of all suppliers linked to all clients affected by
them (via Stock), and also see the clients who are not affected by any
suppliers (which should really be a minimum number) and the suppliers
who are not affecting any clients (which should also be a small list).
Note that not all suppliers may be linked to live stock items, and not
all clients may be linked to live stock items either - but they all need
to show up in the list.
If I can try my hand at a schematic of the sets showing links:
clients: [ A B C D E ]
- links | | |
stock: [ 1 2 3 4 5 ]
- links | | |
suppliers: [ @ # $ % & ! ]
I would need to see a "linking" query result showing overlap and
non-links like this:
. clnt | supp| stck
. ---- | ---- | ----
. A | NULL | NULL
. B | NULL| 1
. C | NULL| NULL
. D | $ | 2
. E | & | 4
. NULL | @ | NULL
. NULL | # | NULL
. NULL | % | 3
. NULL | ! | NULL
Note that I don't actually need the stck column, just adding it to show
more sensible results.
Also note that items linked to neither client nor supplier need not show
up (stock item 5 in this case), so I only need results having either a
client or a supplier.
The obvious solution was:
SELECT clients.name, suppliers.name
FROM clients
LEFT JOIN stock ON stock.client = clients.id
RIGHT JOIN suppliers ON stock.supplier = suppliers.id
WHERE (clients.name IS NOT NULL) OR (suppliers.name IS NOT NULL)
;
I suppose this could have worked too:
SELECT clients.name, suppliers.name
FROM suppliers
LEFT JOIN stock ON stock.supplier = suppliers.id
RIGHT JOIN clients ON stock.client = clients.id
WHERE (clients.name IS NOT NULL) OR (suppliers.name IS NOT NULL)
;
or even this:
SELECT clients.name, suppliers.name
FROM stock
RIGHT JOIN suppliers ON stock.supplier = suppliers.id
RIGHT JOIN clients ON stock.client = clients.id
WHERE (clients.name IS NOT NULL) OR (suppliers.name IS NOT NULL)
;
I don't see how to achieve that with simple joins in sqlite3. We used
some CTEs to LEFT JOIN stock to suppliers, and then left-joined that CTE
on the stock to the clients - which would also work using sub-query
joins or filtered cross-joins (much slower).
So to add to the discussion: Yeah, we've needed it, but overcoming the
problem was not exactly difficult and the use case itself is certainly
not very common (or even mildly common, it's rather uncommon).
Cheers,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
