MySQL Joins Exercises: Find the name and hire date of the employees who was hired after 'Jones'

Last update on January 30 2026 12:31:40 (UTC/GMT +8 hours)

MySQL Joins: Exercise-5 with Solution

Write a MySQL query to find the name (first_name, last_name) and hire date of the employees who was hired after 'Jones'.

Sample table: employees

+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
| EMPLOYEE_ID | FIRST_NAME  | LAST_NAME   | EMAIL    | PHONE_NUMBER       | HIRE_DATE  | JOB_ID     | SALARY   | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID |
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
|         100 | Steven      | King        | SKING    | 515.123.4567       | 1987-06-17 | AD_PRES    | 24000.00 |           0.00 |          0 |   		  90 |
|         101 | Neena       | Kochhar     | NKOCHHAR | 515.123.4568       | 1987-06-18 | AD_VP      | 17000.00 |           0.00 |        100 |            90 |
|         102 | Lex         | De Haan     | LDEHAAN  | 515.123.4569       | 1987-06-19 | AD_VP      | 17000.00 |           0.00 |        100 |            90 |
|         103 | Alexander   | Hunold      | AHUNOLD  | 590.423.4567       | 1987-06-20 | IT_PROG    |  9000.00 |           0.00 |        102 |            60 |
|         104 | Bruce       | Ernst       | BERNST   | 590.423.4568       | 1987-06-21 | IT_PROG    |  6000.00 |           0.00 |        103 |            60 |
|         105 | David       | Austin      | DAUSTIN  | 590.423.4569       | 1987-06-22 | IT_PROG    |  4800.00 |           0.00 |        103 |            60 |
|         106 | Valli       | Pataballa   | VPATABAL | 590.423.4560       | 1987-06-23 | IT_PROG    |  4800.00 |           0.00 |        103 |            60 |
|         107 | Diana       | Lorentz     | DLORENTZ | 590.423.5567       | 1987-06-24 | IT_PROG    |  4200.00 |           0.00 |        103 |            60 |
|         108 | Nancy       | Greenberg   | NGREENBE | 515.124.4569       | 1987-06-25 | FI_MGR     | 12000.00 |           0.00 |        101 |           100 |
............
|         206 | William     | Gietz       | WGIETZ   | 515.123.8181       | 1987-10-01 | AC_ACCOUNT |  8300.00 |           0.00 |        205 |           110 |
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+

View the table

Code:

-- This SQL query selects specific columns from the 'employees' table to retrieve information about employees hired after an employee with the last name 'Jones'.

SELECT 
    e.first_name, -- Selecting the 'first_name' column from the 'employees' table.
    e.last_name, -- Selecting the 'last_name' column from the 'employees' table.
    e.hire_date -- Selecting the 'hire_date' column from the 'employees' table.
FROM 
    employees e -- Specifying the 'employees' table and aliasing it as 'e'.
JOIN 
    employees davies -- Joining the 'employees' table again and aliasing it as 'davies'.
ON 
    (davies.last_name = 'Jones') -- Performing a join based on the condition where the last name in 'davies' is 'Jones'.
WHERE 
    davies.hire_date < e.hire_date; -- Filtering the result to include only employees hired after the employee with the last name 'Jones'.

Explanation:

This SQL query retrieves specific columns ('first_name', 'last_name', 'hire_date') from the 'employees' table.
The 'employees' table is referenced twice in the query, once as 'e' for the general employees and once as 'davies'.
It performs a join based on the condition where the last name in the 'davies' table is 'Jones'.
The WHERE clause filters the result to include only employees hired after the employee with the last name 'Jones', comparing their hire dates.

Relational Algebra Expression:

Relational Algebra Tree:

Sample Output:

first_name	last_name	hire_date
Alana		Walsh		1987-09-21T04:00:00.000Z
Kevin		Feeney		1987-09-22T04:00:00.000Z
Donald		OConnell	1987-09-23T04:00:00.000Z
Douglas		Grant		1987-09-24T04:00:00.000Z
Jennifer	Whalen		1987-09-25T04:00:00.000Z
Michael		Hartstein	1987-09-26T04:00:00.000Z
Pat		Fay		1987-09-27T04:00:00.000Z
Susan		Mavris		1987-09-28T04:00:00.000Z
Hermann		Baer		1987-09-29T04:00:00.000Z
Shelley		Higgins		1987-09-30T04:00:00.000Z
William		Gietz		1987-10-01T04:00:00.000Z

Go to:

PREV :Write a MySQL query to find the employee id, name (last_name) along with their manager_id and name (last_name).
NEXT :Write a MySQL query to get the department name and number of employees in the department.

MySQL Code Editor:

Structure of 'hr' database:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.