Join Tables Flashcards
List the 6 steps that are involved in writing a query that requires joining tables:
- Identify the entities and relationships
- Define the cardinality of the relationships
- Identify the logical expressions for those relationships
- Determine whether to include entities for which the logical expressions are never satisfied
- Write the FROM clause
- Write the SELECT clause
(6.5)
Where are table aliases defined?
In the FROM clause.
6.5
Are table aliases required to be unique?
Yes. Each table alias in the FROM clause is unique.
6.5
Why is it important to identify which entity relationship will be used by the query?
Because multiple relationships between the entities are possible.
(6.6)
What does the cardinality of a relationship describe?
The numeric relationship between two entities.
6.6
The cardinality of a relationship can be summarized with a phrase of the form “M to N” where M and N are each one of four phrases. What are those phrases?
- Zero or One
- Zero or More
- One (One exactly)
- One or More
(6.6 - 6.7)
Should you avoid many-to-many relationships?
Yes
6.8
“One to Zero or More” can be read as…
1-to-many
6.8
“Zero or More to One exactly” can be read as…
many-to-1
6.8
“Zero or more to Zero or More” can be read as…
many-to-many
6.8
What is each type of entity represented in a relational database known as?
A table.
6.9
What does the join type of a join dictate?
Whether or not rows in one table that have no valid matches in the other table will be in the query result.
(6.10)
What type of join only includes rows that represent a match between the two tables?
The INNER JOIN (or just JOIN)
6.10
What type of join should you use if you want your query result to only include valid matches?
INNER JOIN
6.10
Why is an INNER JOIN an appropriate choice for relationship cardinalities that don’t include “Zero or…” on either side?
If there aren’t “Zero or…” on either side than the INNER JOIN would return everything.
(6.10)
What rows does a LEFT OUTER JOIN keep?
Rows in the left table even if there is not match in the right table.
(6.11)
In terms of relationship cardinality, when is the LEFT OUTER JOIN an appropriate choice?
- When the right side is “Zero or…”
- When the query result should include rows in the left table even if there is no match in the right table
(6.11)
Why would you swap the order of the tables when using a LEFT OUTER JOIN?
To keep all rows in the right table rather than the left.
6.11
How do you know which table is considered the left table?
The table listed before the join type is considered the left table.
(6.12)
When using a LEFT OUTER JOIN, why would you put a logical expression in the JOIN condition instead of the WHERE clause?
When the logical expression should only affect the right table.
(6.12)
What operator would you use when adding a logical expression in a JOIN clause?
AND
Ex: AND PAT_ENC_DX.PRIMARY_DX_YN = ‘Y’
(6.12-6.13)
Which clause always maintains or reduces the number of rows in the result?
The WHERE clause.
6.13
What’s the difference between putting a condition in the WHERE clause verses a LEFT OUTER JOIN?
Conditions in the WHERE clause remove rows from the overall query result whereas a LEFT OUTER JOIN condition just ignores mismatches in the right table.
(6.12)
How many joins does the FROM clause evaluate at a time?
One at a time in the order listed.
Note: If you perform a left outer join, then the columns that came from the right table may be NULL for some rows. If you then perform an inner join, those rows may be discarded if NULL s are not properly handled.
(6.18)
