Confusing SQL query, Virtual tables and self-joins

Question

I am learning SQL, and I'm working with virtual tables, and self-joins, the table (name: SALESREPS) I'm working on is in the image.

The objective for the query is to find the employees with higher quota than their manager, but I'm having troubles to understand why, for this query

SELECT EMPL.NAME, EMPL.`QUOTA`, MNG.`QUOTA`
FROM `SALESREPS` EMPL, `SALESREPS` MNG
WHERE MNG.`EMPL_NUM` = EMPL.`MANAGER`
AND EMPL.`QUOTA` > MNG.`QUOTA`;

the output is:

+-------------+-----------+-----------+
| NAME        | QUOTA     | QUOTA     |
+-------------+-----------+-----------+
| Mary Jones  | 300000.00 | 275000.00 |
| Larry Fitch | 350000.00 | 275000.00 |
| Bill Adams  | 350000.00 | 200000.00 |
| Dan Roberts | 300000.00 | 200000.00 |
| Paul Cruz   | 275000.00 | 200000.00 |
+-------------+-----------+-----------+

Which is correct, that is what i want. But if i change the WHERE clause to

SELECT EMPL.NAME, EMPL.`QUOTA`, MNG.`QUOTA`
FROM `SALESREPS` MNG, `SALESREPS` EMPL
WHERE MNG.`MANAGER` = EMPL.`EMPL_NUM`
AND EMPL.`QUOTA` > MNG.`QUOTA`;

the output is:

+-------------+-----------+-----------+
| NAME        | QUOTA     | QUOTA     |
+-------------+-----------+-----------+
| Sam Clarck  | 275000.00 | 200000.00 |
| Larry Fitch | 350000.00 | 300000.00 |
+-------------+-----------+-----------+

Which is incorrect. And i can't understand why that happen. I'm just swapping the column I'm referencing in each virtual table (EMPL and MNG). I changed from

WHERE MNG.EMPL_NUM = EMPL.MANAGER

to

WHERE MNG.MANAGER = EMPL.EMPL_NUM

but the equality should yield the same result, however, it doesn't happen that way.

Answer 1

Given both examples include this same predicate expression:

EMPL.`QUOTA` > MNG.`QUOTA`

This:

WHERE MNG.EMPL_NUM = EMPL.MANAGER

is NOT the same as this:

WHERE MNG.MANAGER = EMPL.EMPL_NUM

Because it inverts the roles relative to which must now be greater than the other.

---

While I'm here, NEVER combine tables using this syntax:

 FROM `SALESREPS` EMPL, `SALESREPS` MNG

This has been obsolete since the release of the ANSI-92 standard more than 30 years ago. If you're learning from material that teaches it this way, you should ditch that material. It's not leading you in a good direction.

The correct way to write the query is like this:

SELECT EMPL.NAME, EMPL.`QUOTA`, MNG.`QUOTA`
FROM `SALESREPS` EMPL
INNER JOIN `SALESREPS` MNG ON MNG.`EMPL_NUM` = EMPL.`MANAGER`
WHERE EMPL.`QUOTA` > MNG.`QUOTA`;

Answer 2

Basically, it is very simple, if you really put this one table as two tables in your mind.

This is your the first variant, which one is very good and logically. You satarted from employees, joined managers, checked differences in quota and put very clear columns for result - names from employees and quotes from both tables. Your first variant

But, your second variant is really confused, because you started from a wrong table, from managers, and put in the result data from employees: Your second variant

So, in the second case, your task for the SQL server sounds like: Please, show me those employees, who is the manager of managers (because you link table EMP to table MNG by manager's code, so you want to see, who is manager of each manager from the table), and show just those results, where quota manager of manager is bigger then for the manager itself.

So, if you want to be clear with yourself, just start SQL from logical point. Think, which tables shuld be on the left side, and how you should join all other tables step-by-step to this the very first table. And please use join for joining =)