PostgreSQL FULL JOIN

FULL JOIN

FULL JOIN 关键字从两个表中选择所有记录，即使没有匹配项也是如此。对于有匹配的行，两个表的值都是可用的，如果没有匹配项，空字段将获得 NULL 值。

让我们看一个使用虚拟 testproducts 表的实例：

 testproduct_id |      product_name      | category_id
----------------+------------------------+-------------
              1 | Johns Fruit Cake       |           3
              2 | Marys Healthy Mix      |           9
              3 | Peters Scary Stuff     |          10
              4 | Jims Secret Recipe     |          11
              5 | Elisabeths Best Apples |          12
              6 | Janes Favorite Cheese  |           4
              7 | Billys Home Made Pizza |          13
              8 | Ellas Special Salmon   |           8
              9 | Roberts Rich Spaghetti |           5
             10 | Mias Popular Ice       |          14
(10 rows)

我们将尝试把 testproducts 表与 categories 表连接起来：

 category_id | category_name  |                       description
-------------+----------------+------------------------------------------------------------
           1 | Beverages      | Soft drinks, coffees, teas, beers, and ales
           2 | Condiments     | Sweet and savory sauces, relishes, spreads, and seasonings
           3 | Confections    | Desserts, candies, and sweet breads
           4 | Dairy Products | Cheeses
           5 | Grains/Cereals | Breads, crackers, pasta, and cereal
           6 | Meat/Poultry   | Prepared meats
           7 | Produce        | Dried fruit and bean curd
           8 | Seafood        | Seaweed and fish
(8 rows)

注意：testproducts 中的许多产品都有一个 category_id，它与 categories 表中的任何类别都不匹配。

通过使用 FULL JOIN，我们将从 categories 表和 testproducts 表中获取所有记录：

实例

使用 category_id 列将 testproducts 连接到 categories：

SELECT testproduct_id, product_name, category_name
FROM testproducts
FULL JOIN categories ON testproducts.category_id = categories.category_id;

运行实例

结果

返回两个表中的所有记录。

没有匹配的行将从对面的表中获取字段的 NULL 值：

 testproduct_id |      product_name       | category_name
----------------+-------------------------+----------------
              1 | Johns Fruit Cake        | Confections
              2 | Marys Healthy Mix       |
              3 | Peters Scary Stuff      |
              4 | Jims Secret Recipe      |
              5 | Elisabeths Best Apples  |
              6 | Janes Favorite Cheese   | Dairy Products
              7 | Billys Home Made Pizza  |
              8 | Ellas Special Salmon    | Seafood
              9 | Roberts Rich Spaghetti  | Grains/Cereals
             10 | Mias Popular Ice        |
                |                         | Condiments
                |                         | Meat/Poultry
                |                         | Beverages
                |                         | Produce
(14 rows)

注意：FULL JOIN 和 FULL OUTER JOIN 将给出相同的结果。

OUTER 是 FULL JOIN 的默认连接类型，因此当您编写 FULL JOIN 时，解析器实际上会写入 FULL OUTER JOIN。