sql的 几种join 用法,和区别

(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
返回两个表中的匹配和不匹配的所有记录。