8. 联结表

本节使用的表

vendors

postgres=# \d vendors

                    Table "public.vendors"
    Column    |     Type      | Collation | Nullable | Default
--------------+---------------+-----------+----------+---------
 vend_id      | character(10) |           | not null |
 vend_name    | character(50) |           | not null |
 vend_address | character(50) |           |          |
 vend_city    | character(50) |           |          |
 vend_state   | character(5)  |           |          |
 vend_zip     | character(10) |           |          |
 vend_country | character(50) |           |          |
Indexes:
    "vendors_pkey" PRIMARY KEY, btree (vend_id)
Referenced by:
    TABLE "products" CONSTRAINT "fk_products_vendors" FOREIGN KEY (vend_id) REFERENCES vendors(vend_id)
SELECT
    *
FROM
    vendors;

 vend_id |  vend_name    | vend_address      | vend_city  | vend_state | vend_zip | vend_country
---------+---------------+-------------------+------------+------------+----------+------------------
 BRS01   | Bears R Us    | 123 Main Street   | Bear Town  | MI         | 44444    | USA
 BRE02   | Bear Emporium   | 500 Park Street | Anytown    | OH         | 44333    | USA
 DLL01   | Doll House Inc. | 555 High Street | Dollsville | CA         | 99999    | USA
 FRB01   | Furball Inc.    | 1000 5th Avenue | New York   | NY         | 11111    | USA
 FNG01   | Fun and Games   | 42 Galaxy Road  | London     |            | N16 6PS  | England
 JTS01   | Jouets et ours  | 1 Rue Amusement | Paris      |            | 45678    | France
(6 rows)

products

postgres=# \d products

                        Table "public.products"
   Column   |          Type           | Collation | Nullable | Default
------------+-------------------------+-----------+----------+---------
 prod_id    | character(10)           |           | not null |
 vend_id    | character(10)           |           | not null |
 prod_name  | character(255)          |           | not null |
 prod_price | numeric(8,2)            |           | not null |
 prod_desc  | character varying(1000) |           |          |
Indexes:
    "products_pkey" PRIMARY KEY, btree (prod_id)
Foreign-key constraints:
    "fk_products_vendors" FOREIGN KEY (vend_id) REFERENCES vendors(vend_id)
Referenced by:
    TABLE "orderitems" CONSTRAINT "fk_orderitems_products" FOREIGN KEY (prod_id) REFERENCES products(prod_id)
SELECT
    * 
FROM
    products;

  prod_id   |  vend_id   |  prod_name         | prod_price |       prod_desc
------------+------------+--------------------+------------+------------------------------
 BR01       | BRS01      | 8 inch teddy bear  |       5.99 | 8 inch teddy bear, comes with cap and jacket
 BR02       | BRS01      | 12 inch teddy bear |       8.99 | 12 inch teddy bear, comes with cap and jacket
 BR03       | BRS01      | 18 inch teddy bear |      11.99 | 18 inch teddy bear, comes with cap and jacket
 BNBG01     | DLL01      | Fish bean bag toy  |       3.49 | Fish bean bag toy, complete with bean bag worms with which to feed it
 BNBG02     | DLL01      | Bird bean bag toy  |       3.49 | Bird bean bag toy, eggs are notincluded
 BNBG03     | DLL01      | Rabbit bean bag toy|       3.49 | Rabbit bean bag toy, comes withbean bag carrots
 RGAN01     | DLL01      | Raggedy Ann        |       4.99 | 18 inch Raggedy Ann doll
 RYL01      | FNG01      | King doll          |       9.49 | 12 inch king doll with royal garments and crown
 RYL02      | FNG01      | Queen doll         |       9.49 | 12 inch queen doll with royal garments and crown
(9 rows)

orderitems

postgres=# \d orderitems

                  Table "public.orderitems"
   Column   |     Type      | Collation | Nullable | Default
------------+---------------+-----------+----------+---------
 order_num  | integer       |           | not null |
 order_item | integer       |           | not null |
 prod_id    | character(10) |           | not null |
 quantity   | integer       |           | not null |
 item_price | numeric(8,2)  |           | not null |
Indexes:
    "orderitems_pkey" PRIMARY KEY, btree (order_num, order_item)
Foreign-key constraints:
    "fk_orderitems_orders" FOREIGN KEY (order_num) REFERENCES orders(order_num)
    "fk_orderitems_products" FOREIGN KEY (prod_id) REFERENCES products(prod_id)
SELECT
    *
FROM
    orderitems;

 order_num | order_item |  prod_id   | quantity | item_price
-----------+------------+------------+----------+------------
     20005 |          1 | BR01       |      100 |       5.49
     20005 |          2 | BR03       |      100 |      10.99
     20006 |          1 | BR01       |       20 |       5.99
     20006 |          2 | BR02       |       10 |       8.99
     20006 |          3 | BR03       |       10 |      11.99
     20007 |          1 | BR03       |       50 |      11.49
     20007 |          2 | BNBG01     |      100 |       2.99
     20007 |          3 | BNBG02     |      100 |       2.99
     20007 |          4 | BNBG03     |      100 |       2.99
     20007 |          5 | RGAN01     |       50 |       4.49
     20008 |          1 | RGAN01     |        5 |       4.99
     20008 |          2 | BR03       |        5 |      11.99
     20008 |          3 | BNBG01     |       10 |       3.49
     20008 |          4 | BNBG02     |       10 |       3.49
     20008 |          5 | BNBG03     |       10 |       3.49
     20009 |          1 | BNBG01     |      250 |       2.49
     20009 |          2 | BNBG02     |      250 |       2.49
     20009 |          3 | BNBG03     |      250 |       2.49
