AND, OR and NOT

Databases & SQL Lesson 4 2:05 English narration · English + 中文 subtitles burned in

space play · ←/→ 5s · j/l 10s · f fullscreen · ,/. speed

Chapters

Transcript

A WHERE can test more than one thing. 一个 WHERE 可以测试不止一件事。

AND requires both to be true. AND 要求两个条件都成立。

Take form equals eleven A on its own and three survive: Mei, Sara and Lin. 单看 form = '11A'，有三个活下来：Mei、Sara 和 Lin。

Now add AND score above eighty-eight, and only Sara is left, because she is the only one of those three who also clears that bar. 现在加上 AND score > 88，就只剩下 Sara，因为她是这三个人里唯一同时越过那条线的。

Each AND you add can only take rows away. 你每加一个 AND，都只可能拿走更多的行。

OR needs only one side to be true. OR 只需要有一边成立。

Form equals eleven B gives Jamal and Tom — two rows so far. form = '11B' 给出 Jamal 和 Tom——目前是两行。

Add OR score at least ninety-five and Sara joins them, even though she is in eleven A, because the second condition is enough on its own. 再加上 OR score >= 95，Sara 就加了进来，尽管她在 11A 班，因为第二个条件本身就足够了。

Each OR you add can only bring rows in. 你每加一个 OR，都只可能带进更多的行。

If you remember nothing else, remember which way each word moves the count. 如果别的都不记得，就记住每个词让结果数量往哪个方向走。

AND makes the result smaller, because both must hold. AND 让结果变小，因为两个条件都得成立。

OR makes it bigger, because one is enough. OR 让结果变大，因为一个就够了。

And NOT flips a condition, so NOT form equals eleven A gives you exactly the students the plain test left out. 而 NOT 把一个条件取反，所以 NOT form = '11A' 给你的正好是原来那个条件漏掉的那些学生。

Now the part that catches people. 现在说会绊倒人的那部分。

Write an AND and an OR in the same WHERE and the words alone are ambiguous. 在同一个 WHERE 里同时写 AND 和 OR，光看这些词是有歧义的。

Here is what SQL actually does: it groups the AND first. 这是 SQL 实际做的：它先把 AND 那部分组合起来。

And here is what you may have meant, with the brackets somewhere else — a different query, and a different answer. 而这是你可能想表达的意思，括号加在别的地方—— 那是另一条查询，也是另一个答案。

Brackets remove the doubt, so put them in whenever you mix the two. 括号消除疑问，所以只要你混用这两者，就把括号加上。

Four things to take with you. 带走四点。

One: AND needs both conditions, so it returns fewer rows. 第一：AND 需要两个条件都成立，所以返回的行更少。

Two: OR needs only one, so it returns more. 第二：OR 只需要一个，所以返回的行更多。

Three: NOT reverses a condition. 第三：NOT 把一个条件取反。

Four: when you mix them, add brackets, because AND binds tighter. 第四：混用时要加括号，因为 AND 结合得更紧。

Now run the tasks below. 现在去做下面的题。