Rick,
You are most welcome. So sorry for the function confusion, I must be
getting tired if I am confusing ISNULL() with IFNULL(). I sure am glad it's
FRIDAY!!!
About your NOT EXISTS() vs correlated subqueries. I tried to simulate
acting as the query engine by creating an intermediate results table based
on the JOIN then applying a second query to it using your NOT EXISTS
construct. I couldn't get to your missing records with either EXISTS or NOT
EXISTS. Until I rewrote your query, I couldn't see any of the records
where the rateplan was 4 (except in the intermediate table) Maybe I will
hit it again on Monday when my mind is fresher.
On the JOINS... I know exactly how you feel. It was strange for me when I
used Horrical that it DIDN'T have joins. I had the hardest time adjusting
to the (+) notation of partial joining. Just remember that whichever table
is "in the direction" of the join should return all of its records (unless
you get rid of them in the WHERE clause). Tablea LEFT JOIN Tableb means
that you will be building an internal intermediate table that contains all
of the columns of both tables that will have all of the rows of Tablea
matched up to all rows of Tableb that satisfy the ON clause. Any rows where
the ON clause is not satisfied will have NULL in every column from the
"other" table. INNER join is just an "equi-join", if I remember my
terminology correctly (it's been several years since I used Horrical).
The advantage of the JOIN...ON... syntax versus making all of your
comparisons in the WHERE clause is that the engine can pre-select certain
rows from your source tables (using the available indices) based on the
restrictions in the ON clause which will make your temporary internal
working dataset much smaller. This form
SELECT a.*, b.*, c.*
FROM a,b,c
Where b.a_ID = a.ID
and c.somedate > b.somedate
and a.user = 'joe'
could cause the SQL engine to combine all rows of a with all rows of b and
all rows of c THEN apply the WHERE clause (depending on the optimizer, of
course). If a has 100 rows and b has 100 rows and c has 1000 rows you get
potentially 100x100x1000 or 10,000,000 rows in memory to filter through
while this form
SELECT a.*, b.*, c.*
FROM a
INNER JOIN b
on a.user = 'joe'
and b.a_id = a.id
LEFT JOIN c
on c.somedate > b.somedate
will result in an internal set much smaller than 100x100 joined to a set
potentially smaller than 1000 for a much faster result (notice I didn't
need a WHERE pass through the data?) and much smaller memory footprint. To
me, it's all about clock cycles. Smaller query footprints -> less data
pumped through the system bus (and maybe less swap file usage) -> faster
execution -> happier users.
Have a good weekend!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Rick Robinson"
<[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>
cc: "'Mysql'" <[EMAIL PROTECTED]>
06/11/2004 04:01 Fax to:
PM Subject: RE: Possible problem with
outer join in 4.1.2
Please respond to
rick
Hi Shawn-
First, thanks for responding. You're re-written query works as I would
expect, even using IFNULL instead of COASLESCE (see PS:).
I'm not sure I explained my issue well enough. Basically, I feel that
outer
joins with correlated sub-queries using not exists are broken in MySQL.
The NOT EXISTS clause is filtering out records that have effective dates
that are 1) later than the transaction date, and 2) have matching records
with earlier effective dates. (Note: In Oracle, the EXPLAIN shows that
this
is what happens and that the query was not internally rewritten by
Horracle.) Basically, it's picking out the record that was effective at
the
time of the transaction.
I've always had a fun time with NOT EXISTS...kind of mind-bending at times.
I swear it works like a dream in Horracle (both 8.x and 9.x, haven't tried
it in 10.x).
Thanks again for looking at this. The left join syntax in MySQL is new to
me and a little challenging at times - your example helps a lot. Although
I
still feel that there's some sort of problem with the way MySQL is handling
NOT EXISTS for this case.
Best regards,
Rick
PS:
There's no IsNULL() function in MySQL that I know of. I'm using IFNULL() -
according to the doc:
"IFNULL(expr1,expr2) : If expr1 is not NULL, IFNULL() returns expr1, else
it
returns expr2."
Rewritten query (with ifnull):
select
a.id id,
a.trandate trandate,
a.acct acct,
a.rateplan rateplan,
ifnull(a.rangeid, 99999) rangeid,
case ifnull(a.rangeid, 99999) when 99999 then 'null rangeid' else
ifnull(b.descr, 'null descr') end descr
from
transactions a
left join ref_info b
on a.rateplan = b.rateplan
and a.rangeid = b.rangeid
and a.trandate > b.effdate
left join ref_info c
on c.rateplan = b.rateplan
and c.rangeid = b.rangeid
and c.effdate < a.trandate
and c.effdate > b.effdate
where
a.acct = 123
and c.rateplan is null
order by 2 desc, 1 desc
;
----------- previous replies snipped ----------
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
