Hi,

Do MySQL support subqueries? My query looks like this:
SELECT * FROM tblindex WHERE EXISTS
(SELECT * FROM tblindex WHERE indexWord='BMW')

I know maybe this question has been asked before in this forum, but I can't find it.

//M

your query doesn't make much sense

if the subquery finds at least one row in the table with indexWord='BMW', then the EXISTS will evaluate TRUE, thus the outer query returns all rows in the table

so what your query returns is either all the rows in the table or none of them

at least, it would if you were on 4.1

Thank you r937 for your answer, the select statement were just an example.
I realized that my MySQL was an old version, 4.0! I were sure that it was 4.1, but a check turned out that it was 4.0.
Now I'm running the latest version.

//M

I have also a question of EXISTS:

For the above example, what would happen if there is only one row of column indexWord= 'BMW' ?

Does it return the one row or the whole table ?
Does the outer query process the table row by row and referenced by the subquery ?

it doesn't matter how many rows have indexWord= 'BMW'

if there is even one, then the EXISTS is true

and if the EXISTS is true...

I tried the Northwind database, although that is MSSQL Server, but just use the sample data in Query Analyser, but the principal is the same.

1)SELECT * FROM Customers
It returns 91 rows.

1)SELECT * FROM Customers c
WHERE EXISTS (SELECT * FROM Orders o where o.customerid = c.customerid)
It returns 89 rows.

Just don't know why ?
The subquery should always returns TRUE, why there are 2 less?

This should be a kind of correlated query, so the outer query processes the outer table row by row, and the row is then referenced in the subquery in this loop. It should only returns the rows in the outer table when the row referenced in the subquery match the condition ?

two different queries, two different results

yours is a correlated subquery, and it specifically checks to see if there is any order that matches the customer

try your query and replace where o.customerid = c.customerid with where o.customerid = 37 (or some other values)

the original query did not have a correlated subquery, it just checks to see if there is any row with BMW, and if there is, it selects the whole table