CODEWITHSUNDEEP
sqljoin
Kiri
Kiri

Reputation: 1

How order of JOIN USING impacts result?

Here's an exercise with 4 tables: https://www.w3resource.com/sql-exercises/joins-hr/sql-joins-hr-exercise-13.php

I am trying to understand why using:

 SELECT job_title, department_name, first_name || ' ' || last_name AS `enter code here`Employee_name, start_date 
    FROM job_history 
    JOIN jobs USING (job_id) 
    JOIN departments USING (department_id) 
    JOIN  employees USING (employee_id)
WHERE start_date>='1993-01-01' AND start_date<='1997-08-31';

returns a correct value, while using

 SELECT job_title, department_name, first_name || ' ' || last_name AS Employee_name, start_date 
    FROM job_history 
    JOIN jobs USING (job_id) 
    JOIN  employees USING (employee_id) 
    JOIN departments USING (department_id) 
WHERE start_date>='1993-01-01' AND start_date<='1997-08-31';

returns nothing (just changed JOIN order)

Upvotes: 0

Views: 56

Answers (2)

Big Arne
Big Arne

Reputation: 31

Before: Using 'USING' is often a bad idea, because it combines disadvantages from all notaitions, is seldom used and difficult to maintain.

USING, as stated in an other answer, joins all (and only) columns in the tables above. I'll explain by example how this using works. The Row returned in the first place is

Administration Assistant    Executive   Jennifer Whalen 1995-09-17

If we concentrate on this Row, let us see the origin of the first Join:

JOIN jobs USING (job_id) 

job_history
|         200 | 1995-09-17 | 2001-06-17 | AD_ASST    |            90 |

jobs
| AD_ASST    | Administration Assistant        |       3000 |       6000 |

Now we proceed to the second Join. The only table until now containing the department_id is job_history.

JOIN departments USING (department_id) 

|            90 | Executive            |        100 |        1700 |

and the last join:

JOIN  employees USING (employee_id)

|         200 | Jennifer    | Whalen      | JWHALEN  | 515.123.4444       | 2003-09-17 | AD_ASST    |  4400.00 |           0.00 |        101 |            10 |

because the only other table above having a row named employee_id is job_history.

Let us see what happens, if you switch the joins. The first Join is just the same:

JOIN jobs USING (job_id) 

job_history
|         200 | 1995-09-17 | 2001-06-17 | AD_ASST    |            90 |

jobs
| AD_ASST    | Administration Assistant        |       3000 |       6000 |

Now we proceed to the second Join:

JOIN  employees USING (employee_id)

|         200 | Jennifer    | Whalen      | JWHALEN  | 515.123.4444       | 2003-09-17 | AD_ASST    |  4400.00 |           0.00 |        101 |            10 |

But now we come to the important Difference:

JOIN departments USING (department_id)

There are department_id columns in two different tables, job_history (90) and employees (10). So there cannot be any match.

-> NO DATA FOUND:

So: Use JOIN with 'ON' instead of 'USING' and fully qualify any column with the appropriate table aliase so you won't get into this kind of trouble and have maintainable code. Here we go:

SELECT job_title
      ,department_name
      ,first_name || ' ' || last_name AS Employee_name
      ,start_date
FROM job_history joh
JOIN jobs        jos on (joh.job_id = jos.job_id) 
JOIN departments dep on (joh.department_id = dep.department_id) 
JOIN employees   emp on (joh.employee_id = emp.employee_id)
WHERE start_date>='1993-01-01' AND start_date<='1997-08-31'
;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

In general, changing the order of the joins should not make a difference -- if the query compiles, then it should return the same results.

However, you are using the using clause. And that can cause problems, because the scoping of the join keys is only tables up to that point.

One possibility is that at least three tables have a department_id: employees, departments and one or both of the jobs tables.

The first query is looking up the department_id for the jobs tables, and requiring that they be the same.

The second query brings the employees.department_id in scope as well, and requires that it match the others. No rows have employees whose department matches the department of jobs they are assigned to.

Upvotes: 1

Related Questions