Re: [sqlite] Emulate right-join

[Jonathan Moules]

In lieu of adding the syntactic sugar, might it be worth documenting the 
alternative(s)? Currently the docs for these are 
"https://sqlite.org/omitted.html; - which simply says: "LEFT OUTER JOIN is 
implemented, but not RIGHT OUTER JOIN or FULL OUTER JOIN."
A couple of lines saying why this isn't really a problem because it can be 
worked around apparently fairly easily could be helpful to us lay-users of SQL.


 On Wed, 06 Dec 2017 18:07:37 + R Smith rsm...@rsweb.co.za 
wrote  


On 2017/12/06 6:35 PM, Christian Schmitz wrote:
 Actually, the left outer join is sufficient to execute all the 
outer
 join operators:
 - right outer join: just swap the "from" arguments

 - full outer joins: union of left and right outer joins
 Couldn’t SQLite implement that and do the swap for us?
 As well as the union thing?

You mean make SQLite less Lite, but with Zero computational advantage, 
by simply adding syntactic sugar bloat? - I'm going to have to vote No 
on that. (Luckily my vote counts extremely little.)

I think the reason SQLite never implemented it is precisely because of 
the fact that it simply amounts to syntactic specialization and no real 
computational advantage. That said, I'm not against adding those joins, 
just perhaps implemented in a most-efficient way rather than a simple 
transcription of my lazy-code. (Unless of course that ends up being the 
most efficient way to do it.)

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users





___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Emulate right-join

[Simon Slavin]

On 6 Dec 2017, at 6:07pm, R Smith  wrote:

> You mean make SQLite less Lite, but with Zero computational advantage, by 
> simply adding syntactic sugar bloat? - I'm going to have to vote No on that. 
> (Luckily my vote counts extremely little.)

I would normally vote with you, but …

> I think the reason SQLite never implemented it is precisely because of the 
> fact that it simply amounts to syntactic specialization and no real 
> computational advantage. 

I did not know how to use LEFT JOIN to do FULL OUTER JOIN.  And I didn’t think 
it was something simple.  Had I ever needed to do FULL OUTER JOIN I’d probably 
have done it in my programming language plus two or more SQLite commands.

Supporting RIGHT JOIN, at least, looks to be very simple given that LEFT JOIN 
already works.  And as SQLite is said to support a lot of SQL92, then 
implementing something so simple might be desirable.

Here’s the SQL92 listing for JOIN syntax:

 ::=
INNER
  |  [ OUTER ]
  | UNION

  ::=
LEFT
  | RIGHT
  | FULL

Which reduces to

INNER JOIN
UNION JOIN
LEFT  [OUTER] JOIN
RIGHT [OUTER] JOIN
FULL  [OUTER] JOIN

Here’s SQLite syntax for join-operator:

[NATURAL] INNERJOIN
[NATURAL] CROSSJOIN
[NATURAL] LEFT [OUTER] JOIN

Should be easy to add RIGHT.  Though a lot more work (especially once you 
consider testing the numerous possibilities) to add FULL.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Emulate right-join

[R Smith]

On 2017/12/06 6:35 PM, Christian Schmitz wrote:

Actually, the left outer join is sufficient to execute all the outer

join operators:

- right outer join: just swap the "from" arguments

- full outer joins: union of left and right outer joins

Couldn’t SQLite implement that and do the swap for us?
As well as the union thing?


You mean make SQLite less Lite, but with Zero computational advantage, 
by simply adding syntactic sugar bloat? - I'm going to have to vote No 
on that. (Luckily my vote counts extremely little.)


I think the reason SQLite never implemented it is precisely because of 
the fact that it simply amounts to syntactic specialization and no real 
computational advantage. That said, I'm not against adding those joins, 
just perhaps implemented in a most-efficient way rather than a simple 
transcription of my lazy-code. (Unless of course that ends up being the 
most efficient way to do it.)


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Emulate right-join

[Christian Schmitz]

>> 
>>> Actually, the left outer join is sufficient to execute all the outer
>> join operators:
>>> 
>>> - right outer join: just swap the "from" arguments
>>> 
>>> - full outer joins: union of left and right outer joins
>> 

