Joining tables

So far you have looked at inserting and retrieving data from a single table. In this section, we will discuss how to combine two or more tables to retrieve the results. 

A perfect example is that you want to find the employee name and department number of a employee with emp_no: 110022:

• The department number and name are stored in the departments table
• The employee number and other details, such as first_name and last_name, are stored in the employees table
• The mapping of employee and department is stored in the dept_manager table

If you do not want to use JOIN, you can do this:

1. Find the employee name with emp_no as 110022 from the employee table:

mysql> SELECT emp.emp_no, emp.first_name, emp.last_name 
FROM employees AS emp  
WHERE  emp.emp_no=110022;
+--------+------------+------------+
| emp_no | first_name | last_name  |
+--------+------------+------------+
| 110022 | Margareta  | Markovitch |
+--------+------------+------------+
1 row in set (0.00 sec)

2. Find the department...