Also what will be the scenarios where this query is used
select * from TableA where exists
(select null from TableB where TableB.Col1=TableA.Col1)
views:
107answers:
5
+9
A:
As the query is in an EXISTS then you can return anything. It is not even evaluated.
In fact, you can replace the null with (1/0) and it will not even produce a divide by zero error.
Robin Day
2010-03-24 10:56:59
the (1/0) remark is awesome. i had no idea :D
David Hedlund
2010-03-24 11:03:13
+1 1/0, that's the definitive point that the SELECT (1, 0, NULL, field(s), etc) inside of EXISTS is immaterial on end-result.
Michael Buen
2010-03-24 11:32:28
+1 for that 1/0 and that "an EXISTS then you can return anything. It is not even evaluated"
ydobonmai
2010-03-24 11:38:53
+5
A:
The NULL makes no sense. It's simply bad SQL.
The exists clause is supposed to use SELECT *.
People make up stories about the cost of SELECT *. They claim it does an "extra" metadata query. It doesn't. They claim it's a "macro expansion" and requires lots of extra parse time. It doesn't.
S.Lott
2010-03-24 10:57:29
It's bad SQL because it's not `SELECT *`; `SELECT *` is what is expected there.
S.Lott
2010-03-24 11:53:21
+3
A:
It's a tacky way of selecting all records in TableA, which have a matching record (Col1=Col1) in TableB. They might equally well have selected '1', or '*', for instance.
A more human-readable way of achieving the same would be
SELECT * FROM TableA WHERE Col1 IN ( SELECT Col1 IN TableB )
David Hedlund
2010-03-24 10:59:01
I don't really think that is more readable. It uses IN instead of EXISTS, but it still hides that a join is being done. I would prefer something like this: SELECT TableA.* FROM TableA INNER JOIN TableB ON TableA.Col1 = TableB.Col1
AHM
2010-03-24 11:04:11
+4
A:
The EXISTS condition is considered "to be met" if the subquery returns at least one row.
The syntax for the EXISTS condition is:
SELECT columns FROM tables WHERE EXISTS ( subquery );
Please note that "Select Null from mytable" will return number of rows in mytable but all will contain only one column with null in the cell as the requirement of outer query is just to check whether any row fall in the given given condition like in your case it is "TableB.Col1=TableA.Col1"
you can change null to 1, 0 or any column name available in the table. 1/0 may not be a good idea :)
Mubashar Ahmad
2010-03-24 11:34:09
Robin Day's `1/0` was there to prove a point. the point being that it won't return a series of evaluated `1/0` equations, in just the same way that OP's query won't return a set full of nulls. what would've been selected had the select appeared outside of the `exists` seems besides the point. in essence, selecting 1/0 in this context is no worse than selecting null, or *.
David Hedlund
2010-03-24 11:46:23
Yes you are absolutely right there is no argument over that but i was just to inform the questioner to not to take your advice in literal meaning :) thats all
Mubashar Ahmad
2010-03-24 12:46:32
+1
A:
Please, please, all ....
EXISTS returns a BOOLEAN i.e. TRUE or FALSE. If the result set is non empty then return TRUE. Correlation of the sub-query is important as in the case above.
i.e Give me all the rows in A where AT LEAST one col1 exists in B.
It does not matter what is in the select list. Its just a matter of style.
pj
2010-03-24 11:40:13