Couldn’t SQLite implement that and do the swap for us?
As well as the union thing?

Sincerely
Christian

-- 
Read our blog about news on our plugins:

http://www.mbsplugins.de/


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Emulate right-join

[Stephen Chrzanowski]

Thanks all for the input.  My knowledge from nearly 20 years ago is a bit
rusty when it came to material like this.  Just adding "LEFT JOIN" instead
of just "JOIN" has my query working as I need it to.  This type of result
set isn't something I regularly perform.


On Wed, Dec 6, 2017 at 4:41 AM, Simon Slavin  wrote:

> On 6 Dec 2017, at 9:36am, Jean-Luc Hainaut 
> wrote:
>
> > Actually, the left outer join is sufficient to execute all the outer
> join operators:
> >
> > - right outer join: just swap the "from" arguments
> >
> > - full outer joins: union of left and right outer joins
>
> I never realised that.  It’s clever.  And it’s also a cheap way to
> implement FULL JOINs.
>
> Not optimized, but it simple, and changes "can’t do" to "can do".  Thanks.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Emulate right-join

[Simon Slavin]

On 6 Dec 2017, at 9:36am, Jean-Luc Hainaut  wrote:

> Actually, the left outer join is sufficient to execute all the outer join 
> operators:
> 
> - right outer join: just swap the "from" arguments
> 
> - full outer joins: union of left and right outer joins

I never realised that.  It’s clever.  And it’s also a cheap way to implement 
FULL JOINs.

Not optimized, but it simple, and changes "can’t do" to "can do".  Thanks.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Emulate right-join

[Jean-Luc Hainaut]

Actually, the left outer join is sufficient to execute all the outer 
join operators:


- right outer join: just swap the "from" arguments

- full outer joins: union of left and right outer joins

Examples (classical "supplier-part-supply" example):

create table S(SN,NAME);
create table P(PN,COLOR);
create table SP(SN,PN,Q);
insert into S values ('S1','SMITH'),('S2','JONES'),('S3','BLAKE');
insert into P values ('P1','red'),('P2','green'),('P3','blue');
insert into SP values 
('S1','P1',30),('S2','P1',30),('S2','P2',40),('S3','P4',10);


- Left outer join S --> SP --> P:
  --

select S.SN,S.NAME,SP.PN,P.COLOR,SP.Q
from S left join SP using (SN)
left join P  using (PN);

++---++---++
| SN | NAME  | PN | COLOR | Q  |
++---++---++
| S1 | SMITH | P1 | red   | 30 |
| S2 | JONES | P1 | red   | 30 |
| S2 | JONES | P2 | green | 40 |
| S3 | BLAKE | P4 | --| 10 |
++---++---++

- Right outer join P --> SP --> S:
  --

select S.SN,S.NAME,P.PN,P.COLOR,SP.Q
from P left join SP using (PN)
   left join S  using (SN);

++---++---++
| SN | NAME  | PN | COLOR | Q  |
++---++---++
| S1 | SMITH | P1 | red   | 30 |
| S2 | JONES | P1 | red   | 30 |
| S2 | JONES | P2 | green | 40 |
| -- | --| P3 | blue  | -- |
++---++---++

- Full outer join P <--> SP <--> S:
  ---

select S.SN,S.NAME,SP.PN,P.COLOR,SP.Q
from S left join SP using (SN)
   left join P  using (PN)
  union
select S.SN,S.NAME,P.PN,P.COLOR,SP.Q
from P left join SP using (PN)
   left join S  using (SN);

- Full outer join with a "union all" (may be faster but may include 
duplicates):

  -

select S.SN,S.NAME,SP.PN,P.COLOR,SP.Q
from S left join SP using (SN)
   left join P  using (PN)
  union all
select S.SN,S.NAME,P.PN,P.COLOR,SP.Q
from P left join SP using (PN)
   left join S  using (SN)
where Q is null;

