Joins Flashcards
Inner join
Returns only the rows that satisfy the join condition
How does Oracle handle a join of 3 or more tables?
Two at a time. Joins first two, then the result with the third, then that result with the 4th, to the nth. In ANSI format, join operations go from left to right.
Where can aliases be used?
They should be defined in FROM and then references to that table should use the alias in SELECT and/or WHERE instead of defining the table.
NATURAL JOIN
Based on all columns that have the same name in both tables. In this case, don’t specify the table names or aliases. For example:
SELECT location_id, city, department_name
FROM locations NATURAL JOIN departments;
The common column location_id with be used to join the tables. Common column must also have same datatype.
JOIN…USING
Specifies columns to be used to join the tables in case not all matching columns are to be used or if some of them don’t have matching datatypes. Can’t use table identifiers or aliases in USING.
JOIN…ON
Specifies possibly non-matching column names to be used in join. Can use table identifiers and aliases. Can be used if there are 3 or more tables with a common table name to specify which of the tables’ columns to use.
Cartesian joins
If there are no matching columns, no relationship is specified in WHERE and no join condition is in FROM, Oracle joins each row of the first table to every row of the second. A cartesian join on 2 tables of 3 rows and 4 rows will return a result with 12 rows.
Also known as a “cross join” in ANSI syntax.
Outer joins
Returns results based on the INNER JOIN, as well as unmatched rows from one or both of the tables. Place a (+) next to the table you want to include all of in the WHERE clause. Otherwise use LEFT OUTER JOIN to include all of the table on the left, or RIGHT OUTER JOIN for the table on the right.
Full Outer Joins
Can’t use (+) on both tables. Must use either ANSI syntax FULL OUTER JOIN or Oracle’s UNION operator. where the first SELECT has (+) on the first table and the SELECT on the other side of the UNION operator has (+) on the other table.
Self joins
If needing to compare data from one table to other data in the same table but not in the format listed, (such as data in one column or row compared to data in the same column or row), join the table to itself.
Ex. If an employee table lists each employee’s supervisor by employee ID from elsewhere in the table, do a self-join to return a table showing each employee and their supervisor by name.
ANSI syntax requires JOIN…ON.
Nonequality Joins
Describes joins where an operator other than = is used, such as <=, >=, !=, etc.
Set Operators
UNION - all unique rows from either query
UNION-ALL - all rows from both including duplicates
INTERSECT - rows that appear in both queries
MINUS - rows from first query minus the second query
Evaluated by parentheses, then left to right.
Subqueries
These are queries within other queries.
-If in FROM of top query, known as Inline View. No limit to how many of these can be nested together.
Nested Subquery
Query nested in the WHERE clause. Limit 255 of these.
Correlated Subquery
When a column from the parent query’s table is used in the subquery. Evaluated once for every row in parent query.
