This is called a full outer join. The implementation of stored procedures varies from database server to database server, so there will be no examples given here. They can return nothing, individual values, or entire result sets. They can create temporary tables, and they usually are assigned their own thread on the server. This means that is 2 people exexcute the same stored procedure, they will not usually have temporary table collisions.
Stored procedures can execute other stored procedures.
Stored procedures can have a higher access permission than the users who execute them. You can see the possibilities here. I am a lowly user, and I need to log in. If access to the employee table is restricted, I cant access it at all, but if I get full access, I can read everyones password with Excel. Usually all the RAM, and temporary tables created by a stored procedure are destroyed after the procedure exits.