join.out

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

--
-- JOIN
-- Test JOIN clauses
--
CREATE TABLE J1_TBL (
  i integer,
  j integer,
  t text
);
CREATE TABLE J2_TBL (
  i integer,
  k integer
);
INSERT INTO J1_TBL VALUES (1, 4, 'one');
INSERT INTO J1_TBL VALUES (2, 3, 'two');
INSERT INTO J1_TBL VALUES (3, 2, 'three');
INSERT INTO J1_TBL VALUES (4, 1, 'four');
INSERT INTO J1_TBL VALUES (5, 0, 'five');
INSERT INTO J1_TBL VALUES (6, 6, 'six');
INSERT INTO J1_TBL VALUES (7, 7, 'seven');
INSERT INTO J1_TBL VALUES (8, 8, 'eight');
INSERT INTO J1_TBL VALUES (0, NULL, 'zero');
INSERT INTO J1_TBL VALUES (NULL, NULL, 'null');
INSERT INTO J1_TBL VALUES (NULL, 0, 'zero');
INSERT INTO J2_TBL VALUES (1, -1);
INSERT INTO J2_TBL VALUES (2, 2);
INSERT INTO J2_TBL VALUES (3, -3);
INSERT INTO J2_TBL VALUES (2, 4);
INSERT INTO J2_TBL VALUES (5, -5);
INSERT INTO J2_TBL VALUES (5, -5);
INSERT INTO J2_TBL VALUES (0, NULL);
INSERT INTO J2_TBL VALUES (NULL, NULL);
INSERT INTO J2_TBL VALUES (NULL, 0);
--
-- CORRELATION NAMES
-- Make sure that table/column aliases are supported
-- before diving into more complex join syntax.
--
SELECT '' AS "xxx", *
  FROM J1_TBL AS tx;
 xxx | i | j |   t   
-----+---+---+-------
     | 1 | 4 | one
     | 2 | 3 | two
     | 3 | 2 | three
     | 4 | 1 | four
     | 5 | 0 | five
     | 6 | 6 | six
     | 7 | 7 | seven
     | 8 | 8 | eight
     | 0 |   | zero
     |   |   | null
     |   | 0 | zero
(11 rows)

SELECT '' AS "xxx", *
  FROM J1_TBL tx;
 xxx | i | j |   t   
-----+---+---+-------
     | 1 | 4 | one
     | 2 | 3 | two
     | 3 | 2 | three
     | 4 | 1 | four
     | 5 | 0 | five
     | 6 | 6 | six
     | 7 | 7 | seven
     | 8 | 8 | eight
     | 0 |   | zero
     |   |   | null
     |   | 0 | zero
(11 rows)

SELECT '' AS "xxx", *
  FROM J1_TBL AS t1 (a, b, c);
 xxx | a | b |   c   
-----+---+---+-------
     | 1 | 4 | one
     | 2 | 3 | two
     | 3 | 2 | three
     | 4 | 1 | four
     | 5 | 0 | five
     | 6 | 6 | six
     | 7 | 7 | seven
     | 8 | 8 | eight
     | 0 |   | zero
     |   |   | null
     |   | 0 | zero
(11 rows)

SELECT '' AS "xxx", *
  FROM J1_TBL t1 (a, b, c);
 xxx | a | b |   c   
-----+---+---+-------
     | 1 | 4 | one
     | 2 | 3 | two
     | 3 | 2 | three
     | 4 | 1 | four
     | 5 | 0 | five
     | 6 | 6 | six
     | 7 | 7 | seven
     | 8 | 8 | eight
     | 0 |   | zero
     |   |   | null
     |   | 0 | zero
(11 rows)

SELECT '' AS "xxx", *
  FROM J1_TBL t1 (a, b, c), J2_TBL t2 (d, e);
 xxx | a | b |   c   | d | e  
