Shulou(Shulou.com)06/01 Report--

select 1 from table; and select anycol from table; and select from table;

There is no difference from the role, are to see if there are records, generally used for conditional queries. The 1 in select 1 from table is a constant (can be any number), and the value of all rows found is it, but from the efficiency point of view, 1>anycol>, because there is no need to look up dictionary tables.

Note:

When we only care about how many rows there are in a table and don't need to know the specific field values, a SQL statement like "select 1 from table" is a good way to write it, which is usually used for subqueries.

This reduces overhead and improves efficiency. Because SQL statements are written in this way, the database engine does not retrieve each specific record in the data table and each specific field value in each record and put them into memory. Instead, it outputs how many "1" s exist according to how many rows are queried. Each "1" represents one row of records. The number 1 is also selected because it takes up the smallest memory space. Of course, the number 0 has the same effect.

Test: select 1 from table adds temporary columns, and the column values in each row are the numbers written after select. Select count(1) from table No matter how the value of a in count(a) changes, the resulting value is always the number of rows in table select sum(1) from table Calculate the sum of temporary columns

Testing with 1 in Oracle, the results are as follows:

Test results, get a temporary column with the same number of rows and table rows, the column value of each row is 1; get a number, the number is the number of rows in the table; get a number, the number is the number of rows in the table;

Then test with "2" and the result is as follows:

Find a temporary column with the same number of rows as the table, with column values of 2 per row; find a number that is the number of rows in the table; find a number that is the number of rows in the table ×2

and then test it with different numbers.

Get a temporary column with the same number of rows as the table table, the column value of each row is the number written after select; or get a number, which is the number of rows in the table; get a number, which is the number of rows in the table × the number written after select

To sum up: the first way of writing is to add temporary columns, and the column values of each row are the numbers written after select; the second way is to get the values regardless of how the a value of count(a) changes; the third way is to calculate the sum of temporary columns.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.