Why is there a SELECT 1 from table?

Question

I came across a fictitious SQL, i am not sure what is the original intend, it looks like:

SELECT COUNT (*)
INTO miss
FROM billing b
WHERE b.network= network1 
and NOT EXISTS (SELECT 1 from vas NV WHERE NV.network =  
b.network);

Why is there a select 1, and not exists?

Answer 1

When using the EXISTS keyword you need to have a sub-select statement, and only the existence of a row is checked, the contents of the row do not matter. SELECT is a keyword that controls what is inside the columns that are returned. SELECTing 1 or NV.network will return the same number of rows.

Therefore you can SELECT whatever you want, and canonical ways to do that include SELECT NULL or SELECT 1.

Note that an alternative to your query is:

SELECT count(*) INTO miss
  FROM billing b
  LEFT JOIN vas NV ON NV.network = b.network
 WHERE b.network = network1
   AND NV.network IS NULL

(left join fills right-hand columns with NULL values when the ON condition cannot be matched.

Answer 2

SELECT 1 from vas NV WHERE NV.network =  b.network

If this query returns a row, it means that there is a record in the NV table that matches one in the billing table.

NOT EXISTS negates it, satisfying there WHERE clause if there is NOT a record in the NV table that matches one in the billing table.