数据库 的 and 与 or 眼花缭乱的顺序和括号

DB 的 and 与 or 顺序和括号细究

创建测试数据：

drop table IF EXISTS t_and_or;
create table t_and_or(id int,ttype varchar(3),tdate date,mbid varchar(6));
insert into t_and_or values(1,'SYD','2024-05-20','M101'),(2,'SYD','2024-05-20','M102'),(3,'SYD','2024-05-20','M103'),(4,'SYD','2024-06-20','M101'),(5,'SYD','2024-06-20','M102');
select * from t_and_or;
复制

拿PostgreSQL数据库来看看：

-- PG
postgres=# select * from t_and_or;
 id | ttype |   tdate    | mbid 
----+-------+------------+------
  1 | SYD   | 2024-05-20 | M101
  2 | SYD   | 2024-05-20 | M102
  3 | SYD   | 2024-05-20 | M103
  4 | SYD   | 2024-06-20 | M101
  5 | SYD   | 2024-06-20 | M102
(5 rows)
-- and 与 or 的顺序和括号问题
select * from t_and_or where tdate = '2024-05-20' and (ttype = 'SYD' or mbid in ('M101','M102'));
select * from t_and_or where (tdate = '2024-05-20' and ttype = 'SYD') or mbid in ('M101','M102');
select * from t_and_or where tdate = '2024-05-20' and ttype = 'SYD' or mbid in ('M101','M102');
select * from t_and_or where tdate = '2024-05-20' and (ttype = 'SYD' and (mbid = 'M101' or mbid = 'M102'));
select * from t_and_or where tdate = '2024-05-20' and (ttype = 'SYD' and mbid = 'M101' or mbid = 'M102');
复制

按括号进行拆分后逐块分析，and 即交集，or 即并集

结果 = 红框 N 绿框 = 红框 N（黄框 U 蓝框）
结果 = 红框 和 绿框 的交集 =（黄框 和 蓝框 的并集）再与 红框 的交集

postgres=# select * from t_and_or where tdate = '2024-05-20' and (ttype = 'SYD' or mbid in ('M101','M102'));
 id | ttype |   tdate    | mbid 
----+-------+------------+------
  1 | SYD   | 2024-05-20 | M101
  2 | SYD   | 2024-05-20 | M102
  3 | SYD   | 2024-05-20 | M103
(3 rows)
复制

结果 = 绿框 U 黄框 = （红框 N 蓝框）U 黄框
结果 = 绿框 和 黄框 的并集 = （红框 和蓝框 的交集）再和 黄框 的并集

postgres=# select * from t_and_or where (tdate = '2024-05-20' and ttype = 'SYD') or mbid in ('M101','M102');
 id | ttype |   tdate    | mbid 
----+-------+------------+------
  1 | SYD   | 2024-05-20 | M101
  2 | SYD   | 2024-05-20 | M102
  3 | SYD   | 2024-05-20 | M103
  4 | SYD   | 2024-06-20 | M101
  5 | SYD   | 2024-06-20 | M102
(5 rows)

postgres=# select * from t_and_or where tdate = '2024-05-20' and ttype = 'SYD' or mbid in ('M101','M102');
 id | ttype |   tdate    | mbid 
----+-------+------------+------
  1 | SYD   | 2024-05-20 | M101
  2 | SYD   | 2024-05-20 | M102
  3 | SYD   | 2024-05-20 | M103
  4 | SYD   | 2024-06-20 | M101
  5 | SYD   | 2024-06-20 | M102
(5 rows)
复制

结果 = 红框 N 蓝框 = 红框 N （黄框 N 绿框）
结果 = 红框 和 蓝框的交集 =（黄框 和 绿框 的交集）再和 红框 的交集

postgres=# select * from t_and_or where tdate = '2024-05-20' and (ttype = 'SYD' and (mbid = 'M101' or mbid = 'M102'));
 id | ttype |   tdate    | mbid 
----+-------+------------+------
  1 | SYD   | 2024-05-20 | M101
  2 | SYD   | 2024-05-20 | M102
(2 rows)

postgres=# select * from t_and_or where tdate = '2024-05-20' and (ttype = 'SYD' and mbid = 'M101' or mbid = 'M102');
 id | ttype |   tdate    | mbid 
----+-------+------------+------
  1 | SYD   | 2024-05-20 | M101
  2 | SYD   | 2024-05-20 | M102
(2 rows)
复制