test/sql/Join/exp.test020.ksh

   1 echo create table t1(f1 int,f2 int);
   2 Statement Executed
   3 echo create table t2(f1 int,f2 int);
   4 Statement Executed
   5 echo insert into t1 values(101,100);
   6 Statement Executed: Rows Affected = 1
   7 echo insert into t1 values(303,333);
   8 Statement Executed: Rows Affected = 1
   9 echo insert into t1 values(505,555);
  10 Statement Executed: Rows Affected = 1
  11 echo insert into t2 values(505,100);
  12 Statement Executed: Rows Affected = 1
  13 echo insert into t2 values(100,101);
  14 Statement Executed: Rows Affected = 1
  15 echo insert into t2 values(404,444);
  16 Statement Executed: Rows Affected = 1
  17 echo insert into t2 values(303,300);
  18 Statement Executed: Rows Affected = 1
  19 echo insert into t2 values(101,110);
  20 Statement Executed: Rows Affected = 1
  21 Statement Executed
  22 Statement Executed
  23 Statement Executed
  24 Statement Executed
  25 echo select * from t1,t2 where t1.f1 = t2.f1 and t1.f1 = t2.f1;
  26 ---------------------------------------------------------
  27         t1.f1   t1.f2   t2.f1   t2.f2
  28 ---------------------------------------------------------
  29         101     100     101     110
  30         303     333     303     300
  31         505     555     505     100
  32
  33 echo select * from t1,t2 where t1.f1 = t2.f1 and t2.f2 > 100;
  34 ---------------------------------------------------------
  35         t1.f1   t1.f2   t2.f1   t2.f2
  36 ---------------------------------------------------------
  37         101     100     101     110
  38         303     333     303     300
  39
  40 echo select * from t1,t2 where t1.f1 = t2.f1 or t1.f2 > t2.f2;
  41 ---------------------------------------------------------
  42         t1.f1   t1.f2   t2.f1   t2.f2
  43 ---------------------------------------------------------
  44         101     100     101     110
  45         303     333     505     100
  46         303     333     100     101
  47         303     333     303     300
  48         303     333     101     110
  49         505     555     505     100
  50         505     555     100     101
  51         505     555     404     444
  52         505     555     303     300
  53         505     555     101     110
  54
  55 echo select * from t1,t2 where t1.f1 = t2.f1 or t2.f2 > 100;
  56 ---------------------------------------------------------
  57         t1.f1   t1.f2   t2.f1   t2.f2
  58 ---------------------------------------------------------
  59         101     100     100     101
  60         101     100     404     444
  61         101     100     303     300
  62         101     100     101     110
  63         303     333     100     101
  64         303     333     404     444
  65         303     333     303     300
  66         303     333     101     110
  67         505     555     505     100
  68         505     555     100     101
  69         505     555     404     444
  70         505     555     303     300
  71         505     555     101     110
  72
  73 echo select * from t1,t2 where not ( t1.f1 = t2.f1 and t1.f1 = t2.f1 );
  74 ---------------------------------------------------------
  75         t1.f1   t1.f2   t2.f1   t2.f2
  76 ---------------------------------------------------------
  77         101     100     505     100
  78         101     100     100     101
  79         101     100     404     444
  80         101     100     303     300
  81         303     333     505     100
  82         303     333     100     101
  83         303     333     404     444
  84         303     333     101     110
  85         505     555     100     101
  86         505     555     404     444
  87         505     555     303     300
  88         505     555     101     110
  89
  90 echo select * from t1,t2 where ( not t1.f1 = t2.f1 );
  91 ---------------------------------------------------------
  92         t1.f1   t1.f2   t2.f1   t2.f2
  93 ---------------------------------------------------------
  94         101     100     505     100
  95         101     100     100     101
  96         101     100     404     444
  97         101     100     303     300
  98         303     333     505     100
  99         303     333     100     101
 100         303     333     404     444
 101         303     333     101     110
 102         505     555     100     101
 103         505     555     404     444
 104         505     555     303     300
 105         505     555     101     110
 106
 107 echo select * from t1,t2 where t1.f1 = t2.f1 and t1.f2 = 100;
 108 ---------------------------------------------------------
 109         t1.f1   t1.f2   t2.f1   t2.f2
 110 ---------------------------------------------------------
 111         101     100     101     110
 112
 113 echo select * from t1,t2 where t1.f1 > t2.f1 and t2.f2 = 110;
 114 ---------------------------------------------------------
 115         t1.f1   t1.f2   t2.f1   t2.f2
 116 ---------------------------------------------------------
 117         303     333     101     110
 118         505     555     101     110
 119
 120 echo select * from t1,t2 where t1.f1 > t2.f1 and t2.f2 > 100;
 121 ---------------------------------------------------------
 122         t1.f1   t1.f2   t2.f1   t2.f2
 123 ---------------------------------------------------------
 124         101     100     100     101
 125         303     333     100     101
 126         303     333     101     110
 127         505     555     100     101
 128         505     555     404     444
 129         505     555     303     300
 130         505     555     101     110
 131
 132 Statement Executed
 133 Statement Executed