SQL Inner Join in Subqueries

Question

This exercise is from W3Schools.

Given 2 tables, Employees and Jobs, find the names (first_name, last_name), salary of the employees whose salary is equal to the minimum for their job grade.

http://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

The solution given is:

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

The INNER JOIN subquery

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

shouldn't work since the Employees table has not been CROSSJOIN'ed with the Jobs table. However it seems to work while its been nested within a parent call. Are the tables automatically crossjoined when they are called in this manner?

Can someone enlighten me how this works?

EDIT:

This exercise instructs you to do it using subqueries, but I've found a method that makes way more sense:

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

Your query that *makes way more sense* is crippled - [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged — marc_s, Feb 02 '16 at 08:25
@marc_s Crippled how? PS The notion that "," is problematic [is a bit of a stretch, a received wisdom.](http://stackoverflow.com/a/25957600/3404097). (The arguments are basically all defeated by using AS.) (ON *was* needed for OUTER JOIN.) — philipxy, Feb 02 '16 at 08:32
I'm new, so please forgive my lack of knowledge. So my it should read `FROM employees JOIN jobs` ? PS - philip thanks for the formatting, looks way better. — VyRianS, Feb 02 '16 at 08:36
I added tags, others formatted. (Check out the editing buttons & help.) PS see my previous comment for a reality check re "comma considered harmful". — philipxy, Feb 02 '16 at 09:12
In the first query, the tables aren't cross joined. That's a co-related sub-query — , Dec 03 '16 at 21:30

score 0 · Accepted Answer · edited May 23 '17 at 12:01

Tables are cross joined by a comma (","), CROSS JOIN or JOIN in a FROM. (And cross joined with NULL-extended unmatched rows added by an OUTER JOIN.) Here the nested SELECT is in an expression in a WHERE, so its value is conceptually evaluated for every row of the (cross-joinless) outer WHERE's FROM.

It happens that this query with a subselect gives the same answer as the simpler one with a JOIN. Just as with arithmetic expressions, you can rearrange SQL expressions to different forms with the same results. The result is per how it is written, and parentheses and precedence affect that. The implementation can do whatever it takes to get that result. Hence you will read about the "conceptual" cross join(s) in a FROM or the "conceptual" order of evaluation vs what is actually done. The topic is "query optimization". (Really, query implementation since optimization is so important. Eg here where you really don't want the nested SELECT actually evaluated for every row from the outer WHERE's FROM.) (See this answer & its links re query semantics.)

PS Yes, the exercise is for subselects. A question that would really be better with a subselect and not just a JOIN instead is going to be more complicated than a question appropriate to introducing subselects. (You could try to rewrite to eliminate the subselect after you change min_salary to MIN(salary) though.)

I think SQL reads/parses the code a different way and does not take into account the precedence of brackets? Therefore it generates the FROM/WHERE statement in the parent line before it reads the nested SELECT. Because if it would have read the nested INNERJOIN first it would return an error. — VyRianS, Feb 02 '16 at 08:16

score 0 · Answer 2 · edited Dec 03 '16 at 21:19

In case of subquery in WHERE clause, you can think of it as it is executed for every employees row separately, so you can use parent query's fields inside subquery.

An yes, it can be done in a simpler way using JOIN, but it's just an exercise for subquerying. :)

The query that you put at the end of you question is correct, but anyway I prefer using JOIN clause explicitly for better clarity.

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

Maybe it doesn't change much here, but for more complex queries it's helpful. For example this way you have separated JOIN conditions (usually like tab1.id = tab2.tab1_id) from logic/filtering conditions (like tab1.added > (...)).

SQL Inner Join in Subqueries

2 Answers2