Re: [sqlite] Table name syntax

[James K. Lowden]

On Mon, 21 Nov 2016 20:46:45 +
David Raymond  wrote:

> insert into main.foo
> select db1.foo.*
> from db1.foo left outer join db2.bar
> on db1.foo.pk = db2.bar.pk
> where db2.bar.pk is null;

Just by the way, your query could be cast as 

insert into main.foo
select * 
from db1.foo where not exists (
select 1 from db2.bar
on db1.foo.pk = pk 
);

Although DRH's points regarding "select *" are well founded, there are
good uses for it, and yours is one.  In this case, the tables "main"
and "foo" are supposed to have the same columns.  "select *" propagates
that symmetry if foo is altered.  Whether or not that's actually
desirable depends on how "main" is defined, on its meaning in the mind
fo the designer.  

IMO the criticism of "select *" is sometimes overwrought.  An
application that uses columns by name -- as it should -- will not fail
in the face of extra columns.  The efficiency gain could be small or
large, depending.  It's a good guideline, but doesn't deserve
veneration in all circumstances.  

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

Re: [sqlite] Table name syntax

[David Raymond]

Makes sense, thanks. For one-offs and things like the "Copying from one table 
to another" thread that aren't going to be part of a regular running program 
it's easy enough to use the alias version.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Monday, November 21, 2016 4:33 PM
To: SQLite mailing list
Subject: Re: [sqlite] Table name syntax

On 11/21/16, Don V Nielsen <donvniel...@gmail.com> wrote:
>> And since the "*" forms are considered bad style
>
> I have done this, not knowing it is bad style. Can you provide some reasons
> why it is bad?

Years later when somebody does "ALTER TABLE ... ADD COLUMN" your
application will begin doing unnecessary work to extract columns that
or not used (best case) or fail completely when it gets back a
different number of columns from what it expected (worst case).

Or, somebody my use the techniques outlined in
https://www.sqlite.org/lang_altertable.html#otheralter to change the
order of the columns in the table, which would definitely break your
application.

-- 
D. Richard Hipp
d...@sqlite.org
___
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] Table name syntax

[Richard Hipp]

On 11/21/16, Don V Nielsen  wrote:
>> And since the "*" forms are considered bad style
>
> I have done this, not knowing it is bad style. Can you provide some reasons
> why it is bad?

Years later when somebody does "ALTER TABLE ... ADD COLUMN" your
application will begin doing unnecessary work to extract columns that
or not used (best case) or fail completely when it gets back a
different number of columns from what it expected (worst case).

Or, somebody my use the techniques outlined in
https://www.sqlite.org/lang_altertable.html#otheralter to change the
order of the columns in the table, which would definitely break your
application.

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

Re: [sqlite] Table name syntax

[Don V Nielsen]

> And since the "*" forms are considered bad style

I have done this, not knowing it is bad style. Can you provide some reasons
why it is bad? I can assume, "Applications are supposed to be controlled
environments, and using tbl.* introduces uncertainty outside the
applications control." But are there more specific reasons?

Thanks,dvn


On Mon, Nov 21, 2016 at 3:09 PM, Richard Hipp  wrote:

> On 11/21/16, David Raymond  wrote:
> >
> > Following the nice SQL diagrams it looks like in a select you can only
> have
> > * or table-name.*, whereas in other places you can have
> > schema-name.table-name. Granted, the second version can be made prettier
> and
> > more readable, but I would have assumed the first version would be ok. Is
> > this par for all SQL versions?
>
> I don't know what other database engines do, but you are correct that
> SCHEMA.TABLE.* is not allowed in SQLite.  And since the "*" forms are
> considered bad style (to be used only interactively, and not in
> applications) we are not motivated to change it, lest developers be
> tempted to use "*" in their applications.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Table name syntax

[Richard Hipp]

On 11/21/16, David Raymond  wrote:
>
> Following the nice SQL diagrams it looks like in a select you can only have
> * or table-name.*, whereas in other places you can have
> schema-name.table-name. Granted, the second version can be made prettier and
> more readable, but I would have assumed the first version would be ok. Is
> this par for all SQL versions?

I don't know what other database engines do, but you are correct that
SCHEMA.TABLE.* is not allowed in SQLite.  And since the "*" forms are
considered bad style (to be used only interactively, and not in
applications) we are not motivated to change it, lest developers be
tempted to use "*" in their applications.

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

[sqlite] Table name syntax

[David Raymond]

Basic syntax question on qualified table names in a select. I've got 2 attached 
databases, say db1 and db2, and I try to run...

insert into main.foo
select db1.foo.*
from db1.foo left outer join db2.bar
on db1.foo.pk = db2.bar.pk
where db2.bar.pk is null;

and I get "Error: near "*": syntax error"

If I give db1.foo an alias though it treats it as fine.

insert into main.foo
select aliasName.*
from db1.foo as aliasName left outer join db2.bar
on aliasName.pk = db2.bar.pk
where db2.bar.pk is null;

Following the nice SQL diagrams it looks like in a select you can only have * 
or table-name.*, whereas in other places you can have schema-name.table-name. 
Granted, the second version can be made prettier and more readable, but I would 
have assumed the first version would be ok. Is this par for all SQL versions?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users