Recent company to do system database upgrade, mainly by the original SQL database upgrade Oracle database, for the utility system with Tens database, migration is not easy, optical data synchronization will take a long time, let alone to modify the original SQL library used by the stored procedures and views. Of course, after the team's tireless efforts, or one swoop to break the threshold, the storage process all replaced, the data are all synchronized, but I'm sorry to say but, it is with this but also have this blog; I. Qualitative thinking problem: We often use such queries in SQL statements, I wonder if you have noticed that:SELECT * Table name where ISNULL (column name, ') = ' "This general way of writing is widely used in SQL, just because at the beginning of the design, we do filter the column name set can be empty, and subsequent data addition process, may set a null (') Insert, so we filter to include null (") and null data, so we have the above statement; Oracle comes with a different kind of appeal SQL, as Oracle doesSELECT * Table name where NVL (column name, ') = ' "But it turns out that Oracle's complete imitation of SQL is not going to get the result you want because Oracle's empty (") = null (") is the expression of false, because in Oracle Hollow (") is null, and Oracle makes NULL = (equals) null Obviously, Oracle does not agree, at least it should be NULL is NULL is the correct wording;
Full Han full null Come Again (SQL Edition)
full-han full null again (Oracle version)We can compare the results, basically the opposite (except C3,C4,C5,C7), so I have to admit that I may not have noticed this hole, but now met, presumably also make me remember deeply ~ ~ ~ Four, Summary regaling the definition of NULL (null value) in Oracle (from Baidu): 1, which is equivalent to no value, is unknown (the most common x in mathematics is thought of here). 2, null and 0, empty string, space are different. 3, add, subtract, multiply and divide the null value, and the result is still empty. 4, NULL processing uses the NVL function. 5. Use the keyword "is null" and "is not NULL" when comparing. 6, the null value cannot be indexed, so the query when some eligible data may not be found, COUNT (*), with NVL (column name, 0) processing and then check. 7, the sorting is larger than other data (the index by default is descending, small → large), so the null value is always ranked at the end. See Oracle Help Documentation: Oracle Assistance documentation Address: http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements005.htm
Good Luck for you!
SQL Upgrade Oracle dug the null pit, you know!