(18 rows)

customers

postgres=# \d customers

                    Table "public.customers"
    Column    |      Type      | Collation | Nullable | Default
--------------+----------------+-----------+----------+---------
 cust_id      | character(10)  |           | not null |
 cust_name    | character(50)  |           | not null |
 cust_address | character(50)  |           |          |
 cust_city    | character(50)  |           |          |
 cust_state   | character(5)   |           |          |
 cust_zip     | character(10)  |           |          |
 cust_country | character(50)  |           |          |
 cust_contact | character(50)  |           |          |
 cust_email   | character(255) |           |          |
Indexes:
    "customers_pkey" PRIMARY KEY, btree (cust_id)
Referenced by:
    TABLE "orders" CONSTRAINT "fk_orders_customers" FOREIGN KEY (cust_id) REFERENCES customers(cust_id)
SELECT
    *
FROM
    customers;

 cust_id    | cust_name     | cust_address         | cust_city | cust_state | cust_zip | cust_country | cust_contact
   | cust_email
------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------
---+----------------------------
 1000000001 | Village Toys  | 200 Maple Lane       | Detroit   | MI         | 44444    | USA          | John Smith
   | sales@villagetoys.com
 1000000002 | Kids Place    | 333 South Lake Drive | Columbus  | OH         | 43333    | USA          | Michelle Green
   |
 1000000003 | Fun4All       | 1 Sunny Place        | Muncie    | IN         | 42222    | USA          | Jim Jones
   | jjones@fun4all.com
 1000000004 | Fun4All       | 829 Riverside Drive  | Phoenix   | AZ         | 88888    | USA          | Denise L. Stephens
   | dstephens@fun4all.com
 1000000005 | The Toy Store | 4545 53rd Street     | Chicago   | IL         | 54545    | USA          | Kim Howard
   |
(5 rows)

创建联结

SELECT
    vend_name,
    prod_name,
    prod_price 
FROM
    vendors,
    products 
WHERE
    vendors.vend_id = products.vend_id;

 vend_name        | prod_name                 | prod_price
------------------+---------------------------+------------
 Bears R Us       | 18 inch teddy bear        |      11.99
 Bears R Us       | 12 inch teddy bear        |       8.99
 Bears R Us       | 8 inch teddy bear         |       5.99
 Doll House Inc.  | Raggedy Ann               |       4.99
 Doll House Inc.  | Rabbit bean bag toy       |       3.49
 Doll House Inc.  | Bird bean bag toy         |       3.49
 Doll House Inc.  | Fish bean bag toy         |       3.49
 Fun and Games    | Queen doll                |       9.49
 Fun and Games    | King doll                 |       9.49
(9 rows)

在联结两个表时,实际要做的是将第一个表A中的每一行与第二个表B中的每一行配对。WHERE子句作为过滤条件,只包含那些匹配给定条件的行。没有WHERE子句,A中的每一行将与B中的每一行配对。

由没有联结条件的表关系返回的结果为笛卡尔积,检索出的行的数目将是A中的行数乘以B中的行数。有时,返回笛卡尔积的联结,也成叉联结。

内联结

目前为止使用的联结称为等值联结,它基于两个表之间的相等测试。这种联结也成为内联结。下面的SELECT语句返回与前面例子完全相同的数据:

SELECT
    vend_name,
    prod_name,
    prod_price 
FROM
    vendors INNER JOIN products 
    ON vendors.vend_id = products.vend_id;

 vend_name        | prod_name                 | prod_price
------------------+---------------------------+------------
 Bears R Us       | 18 inch teddy bear        |      11.99
 Bears R Us       | 12 inch teddy bear        |       8.99
 Bears R Us       | 8 inch teddy bear         |       5.99
 Doll House Inc.  | Raggedy Ann               |       4.99
 Doll House Inc.  | Rabbit bean bag toy       |       3.49
 Doll House Inc.  | Bird bean bag toy         |       3.49
 Doll House Inc.  | Fish bean bag toy         |       3.49
 Fun and Games    | Queen doll                |       9.49
 Fun and Games    | King doll                 |       9.49
(9 rows)

ANSI SQL规范首选INNER JOIN语法,之前使用的是简单的等值语法。

联结多个表

SELECT
    prod_name,
    vend_name,
    prod_price,
    quantity 
FROM
    orderitems,
    products,
    vendors 
WHERE
    products.vend_id = vendors.vend_id  
    AND orderitems.prod_id = products.prod_id  
    AND order_num = 20007;

 prod_name           | vend_name       | prod_price | quantity
---------------------+-----------------+------------+----------
 18 inch teddy bear  | Bears R Us      |      11.99 |       50
 Fish bean bag toy   | Doll House Inc. |       3.49 |      100
 Bird bean bag toy   | Doll House Inc. |       3.49 |      100
 Rabbit bean bag toy | Doll=House Inc. |       3.49 |      100
 Raggedy Ann         | Doll House Inc. |       4.99 |       50
(5 rows)
SELECT
    cust_name,
    cust_contact 
FROM
    customers,
    orders,
    orderitems 
WHERE
    customers.cust_id = orders.cust_id  
    AND orders.order_num = orderitems.order_num  
    AND prod_id = 'RGAN01';
  
 cust_name          |      cust_contact
--------------------+------------------------------------------
 Fun4All            | Denise L. Stephens
 The Toy Store      | Kim Howard
(2 rows)

执行任一给定的SQL操作一般不止一种方法。性能可能会受操作类型、所使用的DBMS、表中数据量、是否存在索引或键等条件的影响。因此,有必要试验不同的选择机制,找出最适合具体情况的方法。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值