Re: subquery problem, wrong reference in the subquery?
From: Joe Celko (jcelko212_at_earthlink.net)
Date: 07/09/04
- Next message: Dan D.: "problem using sp from vb program"
- Previous message: Chumma Dede: "Re: Help from Gurus with SQL query"
- In reply to: MailSurfer: "subquery problem, wrong reference in the subquery?"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 09 Jul 2004 12:25:46 -0700
>> In the following example, the t2 table doesn't even have that key_id
field [sic]. The query still runs and give out results. Why? <<
Columns and fields are not the same thing! If you’d use the right
terms, you’d stand a chance of seeing why this works this way. Did you
mean to have all those NULLs and absolutely terrible data element names?
References to a row identifier imply a file; key_id is just plain silly
as well as violation of ISO-11179.
I know this is sample data and the names are not the point; but they are
a Rorschach test that shows your mind set – which is not relational.
Okay, look at the query. Using the usual scoping rules for a
block-structured language, we get to the innermost FROM clause and
construct a working copy of the results of its table expression (in this
case it is T2, but it could be complicated). There is no T2.key_id, so
we move to the containing environment; the nearest FROM clause in this
example. There is a T1.key_id! Hoorah!! We then use him in each row of
the original subquery.
SELECT *
FROM T1 <---------| second attempt, hit
WHERE key_id |
IN (SELECT key_id --| first attempt, fail
FROM T2) <----|
If you never worked with a block-structured language, you might want to
look at one –- Algol, Pascal, the C family, ADA, PL/I, etc. It is very
different from BASIC or FORTRAN.
In an RDBMS, the tables are part of a whole schema. In a file system
(your mental model), files are disjoint and often in separate physical
storage devices. There is no scoping -- a READ statement in a loop
reads records from one and only one file in a tape merge. Tables model
sets.
In an RDBMS, a column is part of a particular table with its own name,
constraints, datatype, etc. – It has an independent existence. In a 3GL
file system, fields exist only because a program reads them into the
host program. I can name the same fields anything I wish in each
program and apply pretty much any datatype to it. In fact, in COBOL, I
can cut up the string of characters in a file anyway I wish and have
multiple record templates defined on the same data.
You need to get a good book on foundations.
--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
- Next message: Dan D.: "problem using sp from vb program"
- Previous message: Chumma Dede: "Re: Help from Gurus with SQL query"
- In reply to: MailSurfer: "subquery problem, wrong reference in the subquery?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
