Explain subqueries with an example.
A subquery is a query within a query. The inner query is processed first. They are most commonly used to return data from multiple tables when the exact value is not known.
Example:
To display salaries of all employees in company test.
SELECT salary, emp_name FROM employee WHERE emp_name IN (SELECT emp_name FROM company WHERE company_name = 'test')
A multiple-row subquery with an example
A multiple row subquery returns one or more rows to the outer SQL query.
To display salaries of all employees with names like e in company.
SELECT salary, emp_name FROM employee WHERE emp_name IN (SELECT emp_name FROM company WHERE company_name LIKE ‘%e%’)
Single-row subqueries with an example
A single row subquery returns a maximum of one row to outer SQL query.
To display salary of an employee with id 4 in company test
SELECT salary FROM employee WHERE emp_name IN (SELECT emp_name FROM company WHERE emp_id= ‘4’)
