Question: Technical Background Of The Result_Set
Greetings,
I’m a beginner with SQL and I have a question related to the technical background of (my)SQL:
When I query my database, let’s say in a form like this:
SELECT * from tblMyTable INNER JOIN tblMyOtherTable ON tblMyTable.pk = tblMyOtherTable.fi
What does the DBMS actually do?
From the W3Schools-Tutorial I know that what I get to see as a result is actually a newly created table called the result_set.
1.) Is the result_set a temporary table, stored in RAM as long as the user (or the process) that made the query is active or logged on to the DBMS? Or is it deleted from RAM as soon as the result is shown to the user, in a what-so-ever form, e.g. a web-interface like phpMyAdmin? If so, it would mean that I could not do further work with that table directly. I would have to safe this result back to the DBMS/the harddrive, wouldn’t I?
Furthermore
2.) What I primarily do with such a query is kind of “merging” those two tables together into the previously mentioned result_set, am I not? So, does the DBMS merge the complete tables and then apply a kind of filter that follows the definitions I have set by using the paramaters and conditions, like the type of the JOIN and the ON-Statement? Clearer: If I use the statement from above, is the result_set directly being put together using all of the conditions, or is a complete mix of both tables made in the first step, followed by a new result_set as a second step, which based on the first step result, only this time altered by the definitions I made with my ON-Statement?
This would be very interesting to know, Thanks :)