Mysql – How to name the thing that is done in the FROM part of the query

group byMySQLselect

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.

Best Answer

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.

Related Question