OCA 11G- Chapter 4

Subqueries

A query within a query (query inception) Subquery answers one part of a question, and the parent query answers the other Can be used with all DML statements When you nest many subqueries, the innermost query is evaluated first 3 kinds: 1)Nested Subquery 2)Correlated Subquery 3)Scalar Subquery

Nested Subquery

A subquery in the WHERE clause of a query. Can have 255 levels of nested subqueries

2 operators allowed when doing an outer join

AND & =

Simple INNER JOIN

Has only the join condition specified, w/out any other filtering conditions -It is not necessary for the column names in both tables to have the same name -Qualify column names (locations.location_id) to avoid ambiguity and/or if the same column name appears in more than one table used in the query

LEFT OUTER JOIN

Join between 2 tables that returns rows based on the matching condition, as well as unmatched rows from the table to the left of the JOIN clause "OUTER" is optional. LEFT JOIN returns same data

Table Alias

Just like columns, tables can have aliases Increase readability Specify table alias name next to the table, separated w/ a space Example: SELECT l.location_id, city, dept_name FROM locations l, dept d WHERE l.location_id=d.location_id; When you use table alias names, you must qualify the column names with the alias name only (if there are no common columns you don't HAVE to qualify, but it increases DB performance to do so) If there are column names common to multiple tables used in a join, you must qualify the column name with a table name or table alias

Multiple-Row Subqueries

Return more than one row of results from the subquery You cannot use a single-row comparison operator with these Use multiple-row operators (IN, EXISTS, ANY, SOME, & ALL)[you can also use NOT for the IN and EXISTS] ANY & SOME are synonymous ANY, SOME, & ALL operators must always be preceded by any of the single-row conditional operators & are used to compare a value returned by the subquery Review pg 224 for the list of ANY and ALL operator meaning (IE: "<ANY" = less than the maximum)

Single Row Subqueries

Return only one row of result Uses a single-row operator (the common operator is the equality operator (=)) The parent query can return more than one row All single-row comparison operators can be used ( =, >, >=, <, <=, or <>) Example: SELECT last_name, first_name, salary FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);

INNER JOIN

Return only the rows that satisfy the Join condition Most common operator used to relate 2 tables is the equality operator (=) If you relate 2 tables using an equality operator, it is an "equijoin" -Combines rows from 2 tables that have equivalent values for the specified columns

Scalar Subquery

Returns a single row and a single column value (one column value from one row) Can be used anywhere a column name or expression can be used Can use in most places where you would use a column name or expression (in VALUES clause of an INSERT statement, in an ORDER BY clause, etc.) or in a CASE Examples on pg 229-230

FULL OUTER JOIN

Returns unmatched rows from both sides

UNION ALL

Same as UNION, but does not filter result set so duplicates are possible

RIGHT OUTER JOIN

Same as left, but returns unmatched rows from the table to the right of the JOIN clause

NATURAL JOIN

The join is based on all columns that have the same name in both tables -(Do not qualify the column names w/ the table name on the join line (you can in the SELECT line)) The columns that are being joined need to have the same datatype Example: SELECT location_id, city, dept_name FROM locations NATURAL JOIN depts; --> location_id is the shared column so that acts like the bridge between the two to join them If you use SELECT *, common columns are only listed once in the result set

CROSS JOIN (Cartesian Join)

This happens when data is selected from 2 or more tables & there is no common relation in the WHERE clause -Cartesian joins are default if you don't specify which kind of join "CROSS JOIN" = ANSI syntax of a Cartesian It basically takes table A x table B (joins every row from the first table to every row in the second table) Example: if the first table has 3 rows and the second has 4, the result will have 12 Should try to be avoided and usually only happen when people forget to specify which JOIN condition to use. However, they can be helpful if you want a ton of data at once

JOIN...USING

Used if there are many columns that have the same name in the tables you are joining and they do not have the same datatype OR if you want to specify which columns to use with an EQUIJOIN Example using more than 2 tables: SELECT region_name, country_name, city FROM regions JOIN countries USING (region_id) JOIN locations USING (country_id);

MINUS

Used to find rows from the first query, but not the second (unique rows)

Outer Joins

Used to incorporate data that doesn't have a matching row Returns results based on the inner join condition, as well as the unmatched rows from one or both of the tables Traditional Oracle syntax uses "(+)" to denote an outer join Place this beside the column name of the table in the WHERE clause where there may not be a corresponding row Example: Using A and B to do an outer join on, and you want to return all rows for A, you apply the (+) (outer join operator) next to B. For all rows in A that don't have a match in B, the query returns NULL values for the columns in B. The order of tables in the query's FROM clause determines if it is a left outer or right outer (if all rows returned on the left, then it is a left outer join. The above with A & B would be a left outer if A was listed first)

UNION

Used to return rows from either query, without any duplicate rows.

INTERSECT

Used to return rows returned by both queries. Ex: If you are selecting the same info from the same table, but you have different conditions in the WHERE clause, this would only return rows that combine the two and meet that requirement

Set Operators

Used to select data from multiple tables Basically combines the result of two queries There are 4 set operators: 1)Union 2)Union All 3)Intersect 4)Minus

JOIN...ON

Used when you either don't have common column names between tables to make a join, or if you want to specify arbitrary join conditions

Correlated Subquery

When a column from the table used in the parent query is referenced in the subquery For each row processed in the parent query, the correlated subquery is evaluated once The parent query can be a SELECT, UPDATE, or DELETE statement

Complex INNER JOIN

When you use another condition besides the join condition in the WHERE clause Example (starting from WHERE): WHERE locations.location_id = dept.location_id AND country_id != 'US';

Inline View

When you use subqueries in the FROM clause of a top-level query You can nest any such queries (Oracle has no limit) You can write queries to find top-n values because you can use an ORDER BY clause in an inline view

Joins

Whenever you use 2 or more tables or views in a single query, it is a join query A "join" is a query that combines rows from 2 or more tables or views (redundant? yes.)

False

You can qualify column names when specifying the USING clause

True

You cannot qualify column names using a NATURAL JOIN