w3resource

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:

employee database structure

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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.