PostgreSQL 全连接（FULL JOIN）
FULL JOIN
FULL JOIN 关键字从两个表中选择所有记录，即使不匹配也是如此。对于匹配的行，两个表中的值都可用，如果不匹配，则空字段将获得值 NULL。
让我们看一个使用我们的 testproducts 表的例子:
testproduct_id | product_name | category_id----------------+------------------------+-------------1 | Johns Fruit Cake | 32 | Marys Healthy Mix | 93 | Peters Scary Stuff | 104 | Jims Secret Recipe | 115 | Elisabeths Best Apples | 126 | Janes Favorite Cheese | 47 | Billys Home Made Pizza | 138 | Ellas Special Salmon | 89 | Roberts Rich Spaghetti | 510 | Mias Popular Ice | 14(10 rows)
我们将尝试将 testproducts 表与 categories 表连接起来:
category_id | category_name | description-------------+----------------+------------------------------------------------------------1 | Beverages | Soft drinks, coffees, teas, beers, and ales2 | Condiments | Sweet and savory sauces, relishes, spreads, and seasonings3 | Confections | Desserts, candies, and sweet breads4 | Dairy Products | Cheeses5 | Grains/Cereals | Breads, crackers, pasta, and cereal6 | Meat/Poultry | Prepared meats7 | Produce | Dried fruit and bean curd8 | Seafood | Seaweed and fish(8 rows)
注意：testproducts 中的许多产品的 category_id 与 categories 表中的任何类别都不匹配。
通过使用 FULL JOIN，我们将从 categories 表和 testproducts 表中获取所有记录:
实例
使用 category_id 列将 testproducts 表和 categories 表关联起来:
SELECT testproduct_id, product_name, category_nameFROM testproductsFULL JOIN categories ON testproducts.category_id = categories.category_id;
结果
将返回两个表中的所有记录。
不匹配的行将在对面表的字段中获得 NULL 值:
testproduct_id | product_name | category_name----------------+-------------------------+----------------1 | Johns Fruit Cake | Confections2 | Marys Healthy Mix |3 | Peters Scary Stuff |4 | Jims Secret Recipe |5 | Elisabeths Best Apples |6 | Janes Favorite Cheese | Dairy Products7 | Billys Home Made Pizza |8 | Ellas Special Salmon | Seafood9 | Roberts Rich Spaghetti | Grains/Cereals10 | Mias Popular Ice || | Condiments| | Meat/Poultry| | Beverages| | Produce(14 rows)
注意：FULL JOIN 和 FULL OUTER JOIN 将给出相同的结果。
OUTER 是 FULL JOIN 的默认联接类型，所以当您编写 FULL JOIN 时，解析器实际上会编写 FULL OUTER JOIN。