Chapter 05 Using Joins and Subqueries Flashcards
A join is
A join is a query that combines rows from two or more tables or views. Joins are used to relate two or more tables (or views).
Inner Join
Inner joins return only the rows that satisfy the join condition. The most common operator
used to relate two tables is the equality operator (=). If you relate two tables using an equality
operator, it is an equality join, also known as an equijoin. This type of join combines rows
from two tables that have equivalent values for the specified columns.
Simple Inner Joins
A simple inner join has only the join condition specified, without any other filtering conditions.
SELECT locations.location_id, city, department_name
FROM locations, departments WHERE locations.location_id = departments.location_id;
Natural Join
The NATURAL keyword indicates a natural join, where the join is based on all columns that have the same name in both tables. In this type of join, you should not qualify the column names with the table name or table alias name.
SELECT location_id, city, department_name FROM locations NATURAL JOIN departments;
Outer Joins
An outer join returns results based on the inner join condition, as well as the unmatched rows from one or both of the tables.
left outer join
A left outer join is a join between two tables that returns rows based on the matching condition,
as well as unmatched rows from the table to the left of the JOIN clause.
right outer join
A right outer join is a join between two tables that returns rows based on the matching
condition, as well as unmatched rows from the table to the right of the JOIN clause.
self-join
A self-join joins a table to itself. The table name appears in the FROM clause twice, with different
alias names. The two aliases are treated as two different tables, and they are joined as
you would join any other tables, using one or more related columns.
UNION
Returns all unique rows selected by either query. The UNION operator is used to return rows from either query, without any duplicate rows.
UNION ALL
Returns all rows, including duplicates selected by
either query. The UNION ALL operator does not sort or filter the result set; it returns all rows from both queries.
INTERSECT
Returns rows selected from both queries. The INTERSECT operator is used to return the rows returned by both queries.
What is a subquery?
A subquery is a query within a query. A subquery answers the queries that have multiple parts; the subquery answers one part of the question, and the parent query answers the other part. When you nest several subqueries, the innermost query is evaluated first.
If the join condition uses the equality operator (= or IN), it is known as an [Blank]. If any other operator is used to join the tables, it is a [Blank].
equality join.
nonequality join
What is the EXIST operator?
The EXISTS operator checks for the existence of a row in the subquery based on the condition.
The IN operator is equivalent
to [Blank]
=ANY
