Sub Queries

A sub-query is a select query that is contained inside another query. The inner select query is usually used to determine the results of the outer select query.

Using Murach Database, find employees who work in Payroll Department.

use Murach;

select 
    department_number
from 
    departments 
where 
    department_name = 'Payroll';

SELECT 
    * 
FROM 
    employees 
where 
    department_number = Value from previous query;

Now using Subquery

select 
    * 
from 
    employees 
where 
    department_number = 
    (select 
        department_number 
    from 
        departments 
    where 
        department_name = 'Payroll'

   );

Using Chinook database, simple Subquery to find out Names of Support Representative

SELECT * FROM Employee;

SELECT * FROM Customer;

SELECT * FROM Employee WHERE EmployeeId IN (SELECT SupportRepId FROM Customer);

Using Sakila database, find the actors who acted in the movie Ace Goldfinger

use sakila;

SELECT * FROM actor
WHERE actor_id IN 
    (SELECT actor_id FROM film_actor
    WHERE film_id = 
        (SELECT film_id FROM film 
        WHERE title = 'Ace Goldfinger')
    );