PostgreSQL 左关联(LEFT JOIN)

LEFT JOIN

LEFT JOIN 关键字从 "left" 表中选择所有记录,从 "right" 表中选中匹配的记录。如果没有匹配项,则结果为右侧的 0 条记录。

让我们看一个使用我们的 testproducts 表的例子:

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

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

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

注意testproducts 中的许多产品的 category_idcategories 表中的任何类别都不匹配。

通过使用 LEFT JOIN,我们将从 testpoducts 中获取所有记录,即使是 categories 表中没有匹配项的记录:

实例

使用 category_id 列将 testproducts 表和 categories 关联起来:

  1. SELECT testproduct_id, product_name, category_name
  2. FROM testproducts
  3. LEFT JOIN categories ON testproducts.category_id = categories.category_id;
结果

来自 testproducts 的所有记录,以及仅来自 categories 的匹配记录:

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

注意JOINLEFT OUTER JOIN 将给出相同的结果。

OUTERLEFT JOIN 的默认联接类型,所以当您编写 LEFT JOIN 时,解析器实际上会编写 LEFT OUTER JOIN