++---++---++
| SN | NAME  | PN | COLOR | Q  |
++---++---++
| -- | --| P3 | blue  | -- |
| S1 | SMITH | P1 | red   | 30 |
| S2 | JONES | P1 | red   | 30 |
| S2 | JONES | P2 | green | 40 |
| S3 | BLAKE | P4 | --| 10 |
++---++---++

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Emulate right-join

[Keith Medcalf]

You want a LEFT JOIN not a RIGHT JOIN (these are of course just lazy spellings 
for LEFT OUTER JOIN and RIGHT OUTER JOIN respectively), assuming that you want 
all selected rows from the table of the LEFT and only the matching values (else 
NULL) for the table(s) on the right, which is how you describe what you want to 
achieve.  Simply change the word RIGHT to LEFT.

On the other hand, X RIGHT JOIN Y can be spelled as Y LEFT JOIN X  (which may 
be spelled with the word OUTER preceding the word JOIN) if for some reason you 
really do want a right outer join (which of course means to include all rows 
from the table on the RIGHT and either NULL or the matching values from the 
table(s) on the LEFT.

To simulate a OUTER JOIN (which is an alternate spelling of FULL OUTER JOIN) 
which includes all the rows from both sides with non-matching lookups set to 
null is a wee bit (but not a lot) more complicated when all you can do is a 
LEFT JOIN.

   select EventID,
  ContactInfo,
  TicketID,
  Priority,
  PriorityText,
  CreateDate
 from Events 
left join ColorScheme 
   on ColorScheme.PriorityLevel = Events.EventID 
 order by {SomeSoftwareDefinedOrder}

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Stephen Chrzanowski
>Sent: Tuesday, 5 December, 2017 14:45
>To: General Discussion of SQLite Database
>Subject: [sqlite] Emulate right-join
>
>I'm working on a pretty simplified event tracking system (So I stay
>out of
>trouble of not updating time spent throughout the day), and each item
>that
>I need to track has an associated priority with it, which ranges for
>any
>positive integer value.  This priority can be user defined as a
>positive
>integer. (FWIW, zero is bottom of the pack, while 1 is top priority -
>- Zero
>priority items are displayed at the bottom of the list, and I
>consider them
>"Undefined")
>
>Priorities 0 to 25 can have a color and title associated with it, and
>this
>information is stored in its own table.  Not all of the 26 priorities
>have
>to be defined.  The PriorityLevel is related to Priority (See below)
>but
>has no direct requirement to exist.
>
>So the question I have is how I can build the query to emulate a
>right-join?
>
>The problem encountered is that if I have an event with a priority
>99, and
>with the priority text not being in the table, my query does not
>return
>items with a priority 99.
>
>The two tables goes something along the lines of:
>
>Events
> - EventID
> - ContactInfo
> - TicketID
> - Resolved
> - Priority (Integer)
> - CreateDate
>
>ColorScheme
> - PriortyLevel (Integer)
> - FGColor
> - BGColor
> - PriorityText
>
>Notes:
>There is no direct PK/FK relation between Priority and PriorityLevel.
>The Priority is defaulted to zero when items are created.  No trigger
>exists to enforce a positive value, but my code protects that data
>info.
>If the the Priority of an Event item is defined with a value that
>does not
>exist in ColorScheme, color wise, it defaults to whatever
>PriorityLevel 0
>is defined as.
>The existing ColorScheme table is loaded into memory to toy with on a
>new
>UI form, then on submit, the real table is dumped and repopulated.
>If the user changes the Priority in the Events table, and there is no
>PriorityLevel in ColorScheme that matches, the color scheme is
>defaulted/assumed to be 0 in the application, only for coloring
>purposes,
>but, retains the priority level.
>On the submission of the color schemes, priority zero is force-
>created,
>meaning that if the user deletes priority 0, the software will add a
>default values to the ColorScheme table.
>
>The query I'm attempting to run is something along the lines of:
>(Untested)
>Select EventID,ContactInfo,TicketID,Priority,CreateDate from Events
>order
>by {SomeSoftwareDefinedOrder}
>
>What I want to do is essentially:
>select EventID,ContactInfo,TicketID,Priority,PriorityText,CreateDate
>from
>Events *RIGHT JOIN* ColorScheme on ColorScheme.PriorityLevel =
>Events.EventID order by {SomeSoftwareDefinedOrder}
>
>What this did in MSSQL2000 days, if I remember correctly, give me all
>results in the Events table even if the relevant info isn't in
>ColorScheme
>table.  The PriorityLevel and PriorityText would be returned as NULL.
>
>Does anyone have any working theories on how I can get ALL results in
>the
>Events table regardless if the Events.Priority isn't in
>ColorScheme.PriorityLevel?
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Emulate right-join

