How do I name the thing that is done in the FROM part of the query?

Question

So, I have recently started studying data bases in Uni, MySQL. I am practicing some queries with different stuff that the professor gave us.

One of the questions i have found is as follows:

Show the Name and Wage of the employee supervisor and the average wage of the employees that he is supervising. Arrange the data by name and the average wage in an ascending order. (I'm working on the newest version of the MySQL Workbench with the accompanying local host server)

There is only one table, the employee table. With a foreign key supervisor ID that references the employee IDs which are primary keys.

This is the solution to the query:

SELECT SUP.name, SUP.wage, AVG(EMP.wage) AS AVG_WAGE
FROM employee EMP, employee SUP
WHERE SUP.employee_ID = EMP.supervisor_ID
GROUP BY SUP.name
ORDER BY AVG(EMP.wage) ASC;

I'm wondering, what is the name of the thing that he used here where he like... split the employee table into 2 virtual tables for the sake of the query?

I didn't know this is possible at first so I was doing this:

SELECT name, wage, AVG(wage) AS AVG_WAGE
FROM employee
WHERE emp_ID IN (SELECT sup_ID FROM employee)
GROUP BY name, wage, sup_ID IN (SELECT emp_ID FROM employee)
ORDER BY AVG(wage);

This shows the supervisors and their wages but doesn't show the average wage of the employees that each supervisor supervises and just displays the wage of the supervisors again with a bunch of zeroes at the end.

Thank you!

I'm sorry if I'm not very clear in my wording since I'm not studying databases in English and English is not my first language. Thus it isn't easy to find info on google about a bit specific things like this where I don't know how to word questions.

Answer 1

I just read your examples closer and understand your question better now. Your second example that you were doing uses a subquery but the first example your professor was doing is called an implicit inner join. It's just a shorthand way to write FROM employee EMP INNER JOIN employee SUP ON SUP.employee_ID = EMP.supervisor_ID like @stickybit commented.

Answer 2

I'm wondering, what is the name of the thing that he used here where he like... split the employee table into 2 virtual tables for the sake of the query?

In this query, the author uses 2 copies of the source table. 2 absolutely independent copies. He gives unique aliases (temporary names) to each copy - this allows us to distinguish from which copy of the base table each column is taken.

So in the WHERE clause, he attaches the matched row taken from the copy EMP to the row from the copy SUP (or backwards - the binding is symmetric). The row attached from EMP does not affect the same row from SUP - it will be joined to some another row from EMP if a matched row exists.

You may imagine that for this query we make 2 temporary copies of the source table (with absolutely the same structures and data) and give them the names EMP and SUP, then we join them in the query.