test/sql/OuterJoin/exp.test006.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 LEFT 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 LEFT JOIN t2 ON t1.f1 BETWEEN 11 AND 14;
  72 ---------------------------------------------------------
  73         t1.f1   t1.f4   t2.f1   t2.f4
  74 ---------------------------------------------------------
  75         10      Bijay   NULL    NULL
  76         11      Jitendr 13      Sanjit
  77         11      Jitendr 16      Sanjay
  78         11      Jitendr 17      Arindam
  79         11      Jitendr 10      Bijay
  80         11      Jitendr 11      Jitendr
  81         12      Nihar   13      Sanjit
  82         12      Nihar   16      Sanjay
  83         12      Nihar   17      Arindam
  84         12      Nihar   10      Bijay
  85         12      Nihar   11      Jitendr
  86         13      Kishor  13      Sanjit
  87         13      Kishor  16      Sanjay
  88         13      Kishor  17      Arindam
  89         13      Kishor  10      Bijay
  90         13      Kishor  11      Jitendr
  91         14      Praba   13      Sanjit
  92         14      Praba   16      Sanjay
  93         14      Praba   17      Arindam
  94         14      Praba   10      Bijay
  95         14      Praba   11      Jitendr
  96
  97 echo SELECT t1.f1, t1.f4, t2.f1, t2.f4 FROM t1 LEFT JOIN t2 ON t1.f4 >= 'K' AND t1.f4 LIKE 'N%' OR t1.f4 LIKE '%a';
  98 ---------------------------------------------------------
  99         t1.f1   t1.f4   t2.f1   t2.f4
 100 ---------------------------------------------------------
 101         10      Bijay   NULL    NULL
 102         11      Jitendr NULL    NULL
 103         12      Nihar   13      Sanjit
 104         12      Nihar   16      Sanjay
 105         12      Nihar   17      Arindam
 106         12      Nihar   10      Bijay
 107         12      Nihar   11      Jitendr
 108         13      Kishor  NULL    NULL
 109         14      Praba   13      Sanjit
 110         14      Praba   16      Sanjay
 111         14      Praba   17      Arindam
 112         14      Praba   10      Bijay
 113         14      Praba   11      Jitendr
 114
 115 Statement Executed
 116 Statement Executed
 117 Statement Executed