PostgreSQL 内连接


内部联接

这个INNER JOIN关键字选择在两个表中具有匹配值的记录。

让我们看一个使用我们的虚拟对象的例子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表格。

通过使用INNER JOIN我们不会获取不匹配的记录,我们只会获取匹配的记录两个都表:

示例

加入testproducts categories使用category_id柱子:

SELECT testproduct_id, product_name, category_name
FROM testproducts
INNER JOIN categories ON testproducts.category_id = categories.category_id;
运行示例 »

结果

仅返回两个表中都匹配的记录:

 testproduct_id |      product_name      | category_name
----------------+------------------------+----------------
              1 | Johns Fruit Cake       | Confections
              6 | Janes Favorite Cheese  | Dairy Products
              8 | Ellas Special Salmon   | Seafood
              9 | Roberts Rich Spaghetti | Grains/Cereals
(4 rows)

笔记:JOININNER JOIN会给出相同的结果。

INNER是默认的连接类型JOIN,所以当你写JOIN解析器实际上写的是INNER JOIN


PostgreSQL 练习

通过练习测试一下

练习:

选择正确的 JOIN 子句以选择两个表中存在匹配项的所有记录:

SELECT * FROM orders
 customers
ON orders.customer_id = customers.customer_id;
        

开始练习