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