-----+---+---+-------+---+----
     | 1 | 4 | one   | 1 | -1
     | 2 | 3 | two   | 1 | -1
     | 3 | 2 | three | 1 | -1
     | 4 | 1 | four  | 1 | -1
     | 5 | 0 | five  | 1 | -1
     | 6 | 6 | six   | 1 | -1
     | 7 | 7 | seven | 1 | -1
     | 8 | 8 | eight | 1 | -1
     | 0 |   | zero  | 1 | -1
     |   |   | null  | 1 | -1
     |   | 0 | zero  | 1 | -1
     | 1 | 4 | one   | 2 |  2
     | 2 | 3 | two   | 2 |  2
     | 3 | 2 | three | 2 |  2
     | 4 | 1 | four  | 2 |  2
     | 5 | 0 | five  | 2 |  2
     | 6 | 6 | six   | 2 |  2
     | 7 | 7 | seven | 2 |  2
     | 8 | 8 | eight | 2 |  2
     | 0 |   | zero  | 2 |  2
     |   |   | null  | 2 |  2
     |   | 0 | zero  | 2 |  2
     | 1 | 4 | one   | 3 | -3
     | 2 | 3 | two   | 3 | -3
     | 3 | 2 | three | 3 | -3
     | 4 | 1 | four  | 3 | -3
     | 5 | 0 | five  | 3 | -3
     | 6 | 6 | six   | 3 | -3
     | 7 | 7 | seven | 3 | -3
     | 8 | 8 | eight | 3 | -3
     | 0 |   | zero  | 3 | -3
     |   |   | null  | 3 | -3
     |   | 0 | zero  | 3 | -3
     | 1 | 4 | one   | 2 |  4
     | 2 | 3 | two   | 2 |  4
     | 3 | 2 | three | 2 |  4
     | 4 | 1 | four  | 2 |  4
     | 5 | 0 | five  | 2 |  4
     | 6 | 6 | six   | 2 |  4
     | 7 | 7 | seven | 2 |  4
     | 8 | 8 | eight | 2 |  4
     | 0 |   | zero  | 2 |  4
     |   |   | null  | 2 |  4
     |   | 0 | zero  | 2 |  4
     | 1 | 4 | one   | 5 | -5
     | 2 | 3 | two   | 5 | -5
     | 3 | 2 | three | 5 | -5
     | 4 | 1 | four  | 5 | -5
     | 5 | 0 | five  | 5 | -5
     | 6 | 6 | six   | 5 | -5
     | 7 | 7 | seven | 5 | -5
     | 8 | 8 | eight | 5 | -5
     | 0 |   | zero  | 5 | -5
     |   |   | null  | 5 | -5
     |   | 0 | zero  | 5 | -5
     | 1 | 4 | one   | 5 | -5
     | 2 | 3 | two   | 5 | -5
     | 3 | 2 | three | 5 | -5
     | 4 | 1 | four  | 5 | -5
     | 5 | 0 | five  | 5 | -5
     | 6 | 6 | six   | 5 | -5
     | 7 | 7 | seven | 5 | -5
     | 8 | 8 | eight | 5 | -5
     | 0 |   | zero  | 5 | -5
     |   |   | null  | 5 | -5
     |   | 0 | zero  | 5 | -5
     | 1 | 4 | one   | 0 |   
     | 2 | 3 | two   | 0 |   
     | 3 | 2 | three | 0 |   
     | 4 | 1 | four  | 0 |   
     | 5 | 0 | five  | 0 |   
     | 6 | 6 | six   | 0 |   
     | 7 | 7 | seven | 0 |   
     | 8 | 8 | eight | 0 |   
     | 0 |   | zero  | 0 |   
     |   |   | null  | 0 |   
     |   | 0 | zero  | 0 |   
     | 1 | 4 | one   |   |   
     | 2 | 3 | two   |   |   
     | 3 | 2 | three |   |   
     | 4 | 1 | four  |   |   
     | 5 | 0 | five  |   |   
     | 6 | 6 | six   |   |   
     | 7 | 7 | seven |   |   
     | 8 | 8 | eight |   |   
     | 0 |   | zero  |   |   
     |   |   | null  |   |   
     |   | 0 | zero  |   |   
     | 1 | 4 | one   |   |  0
     | 2 | 3 | two   |   |  0
     | 3 | 2 | three |   |  0
     | 4 | 1 | four  |   |  0
     | 5 | 0 | five  |   |  0
     | 6 | 6 | six   |   |  0
     | 7 | 7 | seven |   |  0
     | 8 | 8 | eight |   |  0
     | 0 |   | zero  |   |  0
     |   |   | null  |   |  0
     |   | 0 | zero  |   |  0
