>My current query looks something like this:
> SELECT tblA.someValue from tblB inner join tblA on
> tblB.fkcol = tblA.col1 where tblB.col1 = ?
>
> Couldn't it just be:
> SELECT fkcol.someValue from tblB where col1 = ?
I think you can, under standard SQL, write the following equivalent query:
SELECT tblA.someValue from tblB, tblA where tblB.fkcol = tblA.col1 and
tblB.col1 = ?
I think this will be equivalent in terms of result, per the standard. I guess
it's possible that different EXPLAIN PLANS will result from the two queries.
But I wrote queries this way for years, and considered the INNER JOIN syntax to
be wordy and academic-looking.
Looking at your proposed query, I guess that a SQL engine could probably infer
all of the things it would need to. But the way the column in your second
select list is qualifed (fkcol.someValue) is odd-looking to me, as is the
omission of a queried table from the WHERE clause. Also, I think the inferences
you describe rely on detailed metadata (FK constraints), which are not always
present. These are the main obstacles I perceive to what you proposed.
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On
Behalf Of Shaun Seckman (Firaxis) [shaun.seck...@firaxis.com]
Sent: Monday, November 16, 2009 11:37 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Verbosity of inner join queries
Hey guys,
This may be more of a question about the SQL language
itself rather than SQLite. I'm finding inner join statements to be more
verbose than they should be and I'm curious if there is any sort of
syntax sugar that can be done to shorten them.
An Example:
CREATE TABLE tblA( col1 integer, someValue integer);
CREATE TABLE tblB( col1 integer, fkcol integer, foreign
key (col2) references tblA(col1));
My current query looks something like this:
SELECT tblA.someValue from tblB inner join tblA on
tblB.fkcol = tblA.col1 where tblB.col1 = ?
Couldn't it just be:
SELECT fkcol.someValue from tblB where col1 = ?
The reasoning here is that SQL knows the schema of tblB
and knows exactly where the FK is pointing to. If fkcol was pointing
to an invalid row or was null, fkcol.someValue could be null. It seems
like this would just be syntax sugar and both should generate the same
query plan. The other thing I like about this syntax is that it would
have to throw an error if fkcol wasn't a true FK. In this example, the
inner join line was fairly short, but it gets pretty intense when you
have multiple inner joins.
Thoughts?
-Shaun
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
The information contained in this e-mail is privileged and confidential
information intended only for the use of the individual or entity named. If
you are not the intended recipient, or the employee or agent responsible for
delivering this message to the intended recipient, you are hereby notified that
any disclosure, dissemination, distribution, or copying of this communication
is strictly prohibited. If you have received this e-mail in error, please
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
