Date: 20140219
Auth: Jin
一、介绍
连接是二元运算，可以对两个表或多个表进行查询。
T-SQL中分两大类，一是符合SQL标准的连接谓词表示形式，二是T-SQL扩展的使用关键字JOIN的表示形式。
二、连接谓词
where子句使用比较运算符给出连接条件对表进行连接，将这种表示形式称为连接谓词表示形式。
连接谓词中的比较字符可以是〈，〈=，〉，〉=，！=，〈〉，!<,
!>,当比较符为=时就是等值连接。若在目标列中除去相同的字段名（重复的只保留一个 ），则为自然连接。
1、select
mysql> select Product.*，ProductPrice.* from
Product，ProductPrice where Product.productId=ProductPrice.productId;
mysql> select a.*,b.* from Product as a,ProductPrice as b where
a.productId=b.productId;
mysql> select a.*,b.* from Product as
a,ProductPrice as b where a.productId>b.productId;
注意：a的id会依次和b每一个id对比。
2.delete
mysql> delete a.*,b.* from Product as a,ProductPrice as b
where a.productId=b.productId and a.created < ‘2004-01-01‘;
Query OK, 8
rows affected (0.00 sec)
3.update
mysql> update Product as a,ProductPrice as b set
b.price=a.price*0.8 where a.productId=b.productId;
三、以JOIN关键字指定的连接
INNER JOIN 内连接
OUTER JOIN 外连接
CROSS JOIN
交叉连接
1、内连接 INNER
tableA as a INNER JOIN tableB as B ON A.productId =
B.productId
tableA P INNER JOIN tableB PP ON P.productId =
PP.productId
内连接是系统默认，可以省略关键字,使用内连接后仍可以使用WHERE子句指定条件。
mysql> select a.*,b.* from Product as a INNER JOIN ProductPrice as b ON
a.productID = b.productID WHERE a.created < ‘2004-
01-01‘;
mysql>
delete a.*,b.* from Product as a INNER JOIN ProductPrice as b ON a.productID =
b.productID WHERE a.created < ‘2004-
01-01‘;
mysql> update Product
as a INNER JOIN ProductPrice as b ON a.productId=b.productId set
b.price=a.price*0.6 where a.created <
‘2004-01-01‘;
2.外连接 OUTER
左外连接 LEFT OUTER JOIN: 结果表中除了包含满足连接条件的行外，还包括左表的行
右外连接 RIGHT
OUTER JOIN: 结果表中除了包含满足连接条件的行外，还包括右表的行
完全外连接 FULL OUTER JOIN:
结果表中除了包含满足连接条件的行外，还包括两个表的所有行
四，例题
1.SQL SERVER中
USE PXSCJ
GO
select * from A;
1 a
2 b
3 c
4 d
5 e
select * from B;
1 A
2 B
2 C
5 D
内连接 4个记录
select * from A inner join B on A.id=B.id;
1 a 1 A
2 b 2 B
2 b 2 C
5 e
5 D
自然连接 4个记录
select A.*,B.uname from A inner join B on A.id=B.id;
1 a
A
2 b B
2 b C
5 e
D
左外连接 加左边表的记录 6个
select * from A left outer join B on
A.id=B.id;
1 a 1 A
2 b 2 B
2 b
2 C
3 c NULL NULL
4 d NULL NULL
5 e
5 D
右外连接 加左边表的记录 4个
select * from A right outer join B on A.id=B.id;
1 a
1 A
2 b 2 B
2 b 2 C
5 e 5 D
完全外连接 包括两个表的所有行 6
select * from A full outer join B on A.id=B.id;
1 a
1 A
2 b 2 B
2 b 2 C
3 c NULL NULL
4 d NULL NULL
5 e 5 D
2、MYSQL
1）准备数据
mysql> create table A( id int(6) not null, name
char(6) );
mysql> create table B( id int(6) not null, uname char(6) );
mysql> insert into A(id,name) value
(1,‘a‘),(2,‘b‘),(3,‘c‘),(4,‘d‘),(5,‘e‘);
mysql> select * from
A;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 |
b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5
rows in set (0.00 sec)
mysql> insert into B(id,Uname) value
(1,‘A‘),(2,‘B‘),(2,‘C‘),(5,‘D‘);
mysql> select * from
B;
+----+-------+
| id | uname |
+----+-------+
| 1 | A |
|
2 | B |
| 2 | C |
| 5 | D |
+----+-------+
2 连接
内连接 4个记录
mysql> select * from A inner join B on
A.id=B.id;
+----+------+----+-------+
| id | name | id | uname
|
+----+------+----+-------+
| 1 | a | 1 | A |
| 2 | b |
2 | B |
| 2 | b | 2 | C |
| 5 | e | 5 | D
|
+----+------+----+-------+
4 rows in set (0.01 sec)
自然连接 4个记录
mysql> select A.*,B.uname from A inner join B on
A.id=B.id;
+----+------+-------+
| id | name | uname
|
+----+------+-------+
| 1 | a | A |
| 2 | b | B
|
| 2 | b | C |
| 5 | e | D
|
+----+------+-------+
4 rows in set (0.00 sec)
左外连接 加左边表的记录 6个
mysql> select * from A left outer join B on
A.id=B.id;
+----+------+------+-------+
| id | name | id | uname
|
+----+------+------+-------+
| 1 | a | 1 | A |
| 2 | b
| 2 | B |
| 2 | b | 2 | C |
| 3 | c | NULL | NULL
|
| 4 | d | NULL | NULL |
| 5 | e | 5 | D
|
+----+------+------+-------+
6 rows in set (0.03 sec)
右外连接 加左边表的记录 4个
mysql> select * from A right outer join B on
A.id=B.id;
+------+------+----+-------+
| id | name | id | uname
|
+------+------+----+-------+
| 1 | a | 1 | A |
| 2 | b
| 2 | B |
| 2 | b | 2 | C |
| 5 | e | 5 | D
|
+------+------+----+-------+
4 rows in set (0.00 sec)
完全外连接 包括两个表的所有行 6
mysql> select * from A full outer join B on A.id=B.id;
ERROR 1064
(42000): You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near ‘outer join B
on A.id=B.id‘ at line 1
由于mysql 不支持 full join 经过查资料可以这样写
mysql> select * from A left outer join
B on A.id=B.id union select * from A right outer join B on
A.id=B.id;
+------+------+------+-------+
| id | name | id | uname
|
+------+------+------+-------+
| 1 | a | 1 | A |
| 2
| b | 2 | B |
| 2 | b | 2 | C |
| 3 | c |
NULL | NULL |
| 4 | d | NULL | NULL |
| 5 | e | 5 | D
|
+------+------+------+-------+
6 rows in set (0.00 sec)
原文：http://www.cnblogs.com/diege/p/3556604.html