How to query with doctrine dql the where in clause?

Difference between a query, sub query and a nested sub query?

• What is difference between a query, sub query and a nested sub query? A query within a query is called sub query, then what is actually nested sub query and some people say that a query in WHERE clause of another sub query is called nested sub query. What is the actual difference between them? If possible, give me an example for easy understanding.What is difference between a query, sub query and a nested sub query? A query within a query is called sub query, then what is actually nested sub query and some people say that a query in WHERE clause of another sub query is called nested sub query. What is the actual difference between them? If possible, give me an example for easy understanding.     What is difference between a query, sub query and a nested sub query? A query within a query is called sub query, then what is actually nested sub query and some people say that a query in WHERE clause of another sub query is called nested sub query. What is the actual difference between them? If possible, give me an example for easy understanding. Link to Questions, Topics, Blogs and People

• Answer:

  I believe your confusion may be because of lax terminology used by the SQL/database community. I will often here the terms sub-query and nested sub-query used interchangeably - in some cases it's inaccurate to do so, but in others not. A nested sub-query is a type of sub-query where the result of the sub-query is only calculated once and applied as a condition to each row retrieved by the parent query. For example, imagine the following example where I want to select users' names where they are considered an adult in the US. select      u.first_name,      u.last_name from      tUsers u where      u.age > (           select                a.age           from                tRegionalAdultAge a           where                a.country_code = 'US'      ); A correlated sub-query is a type of sub-query where the result of the sub-query is calculated for each row retrieved in the parent query because it is logically dependent on a value in that row. For example, imagine the following example where I want to select users' names where they are considered an adult in their own country. select      u.first_name,      u.last_name from      tUsers u where      u.age > (           select                a.age           from                tRegionalAdultAge a           where                a.country_code = u.country_code      );