This sounds like a shortcut optimization in the JOIN processor. Whenever
you write "FROM tableA, tableB ... WHERE tableA.keyfield =
tableB.foreignkeyfield...." it has the same effect as writing "FROM tableA
INNER JOIN tableB ON tableA.keyfield = tableB.foreignkeyfield". The engine
handles the comma separator in the FROM clause as an implicit INNER JOIN.
Since the second table had no rows, there was no way for an INNER JOIN to
actually work (no possible matches) so there was no need for the engine to
go through the effort of creating a Cartesian product. When you added a row
to "oems", now the engine had to create that cartesian product of the two
tables so that the rest of the WHERE statement could be evaluated. AND
since you specified an OR condition you luck-out and get your 4 rows back.
I am 99.99% certain that if you add 3 more rows of data to "oems", you will
have 16 rows as your result. You should see each batch of the original 4
rows repeated once for each row of data you have in "oems"
What exactly was the question you wanted answered with the query "SELECT *
FROM customers,oems WHERE ((customers.companyID= 1509 ) OR (oems.companyID)
= 1509)" ? You may have wanted to write that as a UNION query or as a LEFT
JOIN to get the correct response.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
|---------+---------------------------->
| | Dave Gibson |
| | <[EMAIL PROTECTED]|
| | > |
| | |
| | 07/02/2004 08:32 |
| | AM |
| | |
|---------+---------------------------->
>--------------------------------------------------------------------------------------------------------------------------------|
|
|
| To: [EMAIL PROTECTED]
|
| cc:
|
| Fax to:
|
| Subject: Unexpected behaviour: SELECT with OR returns empty set
|
>--------------------------------------------------------------------------------------------------------------------------------|
Hi,
I'm getting some unexpected behaviour from a query and was
hoping someone could shed some light on whether it's a user
error or something else.
I have two tables:
customers, oems which both have a companyID field (marked
as a foreign key).
When I do:
SELECT * FROM customers WHERE customers.companyID = 1509
I get 4 rows returned.
However, when I do:
SELECT * FROM customers,oems WHERE ((customers.companyID
= 1509 ) OR (oems.companyID) = 1509)
I get 0 rows matched.
Investigation showed that the oems table contains no rows.
If I add a rown to it, even one that doesn't match the
query above, I get the correct result.
ring any bells with anyone?
Thanks,
Dave
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
