join.out

PostgreSQL7.4.6 for Linux

     | 3 | 2 | three | 0 |    | 2 |  4
     | 4 | 1 | four  | 0 |    | 2 |  4
     | 5 | 0 | five  | 0 |    | 2 |  4
     | 6 | 6 | six   | 0 |    | 2 |  4
     | 7 | 7 | seven | 0 |    | 2 |  4
     | 8 | 8 | eight | 0 |    | 2 |  4
     | 0 |   | zero  | 0 |    | 2 |  4
     |   |   | null  | 0 |    | 2 |  4
     |   | 0 | zero  | 0 |    | 2 |  4
     | 1 | 4 | one   | 0 |    | 5 | -5
     | 2 | 3 | two   | 0 |    | 5 | -5
     | 3 | 2 | three | 0 |    | 5 | -5
     | 4 | 1 | four  | 0 |    | 5 | -5
     | 5 | 0 | five  | 0 |    | 5 | -5
     | 6 | 6 | six   | 0 |    | 5 | -5
     | 7 | 7 | seven | 0 |    | 5 | -5
     | 8 | 8 | eight | 0 |    | 5 | -5
     | 0 |   | zero  | 0 |    | 5 | -5
     |   |   | null  | 0 |    | 5 | -5
     |   | 0 | zero  | 0 |    | 5 | -5
     | 1 | 4 | one   | 0 |    | 5 | -5
     | 2 | 3 | two   | 0 |    | 5 | -5
     | 3 | 2 | three | 0 |    | 5 | -5
     | 4 | 1 | four  | 0 |    | 5 | -5
     | 5 | 0 | five  | 0 |    | 5 | -5
     | 6 | 6 | six   | 0 |    | 5 | -5
     | 7 | 7 | seven | 0 |    | 5 | -5
     | 8 | 8 | eight | 0 |    | 5 | -5
     | 0 |   | zero  | 0 |    | 5 | -5
     |   |   | null  | 0 |    | 5 | -5
     |   | 0 | zero  | 0 |    | 5 | -5
     | 1 | 4 | one   | 0 |    | 0 |   
     | 2 | 3 | two   | 0 |    | 0 |   
     | 3 | 2 | three | 0 |    | 0 |   
     | 4 | 1 | four  | 0 |    | 0 |   
     | 5 | 0 | five  | 0 |    | 0 |   
     | 6 | 6 | six   | 0 |    | 0 |   
     | 7 | 7 | seven | 0 |    | 0 |   
     | 8 | 8 | eight | 0 |    | 0 |   
     | 0 |   | zero  | 0 |    | 0 |   
     |   |   | null  | 0 |    | 0 |   
     |   | 0 | zero  | 0 |    | 0 |   
     | 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   | 0 |    |   |  0
     | 2 | 3 | two   | 0 |    |   |  0
     | 3 | 2 | three | 0 |    |   |  0
     | 4 | 1 | four  | 0 |    |   |  0
     | 5 | 0 | five  | 0 |    |   |  0
     | 6 | 6 | six   | 0 |    |   |  0
     | 7 | 7 | seven | 0 |    |   |  0
     | 8 | 8 | eight | 0 |    |   |  0
     | 0 |   | zero  | 0 |    |   |  0
     |   |   | null  | 0 |    |   |  0
     |   | 0 | zero  | 0 |    |   |  0
     | 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
     | 1 | 4 | one   |   |  0 | 1 | -1
     | 2 | 3 | two   |   |  0 | 1 | -1
     | 3 | 2 | three |   |  0 | 1 | -1
     | 4 | 1 | four  |   |  0 | 1 | -1
     | 5 | 0 | five  |   |  0 | 1 | -1
     | 6 | 6 | six   |   |  0 | 1 | -1
     | 7 | 7 | seven |   |  0 | 1 | -1
     | 8 | 8 | eight |   |  0 | 1 | -1
     | 0 |   | zero  |   |  0 | 1 | -1
     |   |   | null  |   |  0 | 1 | -1
     |   | 0 | zero  |   |  0 | 1 | -1
     | 1 | 4 | one   |   |  0 | 2 |  2
     | 2 | 3 | two   |   |  0 | 2 |  2
     | 3 | 2 | three |   |  0 | 2 |  2
     | 4 | 1 | four  |   |  0 | 2 |  2
     | 5 | 0 | five  |   |  0 | 2 |  2
     | 6 | 6 | six   |   |  0 | 2 |  2
     | 7 | 7 | seven |   |  0 | 2 |  2
     | 8 | 8 | eight |   |  0 | 2 |  2
     | 0 |   | zero  |   |  0 | 2 |  2
     |   |   | null  |   |  0 | 2 |  2
     |   | 0 | zero  |   |  0 | 2 |  2
     | 1 | 4 | one   |   |  0 | 3 | -3
     | 2 | 3 | two   |   |  0 | 3 | -3
     | 3 | 2 | three |   |  0 | 3 | -3
     | 4 | 1 | four  |   |  0 | 3 | -3
     | 5 | 0 | five  |   |  0 | 3 | -3
     | 6 | 6 | six   |   |  0 | 3 | -3
     | 7 | 7 | seven |   |  0 | 3 | -3
     | 8 | 8 | eight |   |  0 | 3 | -3
     | 0 |   | zero  |   |  0 | 3 | -3
     |   |   | null  |   |  0 | 3 | -3
     |   | 0 | zero  |   |  0 | 3 | -3
     | 1 | 4 | one   |   |  0 | 2 |  4
     | 2 | 3 | two   |   |  0 | 2 |  4
     | 3 | 2 | three |   |  0 | 2 |  4
     | 4 | 1 | four  |   |  0 | 2 |  4
     | 5 | 0 | five  |   |  0 | 2 |  4
     | 6 | 6 | six   |   |  0 | 2 |  4
     | 7 | 7 | seven |   |  0 | 2 |  4
     | 8 | 8 | eight |   |  0 | 2 |  4
     | 0 |   | zero  |   |  0 | 2 |  4
     |   |   | null  |   |  0 | 2 |  4
     |   | 0 | zero  |   |  0 | 2 |  4
     | 1 | 4 | one   |   |  0 | 5 | -5
     | 2 | 3 | two   |   |  0 | 5 | -5
     | 3 | 2 | three |   |  0 | 5 | -5
     | 4 | 1 | four  |   |  0 | 5 | -5
     | 5 | 0 | five  |   |  0 | 5 | -5
     | 6 | 6 | six   |   |  0 | 5 | -5
     | 7 | 7 | seven |   |  0 | 5 | -5
     | 8 | 8 | eight |   |  0 | 5 | -5
     | 0 |   | zero  |   |  0 | 5 | -5
     |   |   | null  |   |  0 | 5 | -5
     |   | 0 | zero  |   |  0 | 5 | -5
     | 1 | 4 | one   |   |  0 | 5 | -5
     | 2 | 3 | two   |   |  0 | 5 | -5
     | 3 | 2 | three |   |  0 | 5 | -5
     | 4 | 1 | four  |   |  0 | 5 | -5
     | 5 | 0 | five  |   |  0 | 5 | -5
     | 6 | 6 | six   |   |  0 | 5 | -5
     | 7 | 7 | seven |   |  0 | 5 | -5
     | 8 | 8 | eight |   |  0 | 5 | -5
     | 0 |   | zero  |   |  0 | 5 | -5
     |   |   | null  |   |  0 | 5 | -5
     |   | 0 | zero  |   |  0 | 5 | -5
     | 1 | 4 | one   |   |  0 | 0 |   
     | 2 | 3 | two   |   |  0 | 0 |   
     | 3 | 2 | three |   |  0 | 0 |   
     | 4 | 1 | four  |   |  0 | 0 |   
     | 5 | 0 | five  |   |  0 | 0 |   
     | 6 | 6 | six   |   |  0 | 0 |   
     | 7 | 7 | seven |   |  0 | 0 |   
     | 8 | 8 | eight |   |  0 | 0 |   
     | 0 |   | zero  |   |  0 | 0 |   
     |   |   | null  |   |  0 | 0 |   
     |   | 0 | zero  |   |  0 | 0 |   
     | 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   |   |  0 |   |  0
     | 2 | 3 | two   |   |  0 |   |  0
     | 3 | 2 | three |   |  0 |   |  0
     | 4 | 1 | four  |   |  0 |   |  0
     | 5 | 0 | five  |   |  0 |   |  0
     | 6 | 6 | six   |   |  0 |   |  0
     | 7 | 7 | seven |   |  0 |   |  0
     | 8 | 8 | eight |   |  0 |   |  0
     | 0 |   | zero  |   |  0 |   |  0
     |   |   | null  |   |  0 |   |  0
     |   | 0 | zero  |   |  0 |   |  0
