Task with nested subqueries in SQL-CodePudding

Using tables HR.DEPARTMENTS and HR.EMPLOYEES, create a list of departments that have employees named John.

Here is an example of the employees and departments table:

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    17-JUN-03   AD_PRES   24000      -         -             90

DEPARTMENT_ID   DEPARTMENT_NAME MANAGER_ID  LOCATION_ID
10               Administration     200         1700

i tried like this:

select department_id from HR.EMPLOYEES, HR.DEPARTMENTS 
where 'John' = (select first_name from hr.employees)

How can I rewrite the query so that it works correctly?

CodePudding user response：

To rewrite the query so that it works correctly, you can use a join to combine the employees and departments tables and then use a subquery to filter for employees named John. Here is an example of how you could rewrite the query:

select departments.department_id
from hr.employees employees
  join hr.departments departments
    on employees.department_id = departments.department_id
where employees.first_name = 'John';

This query uses a join to combine the employees and departments tables, and then filters the resulting records to only include those where the employee's first name is 'John'. This will give you a list of department IDs for departments that have employees named John.

CodePudding user response：

I would use exists logic here rather than a join:

SELECT DEPARTMENT_ID
FROM HR.DEPARTMENTS d
WHERE EXISTS (
    SELECT 1
    FROM HR.EMPLOYEES e
    WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID AND
          e.FIRST_NAME = 'John'
);

CodePudding user response：

select department_id from HR.EMPLOYEES WHERE first_name = 'Steven';

This should work. You don't need a JOIN.

If you want to ensure that the department_id exists in the DEPARTMENTS table, you can add a foreign key constraint on the DEPARTMENT_ID column of the table EMPLOYEES.

CodePudding user response：

We can use an IN clause here:

SELECT department_id, department_name
FROM hr.departments
WHERE department_id IN (
    SELECT department_id
    FROM hr.employees
    WHERE first_name = 'John');

A JOIN is of course possible here, but should not be necessary because we don't want to apply further conditions here or fetch other data from the employees table.

It's enough to know John works in this department.

A side note: Your task to "create a list of departments" is very likely not correctly done by selecting the id only. So I selected also the department_name in the query above. And I assume it would be even better and correct to select the name only instead of the id and name.