CODEWITHSUNDEEP
mysqlcorrelated-subquery
jokonoo
jokonoo

Reputation: 93

MySQL correlated subqueries with aliases

Hello i don't understant how correlated subqueries work under the hood. I have two tables an i want to check if two values are equal if some value of second one if some value from first table is equal to value from second table. My code to do that is:

SELECT first_name, last_name, salary 
FROM employees 
WHERE employees.salary = (SELECT min_salary
FROM jobs
WHERE employees.job_id = jobs.job_id);

How does it exactly work that it knows if the job_id is correct for employees Object when it comes to comparing salary in outer query? Is it iterating over every value and its passing the value to alias.Object?

Let's say the value of the inner query is 2500. What if there is employee who earn 2500 but min_salary for his job_id would be 2000? How does this query knows when it's right or not?

If someone needs more details then everything is better explained here: https://www.w3resource.com/mysql-exercises/subquery-exercises/find-the-names-salary-of-the-employees-whose-salary-is-equal-to-the-minimum-salary-for-their-job-grade.php

Upvotes: 1

Views: 151

Answers (1)

forpas
forpas

Reputation: 164089

The WHERE clause is executed once for each row in the table employees.

What if there is employee who earn 2500 but min_salary for his job_id would be 2000?

For this case, the subquery:

SELECT min_salary FROM jobs WHERE employees.job_id = jobs.job_id

will return 2000 and the boolean expression in the WHERE clause will be FALSE because 2500 <> 2000 and that employee will be filtered out from the results.

The link between each row of employees and jobs is the columns job_id.

For each job_id in employees the table jobs is scanned again and again to fetch the corresponding min_salary and this value (which must be unique and I assume it is unique in a table called jobs) is compared against the column salary of employees.

This query could also be implemented with an INNER join where I believe it is more clear what is going on:

SELECT e.first_name, e.last_name, e.salary 
FROM employees e INNER JOIN jobs j
ON j.job_id = e.job_id AND j.min_salary = e.salary;

Since you mention aliases in your question, use them to qualify all columns, even if you know that for some columns there is no other column with the same name. Also use short aliases to make the code shorter and more readable.

Upvotes: 1

Related Questions