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:
- 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 ...