test/sql/Join/exp.test019.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 echo select * from t1,t2 where t1.f1 = t2.f1 and t1.f1 = t2.f1;
  22 ---------------------------------------------------------
  23         t1.f1   t1.f2   t2.f1   t2.f2
  24 ---------------------------------------------------------
  25         101     100     101     110
  26         303     333     303     300
  27         505     555     505     100
  28
  29 echo select * from t1,t2 where t1.f1 = t2.f1 and t2.f2 > 100;
  30 ---------------------------------------------------------
  31         t1.f1   t1.f2   t2.f1   t2.f2
  32 ---------------------------------------------------------
  33         101     100     101     110
  34         303     333     303     300
  35
  36 echo select * from t1,t2 where t1.f1 = t2.f1 or t1.f2 > t2.f2;
  37 ---------------------------------------------------------
  38         t1.f1   t1.f2   t2.f1   t2.f2
  39 ---------------------------------------------------------
  40         101     100     101     110
  41         303     333     505     100
  42         303     333     100     101
  43         303     333     303     300
  44         303     333     101     110
  45         505     555     505     100
  46         505     555     100     101
  47         505     555     404     444
  48         505     555     303     300
  49         505     555     101     110
  50
  51 echo select * from t1,t2 where t1.f1 = t2.f1 or t2.f2 > 100;
  52 ---------------------------------------------------------
  53         t1.f1   t1.f2   t2.f1   t2.f2
  54 ---------------------------------------------------------
  55         101     100     100     101
  56         101     100     404     444
  57         101     100     303     300
  58         101     100     101     110
  59         303     333     100     101
  60         303     333     404     444
  61         303     333     303     300
  62         303     333     101     110
  63         505     555     505     100
  64         505     555     100     101
  65         505     555     404     444
  66         505     555     303     300
  67         505     555     101     110
  68
  69 echo select * from t1,t2 where not ( t1.f1 = t2.f1 and t1.f1 = t2.f1 );
  70 ---------------------------------------------------------
  71         t1.f1   t1.f2   t2.f1   t2.f2
  72 ---------------------------------------------------------
  73         101     100     505     100
  74         101     100     100     101
  75         101     100     404     444
  76         101     100     303     300
  77         303     333     505     100
  78         303     333     100     101
  79         303     333     404     444
  80         303     333     101     110
  81         505     555     100     101
  82         505     555     404     444
  83         505     555     303     300
  84         505     555     101     110
  85
  86 echo select * from t1,t2 where ( not t1.f1 = t2.f1 );
  87 ---------------------------------------------------------
  88         t1.f1   t1.f2   t2.f1   t2.f2
  89 ---------------------------------------------------------
  90         101     100     505     100
  91         101     100     100     101
  92         101     100     404     444
  93         101     100     303     300
  94         303     333     505     100
  95         303     333     100     101
  96         303     333     404     444
  97         303     333     101     110
  98         505     555     100     101
  99         505     555     404     444
 100         505     555     303     300
 101         505     555     101     110
 102
 103 echo select * from t1,t2 where t1.f1 = t2.f1 and t1.f2 = 100;
 104 ---------------------------------------------------------
 105         t1.f1   t1.f2   t2.f1   t2.f2
 106 ---------------------------------------------------------
 107         101     100     101     110
 108
 109 echo select * from t1,t2 where t1.f1 > t2.f1 and t2.f2 = 110;
 110 ---------------------------------------------------------
 111         t1.f1   t1.f2   t2.f1   t2.f2
 112 ---------------------------------------------------------
 113         303     333     101     110
 114         505     555     101     110
 115
 116 echo select * from t1,t2 where t1.f1 > t2.f1 and t2.f2 > 100;
 117 ---------------------------------------------------------
 118         t1.f1   t1.f2   t2.f1   t2.f2
 119 ---------------------------------------------------------
 120         101     100     100     101
 121         303     333     100     101
 122         303     333     101     110
 123         505     555     100     101
 124         505     555     404     444
 125         505     555     303     300
 126         505     555     101     110
 127
 128 Statement Executed
 129 Statement Executed