CODEWITHSUNDEEP
sql
Rishabh Ohri
Rishabh Ohri

Reputation: 1310

Can Anyone explain why NULL is used in this query?

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)

Upvotes: 4

Views: 201

Answers (5)

pj.
pj.

Reputation: 479

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.

Upvotes: 1

Mubashar
Mubashar

Reputation: 12658

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

Upvotes: 4

David Hedlund
David Hedlund

Reputation: 129792

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 )

Upvotes: 3

S.Lott
S.Lott

Reputation: 391818

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.

Upvotes: 5

Robin Day
Robin Day

Reputation: 102468

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.

Upvotes: 9

Related Questions