This article describes five(5) misconceptions about the behaviour of the popular Oracle database system. These misconceptions often lead to unexpected results that developers with SQL(Structured Query Language)experience may face when migrating to Oracle database. Make sure to read to the end as I can assure you, surprises await you but don’t worry, I’m sharing my experience so you don’t have to go through what I went through. I just want your programming experience to be successful and your programming skills advanced.

本文介绍了有关流行的Oracle数据库系统行为的五(5)个误解。 这些误解通常会导致具有SQL(结构化查询语言)经验的开发人员在迁移到Oracle数据库时可能会遇到意想不到的结果。 请确保读到最后，以便向您保证，惊喜在等待您，但不用担心，我在分享我的经验，因此您不必经历我所经历的一切。 我只希望您的编程经验成功并且您的编程技能得到提高。

1.对NULL值的测试总是返回FALSE (1. Tests on NULL values always return FALSE)

Would you agree that combining the two requests below gives the Persons table?

您是否同意将下面的两个请求结合起来提供“人员”表？

SELECT * FROM Persons WHERE SALARY > 1000SELECT * FROM Persons WHERE SALARY <= 1000

Well, if so, then you would be wrong. Or, more precisely, you would be wrong if the table were to contain a row where SALARY is NULL.

好吧，如果是这样，那你就错了。 或者，更准确地说，如果表包含SALARY为NULL的行，那将是错误的。

In fact, the conditions SALARY > 1000 and SALARY <= 1000, both implicitly exclude the rows where SALARY is NULL.

实际上，条件SALARY> 1000和SALARY <= 1000 ， 两者都隐式排除SALARY为NULL的行。

Oracle’s handling of NULL values in boolean expressions might seem confusing for beginners, but it’s actually pretty straight forward, and purely mechanical.

Oracle对布尔表达式中的NULL值的处理对于初学者来说似乎令人困惑，但是实际上非常简单，而且纯粹是机械的。

It’s fundamental knowledge and the rules are pretty simple. Take aside the two logical operators that are dedicated to testing whether a value is NULL or NOT. These are, respectively:

这是基础知识，规则很简单。 排除两个专用于测试值是否为NULL或NOT的逻辑运算符。 它们分别是：

IS NULLIS NOT NULL

And their behaviour is self-explanatory.

他们的行为是不言自明的。

All the remaining logical operators will evaluate to FALSE if any of their operands is NULL. For example, all the below expressions are FALSE.

如果所有其他逻辑运算符的任何一个操作数为NULL，则它们的评估结果均为FALSE。 例如，以下所有表达式均为FALSE。

NULL = 1NULL != 1NULL > 1NULL = "ABC"NULL != "ABC"NULL LIKE "Steve%"NULL NOT LIKE "Steve%"NULL = NULL

So, although one might consider that conditions above that are in bold are TRUE, Oracle still evaluates them all to FALSE. It is up to the developer of a query to adjust the conditions to make them return the required data.

因此，尽管人们可能认为上面用粗体显示的条件为TRUE，但是Oracle仍然将它们全部评估为FALSE。 由查询的开发人员来调整条件，以使其返回所需的数据。

For example, if we want the list of people where the salary is less than 1000 or NULL, the request would then become:

例如，如果我们想要薪水少于1000或NULL的人员列表，则请求将变为：

SELECT * FROM Persons WHERE SALARY < 1000 OR SALARY IS NULL.

Additionally, to completely cover the table Persons mentioned in the introduction, we would still need to add all the rows where SALARY is NULL. This we can do by using the following request:

另外，为了完全覆盖介绍中提到的表Persons，我们仍然需要添加SALARY为NULL的所有行。 我们可以通过使用以下请求来做到这一点：

SELECT * FROM Persons WHERE SALARY IS NULL

2. Oracle中没有空字符串 (2. There are no empty strings in Oracle)

The behaviour I am going to describe now — just like the others — may come as a surprise to software developers and non-Oracle database developers of any level of programming experience. A long time ago, I was trying to retrieve the list of users who didn’t have emails. My first attempt for getting this list was a simple query like:

我现在要描述的行为(与其他行为一样)可能会给具有任何编程经验水平的软件开发人员和非Oracle数据库开发人员带来惊喜。 很久以前，我试图检索没有电子邮件的用户列表。 我第一次尝试获取此列表是一个简单的查询，例如：

Select * from Users where Email = '' //Nothing between the quotes

To my surprise, this query did not return any results so I attempted the one below, which wasn’t successful either.

令我惊讶的是，此查询未返回任何结果，因此我尝试了以下结果，但均未成功。

Select * from Users where LENGTH(Email) = 0

At this point, I decided to check some users with no email directly in the table and found out that, surprisingly, they all had NULL inside the email field.

