The difference between these two is what happens when a row
of A has no matches in B.
select * from A left join B on A.Time = B.Time
select * from A left join B where A.Time = B.Time
In the first one the condition is carried out during the join
so if a row of A has no matches in B then the B part
Ah! I see. Thanks for that walk through of OUTER JOIN.
Ben
> On Oct 27, 2019, at 4:18 PM, Keith Medcalf wrote:
>
>
> On Sunday, 27 October, 2019 16:09, Benjamin Asher
> wrote:
>
>> Is there an advantage to putting WHERE-type filtering in the
>> ON constraint vs leaving it in the WHERE clau
On Sunday, 27 October, 2019 16:09, Benjamin Asher wrote:
> Is there an advantage to putting WHERE-type filtering in the
> ON constraint vs leaving it in the WHERE clause for LEFT JOINs?
The ON clause is merely syntactic sugar for a parenthesized AND clause appended
to the parenthesized WHERE
This is exactly the kind of advice I was looking for.
Thanks again!
Ben
> On Oct 27, 2019, at 4:04 PM, Simon Slavin wrote:
>
> On 27 Oct 2019, at 10:45pm, Benjamin Asher wrote:
>
>> Query A
>>
>> SELECT * FROM tab1 LEFT JOIN tab2 ON tab2.x='constant' WHERE
>> tab1.x='constant’;
>>
>> Que
On 27 Oct 2019, at 10:45pm, Benjamin Asher wrote:
> Query A
>
> SELECT * FROM tab1 LEFT JOIN tab2 ON tab2.x='constant' WHERE
> tab1.x='constant’;
>
> Query B
>
> SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x=tab2.x WHERE tab1.x=‘constant'
Your use of JOIN here is extremely usual. Depending on
Okay playing with this some more: it seems like since everything is equal to
the same constant, it doesn’t really matter? That said, it feels like poor form
/ not good practice (join can become not useful if you don’t have a constraint
joining particular columns). If I want to make it easier on
Oh you’re right. I realize now I messed up the example. Here are the updated
queries:
Query A
SELECT * FROM tab1 LEFT JOIN tab2 ON tab2.x='constant' WHERE tab1.x='constant’;
Query B
SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x=tab2.x WHERE tab1.x=‘constant
These return the same results in your
On 27 Oct 2019, at 10:09pm, Benjamin Asher wrote:
> SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x=‘constant’;
You're doing a JOIN here. How should the engine know which row of tab2
corresponds to which row of tab1 ?
Your query is syntactically correct, but it doesn't appear to do anything
usef
On 10/27/19, Benjamin Asher wrote:
> It seems both of the following work,
> but I’m not really sure why:
I get different answers for the two queries when I try them:
CREATE TABLE tab1(x,y);
CREATE TABLE tab2(x,y);
INSERT INTO tab1 VALUES('constant','string'),('abc','def'),(97,15);
INSERT INTO ta
Hi there! My colleagues and I are trying to understand the role of ON
constraints vs WHERE clauses in JOINs. It seems both of the following work, but
I’m not really sure why:
Query A:
SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x=‘constant’;
Query B:
SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x
Number one on my wishlist.
UNSIGNED
Wanted for ordering more than anything else
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
System.Data.SQLite version 1.0.112.0 (with SQLite 3.30.1) is now available
on the System.Data.SQLite website:
https://system.data.sqlite.org/
Further information about this release can be seen at:
https://system.data.sqlite.org/index.html/doc/trunk/www/news.wiki
Please post on the SQ
> SQLite has had geospatial support for years via the r-tree extension, and
> more recently GeoJSON.
But not compatible to standards from Open Geospatial Consortium, as far as I
know. Which requires additional conversions, and considering that geodata
usually have sizes of a GB or more, this is
On Sunday, 27 October, 2019 13:30, Richard Damon
wrote:
>On 10/27/19 10:24 AM, Simon Slavin wrote:
>> On 27 Oct 2019, at 9:12am, Thomas Kurz wrote:
>>> the discussion standard deviation has shown that at least STDEV and
>>> POWER would be very helpful if they part of SQLite core.
>> These a
On 27 Oct 2019, at 7:43pm, Richard Damon wrote:
> My one thought about fuller ALTER TABLE support would be that since
> SQLite stores the schema as the simple CREATE TABLE command, that ALTER
> TABLE needs to know how to edit that string to match the changes, where
> simple adds are much easier t
On 10/26/19 8:07 PM, Thomas Kurz wrote:
>> Feel free to make suggestions. Which missing feature or features causes
> you the most bother?
>
> Thanks, Dan.
>
> To me, the most puzzling thing is the lack of full ALTER TABLE support (DROP
> COLUMN, MODIFY COLUMN, ADD CONSTRAINT, DROP CONSTRAINT). Mo
On 10/27/19 10:24 AM, Simon Slavin wrote:
> On 27 Oct 2019, at 9:12am, Thomas Kurz wrote:
>
>> the discussion standard deviation has shown that at least STDEV and POWER
>> would be very helpful if they part of SQLite core.
> These are presentation issues. Not database issues. The results of su
Thanks Keith. I thought it was a documented function and couldn’t find anything
in the documentation.
From: sqlite-users on behalf of
Keith Medcalf
Sent: Sunday, October 27, 2019 4:46:17 PM
To: SQLite mailing list
Subject: Re: [sqlite] Stream loading SQL
Dne 27. 10. 19 v 15:24 Simon Slavin napsal(a):
> And I agree with you on RIGHT JOIN: it may duplicate what can be done with
> LEFT JOIN but many SQL facilities are duplicates. It's in SQL92 and people
> expect to see it.
+1 This would increase portability of SQL scripts – it would be easier
to
Dne 27. 10. 19 v 10:12 Thomas Kurz napsal(a):
> at least some basic math would be very helpful as well. I don't want to
> suggest a complete math support, that would really be far away from liteness
Generally speaking, it might be difficult to say where is the line
between „light“ and „heavy“. Bu
On Sunday, 27 October, 2019 07:40, x wrote:
>Where is this function Keith? I can find any information on it?
The SQLite3 command line shell (shell.c) reads input from stdin or other file
and processes the commands one at a time by either calling the appropriate
sqlite3 functions and displayin
Hi Simon,
Especially since it can't do something more fundamental than STDEV:
return all surnames starting with the Unicode character 'Ã
'.
Reconsider as this works fine.
JcD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http
On 27 Oct 2019, at 9:12am, Thomas Kurz wrote:
> the discussion standard deviation has shown that at least STDEV and POWER
> would be very helpful if they part of SQLite core.
These are presentation issues. Not database issues. The results of such
calculations are unlikely to be used to deci
Where is this function Keith? I can find any information on it?
From: sqlite-users on behalf of
František Kučera
Sent: Saturday, October 26, 2019 4:49:26 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Stream loading SQL script
Dne 25.
> What do you mean by "SQL-basics"?
I forgot to mention that at least some basic math would be very helpful as
well. I don't want to suggest a complete math support, that would really be far
away from liteness, but the discussion standard deviation has shown that at
least STDEV and POWER would
> Omitting RIGHT JOIN is good, that's a misfeature and LEFT JOIN does
> everything
useful it does.
With all dear respect, but I don't think that it is up to you to define what a
"feature" and a "misfeature" is. iirc, RIGHT JOIN is declared in SQL92, it is
part of the SQL standard, and therefor
26 matches
Mail list logo