Use SQL statements to query all commands being executed in SQL

If your experience in SQL is not rich enough and you have not solved many problems in SQL, read this chapter carefully, it helps you avoid a mistake that is likely to be made frequently!

Create a test table first.

Create Table # Table1 (ID int identity (1, 1) primary key, col1 int not null, col2 decimal (18, 1 ))

Insert data

Insert into # Table1 (col1, col2) Select 0, 0.1 Union all select 1, 1.1 Union all select 2, 2.2 Union all select 3, 3.3

Let's guess the result of the SELECT statement below.

Select * from # Table1 where col1 <>''

Some friends may have said that the preceding SQL statement certainly reports an error. col1 is of the int type. How can it be compared with the varchar value. However, you are wrong. The preceding SQL statement can find the result set. The query result is as follows: Id col1 col2 2 1 1.1 3 2 2.2 4 3 3.3

What's going on? We only need to perform a test. Execute the following statement:

Select convert (INT ,'')

The query result is as follows: no column name 0

Well, you know, ''can be implicitly converted to int type in SQL, And the converted value is 0, so the SELECT statement above is actually equal

Select * from # Table1 where col1 <> 0

A friend may have been thinking about it again. Since ''can be implicitly converted to the int type, it can certainly be converted to the decimal type.

Select * from # Table1 where col2 <>''

When the preceding SQL statement is executed, the following error is reported: an error occurs when the data type varchar is converted to numeric.

That is to say, ''cannot be implicitly converted to the decimal type.

Conclusion: 1. In SQL queries, when the int column is compared with the ''value,'' is implicitly converted to 0, that is, select * from # Table1 where col1 <> ''is equivalent to select * from # Table1 where col1 <> 0 2. If you compare'' with the decimal column, an error type conversion error is returned.

Note: All of the aboveCodeAll the small series are tested in SQL server2005, but not in other versions. the above conclusions are tested by the editor in actual operations. I do not know the reason yet. After the editor finds the reason, it will be released on this site. Stay tuned to the small site!