逻辑条件
逻辑条件组合两个条件的结果以生成一个结果。所有逻辑条件都是具有布尔值返回类型的二进制运算符。
语法
expression { AND | OR } expression NOT expression
逻辑条件使用具有三个值的布尔逻辑,其中 null 值表示未知关系。下表描述逻辑条件的结果,其中 E1
和 E2
表示表达式:
E1 | E2 | E1 AND E2 | E1 OR E2 | NOT E2 |
---|---|---|---|---|
TRUE | TRUE | TRUE | TRUE | FALSE |
TRUE | FALSE | FALSE | TRUE | TRUE |
TRUE | UNKNOWN | UNKNOWN | TRUE | UNKNOWN |
FALSE | TRUE | FALSE | TRUE | |
FALSE | FALSE | FALSE | FALSE | |
FALSE | UNKNOWN | FALSE | UNKNOWN | |
UNKNOWN | TRUE | UNKNOWN | TRUE | |
UNKNOWN | FALSE | FALSE | UNKNOWN | |
UNKNOWN | UNKNOWN | UNKNOWN | UNKNOWN |
NOT 运算符先于 AND 计算,而 AND 运算符先于 OR 运算符计算。使用的任何圆括号可优先于此默认计算顺序。
示例
以下示例将返回 USERS 表中用户同时喜欢拉斯维加斯和运动的 USERID 和 USERNAME:
select userid, username from users where likevegas = 1 and likesports = 1 order by userid; userid | username --------+---------- 1 | JSG99FHE 67 | TWU10MZT 87 | DUF19VXU 92 | HYP36WEQ 109 | FPL38HZK 120 | DMJ24GUZ 123 | QZR22XGQ 130 | ZQC82ALK 133 | LBN45WCH 144 | UCX04JKN 165 | TEY68OEB 169 | AYQ83HGO 184 | TVX65AZX ... (2128 rows)
下一个示例将返回 USERS 表中用户喜欢拉斯维加斯或运动或同时喜欢这二者的 USERID 和 USERNAME。此查询将返回上例中的所有输出以及只喜欢拉斯维加斯或运动的用户。
select userid, username from users where likevegas = 1 or likesports = 1 order by userid; userid | username --------+---------- 1 | JSG99FHE 2 | PGL08LJI 3 | IFT66TXU 5 | AEB55QTM 6 | NDQ15VBM 9 | MSD36KVR 10 | WKW41AIW 13 | QTF33MCG 15 | OWU78MTR 16 | ZMG93CDD 22 | RHT62AGI 27 | KOY02CVE 29 | HUH27PKK ... (18968 rows)
以下查询使用圆括号将 OR
条件括起来以查找纽约或加利福尼亚演出过 Macbeth 的场地:
select distinct venuename, venuecity from venue join event on venue.venueid=event.venueid where (venuestate = 'NY' or venuestate = 'CA') and eventname='Macbeth' order by 2,1; venuename | venuecity ----------------------------------------+--------------- Geffen Playhouse | Los Angeles Greek Theatre | Los Angeles Royce Hall | Los Angeles American Airlines Theatre | New York City August Wilson Theatre | New York City Belasco Theatre | New York City Bernard B. Jacobs Theatre | New York City ...
删除此示例中的圆括号将更改逻辑和查询的结果。
以下示例使用 NOT
运算符:
select * from category where not catid=1 order by 1; catid | catgroup | catname | catdesc -------+----------+-----------+-------------------------------------------- 2 | Sports | NHL | National Hockey League 3 | Sports | NFL | National Football League 4 | Sports | NBA | National Basketball Association 5 | Sports | MLS | Major League Soccer ...
以下示例使用一个 NOT
条件并后跟一个 AND
条件:
select * from category where (not catid=1) and catgroup='Sports' order by catid; catid | catgroup | catname | catdesc -------+----------+---------+--------------------------------- 2 | Sports | NHL | National Hockey League 3 | Sports | NFL | National Football League 4 | Sports | NBA | National Basketball Association 5 | Sports | MLS | Major League Soccer (4 rows)