test/sql/Join/exp.test086.ksh

   1 Statement Executed
   2 Statement Executed: Rows Affected = 1
   3 Statement Executed: Rows Affected = 1
   4 Statement Executed: Rows Affected = 1
   5 Statement Executed: Rows Affected = 1
   6 Statement Executed: Rows Affected = 1
   7 Statement Executed
   8 Statement Executed: Rows Affected = 1
   9 Statement Executed: Rows Affected = 1
  10 Statement Executed: Rows Affected = 1
  11 Statement Executed: Rows Affected = 1
  12 Statement Executed: Rows Affected = 1
  13 Statement Executed
  14 Statement Executed: Rows Affected = 1
  15 Statement Executed: Rows Affected = 1
  16 Statement Executed: Rows Affected = 1
  17 Statement Executed: Rows Affected = 1
  18 Statement Executed: Rows Affected = 1
  19 echo select * from t1;
  20 ---------------------------------------------------------
  21         t1.f1   t1.f2   t1.f3   t1.f4   t1.f5   t1.f6   t1.f7   t1.f8   t1.f9
  22 ---------------------------------------------------------
  23         10      100     1000    Bijay   1000.000000     100000.000000   2009/3/1        11:59:59.0      2009/3/1 11:59:59.0
  24         11      110     1100    Jitendr 2000.000000     200000.000000   2009/3/2        11:59:58.0      2009/3/2 11:59:59.0
  25         12      120     1000    Nihar   3000.000000     300000.000000   2009/3/3        11:59:57.0      2009/3/3 11:59:59.0
  26         13      130     1500    Kishor  4000.000000     400000.000000   2009/3/4        11:59:59.0      2009/3/4 11:59:59.0
  27         14      100     1000    Praba   5000.000000     500000.000000   2009/3/1        11:59:58.0      2009/3/1 11:59:59.0
  28
  29 echo select * from t2;
  30 ---------------------------------------------------------
  31         t2.f1   t2.f2   t2.f3   t2.f4   t2.f5   t2.f6   t2.f7   t2.f8   t2.f9
  32 ---------------------------------------------------------
  33         13      110     1100    Sanjit  6000.000000     600000.000000   2009/3/2        11:59:57.0      2009/3/2 11:59:59.0
  34         16      120     1000    Sanjay  6500.000000     700000.000000   2009/3/3        11:59:59.0      2009/3/3 11:59:59.0
  35         17      130     1100    Arindam 8000.000000     800000.000000   2009/3/4        11:59:58.0      2009/3/4 11:59:59.0
  36         10      100     1000    Bijay   9000.000000     900000.000000   2009/3/1        11:59:57.0      2009/3/1 11:59:59.0
  37         11      110     1100    Jitendr 1000.000000     100000.000000   2009/3/2        11:59:58.0      2009/3/2 11:59:59.0
  38
  39 echo select * from t3;
  40 ---------------------------------------------------------
  41         t3.f1   t3.f2   t3.f3   t3.f4   t3.f5   t3.f6   t3.f7   t3.f8   t3.f9
  42 ---------------------------------------------------------
  43         12      120     1000    Nihar   2000.000000     200000.000000   2009/3/3        11:59:56.0      2009/3/3 11:59:59.0
  44         13      130     1100    Kishor  3000.000000     300000.000000   2009/3/5        11:59:56.0      2009/3/5 11:59:59.0
  45         14      100     1000    Praba   4000.000000     400000.000000   2009/3/1        11:59:55.0      2009/3/4 11:59:59.0
  46         15      110     1100    Sanjit  5000.000000     500000.000000   2009/3/2        11:59:56.0      2009/3/1 11:59:59.0
  47         16      120     1000    Sanjay  6000.000000     600000.000000   2009/3/3        11:59:55.0      2009/3/3 11:59:59.0
  48
  49 echo SELECT t1.f1, t1.f4, t2.f1, t2.f4 FROM t1 INNER JOIN t2 ON t1.f1 IN(12,10,14) OR t1.f1=t2.f1;
  50 ---------------------------------------------------------
  51         t1.f1   t1.f4   t2.f1   t2.f4
  52 ---------------------------------------------------------
  53         10      Bijay   13      Sanjit
  54         10      Bijay   16      Sanjay
  55         10      Bijay   17      Arindam
  56         10      Bijay   10      Bijay
  57         10      Bijay   11      Jitendr
  58         11      Jitendr 11      Jitendr
  59         12      Nihar   13      Sanjit
  60         12      Nihar   16      Sanjay
  61         12      Nihar   17      Arindam
  62         12      Nihar   10      Bijay
  63         12      Nihar   11      Jitendr
  64         13      Kishor  13      Sanjit
  65         14      Praba   13      Sanjit
  66         14      Praba   16      Sanjay
  67         14      Praba   17      Arindam
  68         14      Praba   10      Bijay
  69         14      Praba   11      Jitendr
  70
  71 echo SELECT t1.f1, t1.f4, t2.f1, t2.f4 FROM t1 INNER JOIN t2 ON t1.f1 BETWEEN 11 AND 14;
  72 ---------------------------------------------------------
  73         t1.f1   t1.f4   t2.f1   t2.f4
  74 ---------------------------------------------------------
  75         11      Jitendr 13      Sanjit
  76         11      Jitendr 16      Sanjay
  77         11      Jitendr 17      Arindam
  78         11      Jitendr 10      Bijay
  79         11      Jitendr 11      Jitendr
  80         12      Nihar   13      Sanjit
  81         12      Nihar   16      Sanjay
  82         12      Nihar   17      Arindam
  83         12      Nihar   10      Bijay
  84         12      Nihar   11      Jitendr
  85         13      Kishor  13      Sanjit
  86         13      Kishor  16      Sanjay
  87         13      Kishor  17      Arindam
  88         13      Kishor  10      Bijay
  89         13      Kishor  11      Jitendr
  90         14      Praba   13      Sanjit
  91         14      Praba   16      Sanjay
  92         14      Praba   17      Arindam
  93         14      Praba   10      Bijay
  94         14      Praba   11      Jitendr
  95
  96 echo SELECT t1.f1, t1.f4, t2.f1, t2.f4 FROM t1 INNER JOIN t2 ON t1.f4 >= 'K' AND t1.f4 LIKE 'N%' OR t1.f4 LIKE '%a';
  97 ---------------------------------------------------------
  98         t1.f1   t1.f4   t2.f1   t2.f4
  99 ---------------------------------------------------------
 100         12      Nihar   13      Sanjit
 101         12      Nihar   16      Sanjay
 102         12      Nihar   17      Arindam
 103         12      Nihar   10      Bijay
 104         12      Nihar   11      Jitendr
 105         14      Praba   13      Sanjit
 106         14      Praba   16      Sanjay
 107         14      Praba   17      Arindam
 108         14      Praba   10      Bijay
 109         14      Praba   11      Jitendr
 110
 111 Statement Executed
 112 Statement Executed
 113 Statement Executed