(891 rows)

--
--
-- Inner joins (equi-joins)
--
--
--
-- Inner joins (equi-joins) with USING clause
-- The USING syntax changes the shape of the resulting table
-- by including a column in the USING clause only once in the result.
--
-- Inner equi-join on specified column
SELECT '' AS "xxx", *
  FROM J1_TBL INNER JOIN J2_TBL USING (i);
 xxx | i | j |   t   | k  
-----+---+---+-------+----
     | 0 |   | zero  |   
     | 1 | 4 | one   | -1
     | 2 | 3 | two   |  2
     | 2 | 3 | two   |  4
     | 3 | 2 | three | -3
     | 5 | 0 | five  | -5
     | 5 | 0 | five  | -5
(7 rows)

-- Same as above, slightly different syntax
SELECT '' AS "xxx", *
  FROM J1_TBL JOIN J2_TBL USING (i);
 xxx | i | j |   t   | k  
-----+---+---+-------+----
     | 0 |   | zero  |   
     | 1 | 4 | one   | -1
     | 2 | 3 | two   |  2
     | 2 | 3 | two   |  4
     | 3 | 2 | three | -3
     | 5 | 0 | five  | -5
     | 5 | 0 | five  | -5
(7 rows)

SELECT '' AS "xxx", *
  FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, d) USING (a)
  ORDER BY a, d;
 xxx | a | b |   c   | d  
