A subquery is a SQL SELECT statement that is contained within another SELECT statement. Subqueries can appear in various parts of a query, including the SELECT clause, the FROM clause, the WHERE clause and the HAVING clause. The innermost query is evaluated prior to queries that contain it. Performance problems can result if an "expensive" query is nested as an inner query.
A subquery is called simple or standard to distinguish it from a correlated subquery. It does not require a join between the subquery and the query that contains it.
A simple subquery can be specified in the WHERE clause. A subquery in the WHERE clause is sometimes called a nested subquery.
Display employee information for the employees with the highest salary.
SELECT * FROM employees WHERE salary = ( SELECT max(salary) FROM employees );
This query returns rows from the employees table for the employee (or employees) that has the highest salary. Because the subquery will always return exactly one row, the equality operator (or one of its siblings) should be used. If there is no guarantee that the subquery will always return exactly one row, then the IN operator should be used. Whenever possible use the equality operator because it is faster than the IN operator.