test/sql/Join/exp.test078.ksh

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