If I am writing one query using SELECT subquery and other one without using SELECT subquery then I a-CodePudding

Ques: From the following tables, write a SQL query to find those employees who work in a department where the employee’s first name contains the letter 'T'. Return employee ID, first name and last name.

link to the question https://www.w3resource.com/sql-exercises/sql-subqueries-exercise-14.php

First, one using SELECT

SELECT employee_id, first_name, last_name  
FROM employees  
WHERE department_id IN  
( SELECT department_id  
FROM employees  
WHERE first_name LIKE '%T%' );

Second one without using SELECT

SELECT employee_id, first_name, last_name
FROM employees
WHERE FIRST_NAME LIKE '%t%';

CodePudding user response：

If I'm not mistaken, the exercise wants you to get the requested info regarding ALL employees who are working in the same department with those employees whose fisrt name contains the letter T. So the subquery in your first statement (the correct one) returns all the legitimate department_id for those who have the letter T in their first name, which is then used to get ALL employees working in said department.
Your secondary statement only gets the individual employees whose first name matches a condition, and be done with it , rather than ALL the employees working in the same department with those individual employees.

CodePudding user response：

You will get different results since your second query makes a harder restriction compared to the first. Assume there is a department where two people are working: Frank and Bert. The first query will find both of them since they both work in the same department and there is at least one person (Bert) whose name contains the letter T. Your second query will not find Frank since his name doesn't contain the letter T and you don't care about the department where he and Bert are working.