Can someone explain why this query uses NULL?

Question

Scenarios for using this query will also be considered.

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

Answer 1

An EXISTS clause is considered "satisfied" if the subquery returns at least one row.

EXISTS condition syntax:

SELECT columns FROM WHERE EXISTS tables (subquery);

Please note that “Select Null from mytable” will return the number of rows in mytable, but all of them will contain only one column with a zero value in the cell, since the requirement of an external query is to check if any row is in the given condition for example, in your case it is "TableB.Col1 = TableA.Col1"

you can change the null value to 1, 0 or any column name available in the table. 1/0 may not be a good idea :)

Answer 2

Since the request is in EXISTS, you can return something. He is not even rated.

In fact, you can replace null with (1/0), and it will not even lead to division by zero error.

Answer 3

NULL does not make sense. This is just bad SQL.

The exists SELECT * is supposed to use SELECT * .

People make up SELECT * cost stories. They claim that it is performing an “additional” metadata request. This is not true. They claim to be "macro expansion", and require a lot of extra parsing time. This is not true.

Answer 4

This is a sticky way to select all the records in TableA that have the corresponding record (Col1 = Col1) in TableB. For example, they could choose "1" or "*", for example.

A more humanly readable way to achieve this would be

 SELECT * FROM TableA WHERE Col1 IN ( SELECT Col1 IN TableB ) 

Answer 5

Please, please, all ....

EXISTS returns BOOLEAN ie TRUE or FALSE. If the result set is not empty, return TRUE. Subquery correlation is important, as is the case above.

ie Give me all the lines in A, where AT LEAST one col1 exists in B.

It doesn't matter what is in the selection list. It is just a matter of style.