Selecting Data From Split Tables

This post is going to focus on data selection using a specific example. The purpose is to show that you shouldn't rule out splitting up your tables on the premise that it will make it impossible to work with the data. Your table structure should be purely based on the relationship needs of your database.

Table Setup

CREATE DATABASE test;
USE test;

CREATE TABLE `employee` (
    `id` int unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `active_employee` (
    `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `employee_id` int unsigned NOT NULL,
    `phone_number` varchar(11) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `employee_id` (`employee_id`),
    FOREIGN KEY (employee_id) REFERENCES employee(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `terminated_employee` (
    `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `employee_id` int unsigned NOT NULL,
    `termination_date` DATE NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY employee_id (employee_id),
    FOREIGN KEY (employee_id) REFERENCES employee(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `employee` (id, name) VALUES 
(1, 'John'),
(2, 'Susan'),
(3, 'Samantha'),
(4, 'Timmy');

INSERT INTO `active_employee` (id, employee_id, phone_number) VALUES 
(1, 2, 07874245050),
(2, 3, 07891241023);

INSERT INTO `terminated_employee` (id, employee_id, termination_date) VALUES 
(1, 1, '2017-01-01'),
(2, 4, '2017-02-02');

If you want, you can go the extra step to ensure that an employee cannot be in both the active and terminated tables.

Queries

Selecting All Active Employee Data

SELECT 
    employee.id as employee_id,
    employee.name, 
    `active_employee`.phone_number
FROM `active_employee` 
INNER JOIN `employee` on `active_employee`.`employee_id` = `employee`.`id`;

+-------------+----------+--------------+
| employee_id | name     | phone_number |
+-------------+----------+--------------+
|           2 | Susan    | 7874245050   |
|           3 | Samantha | 7891241023   |
+-------------+----------+--------------+

Selecting Limited Active Employee Data

If you only need limited data, performing a nested query is often faster than performing a join.

SELECT 
    employee.id as employee_id,
    employee.name
FROM `employee`
WHERE `id` IN (SELECT employee_id FROM `active_employee`);

+-------------+----------+
| employee_id | name     |
+-------------+----------+
|           2 | Susan    |
|           3 | Samantha |
+-------------+----------+

Selecting All Terminated Employee Data

SELECT 
    employee.id as employee_id,
    employee.name, 
    `terminated_employee`.termination_date
FROM `terminated_employee` 
INNER JOIN `employee` on `terminated_employee`.`employee_id` = `employee`.`id`;

+-------------+-------+------------------+
| employee_id | name  | termination_date |
+-------------+-------+------------------+
|           1 | John  | 2017-01-01       |
|           4 | Timmy | 2017-02-02       |
+-------------+-------+------------------+

Selecting Limited Terminated Employee Data

If you only need the id and name of employees that were terminated then a nested query is often faster than performing a join. The example below will select all employees that were terminated in January of 2017.

SELECT 
    employee.id as employee_id,
    employee.name
FROM `employee`
WHERE `id` IN (
    SELECT employee_id 
    FROM `terminated_employee` 
    WHERE YEAR(`termination_date`)=2017 AND MONTH(`termination_date`) = 1
);

+-------------+------+
| employee_id | name |
+-------------+------+
|           1 | John |
+-------------+------+

Selecting Everything

Although I think the real needs of an application to do this are limited, you can still select absolutely everything from all of the tables when you need it.

SELECT 
    employee.id as employee_id,
    employee.name, 
    `active_employee`.phone_number,
    terminated_employee.termination_date
FROM `employee` 
LEFT JOIN `active_employee` on `active_employee`.`employee_id` = `employee`.`id`
LEFT JOIN `terminated_employee` on `terminated_employee`.`employee_id` = `employee`.`id`;

This gives you:

+-------------+----------+--------------+------------------+
| employee_id | name     | phone_number | termination_date |
+-------------+----------+--------------+------------------+
|           2 | Susan    | 7874245050   | NULL             |
|           3 | Samantha | 7891241023   | NULL             |
|           1 | John     | NULL         | 2017-01-01       |
|           4 | Timmy    | NULL         | 2017-02-02       |
+-------------+----------+--------------+------------------+

References

• MySQL: FULL OUTER JOIN - How do I merge one column?