[John McKown]

On Tue, Dec 5, 2017 at 3:44 PM, Stephen Chrzanowski 
wrote:

> I'm working on a pretty simplified event tracking system (So I stay out of
> trouble of not updating time spent throughout the day), and each item that
> I need to track has an associated priority with it, which ranges for any
> positive integer value.  This priority can be user defined as a positive
> integer. (FWIW, zero is bottom of the pack, while 1 is top priority -- Zero
> priority items are displayed at the bottom of the list, and I consider them
> "Undefined")
>
> Priorities 0 to 25 can have a color and title associated with it, and this
> information is stored in its own table.  Not all of the 26 priorities have
> to be defined.  The PriorityLevel is related to Priority (See below) but
> has no direct requirement to exist.
>
> So the question I have is how I can build the query to emulate a
> right-join?
>

​Why not just use a RIGHT JOIN? PostgreSQL supports it. On page
https://www.postgresql.org/docs/current/static/sql-select.html



*join_type*

One of

   -

   [ INNER ] JOIN
   -

   LEFT [ OUTER ] JOIN
   -

   RIGHT [ OUTER ] JOIN
   -

   FULL [ OUTER ] JOIN
   -

   CROSS JOIN

For the INNER and OUTER join types, a join condition must be specified,
namely exactly one of NATURAL, ON *join_condition*, or USING (*join_column* [,
...]). See below for the meaning. For CROSS JOIN, none of these clauses can
appear.

A JOIN clause combines two FROM items, which for convenience we will refer
to as “tables”, though in reality they can be any type of FROM item. Use
parentheses if necessary to determine the order of nesting. In the absence
of parentheses, JOINs nest left-to-right. In any case JOIN binds more
tightly than the commas separating FROM-list items.

CROSS JOIN and INNER JOIN produce a simple Cartesian product, the same
result as you get from listing the two tables at the top level of FROM, but
restricted by the join condition (if any). CROSS JOIN is equivalent to INNER
JOIN ON (TRUE), that is, no rows are removed by qualification. These join
types are just a notational convenience, since they do nothing you couldn't
do with plain FROM and WHERE.

LEFT OUTER JOIN returns all rows in the qualified Cartesian product (i.e.,
all combined rows that pass its join condition), plus one copy of each row
in the left-hand table for which there was no right-hand row that passed
the join condition. This left-hand row is extended to the full width of the
joined table by inserting null values for the right-hand columns. Note that
only the JOIN clause's own condition is considered while deciding which
rows have matches. Outer conditions are applied afterwards.

Conversely, RIGHT OUTER JOIN returns all the joined rows, plus one row for
each unmatched right-hand row (extended with nulls on the left). This is
just a notational convenience, since you could convert it to a LEFT OUTER
JOIN by switching the left and right tables.

FULL OUTER JOIN returns all the joined rows, plus one row for each
unmatched left-hand row (extended with nulls on the right), plus one row
for each unmatched right-hand row (extended with nulls on the left).
ON *join_condition*

​



