左连接&右连接&内连接

mysql> select * from tbl_dept;
+----+----------+--------+
| id | deptName | locAdd |
+----+----------+--------+
|  1 | RD       | 11     |
|  2 | HR       | 12     |
|  3 | MK       | 13     |
|  4 | MIS      | 14     |
|  5 | FD       | 15     |
+----+----------+--------+
5 rows in set (0.03 sec)
mysql> select * from tbl_emp;
+----+------+--------+
| id | name | deptId |
+----+------+--------+
|  1 | z3   |      1 |
|  2 | z4   |      1 |
|  3 | z5   |      1 |
|  4 | w5   |      2 |
|  5 | w6   |      2 |
|  6 | s7   |      3 |
|  7 | s8   |      4 |
|  8 | s9   |     51 |
+----+------+--------+
8 rows in set (0.01 sec)

1、左连接

.. left join .. on
table A left join table B on tableA.col1 = tableB.col2 ; 
 --例句:
 mysql> select e.id as emp_id, e.name as emp_name, d.deptName as dept_name from tbl_emp as e left join tbl_dept as d on e.deptId = d.id;
+--------+----------+-----------+
| emp_id | emp_name | dept_name |
+--------+----------+-----------+
|      1 | z3       | RD        |
|      2 | z4       | RD        |
|      3 | z5       | RD        |
|      4 | w5       | HR        |
|      5 | w6       | HR        |
|      6 | s7       | MK        |
|      7 | s8       | MIS       |
|      8 | s9       | NULL      |
+--------+----------+-----------+
8 rows in set (0.00 sec)

2、右连接

.. right join .. on

--例句:
mysql> select e.id as emp_id, e.name as emp_name, d.deptName as dept_name from tbl_emp as e right join tbl_dept as d on e.deptId = d.id;
+--------+----------+-----------+
| emp_id | emp_name | dept_name |
+--------+----------+-----------+
|      1 | z3       | RD        |
|      2 | z4       | RD        |
|      3 | z5       | RD        |
|      4 | w5       | HR        |
|      5 | w6       | HR        |
|      6 | s7       | MK        |
|      7 | s8       | MIS       |
|   NULL | NULL     | FD        |
+--------+----------+-----------+
8 rows in set (0.00 sec)

3、内连接

.. inner join .. on

--例句:
mysql> select e.id as emp_id, e.name as emp_name, d.deptName as dept_name from tbl_emp as e inner join tbl_dept as d on e.deptId = d.id;
+--------+----------+-----------+
| emp_id | emp_name | dept_name |
+--------+----------+-----------+
|      1 | z3       | RD        |
|      2 | z4       | RD        |
|      3 | z5       | RD        |
|      4 | w5       | HR        |
|      5 | w6       | HR        |
|      6 | s7       | MK        |
|      7 | s8       | MIS       |
+--------+----------+-----------+
7 rows in set (0.00 sec)