本节使用的表
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、表中数据量、是否存在索引或键等条件的影响。因此，有必要试验不同的选择机制，找出最适合具体情况的方法。
扫一扫
485
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
