An interesting subquiry in MYSQL : Oh God, I think it will take a while to explain the question but I'll do my best to make it as clear as possible.

Here is the link to the entity-relationship diagram of the database.

https://ibb.co/knPSFe

Here is the statement I'm interesting in. It is taken from the book called "Alan Beaulieu Learning_SQL". It can be found on page 177 in case you want to read about it there. The topic of this query is "Subqueries as Expression Generators". 


[![enter image description here][1]][1]


As you can see the result set includes the three "NULL" rows.They are thought to be sorted out by the WHERE clause of the first subquery:

 **AND p.product_type_cd = 'ACCOUNT'** 

Here is an extract from the book :"The reason for the extra three rows in the result set is that the previous version of the
query included the filter condition p.product_type_cd = 'ACCOUNT'. That filter eliminated
rows with product types of INSURANCE and LOAN, such as small business loans.
Since this version of the query doesn’t include a join to the product table, there is no
way to include the filter condition in the main query. The correlated subquery against
the product table does include this filter, but the only effect is to leave the product name
null."


It is "the previous version of the query":


[![enter image description here][2]][2]


"The correlated subquery against the product table does include this filter, but the only effect is to leave the product name null". **How does it do it? What is the order of evaluation?** 

The query [![enter image description here][3]][3]

returns the first column "product" with INSURANCE and LOAN products types sorted out. **What does make them appear in the result set again and how their names were replaced with NULL values?**


  [1]: https://i.stack.imgur.com/jWBkb.png
  [2]: https://i.stack.imgur.com/m1jjq.png
  [3]: https://i.stack.imgur.com/fznxj.png