1 介绍JOIN

分类

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN
  • SELF JOIN
  • CROSS JOIN

2 使用JOIN

CREATE TABLE t1 (a1 INT, b1 TEXT);
INSERT INTO t1 VALUES
    (1, 'u1'),

    (2, 'u2'),

    (3, 'u3');

CREATE TABLE t2 (b2 TEXT, c2 TEXT);
INSERT INTO t2 VALUES
    ('u1', 'u1-1'),
    ('u1', 'u1-2'),

    ('u2', 'u2-1'),

    ('u4', 'u4-1');

-- INNER JOIN
SELECT a1, b1, b2, c2
FROM t1 INNER JOIN t2
ON b1 = b2
ORDER BY a1, b1, c2;

 a1 | b1 | b2 |  c2
----+----+----+------
  1 | u1 | u1 | u1-1
  1 | u1 | u1 | u1-2
  2 | u2 | u2 | u2-1

-- LEFT JOIN
SELECT a1, b1, b2, c2
FROM t1 LEFT JOIN t2
ON b1 = b2
ORDER BY a1, b1, c2;

 a1 | b1 | b2 |  c2
----+----+----+------
  1 | u1 | u1 | u1-1
  1 | u1 | u1 | u1-2
  2 | u2 | u2 | u2-1
  3 | u3 |    |

-- RIGHT JOIN
SELECT a1, b1, b2, c2
FROM t1 RIGHT JOIN t2
ON b1 = b2
ORDER BY a1, b1, c2;

 a1 | b1 | b2 |  c2
----+----+----+------
  1 | u1 | u1 | u1-1
  1 | u1 | u1 | u1-2
  2 | u2 | u2 | u2-1
    |    | u4 | u4-1

-- FULL JOIN
SELECT a1, b1, b2, c2
FROM t1 FULL JOIN t2
ON b1 = b2
ORDER BY a1, b1, c2;

 a1 | b1 | b2 |  c2
----+----+----+------
  1 | u1 | u1 | u1-1
  1 | u1 | u1 | u1-2
  2 | u2 | u2 | u2-1
  3 | u3 |    |
    |    | u4 | u4-1

-- SELF JOIN
SELECT a1, b1, b2, c2
FROM t1 SELF JOIN t2
ON b1 = b2
ORDER BY a1, b1, c2;

-- CROSS JOIN
SELECT a1, b1, b2, c2
FROM t1 CROSS JOIN t2 -- can't use 'ON'
ORDER BY a1, b1, c2;

 a1 | b1 | b2 |  c2
----+----+----+------
  1 | u1 | u1 | u1-1
  1 | u1 | u1 | u1-2
  1 | u1 | u2 | u2-1
  1 | u1 | u4 | u4-1
  2 | u2 | u1 | u1-1
  2 | u2 | u1 | u1-2
  2 | u2 | u2 | u2-1
  2 | u2 | u4 | u4-1
  3 | u3 | u1 | u1-1
  3 | u3 | u1 | u1-2
  3 | u3 | u2 | u2-1
  3 | u3 | u4 | u4-1