两张表，按指定条件，连接成一张表
从连接的结果表中查询
多表连接
-- 查询员工,显示员工的部门名
select e.id,e.fname,e.sal,d.dept_id,d.dept_name
from emps e,depts d
where e.dept_id=d.dept_id;
-- 习惯上主表在前写
-- 地区表
select * from locations;
-- 查询部门 显示部门的城市
select d.dept_id,d.dept_name,l.city
from depts d,locations l
where d.loc_id=l.loc_id;
员工表
-- 查询部门,显示部门经理名
select d.dept_id,d.dept_name,e.fname
from depts d,emps e
where d.mgr_id=e.id;
运行结果
+---------+------------------+-----------+
| dept_id | dept_name | fname |
+---------+------------------+-----------+
| 10 | Administration | Jennifer |
| 20 | Marketing | Michael |
| 30 | Purchasing | Den |
| 40 | Human Resources | Susan |
| 50 | Shipping | Adam |
| 60 | IT | Alexander |
| 70 | Public Relations | Hermann |
| 80 | Sales | John |
| 90 | Executive | Steven |
| 100 | Finance | Nancy |
| 110 | Accounting | Shelley |
+---------+------------------+-----------+
自连接
-- 查询员工，显示员工主管名
select e1.id,e1.fname as employee,e1.sal,e2.fname as manager
from emps e1,emps e2
where e1.mgr_id=e2.id;
运行结果
+-----+-------------+----------+-----------+
| id | employee | sal | manager |
+-----+-------------+----------+-----------+
| 101 | Neena | 17000.00 | Steven |
| 102 | Lex | 17000.00 | Steven |
| 103 | Alexander | 9000.00 | Lex |
| 104 | Bruce | 6000.00 | Alexander |
| 105 | David | 4800.00 | Alexander |
| 106 | Valli | 4800.00 | Alexander |
| 107 | Diana | 4200.00 | Alexander |
| 108 | Nancy | 12000.00 | Neena |
| 109 | Daniel | 9000.00 | Nancy |
| 110 | John | 8200.00 | Nancy |
| 111 | Ismael | 7700.00 | Nancy |
| 112 | Jose Manuel | 7800.00 | Nancy |
| 113 | Luis | 6900.00 | Nancy |
| 114 | Den | 11000.00 | Steven |
| 115 | Alexander | 3100.00 | Den |
| 116 | Shelli | 2900.00 | Den |
| 117 | Sigal | 2800.00 | Den |
| 118 | Guy | 2600.00 | Den |
| 119 | Karen | 2500.00 | Den |
| 120 | Matthew | 8000.00 | Steven |
| 121 | Adam | 8200.00 | Steven |
| 122 | Payam | 7900.00 | Steven |
| 123 | Shanta | 6500.00 | Steven |
| 124 | Kevin | 5800.00 | Steven |
| 125 | Julia | 3200.00 | Matthew |
| 126 | Irene | 2700.00 | Matthew |
| 127 | James | 2400.00 | Matthew |
| 128 | Steven | 2200.00 | Matthew |
| 129 | Laura | 3300.00 | Adam |
| 130 | Mozhe | 2800.00 | Adam |
| 131 | James | 2500.00 | Adam |
| 132 | TJ | 2100.00 | Adam |
| 133 | Jason | 3300.00 | Payam |
| 134 | Michael | 2900.00 | Payam |
| 135 | Ki | 2400.00 | Payam |
| 136 | Hazel | 2200.00 | Payam |
| 137 | Renske | 3600.00 | Shanta |
| 138 | Stephen | 3200.00 | Shanta |
| 139 | John | 2700.00 | Shanta |
| 140 | Joshua | 2500.00 | Shanta |
| 141 | Trenna | 3500.00 | Kevin |
| 142 | Curtis | 3100.00 | Kevin |
| 143 | Randall | 2600.00 | Kevin |
| 144 | Peter | 2500.00 | Kevin |
| 145 | John | 14000.00 | Steven |
| 146 | Karen | 13500.00 | Steven |
| 147 | Alberto | 12000.00 | Steven |
| 148 | Gerald | 11000.00 | Steven |
| 149 | Eleni | 10500.00 | Steven |
| 150 | Peter | 10000.00 | John |
| 151 | David | 9500.00 | John |
| 152 | Peter | 9000.00 | John |
| 153 | Christopher | 8000.00 | John |
| 154 | Nanette | 7500.00 | John |
| 155 | Oliver | 7000.00 | John |
| 156 | Janette | 10000.00 | Karen |
| 157 | Patrick | 9500.00 | Karen |
| 158 | Allan | 9000.00 | Karen |
| 159 | Lindsey | 8000.00 | Karen |
| 160 | Louise | 7500.00 | Karen |
| 161 | Sarath | 7000.00 | Karen |
| 162 | Clara | 10500.00 | Alberto |
| 163 | Danielle | 9500.00 | Alberto |
| 164 | Mattea | 7200.00 | Alberto |
| 165 | David | 6800.00 | Alberto |
| 166 | Sundar | 6400.00 | Alberto |
| 167 | Amit | 6200.00 | Alberto |
| 168 | Lisa | 11500.00 | Gerald |
| 169 | Harrison | 10000.00 | Gerald |
| 170 | Tayler | 9600.00 | Gerald |
| 171 | William | 7400.00 | Gerald |
| 172 | Elizabeth | 7300.00 | Gerald |
| 173 | Sundita | 6100.00 | Gerald |
| 174 | Ellen | 11000.00 | Eleni |
| 175 | Alyssa | 8800.00 | Eleni |
| 176 | Jonathon | 8600.00 | Eleni |
| 177 | Jack | 8400.00 | Eleni |
| 178 | Kimberely | 7000.00 | Eleni |
| 179 | Charles | 6200.00 | Eleni |
| 180 | Winston | 3200.00 | Matthew |
| 181 | Jean | 3100.00 | Matthew |
| 182 | Martha | 2500.00 | Matthew |
| 183 | Girard | 2800.00 | Matthew |
| 184 | Nandita | 4200.00 | Adam |
| 185 | Alexis | 4100.00 | Adam |
| 186 | Julia | 3400.00 | Adam |
| 187 | Anthony | 3000.00 | Adam |
| 188 | Kelly | 3800.00 | Payam |
| 189 | Jennifer | 3600.00 | Payam |
| 190 | Timothy | 2900.00 | Payam |
| 191 | Randall | 2500.00 | Payam |
| 192 | Sarah | 4000.00 | Shanta |
| 193 | Britney | 3900.00 | Shanta |
| 194 | Samuel | 3200.00 | Shanta |
| 195 | Vance | 2800.00 | Shanta |
| 196 | Alana | 3100.00 | Kevin |
| 197 | Kevin | 3000.00 | Kevin |
| 198 | Donald | 2600.00 | Kevin |
| 199 | Douglas | 2600.00 | Kevin |
| 200 | Jennifer | 4400.00 | Neena |
| 201 | Michael | 13000.00 | Steven |
| 202 | Pat | 6000.00 | Michael |
| 203 | Susan | 6500.00 | Neena |
| 204 | Hermann | 10000.00 | Neena |
| 205 | Shelley | 12000.00 | Neena |
| 206 | William | 8300.00 | Shelley |
+-----+-------------+----------+-----------+
内连接与外连接
内连接与外连接
内连接：只查询满足连接条件的数据
外连接
不满足连接条件的数据也要查询
左外连接：查询左侧表条件外数据
右外连接：查询右侧表条件外数据
全外连接： 双侧表条件外数据
MySql不支持全外连接
数据库外连接非标准语法不同
sqlserver
where a.id=b.xid(+)右外连接
where a.id(+)=b.xid左外连接
oracle
where a.id=* b.xid左外连接
where a.id*=b.xid右外连接
mysql
没有外连接的非标准语法
标准的表连接语法
select ... from a join b (a.id=b.xid); -- 内连接
select ... from a join b (a.id=b.xid);
join c
on(....); -- 内连接
select ... from a left join b (a.id=b.xid);
join c
on(....); --左外连接
select ... from a right join b (a.id=b.xid);
join c
on(....); --右外练级
-- 查询部门,显示部门经理名
select d.dept_id,d.dept_name,e.fname
from depts d
left join emps e
on(d.mgr_id=e.id);
-- 查询部门,显示部门经理名
select d.dept_id,d.dept_name,e.fname
from depts d,emps e
where d.mgr_id=e.id;
-- 查询等价
-- 所有部门，显示部门经理，没有经理显示null 显示左侧表以外的数据
select d.dept_id,d.dept_name,e.fname
from depts d
left join emps e
on(d.mgr_id=e.id);
运行结果
+---------+----------------------+-----------+
| dept_id | dept_name | fname |
+---------+----------------------+-----------+
| 10 | Administration | Jennifer |
| 20 | Marketing | Michael |
| 30 | Purchasing | Den |
| 40 | Human Resources | Susan |
| 50 | Shipping | Adam |
| 60 | IT | Alexander |
| 70 | Public Relations | Hermann |
| 80 | Sales | John |
| 90 | Executive | Steven |
| 100 | Finance | Nancy |
| 110 | Accounting | Shelley |
| 120 | Treasury | NULL |
| 130 | Corporate Tax | NULL |
| 140 | Control And Credit | NULL |
| 150 | Shareholder Services | NULL |
| 160 | Benefits | NULL |
| 170 | Manufacturing | NULL |
| 180 | Construction | NULL |
| 190 | Contracting | NULL |
| 200 | Operations | NULL |
| 210 | IT Support | NULL |
| 220 | NOC | NULL |
| 230 | IT Helpdesk | NULL |
| 240 | Government Sales | NULL |
| 250 | Retail Sales | NULL |
| 260 | Recruiting | NULL |
| 270 | Payroll | NULL |
+---------+----------------------+-----------+
-- 查询107个员工，显示部门和城市
-- 员工和部门先连
select e.id,e.fname,e.sal,d.dept_name
from emps e
left join depts d
on(e.dept_id=d.dept_id);
-- 员工和部门先连后和地区表连
select e.id,e.fname,e.sal,d.dept_name,l.city
from emps e
left join depts d
on(e.dept_id=d.dept_id)
left join locations l
on(d.loc_id=l.loc_id);
