github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/xform/testdata/coster/set (about)

     1  exec-ddl
     2  CREATE TABLE a (k INT PRIMARY KEY, i INT, s STRING, d DECIMAL NOT NULL)
     3  ----
     4  
     5  exec-ddl
     6  CREATE TABLE b (x INT, z INT NOT NULL)
     7  ----
     8  
     9  opt
    10  SELECT k, i FROM a UNION SELECT * FROM b
    11  ----
    12  union
    13   ├── columns: k:8 i:9
    14   ├── left columns: a.k:1 a.i:2
    15   ├── right columns: x:5 z:6
    16   ├── stats: [rows=2000, distinct(8,9)=2000, null(8,9)=0]
    17   ├── cost: 2150.05
    18   ├── key: (8,9)
    19   ├── scan a
    20   │    ├── columns: a.k:1!null a.i:2
    21   │    ├── stats: [rows=1000, distinct(1,2)=1000, null(1,2)=0]
    22   │    ├── cost: 1060.02
    23   │    ├── key: (1)
    24   │    └── fd: (1)-->(2)
    25   └── scan b
    26        ├── columns: x:5 z:6!null
    27        ├── stats: [rows=1000, distinct(5,6)=1000, null(5,6)=0]
    28        └── cost: 1050.02
    29  
    30  opt
    31  SELECT k, i FROM a UNION ALL SELECT * FROM b
    32  ----
    33  union-all
    34   ├── columns: k:8 i:9
    35   ├── left columns: a.k:1 a.i:2
    36   ├── right columns: x:5 z:6
    37   ├── stats: [rows=2000]
    38   ├── cost: 2130.05
    39   ├── scan a
    40   │    ├── columns: a.k:1!null a.i:2
    41   │    ├── stats: [rows=1000]
    42   │    ├── cost: 1060.02
    43   │    ├── key: (1)
    44   │    └── fd: (1)-->(2)
    45   └── scan b
    46        ├── columns: x:5 z:6!null
    47        ├── stats: [rows=1000]
    48        └── cost: 1050.02
    49  
    50  opt
    51  SELECT k, i FROM a INTERSECT SELECT * FROM b
    52  ----
    53  intersect
    54   ├── columns: k:1 i:2
    55   ├── left columns: k:1 i:2
    56   ├── right columns: x:5 z:6
    57   ├── stats: [rows=1000, distinct(1,2)=1000, null(1,2)=0]
    58   ├── cost: 2140.05
    59   ├── key: (1,2)
    60   ├── scan a
    61   │    ├── columns: k:1!null i:2
    62   │    ├── stats: [rows=1000, distinct(1,2)=1000, null(1,2)=0]
    63   │    ├── cost: 1060.02
    64   │    ├── key: (1)
    65   │    └── fd: (1)-->(2)
    66   └── scan b
    67        ├── columns: x:5 z:6!null
    68        ├── stats: [rows=1000, distinct(5,6)=1000, null(5,6)=0]
    69        └── cost: 1050.02
    70  
    71  opt
    72  SELECT k, i FROM a INTERSECT ALL SELECT * FROM b
    73  ----
    74  intersect-all
    75   ├── columns: k:1 i:2
    76   ├── left columns: k:1 i:2
    77   ├── right columns: x:5 z:6
    78   ├── stats: [rows=1000]
    79   ├── cost: 2140.05
    80   ├── scan a
    81   │    ├── columns: k:1!null i:2
    82   │    ├── stats: [rows=1000]
    83   │    ├── cost: 1060.02
    84   │    ├── key: (1)
    85   │    └── fd: (1)-->(2)
    86   └── scan b
    87        ├── columns: x:5 z:6!null
    88        ├── stats: [rows=1000]
    89        └── cost: 1050.02
    90  
    91  opt
    92  SELECT k, i FROM a EXCEPT SELECT * FROM b
    93  ----
    94  except
    95   ├── columns: k:1 i:2
    96   ├── left columns: k:1 i:2
    97   ├── right columns: x:5 z:6
    98   ├── stats: [rows=1000, distinct(1,2)=1000, null(1,2)=0]
    99   ├── cost: 2140.05
   100   ├── key: (1,2)
   101   ├── scan a
   102   │    ├── columns: k:1!null i:2
   103   │    ├── stats: [rows=1000, distinct(1,2)=1000, null(1,2)=0]
   104   │    ├── cost: 1060.02
   105   │    ├── key: (1)
   106   │    └── fd: (1)-->(2)
   107   └── scan b
   108        ├── columns: x:5 z:6!null
   109        ├── stats: [rows=1000, distinct(5,6)=1000, null(5,6)=0]
   110        └── cost: 1050.02
   111  
   112  opt
   113  SELECT k, i FROM a EXCEPT ALL SELECT * FROM b
   114  ----
   115  except-all
   116   ├── columns: k:1 i:2
   117   ├── left columns: k:1 i:2
   118   ├── right columns: x:5 z:6
   119   ├── stats: [rows=1000]
   120   ├── cost: 2140.05
   121   ├── scan a
   122   │    ├── columns: k:1!null i:2
   123   │    ├── stats: [rows=1000]
   124   │    ├── cost: 1060.02
   125   │    ├── key: (1)
   126   │    └── fd: (1)-->(2)
   127   └── scan b
   128        ├── columns: x:5 z:6!null
   129        ├── stats: [rows=1000]
   130        └── cost: 1050.02