Differences between select 1 and select * in Oracle

Create a myt table and insert data as follows:

Create table myt (name varchar2, create_time date)

Insert into myt values ('john', to_date (sysdate, 'dd-MON-YY '));

Insert into myt values ('Tom ', to_date (sysdate, 'dd-MON-YY '));

Insert into myt values ('lili', to_date (sysdate, 'dd-MON-YY '));

The SQL * plus statement is as follows:

SQL> select * from myt;
 
NAME CREATE_TIME
---------------------
John 2010-5-19
Tom 2010-5-19
Lili 2010-5-19
 
SQL> select 1 from myt;
 
1
----------
1
1
1

SQL> select 0 from myt;
 
0
----------
0
0
0
From the above results, we can see that select constant fromtable returns the corresponding constant value for all rows (see the following for specific applications ),

Select * from table returns all columns corresponding to all rows.

Select 1 is often used in the exists clause to check whether a qualified record exists.

For example, select * from T1 where exists (select 1 from T2 where T1.a = T2.a );
Small Data size of T1 and large data size of T2, T1 <T2, 1) high query efficiency.
"Select 1" here "1" is actually irrelevant, but it is okay to replace it with "*". www.bkjia.com only cares whether the data in the brackets can be searched out, whether such a record exists. If the where condition exists, it is true.
Example:

SQL> select 1/0 from dual;
 
Select 1/0 from dual
 
ORA-01476: divisor is 0

SQL> select * from myt where exists (select 1/0 from dual );
 
NAME CREATE_TIME
---------------------
John 2010-5-19
Tom 2010-5-19
Lili 2010-5-19

No error is returned. It indicates that the select statement is not involved in calculation.

Writing select * in the exists clause does not return all columns in the table, but checks whether the qualified records exist.