test/sql/Join/exp.test079.ksh

   1 echo create table t1(f1 int,f2 int);
   2 Statement Executed
   3 echo create index idx1 on t1(f1) tree;
   4 Statement Executed
   5 echo create table t2(f1 int,f2 int);
   6 Statement Executed
   7 echo create index idx2 on t2(f2,f1) hash;
   8 Statement Executed
   9 echo insert into t1 values(101,100);
  10 Statement Executed: Rows Affected = 1
  11 echo insert into t1 values(303,333);
  12 Statement Executed: Rows Affected = 1
  13 echo insert into t1 values(505,555);
  14 Statement Executed: Rows Affected = 1
  15 echo insert into t2 values(505,100);
  16 Statement Executed: Rows Affected = 1
  17 echo insert into t2 values(100,101);
  18 Statement Executed: Rows Affected = 1
  19 echo insert into t2 values(404,444);
  20 Statement Executed: Rows Affected = 1
  21 echo insert into t2 values(303,333);
  22 Statement Executed: Rows Affected = 1
  23 echo insert into t2 values(101,100);
  24 Statement Executed: Rows Affected = 1
  25 echo select * from t1;
  26 ---------------------------------------------------------
  27         t1.f1   t1.f2
  28 ---------------------------------------------------------
  29         101     100
  30         303     333
  31         505     555
  32
  33 echo select * from t2;
  34 ---------------------------------------------------------
  35         t2.f1   t2.f2
  36 ---------------------------------------------------------
  37         505     100
  38         100     101
  39         404     444
  40         303     333
  41         101     100
  42
  43 echo select * from t1,t2;
  44 ---------------------------------------------------------
  45         t1.f1   t1.f2   t2.f1   t2.f2
  46 ---------------------------------------------------------
  47         101     100     505     100
  48         101     100     100     101
  49         101     100     404     444
  50         101     100     303     333
  51         101     100     101     100
  52         303     333     505     100
  53         303     333     100     101
  54         303     333     404     444
  55         303     333     303     333
  56         303     333     101     100
  57         505     555     505     100
  58         505     555     100     101
  59         505     555     404     444
  60         505     555     303     333
  61         505     555     101     100
  62
  63 echo select * from t1,t2 where t1.f1=t2.f1 and t1.f2=t2.f2;
  64 ---------------------------------------------------------
  65         t1.f1   t1.f2   t2.f1   t2.f2
  66 ---------------------------------------------------------
  67         101     100     101     100
  68         303     333     303     333
  69
  70 echo select * from t1,t2 where t1.f2=t2.f2 and t1.f1=t2.f1;
  71 ---------------------------------------------------------
  72         t1.f1   t1.f2   t2.f1   t2.f2
  73 ---------------------------------------------------------
  74         101     100     101     100
  75         303     333     303     333
  76
  77 echo select * from t1,t2 where t1.f1>t2.f1 and t1.f2>t2.f2;
  78 ---------------------------------------------------------
  79         t1.f1   t1.f2   t2.f1   t2.f2
  80 ---------------------------------------------------------
  81         303     333     100     101
  82         303     333     101     100
  83         505     555     100     101
  84         505     555     404     444
  85         505     555     303     333
  86         505     555     101     100
  87
  88 echo select * from t1,t2 where t1.f2<t2.f2 and t1.f1<=t2.f1;
  89 ---------------------------------------------------------
  90         t1.f1   t1.f2   t2.f1   t2.f2
  91 ---------------------------------------------------------
  92         101     100     404     444
  93         101     100     303     333
  94         303     333     404     444
  95
  96 echo select * from t1,t2 where t1.f1=t2.f1 or t1.f2=t2.f2;
  97 ---------------------------------------------------------
  98         t1.f1   t1.f2   t2.f1   t2.f2
  99 ---------------------------------------------------------
 100         101     100     505     100
 101         101     100     101     100
 102         303     333     303     333
 103         505     555     505     100
 104
 105 echo select * from t1,t2 where t1.f2=t2.f2 or t1.f1=t2.f1;
 106 ---------------------------------------------------------
 107         t1.f1   t1.f2   t2.f1   t2.f2
 108 ---------------------------------------------------------
 109         101     100     505     100
 110         101     100     101     100
 111         303     333     303     333
 112         505     555     505     100
 113
 114 echo select * from t1,t2 where t1.f1>t2.f1 or t1.f2>=t2.f2;
 115 ---------------------------------------------------------
 116         t1.f1   t1.f2   t2.f1   t2.f2
 117 ---------------------------------------------------------
 118         101     100     505     100
 119         101     100     100     101
 120         101     100     101     100
 121         303     333     505     100
 122         303     333     100     101
 123         303     333     303     333
 124         303     333     101     100
 125         505     555     505     100
 126         505     555     100     101
 127         505     555     404     444
 128         505     555     303     333
 129         505     555     101     100
 130
 131 echo select * from t1,t2 where t1.f2<t2.f2 or t1.f1<t2.f1;
 132 ---------------------------------------------------------
 133         t1.f1   t1.f2   t2.f1   t2.f2
 134 ---------------------------------------------------------
 135         101     100     505     100
 136         101     100     100     101
 137         101     100     404     444
 138         101     100     303     333
 139         303     333     505     100
 140         303     333     404     444
 141
 142 echo select * from t1,t2 where t1.f1>t2.f1 or t1.f2>t2.f2;
 143 ---------------------------------------------------------
 144         t1.f1   t1.f2   t2.f1   t2.f2
 145 ---------------------------------------------------------
 146         101     100     100     101
 147         303     333     505     100
 148         303     333     100     101
 149         303     333     101     100
 150         505     555     505     100
 151         505     555     100     101
 152         505     555     404     444
 153         505     555     303     333
 154         505     555     101     100
 155
 156 echo select * from t1,t2 where t1.f1=t2.f1 and not(t1.f2=t2.f2);
 157 ---------------------------------------------------------
 158         t1.f1   t1.f2   t2.f1   t2.f2
 159 ---------------------------------------------------------
 160         505     555     505     100
 161
 162 echo select * from t1,t2 where t1.f2=t2.f2 and not(t1.f1=t2.f1);
 163 ---------------------------------------------------------
 164         t1.f1   t1.f2   t2.f1   t2.f2
 165 ---------------------------------------------------------
 166         101     100     505     100
 167
 168 echo select * from t1,t2 where not(t1.f1>t2.f1) and t1.f2>t2.f2;
 169 ---------------------------------------------------------
 170         t1.f1   t1.f2   t2.f1   t2.f2
 171 ---------------------------------------------------------
 172         303     333     505     100
 173         505     555     505     100
 174
 175 echo select * from t1,t2 where not(t1.f2<t2.f2) and not(t1.f1<t2.f1);
 176 ---------------------------------------------------------
 177         t1.f1   t1.f2   t2.f1   t2.f2
 178 ---------------------------------------------------------
 179         101     100     101     100
 180         303     333     100     101
 181         303     333     303     333
 182         303     333     101     100
 183         505     555     505     100
 184         505     555     100     101
 185         505     555     404     444
 186         505     555     303     333
 187         505     555     101     100
 188
 189 echo select * from t1,t2 where not(t1.f1=t2.f1) or not(t1.f2=t2.f2);
 190 ---------------------------------------------------------
 191         t1.f1   t1.f2   t2.f1   t2.f2
 192 ---------------------------------------------------------
 193         101     100     505     100
 194         101     100     100     101
 195         101     100     404     444
 196         101     100     303     333
 197         303     333     505     100
 198         303     333     100     101
 199         303     333     404     444
 200         303     333     101     100
 201         505     555     505     100
 202         505     555     100     101
 203         505     555     404     444
 204         505     555     303     333
 205         505     555     101     100
 206
 207 Statement Executed
 208 Statement Executed