Why is there a SELECT 1 from table?

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 开发者_如何学Cthere a select 1, and not exists?

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.

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.

There is a great AskTom Q&A on the use of EXISTS vs IN (or NOT EXISTS vs NOT IN):

http://asktom.oracle.com/pls/asktom/f?p=100:11:1371782877074057::::P11_QUESTION_ID:953229842074

Basically using EXISTS only checks for the existence of the row in the subselect and does not check every matching row (which IN would). Therefore when using EXISTS or NOT EXISTS you do not need to actually select a particular value so selecting a placeholder (in this case "1") is enough.

In your particular SQL statement, the NOT EXISTS clause ensures that the main SELECT will only return rows where there isn't a corresponding row in the VAS table.

This code of yours is for mainly written from a performance stand point

I am mentioning only about the inner query. Since it needed explanation for user. What ever sql I have used should be inserted to the actual query user has used above

and NOT EXISTS (SELECT 1 from vas NV WHERE NV.network =  
b.network);

Explaining the inner query only Usually people use to put select NV.netword but this return a data that is just used to identify if there doesn't exist a data. So, ideally what ever is returned in inner query is not even checked in the parent query. So to reduce Bytes in explain plan, we use select 1 which will have minimum byte cost and which in-turn will reduce the cost of the query.

To view the difference i suggest downloading oracle sql developer and running both the query in explain plan window and watch out the bytes column for each query

SELECT nv.network from vas NV WHERE NV.network =  b.network
// cost will be depended on the value nv.network contain and that is selected in the where condition

while

SELECT 1 from vas NV WHERE NV.network =  b.network 
// cost will be independent of the column and cost lesser bytes selected and lesser cost.

Not exist you will be able to check with other answers.