-----+---+---+-------+----
     | 0 |   | zero  |   
     | 1 | 4 | one   | -1
     | 2 | 3 | two   |  2
     | 2 | 3 | two   |  4
     | 3 | 2 | three | -3
     | 5 | 0 | five  | -5
     | 5 | 0 | five  | -5
(7 rows)

SELECT '' AS "xxx", *
  FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, b) USING (b)
  ORDER BY b, t1.a;
 xxx | b | a |   c   | a 
-----+---+---+-------+---
     | 0 | 5 | five  |  
     | 0 |   | zero  |  
     | 2 | 3 | three | 2
     | 4 | 1 | one   | 2
(4 rows)

--
-- NATURAL JOIN
-- Inner equi-join on all columns with the same name
--
SELECT '' AS "xxx", *
  FROM J1_TBL NATURAL JOIN J2_TBL;
 xxx | i | j |   t   | k  
-----+---+---+-------+----
     | 0 |   | zero  |   
     | 1 | 4 | one   | -1
     | 2 | 3 | two   |  2
     | 2 | 3 | two   |  4
     | 3 | 2 | three | -3
     | 5 | 0 | five  | -5
     | 5 | 0 | five  | -5
(7 rows)

SELECT '' AS "xxx", *
  FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (a, d);
 xxx | a | b |   c   | d  
-----+---+---+-------+----
     | 0 |   | zero  |   
     | 1 | 4 | one   | -1
     | 2 | 3 | two   |  2
     | 2 | 3 | two   |  4
     | 3 | 2 | three | -3
     | 5 | 0 | five  | -5
     | 5 | 0 | five  | -5
(7 rows)

SELECT '' AS "xxx", *
  FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (d, a);
 xxx | a | b |  c   | d 
-----+---+---+------+---
     | 0 |   | zero |  
     | 2 | 3 | two  | 2
     | 4 | 1 | four | 2
(3 rows)

-- mismatch number of columns
-- currently, Postgres will fill in with underlying names
SELECT '' AS "xxx", *
  FROM J1_TBL t1 (a, b) NATURAL JOIN J2_TBL t2 (a);
 xxx | a | b |   t   | k  
-----+---+---+-------+----
     | 0 |   | zero  |   
     | 1 | 4 | one   | -1
     | 2 | 3 | two   |  2
     | 2 | 3 | two   |  4
     | 3 | 2 | three | -3
     | 5 | 0 | five  | -5
     | 5 | 0 | five  | -5
(7 rows)

--
-- Inner joins (equi-joins)
--
SELECT '' AS "xxx", *
  FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.i);
 xxx | i | j |   t   | i | k  
-----+---+---+-------+---+----
     | 0 |   | zero  | 0 |   
     | 1 | 4 | one   | 1 | -1
     | 2 | 3 | two   | 2 |  2
     | 2 | 3 | two   | 2 |  4
     | 3 | 2 | three | 3 | -3
     | 5 | 0 | five  | 5 | -5
     | 5 | 0 | five  | 5 | -5
(7 rows)

SELECT '' AS "xxx", *
  FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.k);
 xxx | i | j |  t   | i | k 
-----+---+---+------+---+---
     | 0 |   | zero |   | 0
     | 2 | 3 | two  | 2 | 2
     | 4 | 1 | four | 2 | 4
(3 rows)

--
-- Non-equi-joins
--
SELECT '' AS "xxx", *
  FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i <= J2_TBL.k);
 xxx | i | j |   t   | i | k 
-----+---+---+-------+---+---
     | 1 | 4 | one   | 2 | 2
     | 1 | 4 | one   | 2 | 4
     | 2 | 3 | two   | 2 | 2
     | 2 | 3 | two   | 2 | 4
     | 3 | 2 | three | 2 | 4
     | 4 | 1 | four  | 2 | 4
     | 0 |   | zero  | 2 | 2
     | 0 |   | zero  | 2 | 4
     | 0 |   | zero  |   | 0
(9 rows)

--
-- Outer joins
-- Note that OUTER is a noise word
--
SELECT '' AS "xxx", *
  FROM J1_TBL LEFT OUTER JOIN J2_TBL USING (i)