PostgreSQL JOINS: Make a join with a table employees and itself to find the name and hire date of the employees who hired after a specific employee

5. Write a query to make a join with a table employees and itself to find the name, including first_name and last_name and hire date for those employees who were hired after the employee Jones.

Sample Solution:

Code:

-- This SQL query retrieves the first name, last name, and hire date of employees who were hired after an employee with the last name 'Jones'.

SELECT e.first_name, -- Selects the first_name column from the employees table
       e.last_name, -- Selects the last_name column from the employees table
       e.hire_date -- Selects the hire_date column from the employees table
FROM employees e -- Specifies the first instance of the employees table and aliases it as 'e'
JOIN employees davies -- Specifies the second instance of the employees table and aliases it as 'davies'
ON (davies.last_name = 'Jones') -- Specifies the join condition where the last name of the second instance is 'Jones'
WHERE davies.hire_date < e.hire_date; -- Filters the rows to include only those where the hire date of 'Jones' is before the hire date of other employees

Explanation:

• This SQL query retrieves the first name, last name, and hire date of employees who were hired after an employee with the last name 'Jones'.
• Two instances of the employees table are used, aliased as e and davies.
• The JOIN keyword is used to join the employees table (e) with itself based on the condition that the last name of the second instance (davies) is 'Jones'.
• The ON clause specifies the join condition where the last name of the second instance (davies) is 'Jones'.
• The WHERE clause filters the rows to include only those where the hire date of 'Jones' is less than the hire date of other employees, indicating that the employee was hired after 'Jones'.
• The result set will contain the first name, last name, and hire date of employees who were hired after an employee with the last name 'Jones'.

Sample table: employees

Output:

pg_exercises=# SELECT e.first_name, e.last_name, e.hire_date
pg_exercises-# FROM employees e
pg_exercises-# JOIN employees davies
pg_exercises-# ON (davies.last_name = 'Jones')
pg_exercises-# WHERE davies.hire_date < e.hire_date;

 first_name | last_name | hire_date
------------+-----------+------------
 Alana      | Walsh     | 1987-09-21
 Kevin      | Feeney    | 1987-09-22
 Donald     | OConnell  | 1987-09-23
 Douglas    | Grant     | 1987-09-24
 Jennifer   | Whalen    | 1987-09-25
 Michael    | Hartstein | 1987-09-26
 Pat        | Fay       | 1987-09-27
 Susan      | Mavris    | 1987-09-28
 Hermann    | Baer      | 1987-09-29
 Shelley    | Higgins   | 1987-09-30
 William    | Gietz     | 1987-10-01
(11 rows)

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

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

Previous: Write a query to make a join with two tables employees and itself to find the employee id, last_name as Employee along with their manager_id and last name as Manager.
Next: Write a query to make a join with two tables employees and departments to get the department name and number of employees working in each department.