granicus.if.org Git - postgresql/blob - src/test/regress/sql/join.sql

   1 --
   2 -- JOIN
   3 -- Test join clauses
   4 --
   5
   6 CREATE TABLE J1_TBL (
   7   i integer,
   8   j integer,
   9   t text
  10 );
  11
  12 CREATE TABLE J2_TBL (
  13   i integer,
  14   k integer
  15 );
  16
  17 CREATE TABLE JOIN3_TBL (
  18   i integer,
  19   j integer,
  20   y integer
  21 );
  22
  23 CREATE TABLE JOIN4_TBL (
  24   k integer,
  25   z integer
  26 );
  27
  28 INSERT INTO J1_TBL VALUES (1, 3, 'one');
  29 INSERT INTO J1_TBL VALUES (2, 2, 'two');
  30 INSERT INTO J1_TBL VALUES (3, 1, 'three');
  31 INSERT INTO J1_TBL VALUES (4, 0, 'four');
  32
  33 INSERT INTO J2_TBL VALUES (1, -1);
  34 INSERT INTO J2_TBL VALUES (2, 2);
  35 INSERT INTO J2_TBL VALUES (3, -3);
  36 INSERT INTO J2_TBL VALUES (2, 4);
  37
  38 --
  39 -- CORRELATION NAMES
  40 -- Make sure that table/column aliases are supported
  41 -- before diving into more complex join syntax.
  42 --
  43
  44 SELECT '' AS "xxx", *
  45   FROM J1_TBL AS tx;
  46
  47 SELECT '' AS "xxx", *
  48   FROM J1_TBL tx;
  49
  50 SELECT '' AS "xxx", *
  51   FROM J1_TBL AS t1 (a, b, c);
  52
  53 SELECT '' AS "xxx", *
  54   FROM J1_TBL t1 (a, b, c);
  55
  56 SELECT '' AS "xxx", *
  57   FROM J1_TBL t1 (a, b, c), J2_TBL t2 (d, e);
  58
  59 SELECT '' AS "xxx", t1.a, t2.e
  60   FROM J1_TBL t1 (a, b, c), J2_TBL t2 (d, e)
  61   WHERE t1.a = t2.d;
  62
  63
  64 --
  65 -- CROSS JOIN
  66 -- Qualifications are not allowed on cross joins,
  67 -- which degenerate into a standard unqualified inner join.
  68 --
  69
  70 SELECT '' AS "xxx", *
  71   FROM J1_TBL CROSS JOIN J2_TBL;
  72
  73 -- ambiguous column
  74 SELECT '' AS "xxx", i, k, t
  75   FROM J1_TBL CROSS JOIN J2_TBL;
  76
  77 -- resolve previous ambiguity by specifying the table name
  78 SELECT '' AS "xxx", t1.i, k, t
  79   FROM J1_TBL t1 CROSS JOIN J2_TBL t2;
  80
  81 SELECT '' AS "xxx", ii, tt, kk
  82   FROM (J1_TBL CROSS JOIN J2_TBL)
  83     AS tx (ii, jj, tt, ii2, kk);
  84
  85 SELECT '' AS "xxx", tx.ii, tx.jj, tx.kk
  86   FROM (J1_TBL t1 (a, b, c) CROSS JOIN J2_TBL t2 (d, e))
  87     AS tx (ii, jj, tt, ii2, kk);
  88
  89
  90 --
  91 --
  92 -- Inner joins (equi-joins)
  93 --
  94 --
  95
  96 --
  97 -- Inner joins (equi-joins) with USING clause
  98 -- The USING syntax changes the shape of the resulting table
  99 -- by including a column in the USING clause only once in the result.
 100 --
 101
 102 -- Inner equi-join on specified column
 103 SELECT '' AS "xxx", *
 104   FROM J1_TBL INNER JOIN J2_TBL USING (i);
 105
 106 -- Same as above, slightly different syntax
 107 SELECT '' AS "xxx", *
 108   FROM J1_TBL JOIN J2_TBL USING (i);
 109
 110 SELECT '' AS "xxx", *
 111   FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, d) USING (a);
 112
 113 SELECT '' AS "xxx", *
 114   FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, b) USING (b);
 115
 116
 117 --
 118 -- NATURAL JOIN
 119 -- Inner equi-join on all columns with the same name
 120 --
 121
 122 SELECT '' AS "xxx", *
 123   FROM J1_TBL NATURAL JOIN J2_TBL;
 124
 125 SELECT '' AS "xxx", *
 126   FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (a, d);
 127
 128 SELECT '' AS "xxx", *
 129   FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (d, a);
 130
 131 SELECT '' AS "xxx", *
 132   FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (d, a);
 133
 134 -- mismatch number of columns
 135 -- currently, Postgres will fill in with underlying names
 136 SELECT '' AS "xxx", *
 137   FROM J1_TBL t1 (a, b) NATURAL JOIN J2_TBL t2 (a);
 138
 139
 140 --
 141 -- Inner joins (equi-joins)
 142 --
 143
 144 SELECT '' AS "xxx", *
 145   FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.i);
 146
 147 SELECT '' AS "xxx", *
 148   FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.k);
 149
 150 SELECT '' AS "xxx", *
 151   FROM J1_TBL CROSS JOIN J2_TBL;
 152
 153
 154 --
 155 -- Non-equi-joins
 156 --
 157
 158 SELECT '' AS "xxx", *
 159   FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i <= J2_TBL.k);
 160
 161
 162 --
 163 -- Outer joins
 164 --
 165
 166 SELECT '' AS "xxx", *
 167   FROM J1_TBL OUTER JOIN J2_TBL USING (i);
 168
 169 SELECT '' AS "xxx", *
 170   FROM J1_TBL LEFT OUTER JOIN J2_TBL USING (i);
 171
 172 SELECT '' AS "xxx", *
 173   FROM J1_TBL RIGHT OUTER JOIN J2_TBL USING (i);
 174
 175 SELECT '' AS "xxx", *
 176   FROM J1_TBL FULL OUTER JOIN J2_TBL USING (i);
 177
 178
 179 --
 180 -- More complicated constructs
 181 --
 182
 183 --
 184 -- Clean up
 185 --
 186
 187 DROP TABLE J1_TBL;
 188 DROP TABLE J2_TBL;
 189