此时，我决定直接在表中检查一些没有电子邮件的用户，结果令人惊讶地发现，他们在email字段中都为NULL。

So, I reviewed my query and changed it as shown below:

因此，我查看了查询并进行了更改，如下所示：

Select * from Users where Email IS NULL

This time, bingo! I was relieved to see many rows returned by my query. Although a bit satisfied with my small achievement, I did not understand why the test on the length of the string did not work out, and why the strings were being represented by NULL instead of a simple empty string. The whole notion was counter-intuitive to even the most experienced developers.

这次，宾果游戏！ 看到查询返回的许多行令我感到宽慰。 尽管对我的小成就感到满意，但我不明白为什么无法对字符串的长度进行测试，以及为什么用NULL而不是简单的空字符串表示字符串。 整个概念即使是最有经验的开发人员也违反直觉。

The answer to the first question came quickly after checking the documentation. It turns out that Length(NULL) returns NULL instead of 0, the value that I was expecting.

检查文档后，第一个问题的答案很快就出现了。 事实证明，Length(NULL)返回NULL而不是0(我所期望的值)。

The answer to the second questions did not come until a few months later when I was struggling to correctly express the condition ‘made out of spaces’.

直到几个月后，当我努力正确地表达“由空间产生”的条件时，第二个问题的答案才出现。

My first bet was to use something like:

我的第一个赌注是使用类似：

RTRIM(COL) = '' //Nothing between the quotes

but this was returning 0 rows, just like the more conservative version:

但这将返回0行，就像更保守的版本一样：

LENGTH(RTRIM(COL)) = 0

A long session of research followed, leading me to discover that Oracle does not support zero-length strings.

随后进行了长时间的研究，使我发现Oracle不支持零长度字符串。

If we try to insert the literal empty string ‘’ in a column, the request would be executed without warnings but NULL would be inserted in the column. Moreover, a condition like COL = ‘’ is simply interpreted as if it was COL = NULL, which is always FALSE. Recall from earlier than comparisons to NULL always yield FALSE.

如果我们尝试将原义的空字符串''插入列中，则该请求将在没有警告的情况下执行，但NULL将被插入column 。 而且，像COL =''这样的条件被简单地解释为好像COL = NULL，始终为FALSE。 从比对更早的比较中回忆起，总是产生FALSE。

This can be verified quickly by running the below queries:

通过运行以下查询，可以快速验证这一点：

CREATE TABLE EMPTY_STRING ( Col varchar(255) );Insert into EMPTY_STRING values( '' );Select * from EMPTY_STRING;Select * from EMPTY_STRING Where COL is NULL;Select * from EMPTY_STRING Where COL = ''; One line with NULL 一行为NULL COL is NULL works as expected COL为NULL符合预期 Col = ‘’ returns no results Col =''不返回任何结果

Moving back to our condition for detecting the columns made from spaces. Since zero-length strings do not exist in Oracle, RTRIM of a string made of spaces can’t return anything other than NULL. So, the correct test is:

回到我们检测由空间组成的列的条件。 由于Oracle中不存在零长度的字符串，因此由空格组成的字符串的RTRIM不能返回NULL以外的任何内容。 因此，正确的测试是：

RTRIM(COL) is NULL

3.按by或where子句顺序使用别名 (3. Using aliases in order by or where clauses)

One thing that I find frustrating is the need to repeat the definition of my columns inside Order by or Where clauses. Oracle never accepts requests like the ones below:

我感到沮丧的一件事是需要在Order by或Where子句中重复列的定义。 Oracle从不接受以下请求：

SELECT NAME, age , weight / ( height * height ) AS BMI FROM personsORDER BY BMISELECT NAME, age , weight / ( height * height ) AS BMI FROM personsWHERE BMI > 25

That database design insists on having the definition of the column alias repeated as in the queries below:

该数据库设计坚持要重复列别名的定义，如以下查询所示：

SELECT NAME, age , weight / ( height * height ) AS BMI FROM personsORDER BY weight / ( height * height )SELECT NAME, age , weight / ( height * height ) AS BMI FROM personsWHERE weight / ( height * height ) > 25

You might think that I am being too picky, but remember that this is a very simple example.In real-life queries, we can have several long formulae that need to be repeated several times in the same query.

您可能会认为我太挑剔了，但是请记住，这是一个非常简单的示例。在实际查询中，我们可以有多个长公式，在同一查询中需要重复多次。

Fortunately, there is a better way to go about this. Let me show you the new syntax and then I will give you some explanations.

幸运的是，有更好的方法可以解决此问题。 让我向您展示新语法，然后给您一些解释。

SELECT * FROM(SELECT NAME, age, weight / (height * height) AS BMI FROM persons) WHERE BMI > 25SELECT * FROM(SELECT NAME, age, weight / (height * height) AS BMI FROM persons) ORDER BY BMI

