How subquieries work?

Question

Hello i want to trully understand the subquieries work. If I have a subquieries in the where statement what is the outer select able to grab info?

Answer 1

Subquery is a very powerful concept, if used properly. To be honest it is mostly the everyday task of a database analyst to construct a properly nested query with lots of subqueries inside, usually.

Subqueries are usually used to extract some value or a whole dataset, which is later used as reference for the "parent" query. It is done "on-the-fly" while processing the query, so its output is just a one-time thing, used specifically by the parent (which, in turn, might itself be a subquery of another higher level query :)

I am not sure if the analogy is correct here, but I perceive subqueries a bit similar to functional programming concept. To me it is a very similar way of thinking and structuring the calculation flow.

Finally, some examples of what its applications may be:
http://allenbrowne.com/subquery-01.html

Answer 2

can u elaborate or even give some ```CODE``` example ❔

Answer 3

it is simply general knowlegde...the tutorial here is very basic...when you make a subquiry what elements are availble st the time...

Answer 4

Consider a situation where you have a table Emp(e_id, m_id ->manager's id, name, salary). Your task is to find the name of the Managers from the same table. Here you can use the concept of the subquery. Note, it is also one of the most important questions for SQL Interview.

Best Regards

Answer 5

uhmmm but the table has the name for starters, did not get the you mentioned.