SELECT a.account_id,a.cust_id,b.name
FROM account a LEFT JOIN business b
on a.cust_id=b.cust_id
10.1.1 左外连接与右外连接;
SELECT c.cust_id,b.`name`
FROM customer c left OUTER JOIN business b
ON c.cust_id=b.cust_id
右外连接:
SELECT c.cust_id,b.`name`
FROM customer c RIGHT OUTER JOIN business b
ON c.cust_id=b.cust_id
10.1.2 三路外连接:
SELECT a.account_id,a.product_cd,
CONCAT(i.fname,'',i.lname) person_name,
b.`name` business_name
FROM account a left OUTER JOIN individual i
on a.cust_id=i.cust_id
LEFT OUTER JOIN business b
ON a.cust_id =b.cust_id;
10.1.3 自外连接
将employee 表连接到自己的生成雇员和他们的主管列表。
SELECT e.fname,e.lname
FROM employee e INNER JOIN employee e_mgr
on e.superior_emp_id=e_mgr.emp_id;
内联和外联的区别:
笛卡尔连接:
自然连接:
SELECT a.account_id,a.cust_id, c.cust_type_cd,c.fed_id
from account a NATURAL join customer c
测试: