# 一、布尔运算推演

已知，

```txt
A∩(B∪C) = (A∩C)∪(A∪C)
A∪(B∩C) = (A∪C)∩(A∪C)
A∩A = A
(A∩B)∩C = A∩B∩C = A∩C∩B
```

那么，

```txt
(A∩B)∪(C∩D) = ((A∩B)∪C) ∩ ((A∩B)∪D)
= ((A∪C)∩(B∪C)) ∩ ((A∪D)∩(B∪D))
= (A∪C) ∩ (B∪C) ∩ (A∪D) ∩ (B∪D)

=> ((A∩B)∪(C∩D)) ∩ (A∪C) ∩ (B∪D) = (A∪C) ∩ (B∪C) ∩ (A∪D) ∩ (B∪D) ∩ (A∪C) ∩ (B∪D)
=> ((A∩B)∪(C∩D)) ∩ (A∪C) ∩ (B∪D) = (A∪C) ∩ (B∪C) ∩ (A∪D) ∩ (B∪D)
```

同理，

```txt
((A∩B)∪(C∩D)) ∩ (A∪C) = (A∪C) ∩ (B∪C) ∩ (A∪D) ∩ (B∪D) ∩ (A∪C)
或，
((A∩B)∪(C∩D)) ∩ (B∪D) = (A∪C) ∩ (B∪C) ∩ (A∪D) ∩ (B∪D) ∩ (B∪D)

=> ((A∩B)∪(C∩D)) ∩ (A∪C) = (A∪C) ∩ (B∪C) ∩ (A∪D) ∩ (B∪D)
或，
((A∩B)∪(C∩D)) ∩ (B∪D) = (A∪C) ∩ (B∪C) ∩ (A∪D) ∩ (B∪D)
```

所以，

```
(A∩B)∪(C∩D) = ((A∩B)∪(C∩D)) ∩ (A∪C) ∩ (B∪D)
= ((A∩B)∪(C∩D)) ∩ (A∪C)
= ((A∩B)∪(C∩D)) ∩ (B∪D)
```

# 二、Q07等价SQL

基于上述布尔等式，Q07，

```sql
……
select
    n1.n_name as supp_nation,
    n2.n_name as cust_nation,
    extract(year from l_shipdate) as l_year,
    l_extendedprice * (1 - l_discount) as volume
from
    supplier, lineitem, orders, customer, nation n1, nation n2
where
    s_suppkey = l_suppkey
    and o_orderkey = l_orderkey
    and c_custkey = o_custkey
    and s_nationkey = n1.n_nationkey
    and c_nationkey = n2.n_nationkey
    and (
        (n1.n_name = 'ROMANIA' and n2.n_name = 'INDONESIA')
        or (n1.n_name = 'INDONESIA' and n2.n_name = 'ROMANIA')
    )
    and l_shipdate between date '1995-01-01' and date '1996-12-31';
……
```

等价改写方法为：

```sql
……
select
    n1.n_name as supp_nation,
    n2.n_name as cust_nation,
    extract(year from l_shipdate) as l_year,
    l_extendedprice * (1 - l_discount) as volume
from
    supplier, lineitem, orders, customer, nation n1, nation n2
where
    s_suppkey = l_suppkey
    and o_orderkey = l_orderkey
    and c_custkey = o_custkey
    and s_nationkey = n1.n_nationkey
    and c_nationkey = n2.n_nationkey
    and (
        (n1.n_name = 'ROMANIA' and n2.n_name = 'INDONESIA')
        or (n1.n_name = 'INDONESIA' and n2.n_name = 'ROMANIA')
    )
    and (n1.n_name = 'ROMANIA' or n1.n_name = 'INDONESIA')
    and (n2.n_name = 'INDONESIA' or n2.n_name = 'ROMANIA')
    and l_shipdate between date '1995-01-01' and date '1996-12-31';
……
```

以上如果在执行计划层优化，则，

1. 相当于增加了两组单表条件。
2. (n1.n\_name = 'ROMANIA' and n2.n\_name = 'INDONESIA')or (n1.n\_name = 'INDONESIA' and n2.n\_name = 'ROMANIA')可以作为复杂Join条件一次执行，避免or算子拆分条件导致两次Join运算。