Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2005
    Location
    Sweden
    Posts
    45

    Unanswered: Does MySQL support subqueries? (was "Subquery")

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2005
    Location
    Sweden
    Posts
    45
    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

  4. #4
    Join Date
    Jan 2004
    Location
    Australia
    Posts
    42
    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 ?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2004
    Location
    Australia
    Posts
    42
    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 ?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •