(1)连接
select * from table1,table2
等价于
select * from table1 cross join table2
eg:mysql数据库
CREATE TABLE Articles (
ArticleID SMALLINT NOT NULL PRIMARY KEY,
ArticleTitle VARCHAR(60) NOT NULL,
Copyright YEAR NOT NULL
)
ENGINE=INNODB;
INSERT INTO Articles VALUES (12786, 'How write a paper', 1934),
(13331, 'Publish a paper', 1919),
(14356, 'Sell a paper', 1966),
(15729, 'Buy a paper', 1932),
(16284, 'Conferences', 1996),
(17695, 'Journal', 1980),
(19264, 'Information', 1992),
(19354, 'AI', 1993);
CREATE TABLE Authors (
AuthID SMALLINT NOT NULL PRIMARY KEY,
AuthorFirstName VARCHAR(20),
AuthorMiddleName VARCHAR(20),
AuthorLastName VARCHAR(20)
)
ENGINE=INNODB;
INSERT INTO Authors VALUES (1006, 'Henry', 'S.', 'Thompson'),
(1007, 'Jason', 'Carol', 'Oak'),
(1008, 'James', NULL, 'Elk'),
(1009, 'Tom', 'M', 'Ride'),
(1010, 'Jack', 'K', 'Ken'),
(1011, 'Mary', 'G.', 'Lee'),
(1012, 'Annie', NULL, 'Peng'),
(1013, 'Alan', NULL, 'Wang'),
(1014, 'Nelson', NULL, 'Yin');
CREATE TABLE AuthorArticle (
AuthID SMALLINT NOT NULL,
ArticleID SMALLINT NOT NULL,
PRIMARY KEY (AuthID, ArticleID),
FOREIGN KEY (AuthID) REFERENCES Authors (AuthID),
FOREIGN KEY (ArticleID) REFERENCES Articles (ArticleID)
)
ENGINE=INNODB;
INSERT INTO AuthorArticle VALUES (1006, 14356),
(1008, 15729),
(1009, 12786),
(1010, 17695),
(1011, 15729),
(1012, 19264),
(1012, 19354),
(1014, 16284);
SELECT ArticleTitle, AuthID FROM Articles CROSS JOIN AuthorArticle; //CROSS JOIN 跟JOIN结果集一样
这句相当于SELECT ArticleTitle, AuthID FROM Articles AuthorArticle;
(2)自连接
SELECT p1.authid ,p1.articleid ,p2.authid,p2.articleid
from AuthorArticle as p1 ,AuthorArticle as p2
where p1.authid = p2.authid and p1.articleid ='19264' and p2.articleid = '19354';
--------+-----------+--------+
authid | articleid | authid |articleid
--------+-----------+--------+
1012 | 19264 | 1012 | 19354
--------+-----------+--------+
row in set (0.00 sec)
(3) 内连接
select p1.authorfirstname, p1.AuthorMiddleName ,p1.AuthorLastName, p2.articleid
from authors as p1
inner join authorarticle as p2 on p1.authid = p2.authid;
inner join '表名' on 条件 --连接多个表
它等价于: select p1.authorfirstname, p1.AuthorMiddleName ,p1.AuthorLastName, p2.articleid
from authors as p1 , authorarticle as p2
where p1.authid = p2.authid;
(4)外连接：（outer join）
允许限制一张表中的行，而不限制另外一张表中的行。
注意:外连接不一定非要有外键约束
1: left outer join --不能用left out join
左表中的记录全部会出现在结果集中，匹配不上的显示NULL
mysql> SELECT ArticleTitle, Copyright, AuthID
-> FROM Articles AS b LEFT JOIN AuthorArticle AS ab
-> ON b.ArticleID=ab.ArticleID
-> ORDER BY ArticleTitle;
+-------------------+-----------+--------+
| ArticleTitle | Copyright | AuthID |
+-------------------+-----------+--------+
| AI | 1993 | 1012 |
| Buy a paper | 1932 | 1008 |
| Buy a paper | 1932 | 1011 |
| Conferences | 1996 | 1014 |
| How write a paper | 1934 | 1009 |
| Information | 1992 | 1012 |
| Journal | 1980 | 1010 |
| Publish a paper | 1919 | NULL | //显示b表和ab表里ID相同的记录，但还显示b里有但ab里没有的记录 right join则相反| Sell a paper | 1966 | 1006 |
+-------------------+-----------+--------+
2: right outer join
右表中的记录全部会出现在结果集中，匹配不上的显示NULL
mysql> SELECT ArticleTitle, Copyright, CONCAT_WS(' ', AuthorFirstName, AuthorMiddleName, AuthorLastName) AS Author
-> FROM Articles AS b RIGHT JOIN AuthorArticle AS ab ON b.ArticleID=ab.ArticleID
-> RIGHT JOIN Authors AS a ON ab.AuthID=a.AuthID
-> ORDER BY ArticleTitle;
+-------------------+-----------+-------------------+
| ArticleTitle | Copyright | Author |
+-------------------+-----------+-------------------+
| NULL | NULL | Alan Wang |
| NULL | NULL | Jason Carol Oak |
| AI | 1993 | Annie Peng |
| Buy a paper | 1932 | James Elk |
| Buy a paper | 1932 | Mary G. Lee |
| Conferences | 1996 | Nelson Yin |
| How write a paper | 1934 | Tom M Ride |
| Information | 1992 | Annie Peng |
| Journal | 1980 | Jack K Ken |
| Sell a paper | 1966 | Henry S. Thompson |
+-------------------+-----------+-------------------+
3: full outer join|full join --不能用full out join
返回两个表中的匹配和不匹配的所有记录。
