CODEWITHSUNDEEP
sql
hohoho236
hohoho236

Reputation: 29

Weird condition with SQL query

Write a query in SQL to display the first and last name and salary for those employees who earn less than the employee earn whose number is 182.

Table Columns :

EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMAIL | PHONE_NUMBER| HIRE_DATE | JOB_ID     | SALARY

My solution is :

SELECT first_name, last_name, salary 
FROM employees 
WHERE salary < (SELECT salary FROM employees WHERE employee_id = 182)

The answer is :

SELECT E.first_name, E.last_name, E.salary 
FROM employees E 
JOIN employees S ON E.salary < S.salary AND S.employee_id = 182;

I am not sure how and why the query in the answer works because there are many records for S.salary, how can we specify the salary of employee_id = 182 that we wanted to find. Also, the condition S.employee_id = 182 doesn't not the tell us the salary, it just provides the employee_id.

Upvotes: 0

Views: 351

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270191

First, your solution is fine, but you should learn to qualify column references:

SELECT e.first_name, e.last_name, e.salary 
FROM employees e 
WHERE e.salary < (SELECT e182.salary FROM employees e182 WHERE e182. employee_id = 182);

As for your question, do you understand what this is doing?

SELECT E.first_name, E.last_name, E.salary 
FROM employees E JOIN
     (SELECT S.*
      FROM employees S
      WHERE S.employee_id = 182
     ) S
     ON E.salary < S.salary ;

The subquery is selecting all columns for S -- including the salary. The outer query then has a non-equijoin condition to get all E whose salary is less.

The query in your question is doing the same thing, but without the subquery. All the columns are available, not just the one used for the filtering condition.

Upvotes: 0

Dario Griffo
Dario Griffo

Reputation: 4274

Your salary comparison is made against a result set, you need to compare it against a number (another salary). That's why the answer uses a join. The join will "add the columns" of the S employee that match the criteria

E.salary < S.salary AND S.employee_id = 182

That is 1 single employee S with Id 182 against all E employees with salary < than the employee 182

And then the projection with E.x... will show only E columns not the employee S data.

Upvotes: 2

Related Questions