Hello and welcome. In this video, you will learn how to write queries that access more than one table. There are several ways to access multiple tables in the same query. Namely, using Sub-queries, Implicit JOIN, and JOIN operators, such as INNER JOIN and OUTER JOIN. In this video, we'll examine the first two options. The third option is covered in more detail in other videos. Let's consider the employees and departments tables from a previous video. The employees table contains several columns for categories, such as employee ID, first name, last name, and salary to name a few. The Departments table contains a department ID, department name, Manager ID, and location ID. Some sample data from these tables is shown here. We will utilize these tables for the examples in this video. In a previous video, we learned how to use sub-queries. Now, let's use sub-queries to work with multiple tables. If we want to retrieve only the employee records from the employees table for which a department ID exists in the departments table, we can use a sub-query as follows. Select star from employees, where department_ID IN, select department_ID_department from departments. Here the outer query accesses the employees table and the sub-query on the departments table is used for filtering the result set of the outer query. Let's say we want to retrieve only the list of employees from a specific location. We do not have any location information in the employees table, but the departments table has a column called location ID. Therefore, we can use a sub-query from the Departments table as input to the employee table query as follows. Select star from employees, where department_ID IN, select department_ID_department from departments, where location ID equals L0002. Now, let's retrieve the department ID and department name for employees who earn more than $70,000. To do so, we will need a sub-query on the employees table to satisfy the salary criteria, and then feed it as input to an outer query on the departments table in order to get the matching department info. Select department_ID_department department_name from departments, where department_ID_department IN, select department_ID from employees where salary is greater than 70,000. We can also access multiple tables by specifying them in the FROM clause of the query. Consider the example, select star from employees, departments. Here we specify two tables in the FROM clause. This results in a table join, but note we are not explicitly using the join operator. The resulting join in this example is called a full join or Cartesian join, because every row in the first table is joined with every row in the second table. If you examine the results set, you will see more rows than in both tables individually. We can use additional operands to limit the result set. Let's look at an example where we limit the result set to only rows with matching department IDs. Select star from employees, departments, where employees department_ID equals departments, department_ID_department. Notice that in the WHERE clause, we prefix the name of the column with the name of the table. This is to fully qualify the column name, since it's possible that different tables could have some column names that are exactly the same. Since the table names can sometimes be long, we can use shorter aliases for table names as shown here. Select star from employees E, departments D, where E department_ID equals D department_ID_department. Here, we define the alias E for employees table and D for departments table, and then use these aliases in the WHERE clause. If we wanted to see the department name for each employee, we would enter the code as follows: Select Employee_ID, Department_Name from employees E, departments D, where E department_ID equals D department_ID_department. Similar to before, the column names in the select clause can also be prefixed by aliases as shown in the query. Select E. Employee_ID, D. Department_ID_department, from employees E. Departments D where E.Department_ID equals D. Department_ID_department. In this lesson, we have shown you how to work with multiple tables using sub-queries and implicit joins. Thanks for watching.