>
> The problem encountered is that if I have an event with a priority 99, and
> with the priority text not being in the table, my query does not return
> items with a priority 99.
>
> The two tables goes something along the lines of:
>
> Events
>  - EventID
>  - ContactInfo
>  - TicketID
>  - Resolved
>  - Priority (Integer)
>  - CreateDate
>
> ColorScheme
>  - PriortyLevel (Integer)
>  - FGColor
>  - BGColor
>  - PriorityText
>
> Notes:
> There is no direct PK/FK relation between Priority and PriorityLevel.
> The Priority is defaulted to zero when items are created.  No trigger
> exists to enforce a positive value, but my code protects that data info.
> If the the Priority of an Event item is defined with a value that does not
> exist in ColorScheme, color wise, it defaults to whatever PriorityLevel 0
> is defined as.
> The existing ColorScheme table is loaded into memory to toy with on a new
> UI form, then on submit, the real table is dumped and repopulated.
> If the user changes the Priority in the Events table, and there is no
> PriorityLevel in ColorScheme that matches, the color scheme is
> defaulted/assumed to be 0 in the application, only for coloring purposes,
> but, retains the priority level.
> On the submission of the color schemes, priority zero is force-created,
> meaning that if the user deletes priority 0, the software will add a
> default values to the ColorScheme table.
>
> The query I'm attempting to run is something along the lines of: (Untested)
> Select EventID,ContactInfo,TicketID,Priority,CreateDate from Events order
> by {SomeSoftwareDefinedOrder}
>
> What I want to do is essentially:
> select EventID,ContactInfo,TicketID,Priority,PriorityText,CreateDate from
> Events *RIGHT JOIN* ColorScheme on ColorScheme.PriorityLevel =
> Events.EventID 

[sqlite] Emulate right-join

[Stephen Chrzanowski]

I'm working on a pretty simplified event tracking system (So I stay out of
trouble of not updating time spent throughout the day), and each item that
I need to track has an associated priority with it, which ranges for any
positive integer value.  This priority can be user defined as a positive
integer. (FWIW, zero is bottom of the pack, while 1 is top priority -- Zero
priority items are displayed at the bottom of the list, and I consider them
"Undefined")

Priorities 0 to 25 can have a color and title associated with it, and this
information is stored in its own table.  Not all of the 26 priorities have
to be defined.  The PriorityLevel is related to Priority (See below) but
has no direct requirement to exist.

So the question I have is how I can build the query to emulate a right-join?

The problem encountered is that if I have an event with a priority 99, and
with the priority text not being in the table, my query does not return
items with a priority 99.

The two tables goes something along the lines of:

Events
 - EventID
 - ContactInfo
 - TicketID
 - Resolved
 - Priority (Integer)
 - CreateDate

ColorScheme
 - PriortyLevel (Integer)
 - FGColor
 - BGColor
 - PriorityText

Notes:
There is no direct PK/FK relation between Priority and PriorityLevel.
The Priority is defaulted to zero when items are created.  No trigger
exists to enforce a positive value, but my code protects that data info.
If the the Priority of an Event item is defined with a value that does not
exist in ColorScheme, color wise, it defaults to whatever PriorityLevel 0
is defined as.
The existing ColorScheme table is loaded into memory to toy with on a new
UI form, then on submit, the real table is dumped and repopulated.
If the user changes the Priority in the Events table, and there is no
PriorityLevel in ColorScheme that matches, the color scheme is
defaulted/assumed to be 0 in the application, only for coloring purposes,
but, retains the priority level.
On the submission of the color schemes, priority zero is force-created,
meaning that if the user deletes priority 0, the software will add a
default values to the ColorScheme table.

The query I'm attempting to run is something along the lines of: (Untested)
Select EventID,ContactInfo,TicketID,Priority,CreateDate from Events order
by {SomeSoftwareDefinedOrder}

What I want to do is essentially:
select EventID,ContactInfo,TicketID,Priority,PriorityText,CreateDate from
Events *RIGHT JOIN* ColorScheme on ColorScheme.PriorityLevel =
Events.EventID order by {SomeSoftwareDefinedOrder}

What this did in MSSQL2000 days, if I remember correctly, give me all
results in the Events table even if the relevant info isn't in ColorScheme
table.  The PriorityLevel and PriorityText would be returned as NULL.

Does anyone have any working theories on how I can get ALL results in the
Events table regardless if the Events.Priority isn't in
ColorScheme.PriorityLevel?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users