Lesson 7

This module explored some of the advanced query techniques available to you in Oracle. You learned how to create sub-queries WHERE you use the IN clause as the connection between the main query and the subquery. You examined Oracle's syntax and terminology for outer joins and practiced picking out syntax errors in sample queries.
You saw what makes a subquery become a correlated subquery and found out how to create a correlated subquery. Then you tried your hand at converting a query with a subquery into a query with a correlated subquery and changed the query FROM using the IN clause to using the EXISTS clause. In this module, you learned how to:

1. Identify two proper syntax formats for the IN clause
2. Interpret an outer JOIN using Oracle's syntax
3. Identify the connection between a query and a subquery
4. Use EXISTS instead of IN for a subquery
5. Describe the parsing sequence of queries that use queries in place of a table name

Question: How does one interpret a JOIN using Oracle's syntax?
In Oracle, a JOIN operation combines data from two or more tables based on a related column. There are several types of JOINs, such as INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN. Oracle uses the ANSI SQL-92 standard as well as its traditional syntax for JOIN operations. We will discuss the ANSI SQL-92 syntax here.
Here's a brief explanation of different JOIN types and their interpretation using Oracle's syntax:

1. INNER JOIN: The INNER JOIN retrieves only the rows with matching data in both tables based on the specified condition. In Oracle, you can use the INNER JOIN keyword or simply JOIN.
   

   SELECT e.employee_id, e.name, d.name as department_name
   FROM employees e
   JOIN departments d ON e.department_id = d.department_id;
   

2. LEFT OUTER JOIN: The LEFT OUTER JOIN retrieves all rows from the left table and the matching rows from the right table. If no match is found in the right table, NULL values are returned for the right table's columns. In Oracle, you can use the LEFT OUTER JOIN or LEFT JOIN keyword.
   

   SELECT e.employee_id, e.name, d.name as department_name
   FROM employees e
   LEFT OUTER JOIN departments d ON e.department_id = d.department_id;
   

3. RIGHT OUTER JOIN: The RIGHT OUTER JOIN retrieves all rows from the right table and the matching rows from the left table. If no match is found in the left table, NULL values are returned for the left table's columns. In Oracle, you can use the RIGHT OUTER JOIN or RIGHT JOIN keyword.
   

   SELECT e.employee_id, e.name, d.name as department_name
   FROM employees e
   RIGHT OUTER JOIN departments d ON e.department_id = d.department_id;
   

4. FULL OUTER JOIN: The FULL OUTER JOIN retrieves all rows from both tables, and if there is no match in either table, NULL values are returned for the corresponding columns. In Oracle, you can use the FULL OUTER JOIN or FULL JOIN keyword.
   

   SELECT e.employee_id, e.name, d.name as department_name
   FROM employees e
   FULL OUTER JOIN departments d ON e.department_id = d.department_id;
   

In summary, interpreting a JOIN using Oracle's syntax involves understanding the different JOIN types and their keywords. The JOIN operation is performed based on a related column and a specified condition, which links the tables and defines the matching criteria.

In this module you were introduced to the following glossary terms:

1. Correlated subquery: A subquery in which part of the subquery refers back to data in the outer query.
2. Dynamic list: A list of values created by adding a subquery to a query. This kind of list is dynamic because the list of values always reflects the current values in the database.
3. Inner join: A join where only the rows of one table that are matched with the rows of the other table are used in the query results.
4. Left outer join: An outer join in which the left table in the Fromclause may contain rows that do not match with a row in the right table.
5. Outer join: A special variation of the kind of query that combines two or more tables into a single query. In an outer join, rows in one table that contain no match with the other table are included in the result set returned from the query.
6. Right outer join: An outer join in which the right table in the Fromclause may contain rows that do not match with a row in the left table.

In the next module you will experiment with creating reports within the SQL*Plus environment.