LEFT JOIN & RIGHT JOIN

參考資料

SQL JOIN

SQL INNER JOIN 關(guān)鍵字

SQL LEFT JOIN 關(guān)鍵字

SQL RIGHT JOIN 關(guān)鍵字

準(zhǔn)備工作

SQL:

create database test;
use test;
create table Persons(
Id_P int primary key AUTO_INCREMENT,
LastName varchar(30) not null default '',
FirstName varchar(30) not null default '',
Address varchar(30) not null default '',
City varchar(30) not null default ''
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO Persons(Id_P, LastName, FirstName, Address, City)VALUES(1, 'Adams', 'John', 'Oxford Street', 'London');
INSERT INTO Persons(Id_P, LastName, FirstName, Address, City)VALUES(2, 'Bush', 'George', 'Fifth Avenue', 'New York');
INSERT INTO Persons(Id_P, LastName, FirstName, Address, City)VALUES(3, 'Carter', 'Thomas', 'Changan Street  ', 'Beijing');

create table Orders(
Id_O int primary key AUTO_INCREMENT,
OrderNo int not null default 0,
Id_P int not null default 0
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO Orders(Id_O, OrderNo, Id_P)VALUES(1, 77895, 3);
INSERT INTO Orders(Id_O, OrderNo, Id_P)VALUES(2, 44678, 3);
INSERT INTO Orders(Id_O, OrderNo, Id_P)VALUES(3, 22456, 1);
INSERT INTO Orders(Id_O, OrderNo, Id_P)VALUES(4, 24562, 1);
INSERT INTO Orders(Id_O, OrderNo, Id_P)VALUES(5, 34764, 65);

查看準(zhǔn)備工作

Persons表

SQL:

select * from Persons;

結(jié)果：

+------+----------+-----------+-----------------+----------+
| Id_P | LastName | FirstName | Address         | City     |
+------+----------+-----------+-----------------+----------+
|    1 | Adams    | John      | Oxford Street   | London   |
|    2 | Bush     | George    | Fifth Avenue    | New York |
|    3 | Carter   | Thomas    | Changan Street   | Beijing  |
+------+----------+-----------+-----------------+----------+
3 rows in set (0.00 sec)

Orders表

SQL:

select * from Orders;

結(jié)果：

+------+---------+------+
| Id_O | OrderNo | Id_P |
+------+---------+------+
|    1 |   77895 |    3 |
|    2 |   44678 |    3 |
|    3 |   22456 |    1 |
|    4 |   24562 |    1 |
|    5 |   34764 |   65 |
+------+---------+------+
5 rows in set (0.00 sec)

RIGHT JOIN

SQL:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName;

結(jié)果

+----------+-----------+---------+
| LastName | FirstName | OrderNo |
+----------+-----------+---------+
| NULL     | NULL      |   34764 |
| Adams    | John      |   22456 |
| Adams    | John      |   24562 |
| Carter   | Thomas    |   44678 |
| Carter   | Thomas    |   77895 |
+----------+-----------+---------+s
5 rows in set (0.00 sec)

LEFT JOIN

SQL:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName;

結(jié)果

+----------+-----------+---------+
| LastName | FirstName | OrderNo |
+----------+-----------+---------+
| Adams    | John      |   24562 |
| Adams    | John      |   22456 |
| Bush     | George    |    NULL |
| Carter   | Thomas    |   77895 |
| Carter   | Thomas    |   44678 |
+----------+-----------+---------+
5 rows in set (0.00 sec)

準(zhǔn)備工作

SQL:

CREATE TABLE `EMP` (
  `EMPNO` int(11) NOT NULL AUTO_INCREMENT,
  `ENAME` varchar(10) DEFAULT NULL,
  `JOB` varchar(9) DEFAULT NULL,
  `MGR` int(11) DEFAULT NULL,
  `HIREDATE` date DEFAULT NULL,
  `SAL` float DEFAULT NULL,
  `COMM` float DEFAULT NULL,
  `DEPTNO` int(11) DEFAULT NULL,
  PRIMARY KEY (`EMPNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.0, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.0, 300, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.0, 500, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.0, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.0, 1400, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.0, NULL, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES(7782, 'CLARK', 'MANAGER', 7839, '1981-06-21', 2450.0, NULL, 10);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES(7788, 'SCOTT', 'ANALYST', 7566, '1987-06-13', 3000.0, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES(7839, 'KING', 'PRESIDENT', 0, '1981-11-17', 5000.0, NULL, 10);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.0, NULL, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES(7876, 'ADAMS', 'CLERK', 7788, '1987-06-13', 1100.0, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.0, NULL, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES(7902, 'FORD', 'ANALYST', 7566, '1982-12-03', 3000.0, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.0, NULL, 10);

CREATE TABLE `DEPT` (
  `DEPTNO` int(11) NOT NULL AUTO_INCREMENT,
  `DNAME` varchar(14) DEFAULT NULL,
  `LOC` varchar(13) DEFAULT NULL,
  PRIMARY KEY (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES(20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES(30, 'SALES', 'CHICAGO');
INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES(40, 'OPERATIONS', 'BOSTON');

查看準(zhǔn)備的環(huán)境

EMP表

SQL:

select * from emp;

結(jié)果：

+-------+--------+-----------+------+------------+------+------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL  | COMM | DEPTNO |
+-------+--------+-----------+------+------------+------+------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800 | NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600 |  300 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250 |  500 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250 | 1400 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-21 | 2450 | NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-06-13 | 3000 | NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500 |    0 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-06-13 | 1100 | NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950 | NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1982-12-03 | 3000 | NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300 | NULL |     10 |
+-------+--------+-----------+------+------------+------+------+--------+
14 rows in set (0.00 sec)

DEPT表

SQL:

select * from dept;

結(jié)果：

+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

RIGHT JOIN

select e.*,d.dname,d.loc from emp e right join dept d on e.deptno = d.deptno;

結(jié)果：

+-------+--------+-----------+------+------------+------+------+--------+------------+----------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL  | COMM | DEPTNO | dname      | loc      |
+-------+--------+-----------+------+------------+------+------+--------+------------+----------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800 | NULL |     20 | RESEARCH   | DALLAS   |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600 |  300 |     30 | SALES      | CHICAGO  |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250 |  500 |     30 | SALES      | CHICAGO  |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 | RESEARCH   | DALLAS   |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250 | 1400 |     30 | SALES      | CHICAGO  |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 | SALES      | CHICAGO  |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-21 | 2450 | NULL |     10 | ACCOUNTING | NEW YORK |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-06-13 | 3000 | NULL |     20 | RESEARCH   | DALLAS   |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 | ACCOUNTING | NEW YORK |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500 |    0 |     30 | SALES      | CHICAGO  |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-06-13 | 1100 | NULL |     20 | RESEARCH   | DALLAS   |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950 | NULL |     30 | SALES      | CHICAGO  |
|  7902 | FORD   | ANALYST   | 7566 | 1982-12-03 | 3000 | NULL |     20 | RESEARCH   | DALLAS   |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300 | NULL |     10 | ACCOUNTING | NEW YORK |
|  NULL | NULL   | NULL      | NULL | NULL       | NULL | NULL |   NULL | OPERATIONS | BOSTON   |
+-------+--------+-----------+------+------------+------+------+--------+------------+----------+
15 rows in set (0.00 sec)

LEFT JOIN

SQL:

select e.*,d.dname,d.loc from emp e left join dept d on e.deptno = d.deptno;

結(jié)果：

+-------+--------+-----------+------+------------+------+------+--------+------------+----------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL  | COMM | DEPTNO | dname      | loc      |
+-------+--------+-----------+------+------------+------+------+--------+------------+----------+
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-21 | 2450 | NULL |     10 | ACCOUNTING | NEW YORK |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 | ACCOUNTING | NEW YORK |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300 | NULL |     10 | ACCOUNTING | NEW YORK |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800 | NULL |     20 | RESEARCH   | DALLAS   |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 | RESEARCH   | DALLAS   |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-06-13 | 3000 | NULL |     20 | RESEARCH   | DALLAS   |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-06-13 | 1100 | NULL |     20 | RESEARCH   | DALLAS   |
|  7902 | FORD   | ANALYST   | 7566 | 1982-12-03 | 3000 | NULL |     20 | RESEARCH   | DALLAS   |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600 |  300 |     30 | SALES      | CHICAGO  |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250 |  500 |     30 | SALES      | CHICAGO  |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250 | 1400 |     30 | SALES      | CHICAGO  |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 | SALES      | CHICAGO  |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500 |    0 |     30 | SALES      | CHICAGO  |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950 | NULL |     30 | SALES      | CHICAGO  |
+-------+--------+-----------+------+------------+------+------+--------+------------+----------+
14 rows in set (0.00 sec)
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 人面猴
    序言：七十年代末悔政，一起剝皮案震驚了整個(gè)濱河市晦溪，隨后出現(xiàn)的幾起案子丹禀，更是在濱河造成了極大的恐慌浩聋，老刑警劉巖魁淳，帶你破解...
    沈念sama閱讀 219,188評(píng)論 6贊 508
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件盆驹，死亡現(xiàn)場(chǎng)離奇詭異，居然都是意外死亡居凶，警方通過查閱死者的電腦和手機(jī)虫给，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 93,464評(píng)論 3贊 395
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進(jìn)店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來侠碧，“玉大人抹估，你說我怎么就攤上這事∨担” “怎么了药蜻？”我有些...
    開封第一講書人閱讀 165,562評(píng)論 0贊 356
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵，是天一觀的道長(zhǎng)替饿。 經(jīng)常有香客問我语泽，道長(zhǎng)，這世上最難降的妖魔是什么视卢？ 我笑而不...
    開封第一講書人閱讀 58,893評(píng)論 1贊 295
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任踱卵，我火速辦了婚禮，結(jié)果婚禮上据过，老公的妹妹穿的比我還像新娘颊埃。我一直安慰自己，他們只是感情好蝶俱，可當(dāng)我...
    茶點(diǎn)故事閱讀 67,917評(píng)論 6贊 392
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布。 她就那樣靜靜地躺著饥漫，像睡著了一般榨呆。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上庸队，一...
    開封第一講書人閱讀 51,708評(píng)論 1贊 305
  • 城市分裂傳說
    那天积蜻，我揣著相機(jī)與錄音闯割，去河邊找鬼。 笑死竿拆，一個(gè)胖子當(dāng)著我的面吹牛宙拉，可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播丙笋，決...
    沈念sama閱讀 40,430評(píng)論 3贊 420
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼谢澈，長(zhǎng)吁一口氣：“原來是場(chǎng)噩夢(mèng)啊……” “哼！你這毒婦竟也來了御板？” 一聲冷哼從身側(cè)響起锥忿，我...
    開封第一講書人閱讀 39,342評(píng)論 0贊 276
  • 萬榮殺人案實(shí)錄
    序言：老撾萬榮一對(duì)情侶失蹤帝簇，失蹤者是張志新（化名）和其女友劉穎掘剪，沒想到半個(gè)月后，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體偏塞，經(jīng)...
    沈念sama閱讀 45,801評(píng)論 1贊 317
  • ?護(hù)林員之死
    正文 獨(dú)居荒郊野嶺守林人離奇死亡笙各，尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 37,976評(píng)論 3贊 337
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年钉答，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片杈抢。...
    茶點(diǎn)故事閱讀 40,115評(píng)論 1贊 351
  • 活死人
    序言：一個(gè)原本活蹦亂跳的男人離奇死亡数尿，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出春感，到底是詐尸還是另有隱情砌创，我是刑警寧澤，帶...
    沈念sama閱讀 35,804評(píng)論 5贊 346
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布鲫懒，位于F島的核電站嫩实，受9級(jí)特大地震影響，放射性物質(zhì)發(fā)生泄漏窥岩。R本人自食惡果不足惜甲献，卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 41,458評(píng)論 3贊 331
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望颂翼。 院中可真熱鬧晃洒，春花似錦、人聲如沸朦乏。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,008評(píng)論 0贊 22
  • 一樁弒父案，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽呻疹。三九已至吃引，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背镊尺。 一陣腳步聲響...
    開封第一講書人閱讀 33,135評(píng)論 1贊 272
  • 情欲美人皮
    我被黑心中介騙來泰國(guó)打工朦佩， 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留，地道東北人庐氮。 一個(gè)月前我還...
    沈念sama閱讀 48,365評(píng)論 3贊 373
  • 代替公主和親
    正文 我出身青樓语稠，卻偏偏與公主長(zhǎng)得像，于是被迫代替她去往敵國(guó)和親弄砍。 傳聞我的和親對(duì)象是個(gè)殘疾皇子仙畦，可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 45,055評(píng)論 2贊 355

推薦閱讀更多精彩內(nèi)容