(99 rows)

SELECT '' AS "xxx", t1.a, t2.e
  FROM J1_TBL t1 (a, b, c), J2_TBL t2 (d, e)
  WHERE t1.a = t2.d;
 xxx | a | e  
-----+---+----
     | 0 |   
     | 1 | -1
     | 2 |  2
     | 2 |  4
     | 3 | -3
     | 5 | -5
     | 5 | -5
(7 rows)

--
-- CROSS JOIN
-- Qualifications are not allowed on cross joins,
-- which degenerate into a standard unqualified inner join.
--
SELECT '' AS "xxx", *
  FROM J1_TBL CROSS JOIN J2_TBL;
 xxx | i | j |   t   | i | k  
-----+---+---+-------+---+----
     | 1 | 4 | one   | 1 | -1
     | 2 | 3 | two   | 1 | -1
     | 3 | 2 | three | 1 | -1
     | 4 | 1 | four  | 1 | -1
     | 5 | 0 | five  | 1 | -1
     | 6 | 6 | six   | 1 | -1
     | 7 | 7 | seven | 1 | -1
     | 8 | 8 | eight | 1 | -1
     | 0 |   | zero  | 1 | -1
     |   |   | null  | 1 | -1
     |   | 0 | zero  | 1 | -1
     | 1 | 4 | one   | 2 |  2
     | 2 | 3 | two   | 2 |  2
     | 3 | 2 | three | 2 |  2
     | 4 | 1 | four  | 2 |  2
     | 5 | 0 | five  | 2 |  2
     | 6 | 6 | six   | 2 |  2
     | 7 | 7 | seven | 2 |  2
     | 8 | 8 | eight | 2 |  2
     | 0 |   | zero  | 2 |  2
     |   |   | null  | 2 |  2
     |   | 0 | zero  | 2 |  2
     | 1 | 4 | one   | 3 | -3
     | 2 | 3 | two   | 3 | -3
     | 3 | 2 | three | 3 | -3
     | 4 | 1 | four  | 3 | -3
     | 5 | 0 | five  | 3 | -3
     | 6 | 6 | six   | 3 | -3
     | 7 | 7 | seven | 3 | -3
     | 8 | 8 | eight | 3 | -3
     | 0 |   | zero  | 3 | -3
     |   |   | null  | 3 | -3
     |   | 0 | zero  | 3 | -3
     | 1 | 4 | one   | 2 |  4
     | 2 | 3 | two   | 2 |  4
     | 3 | 2 | three | 2 |  4
     | 4 | 1 | four  | 2 |  4
     | 5 | 0 | five  | 2 |  4
     | 6 | 6 | six   | 2 |  4
     | 7 | 7 | seven | 2 |  4
     | 8 | 8 | eight | 2 |  4
     | 0 |   | zero  | 2 |  4
     |   |   | null  | 2 |  4
     |   | 0 | zero  | 2 |  4
     | 1 | 4 | one   | 5 | -5
     | 2 | 3 | two   | 5 | -5
     | 3 | 2 | three | 5 | -5
     | 4 | 1 | four  | 5 | -5
     | 5 | 0 | five  | 5 | -5
     | 6 | 6 | six   | 5 | -5
     | 7 | 7 | seven | 5 | -5
     | 8 | 8 | eight | 5 | -5
     | 0 |   | zero  | 5 | -5
     |   |   | null  | 5 | -5
     |   | 0 | zero  | 5 | -5
     | 1 | 4 | one   | 5 | -5
     | 2 | 3 | two   | 5 | -5
     | 3 | 2 | three | 5 | -5
     | 4 | 1 | four  | 5 | -5
     | 5 | 0 | five  | 5 | -5
     | 6 | 6 | six   | 5 | -5
     | 7 | 7 | seven | 5 | -5
     | 8 | 8 | eight | 5 | -5
     | 0 |   | zero  | 5 | -5
     |   |   | null  | 5 | -5
     |   | 0 | zero  | 5 | -5
     | 1 | 4 | one   | 0 |   
     | 2 | 3 | two   | 0 |   
     | 3 | 2 | three | 0 |   
     | 4 | 1 | four  | 0 |   
     | 5 | 0 | five  | 0 |   
     | 6 | 6 | six   | 0 |   
     | 7 | 7 | seven | 0 |   
     | 8 | 8 | eight | 0 |   
     | 0 |   | zero  | 0 |   
     |   |   | null  | 0 |   
     |   | 0 | zero  | 0 |   
     | 1 | 4 | one   |   |   
     | 2 | 3 | two   |   |   
     | 3 | 2 | three |   |   
     | 4 | 1 | four  |   |   
     | 5 | 0 | five  |   |   
     | 6 | 6 | six   |   |   
     | 7 | 7 | seven |   |   
     | 8 | 8 | eight |   |   
     | 0 |   | zero  |   |   
     |   |   | null  |   |   
     |   | 0 | zero  |   |   
     | 1 | 4 | one   |   |  0
     | 2 | 3 | two   |   |  0
     | 3 | 2 | three |   |  0
     | 4 | 1 | four  |   |  0
     | 5 | 0 | five  |   |  0
     | 6 | 6 | six   |   |  0
     | 7 | 7 | seven |   |  0
     | 8 | 8 | eight |   |  0
     | 0 |   | zero  |   |  0
     |   |   | null  |   |  0
     |   | 0 | zero  |   |  0
