This happens because a 'normal' join is like a multiplicative product between all the rows matched in all the tables: result_rows = a_rows * b_rows * c_rows * d_rows so if any of the factors are zero, the result is zero. Your WHERE clause will cut down on the number of rows you get back, and you expect that the optimizer will prevent this obscene number of rows from ever really being generated, but this is how it logically works.
If you want different behavior, you can use a LEFT JOIN, which effectively rounds all the factors up to one by adding a row to each table with all NULL fields. On 7/6/02 at 8:47 am, Jay Blanchard <[EMAIL PROTECTED]> wrote: > I located this behavior this morning and was wondering how to prevent it; > > I have 3 (or more tables) where I perform a query like this; > > SELECT a.btn, b.dateFlag, c.dateFlag, d.dateFlag > FROM tblA a, tblB b, tblC c, tblD d > WHERE a.btn = b.btn > > tblA and tblB have information in them and btn is a key column in all > tables. tblC and tblD are currently empty. When I perform the query I get > "Empty set (0.00 sec)". If I remove c.dateFlag and d.dateFlag like this; > > SELECT a.btn, b.dateFlag > FROM tblA a, tblB b > WHERE a.btn = b.btn > > I do get results back. Any mention of a table that is empty returns nothing. > I don't ever remember running into this before, is this standard? > > Jay > mysql, sql, query > > "It's hip to snip!" > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
