Can Anyone explain why NULL is used in this query?

Also what will be the scenarios where this query i开发者_C百科s used

select * from TableA where exists  
    (select null from TableB where TableB.Col1=TableA.Col1)

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.

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.

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 :)

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 )

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.