Joining tables

A perfect example is that you want to find the employee name and department number of a employee with emp_no as 110022. To avoid look ups on three different tables using three statements, you can use JOIN to club them.

SELECT
    emp.emp_no,
    emp.first_name,
    emp.last_name,
    dept.dept_name
FROM
    employees AS emp
JOIN dept_manager AS dept_mgr
    ON emp.emp_no=dept_mgr.emp_no AND emp.emp_no=110022
JOIN departments AS dept
    ON dept_mgr.dept_no=dept.dept_no;

+--------+------------+------------+-----------+
| emp_no | first_name | last_name  | dept_name |
+--------+------------+------------+-----------+
| 110022 | Margareta  | Markovitch | Marketing |
+--------+------------+------------+-----------+
1 row in set (0.00 sec)

Identifying Duplicates using SELF JOIN

You need to add an index on the columns you want to join.

ALTER TABLE employees ADD INDEX name(first_name, last_name);

Query OK, 0 rows affected (1.67 sec)
Records: 0  Duplicates: 0  Warnings: 0

SELECT
     emp1.*
 FROM
     employees emp1
 JOIN employees emp2
     ON emp1.first_name=emp2.first_name
     AND emp1.last_name=emp2.last_name
     AND emp1.gender=emp2.gender
     AND emp1.hire_date=emp2.hire_date
     AND emp1.emp_no!=emp2.emp_no
 ORDER BY
     first_name, last_name;

+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
| 493600 | 1964-01-26 | Keung      | Heusch    | M      | 1986-06-01 |
| 232772 | 1962-05-14 | Keung      | Heusch    | M      | 1986-06-01 |
| 424486 | 1952-07-06 | Marit      | Kolvik    | F      | 1993-12-08 |
|  64089 | 1958-01-19 | Marit      | Kolvik    | F      | 1993-12-08 |
|  14641 | 1953-05-08 | Marsha     | Farrow    | M      | 1989-02-18 |
|  40965 | 1952-05-11 | Marsha     | Farrow    | M      | 1989-02-18 |
| 427429 | 1962-11-06 | Naftali    | Mawatari  | M      | 1985-09-14 |
| 422332 | 1954-08-17 | Naftali    | Mawatari  | M      | 1985-09-14 |
| 243627 | 1957-02-14 | Taisook    | Hutter    | F      | 1985-02-26 |
|  19454 | 1955-05-14 | Taisook    | Hutter    | F      | 1985-02-26 |
+--------+------------+------------+-----------+--------+------------+
10 rows in set (2.08 sec)

You have to mention emp1.emp_no != emp2.emp_no because the employees will have different emp_no. Otherwise, the same employee will appear.

Using SUB queries

A subquery is a SELECT statement within another statement. Suppose you want to find the name of the employees who started as a Senior Engineer on 1986-06-26. You can get the emp_no from the titles table, and name from the employees table. You can also use JOIN to find out the results.

SELECT
    first_name,
    last_name
FROM
    employees
WHERE
    emp_no
IN (SELECT emp_no FROM titles WHERE title="Senior Engineer" AND from_date="1986-06-26");

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Georgi     | Facello   |
| Minghong   | Kalloufi  |
| Nechama    | Bennet    |
| Nagui      | Restivo   |
| Shuzo      | Kirkerud  |
+------------+-----------+
5 rows in set (0.22 sec)

Find the employee making the maximum salary:

SELECT emp_no FROM salaries WHERE salary=(SELECT MAX(salary) FROM salaries);

+--------+
| emp_no |
+--------+
|  43624 |
+--------+
1 row in set (1.94 sec)

Finding mismatched rows between tables

Suppose you want to find rows in a table that are not in other tables. You can achieve this in two ways. Using the NOT IN clause or using OUTER JOIN.

To understand the usage of OUTER JOIN, create two employee tables and insert some values:

CREATE TABLE employees_list1 AS SELECT * FROM employees WHERE first_name LIKE 'aa%';

Query OK, 444 rows affected (0.06 sec)
Records: 444  Duplicates: 0  Warnings: 0

CREATE TABLE employees_list2 AS SELECT * FROM employees WHERE emp_no BETWEEN 400000 AND 500000 AND gender='F';

Query OK, 39892 rows affected (0.89 sec)
Records: 39892  Duplicates: 0  Warnings: 0

To find out the employees who exist in employees_list1 but not in employees_list2:

SELECT * FROM employees_list1 WHERE emp_no NOT IN (SELECT emp_no FROM  employees_list2);

Or you can use OUTER JOIN:

SELECT l1.* FROM employees_list1 l1 LEFT OUTER JOIN employees_list2 l2 ON l1.emp_no=l2.emp_no WHERE l2.emp_no IS NULL;

...
| 431093 | 1952-07-09 | Aamod      | Wolniewicz      | M      | 1989-08-21 |
| 232855 | 1953-03-14 | Aamod      | Wossner         | M      | 1991-03-12 |
| 446842 | 1960-04-29 | Aamod      | Wossner         | M      | 1991-09-21 |
|  91690 | 1962-08-06 | Aamod      | Xiaoshan        | F      | 1989-11-10 |
| 274394 | 1960-04-06 | Aamod      | Zastre          | M      | 1991-02-14 |
| 201437 | 1961-06-08 | Aamod      | Zirintsis       | M      | 1991-05-10 |
| 298239 | 1964-03-18 | Aamod      | Zolotykh        | F      | 1987-12-29 |
+--------+------------+------------+-----------------+--------+------------+
397 rows in set (2.81 sec)

The outer join creates NULL columns of the second table in the join list for each unmatched row.