A sub-query is a SQL query that is used inside another query.

There is no specific pattern for sub-query. Sub-query is used when need to query from another or multiple tables and show data.

Suppose, you have to query with a value from a table that value is not available in the table but stored in another table. You have to first query the value from another table and then have to query to the first table. In this case, you have to write the sub-query for which table the value is stored.

Example:

Write a query to find out only female members from employee table.

Mention here, Which member is male or female that is not mentioned in employees table. So we have to query to know the gender from the personalinfo table and then have to search from employees table with the returned value.

SELECT * FROM employees WHERE Emp_id= (SELECT Emp_Id FROM personalinfo p WHERE p.`Gender`='Female');

Write a query to find out who gets the second highest salary from the employee table and also show his/her Emp_Id, FirstName, LastName, Department, PhoneNumber, and Salary.

SELECT e.Emp_Id, FirstName,LastName,Department, p.`PhoneNUmber`,MAX(Salary) AS Salary 
FROM employees e
LEFT JOIN personalInfo p
ON e.`Emp_Id`=p.`Emp_Id`
WHERE Salary!=(SELECT MAX(Salary) FROM employees)