Ok, "question" one - I'm pretty sure I know the answer to this:

Given:

CREATE TABLE A (
INTEGER PRIMARY KEY AUTOINCREMENT id,
VARCHAR(255) value
)

CREATE TABLE B (
INTEGER PRIMARY KEY AUTOINCREMENT id,
VARCHAR(255) value
)

if B contains now rows then A INNER JOIN B (USING id) will return zero
rows, correct?

Ok, now that we have that out of the way, question two:

CREATE TABLE A (
INTEGER PRIMARY KEY AUTOINCREMENT id,
VARCHAR(255) value
)

CREATE TABLE B (
INTEGER PRIMARY KEY AUTOINCREMENT id,
VARCHAR(255) value
)

CREATE TABLE C (
INTEGER PRIMARY KEY AUTOINCREMENT id,
VARCHAR(255) value
)

CREATE TABLE D (
INTEGER PRIMARY KEY AUTOINCREMENT id,
VARCHAR(255) value
)

If we issue the query:

A INNER JOIN B (USING id) INNER JOIN C (USING id) INNER JOIN D (USING
id)

and we know that there are zero rows in common between C and D, can we
not conclude with 100% certainty that the overall result will be zero
rows?

Finally: what books do you recommend for learning about SQL and
databases? I liked "SQL Queries for Mere Mortals" by Viescas and
Michael J. Hernandez, but maybe it was not elementary enough for me.
Maybe even a good web tutorial is fine, but I need to re-lock down my
basics.


Thanks!

metaperl wrote:
Correct, eventhough I'm unfamiliar with the syntax you have used.
Standard sql would have been:

A INNER JOIN B on A.id = B.id

Still uncertain by the syntax you have used but I'll continue guessing.
Since natural join is distributive [ (a join b) join c = a join ( b join
c )], and since C join D is the empty set, and since anything joined
with an empty set is the empty set, then yes.

Not sure what you want, but:

http://www.firstsql.com/tutor.htm

is a farely good start if you are only interested in sql and not the
theory behind it.


/Lennart

On Jul 3, 11:20 am, Lennart <erik.lennart.jons... (AT) gmail (DOT) com> wrote:
It's MySQL syntax.

"metaperl" <metaperl (AT) gmail (DOT) com> wrote

It is in fact ANSI/ISO SQL-92 standard syntax.

Roy

Roy Hann wrote:
Yes, you're right. I never saw that before and figured it was one of
those mysql invented thingies. I checked against sql2003 and did not
find this kind of join. When I checked again, there it was. I dont
understand how I could miss it. Thanks for the correction

/Lennart

metaperl wrote:
As Roy pointed out, it is better than that. It is ANSI/ISO SQL-92 (and
-99, -2003). Sorry for any confusion I might have caused


/Lennart

Lennart wrote:
What's life without nitpicking :-) Should'nt it have been:

select * from A INNER JOIN B USING (id)

instead of

select * from A INNER JOIN B (USING id)?

Not that it would have made any difference in my recognition of the stmt.


/Lennart

"metaperl" <metaperl (AT) gmail (DOT) com> wrote

The above syntax is different from what I'm accustomed to. I'm accustomed
to seeing the column name come before the properties of the column, not
after them. Does the above syntax work on some dialect of SQL?

metaperl wrote:
Usually, the DBMS controls the values assigned to 'autoincrement' type
columns, and there is no relationship between the values of an
autoincrement column in one table and those of an autoincrement column
in another table. Unless you are utilizing some special aspect of
autoincrement types in your DBMS or overriding the column values
yourself, your joins make no sense. There is no relationship between
id in Table A and id in Table B, and so on.

What are you trying to accomplish, or is this homework?

As others have pointed out, your syntax for a column declaration is
incorrect. The column name should come first, followed by the datatype
and then optional constraints.

--
Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
================================================== ============
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)

Lennart wrote:
[...]
Since natural join is distributive [ (a join b) join c = a join ( b join

Should have been: Since natural join is associative ...

/L

[...]