EXISTS query logic question

[phpknight]

Hi,

 

I am starting to use a few subqueries like NOT EXISTS and EXISTS.  I was looking at the docs, and I still cannot understand one element because the explanation is so brief.

 

When you say NOT EXISTS or EXISTS and you are joining different two tables in the main query, what exactly is the subquery matching to determine if whether the value exists or not?  Any field value, or just key values?

 

If anybody could help me out or point me to some place where the logic is fully explained, that would help quite a bit.

 

 

[fenway]

It returns a boolean based on the where clause.

[phpknight]

Can you elaborate on that a little bit?  I am not sure I understand.  It has to be matching up the rows somehow, right?

[fenway]

It's not different than any other query... except that it returns true/false instead of the actual records that matched.

[phpknight]

Okay, but how does it determine whether to return true or false.  It has to be comparing the fields of the main query to the subquery, right?  To be clear, I am talking about something like this:

 

SELECT a,b, c,d,e,f from table1, table2 where NOT EXISTS (SELECT * from table2)

 

where some field names might be the same or not.  When mysql does the subquery, what does it compare to the main query to determine if it matches?  Does it use a key field, any field, or what?

 

So, let's say a is a key field but f is just a boolean field.  Will it match up any true/false from f or only use a, etc?  Or does every field from the subquery result have to match in a main query row as well?

 

[beebum]

You may want to try something like this:

 

SELECT a,b,c,d,e,f from table1 where a not in (SELECT id from table2);

[phpknight]

So far, nobody has really answered my question.  Maybe am missing something or possibly am not clear.

[fenway]

Okay, but how does it determine whether to return true or false.  It has to be comparing the fields of the main query to the subquery, right?

No, entirely incorrect -- it has nothing to do with the outer query unless you specifically reference fields from the outer query in the where clause of the exists subquery.

 

SELECT a,b, c,d,e,f from table1, table2 where NOT EXISTS (SELECT * from table2)

 

where some field names might be the same or not.  When mysql does the subquery, what does it compare to the main query to determine if it matches?  Does it use a key field, any field, or what?

In this case, if there are any rows in table 2, the subquery will return the "TRUE" boolean.

[phpknight]

Okay, I understand what you mean about the boolean thing.  Thanks! 

 

How does it work if it is like this: if

you specifically reference fields from the outer query in the where clause of the exists subquery.

[fenway]

From the mysql manual:

 

SELECT DISTINCT store_type FROM stores
  WHERE EXISTS (SELECT * FROM cities_stores
                WHERE cities_stores.store_type = stores.store_type);

Is the correct statement to answer the question: What kind of store is present in one or more cities?

 

HTH.

[phpknight]

What is HTH?

 

Yeah, I saw that example, but I would really like to see a chapter in a book or about 20 more examples.  That didn't quite answer it for me how it works field for field.  With the store_type it is obvious, but what if you just said * and *?  Then, does it have to match all to match, or just the keys, or what?  It doesn't answer that question for me.

[fenway]

What is HTH?

 

Yeah, I saw that example, but I would really like to see a chapter in a book or about 20 more examples.  That didn't quite answer it for me how it works field for field.  With the store_type it is obvious, but what if you just said * and *?  Then, does it have to match all to match, or just the keys, or what?  It doesn't answer that question for me.

Forget this "field to field" thing that you've envisioned - it doesn't exist.

 

You can put whatever you want in the outer select list -- even * -- and it won't change anything, just like in a regular sql statement, it doesn't affect the table relationships, just the columns displayed.

 

The inner "*" -- the one after select in the subquery -- has no meaning, and is just a place holder.  It's just so that it looks like regular sql, but it's not doing anything.

 

The "match" is ONLY determined by the where clause... there's nothing fancy about this at all.  You're making this more complicated than it is.