Here I am using what is technically known as an inline view. So, I am - theoretically - asking Oracle to create a temporary table containing:

在这里，我使用的是技术上称为内联视图的视图。 因此，从理论上讲，我要求Oracle创建一个包含以下内容的临时表：

Select Name, Age, Weight/Height*Height as BMI from Persons

And then filtering or ordering on the columns of this temporary table (that clearly has a column named BMI).

然后对该临时表的列进行过滤或排序(该表显然具有名为BMI的列)。

As we can see, it’s an easy fix that is flexible enough to handle all scenarios. I believe that some caution should be exercised when the inline view is huge since it might slow down the execution of the request. But, based on my experience, it seems that the query optimizer manages most of the time to retrieve the data without actually creating the temporary table.

如我们所见，这是一个简单的修复程序，具有足够的灵活性来处理所有情况。 我认为，当内联视图很大时，应谨慎行事，因为这可能会减慢请求的执行速度。 但是，根据我的经验，查询优化器似乎大部分时间都在不实际创建临时表的情况下管理数据的检索。

4，结合使用*和选定的列 (4.Using * in combination with selected columns)

We all know that the asterisk can be used in SQL as a shortcut for all the columns in a table. So, the below request would clearly display all the columns of the table mytable:

我们都知道，星号可以在SQL中用作表中所有列的快捷方式。 因此，以下请求将清楚显示表mytable的所有列：

Select * from mytable

One annoying problem in Oracle is that it does not accept the usage of the asterisk together with column names. So, for example, the below queries are not accepted by Oracle.

Oracle中一个令人讨厌的问题是它不接受将星号和列名一起使用。 因此，例如，Oracle不接受以下查询。

Select SelectedCol1, * from MytableSelect SelectedCol1, SelectedCol2, * from Mytable

The good news is, there is a quick solution for this problem that consists of prefixing the asterisk symbol with the table name (or table alias name).

好消息是，有一个针对此问题的快速解决方案，其中包括在星号符号前加上表名(或表别名)。

Select col1, Mytable.* from MytableSelect col1, m.* from Mytable m

5.从查询中获取前N行 (5. Getting the top N rows from a query)

To get the first N rows from a query in Oracle, we use the pseudo-column rownum, which represents the row number of the returned row. Recall that Pseudo-columns are not actual columns in a table but they behave like columns.

为了从Oracle查询中获得前N行，我们使用伪列rownum ，它代表返回的行的行号。 回想一下，伪列不是表中的实际列，但它们的行为类似于列。

So, if we want to get the first 3 rows, we can use :

因此，如果我们想获得前三行，可以使用：

Select * from mytable where rownum < 4

Rownum can also be included in the list of fields, allowing us to display the number of each row:

Rownum也可以包含在字段列表中，从而使我们能够显示每行的编号：

select rownum, MYTABLE.* from MYTABLE where rownum < 4

One should keep in mind that the filtering (Where clause) is executed before the sorting (Order By). So, for example:

请记住，过滤(Where子句)在排序(排序依据)之前执行。 因此，例如：

Select Name, Grade from Students where rownum < 4 order by grade

will most likely return the sorted list of the first 3 students in the table — Not very useful.

很有可能会返回表格中前3名学生的排序列表-不太有用。

The proper way to get the top N rows of a sorted query is to use an inline view as shown below:

获取排序查询的前N行的正确方法是使用内联视图，如下所示：

Select * from(Select Name, Grade from Students order by grade)Where rownum < 4

Also, one should exercise some caution when filtering on rownum as the first row that breaks the condition on rownum will stop the execution of the query. The below conditions, for example, will yield no results since they fail on the first row of the table.

另外，在对rownum进行过滤时，应谨慎行事，因为第一行破坏了rownum的条件将停止执行查询。 例如，以下条件将不会产生任何结果，因为它们在表的第一行上失败。

rownum > 1rownum = 3

There is a way to get, say, the 3rd row, again using inline views.

有一种方法可以再次使用内联视图来获得第三行。

Select * from(Select rownum as lineNb, mytable.* from mytable where rownum < 4)Where lineNb = 3

Although this article covered only 5 surprises, they may be more. However, I hope you have found these points helpful in your journey as an Oracle database developer. Stay tuned for more tips, pointers, and guidelines.

尽管本文仅涵盖5个惊喜，但它们可能更多。 但是，我希望您发现这些要点对您作为Oracle数据库开发人员的旅程有所帮助。 请继续关注更多提示，指示和指导。

翻译自: https://medium.com/@bchalouhy/5-surprising-oracle-sql-behaviors-that-very-few-people-know-1934a58b3cb0

相关资源：四史答题软件安装包exe