(99 rows)

-- ambiguous column
SELECT '' AS "xxx", i, k, t
  FROM J1_TBL CROSS JOIN J2_TBL;
ERROR:  column reference "i" is ambiguous
-- resolve previous ambiguity by specifying the table name
SELECT '' AS "xxx", t1.i, k, t
  FROM J1_TBL t1 CROSS JOIN J2_TBL t2;
 xxx | i | k  |   t   
-----+---+----+-------
     | 1 | -1 | one
     | 2 | -1 | two
     | 3 | -1 | three
     | 4 | -1 | four
     | 5 | -1 | five
     | 6 | -1 | six
     | 7 | -1 | seven
     | 8 | -1 | eight
     | 0 | -1 | zero
     |   | -1 | null
     |   | -1 | zero
     | 1 |  2 | one
     | 2 |  2 | two
     | 3 |  2 | three
     | 4 |  2 | four
     | 5 |  2 | five
     | 6 |  2 | six
     | 7 |  2 | seven
     | 8 |  2 | eight
     | 0 |  2 | zero
     |   |  2 | null
     |   |  2 | zero
     | 1 | -3 | one
     | 2 | -3 | two
     | 3 | -3 | three
     | 4 | -3 | four
     | 5 | -3 | five
     | 6 | -3 | six
     | 7 | -3 | seven
     | 8 | -3 | eight
     | 0 | -3 | zero
     |   | -3 | null
     |   | -3 | zero
     | 1 |  4 | one
     | 2 |  4 | two
     | 3 |  4 | three
     | 4 |  4 | four
     | 5 |  4 | five
     | 6 |  4 | six
     | 7 |  4 | seven
     | 8 |  4 | eight
     | 0 |  4 | zero
     |   |  4 | null
     |   |  4 | zero
     | 1 | -5 | one
     | 2 | -5 | two
     | 3 | -5 | three
     | 4 | -5 | four
     | 5 | -5 | five
     | 6 | -5 | six
     | 7 | -5 | seven
     | 8 | -5 | eight
     | 0 | -5 | zero
     |   | -5 | null
     |   | -5 | zero
     | 1 | -5 | one
     | 2 | -5 | two
     | 3 | -5 | three
     | 4 | -5 | four
     | 5 | -5 | five
     | 6 | -5 | six
     | 7 | -5 | seven
     | 8 | -5 | eight
     | 0 | -5 | zero
     |   | -5 | null
     |   | -5 | zero
     | 1 |    | one
     | 2 |    | two
     | 3 |    | three
     | 4 |    | four
     | 5 |    | five
     | 6 |    | six
     | 7 |    | seven
     | 8 |    | eight
     | 0 |    | zero
     |   |    | null
     |   |    | zero
     | 1 |    | one
     | 2 |    | two
     | 3 |    | three
     | 4 |    | four
     | 5 |    | five
     | 6 |    | six
     | 7 |    | seven
     | 8 |    | eight
     | 0 |    | zero