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
andlast_name
, are stored in theemployees
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:
- Find the employee name with
emp_no
as110022
from theemployee
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)
- Find the department...