MySQL Date TypesMySQL LAST_INSERT_IDMySQL InsertMySQL SelectMySQL WhereMySQL Group ByMySQL Order ByMySQL LimitMySQL LikeMySQL ISNULLMySQL BetweenMySQL JoinMySQL Left JoinMySQL Right JoinMySQL Cross JoinMySQL Self JoinMySQL Sub QueryMySQL UpdateMySQL DeleteMySQL ExplainMySQL IfMySQL Full-Text SearchMySQL INFORMATION_SCHEMAMySQL ConnectionMySQL Server Helpful FunctionsMySQL ViewMySQL Stored ProcedureMySQL FunctionsMySQL TransactionMySQL TriggerMySQL EventMySQL DeadlockMySQL DumpMySQL Reset Root PasswordMySQL interview – Advanced QueriesMySQL JsonMySQL BinlogMySQL Closure Table
MySQL Sub Query
A sub query is a query within a query. A subquery can take place in a SELECT, FROM, and WHERE clause. A subquery can be used in a INSERT, UPDATE, SELECT, and DELETE query.
The main query is called the outer query and the subquery in called the inner query.
The sub query is executed and its results are passed to the outer query which is then executed after.
SELECT
column_name1, column_name2,....
FROM
table_name1
WHERE
column_name1 operator (SELECT
column_name
FROM
table_name2
);
Here we are filter out rows with sales_rep_id that is less than the three smallest sales_rep_id which is, in this case, 3, 4, and 5.
Subquery in FROM clause
Subquery in the FROM clause forms a derived or temporary table. This derived or temporary table must have an alias.
As you can see, “(SELECT phone, credit_limit FROM customer) as tempCustomer” formed a derived table.
Correlated subquery
A correlated subquery is different from a regular subquery that stands alone. A correlated subquery uses columns or data from the outer query. It evaluates once for every row in the outer query.
Here is another example where we select all customers with address id that is greater than the average id of the employee.
MySQL Date TypesMySQL LAST_INSERT_IDMySQL InsertMySQL SelectMySQL WhereMySQL Group ByMySQL Order ByMySQL LimitMySQL LikeMySQL ISNULLMySQL BetweenMySQL JoinMySQL Left JoinMySQL Right JoinMySQL Cross JoinMySQL Self JoinMySQL Sub QueryMySQL UpdateMySQL DeleteMySQL ExplainMySQL IfMySQL Full-Text SearchMySQL INFORMATION_SCHEMAMySQL ConnectionMySQL Server Helpful FunctionsMySQL ViewMySQL Stored ProcedureMySQL FunctionsMySQL TransactionMySQL TriggerMySQL EventMySQL DeadlockMySQL DumpMySQL Reset Root PasswordMySQL interview – Advanced QueriesMySQL JsonMySQL BinlogMySQL Closure Table