Select 1 from ... 1 interpretation in SQL statements

Excerpt from:http://blog.csdn.net/zengcong2013/article/details/48224509

Select 1 from ..., what does 1 in the SQL statement mean? What's the result of finding out?

Select 1 from table, and select Anycol (any row in the destination table collection) from table, with select * from table is not different from the role, is to see if there is a record, general is used for conditional queries. Select 1 from the 1 is a constant (can be any number), the value of all the rows found is it, but in terms of efficiency, 1>anycol>*, because do not look up the dictionary table .

Test scenario: (Transferred from the Web document)
Table is a data table, assuming that the table has a row count of 10 rows.

1:select 1 adds a temporary column from table, the column value of each row is the number written after the Select, and the SQL statement is 1

2:select count (1) How a value of a from table tube count (a) changes, the resulting value is always the number of rows in the table row

3:select sum (1) From table calculates the sum of the temporary column

With 1 tests in SQL Server, we found the following results:

1: The test results, the same number of rows and table table row number of temporary columns (so called, I do not know what to call), the column value of each row is 1;

2: A number is obtained, which is the number of rows of table tables;

3: A number is obtained, which is the number of rows of table tables;

Then I use the "2" test, the results are as follows:

1: A temporary column with the same number of rows as the table row, and a column value of 2 for each row;

2: A number is obtained, which is the number of rows of table tables;

3: Draw a number that is the number of rows in table x2

Then I tested it with a larger number:

1: A temporary column with the same number of rows as table rows, and the column value of each row is the number I write after the select;

2: A number is still obtained, which is the number of rows of table tables;

3: Draw a number that is the number of rows of table tables x is written after the Select

in summary: The first method of writing is to increase the temporary column, the column value of each row is written in the number of select, the second is that regardless of how a value of count (a) changes, the resulting value is always the number of rows in table tables, and the third is to calculate the sum of temporary columns .

Statement if not EXISTS (select 1 from deleted D joins inserted I on d.id=i.id
and D.col1=1 and i.col1=2)

If not exists if it does not exist
（.. Here is the validation update, the ID before the update equals the updated ID, and the value of Col before the update is 1, and the updated value is 2

Select 1 from ... 1 interpretation in SQL statements