SQL Exercise: Employees who joined in the company on the same date

SQL subqueries on employee Database: Exercise-43 with Solution

[An editor is available at the bottom of the page to write and execute the scripts.]

43. From the following table, write a SQL query to find those employees who joined in the company on the same date. Return complete information about the employees.

Sample table: employees

Sample Solution:

SELECT *
FROM employees e
WHERE hire_date IN
    (SELECT hire_date
     FROM employees
     WHERE e.emp_id <> emp_id);

Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  69062 | FRANK    | ANALYST  |      65646 | 1991-12-03 | 3100.00 |            |   2001
  69000 | JULIUS   | CLERK    |      66928 | 1991-12-03 | 1050.00 |            |   3001
(2 rows)

Explanation:

The said query in SQL that checks for employees who were hired on the same date as another employee, but excluding the employee who is being selected in the outer query.

The subquery is used to retrieve the hire dates of all employees except the one being selected in the outer query. The IN operator is used to match the hire dates of the outer query's employee to the hire dates returned by the subquery.

Practice Online

Structure of employee Database:

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

Previous SQL Exercise: List departments with the most employees.
Next SQL Exercise: Departments with more employees than average.