Example 1:
SELECT * FROM tab_member WHERE MEMNO = (SELECT MEMNO FROM tab_member WHERE EmpName = 'john')
MySQL subquery in WHERE clause
SELECT
customerNumber, checkNumber, amount
FROM
payments
WHERE
amount = (SELECT
MAX(amount)
FROM
payments);
Example 2:
SELECT Name,
SUM(Value) AS "SUM(VALUE)",
SUM(Value) / totals.total AS "% of Total"
FROM table1,
(
SELECT Name,
SUM(Value) AS total
FROM table1
GROUP BY Name
) AS totals
WHERE table1.Name = totals.Name
AND Year BETWEEN 2000 AND 2001
GROUP BY Name;