Join Tables

Question 1

List the 6 steps that are involved in writing a query that requires joining tables:

Answer 1

1. Identify the entities and relationships
2. Define the cardinality of the relationships
3. Identify the logical expressions for those relationships
4. Determine whether to include entities for which the logical expressions are never satisfied
5. Write the FROM clause
6. Write the SELECT clause

(6.5)

Question 2

Where are table aliases defined?

Answer 2

In the FROM clause.

6.5

Question 3

Are table aliases required to be unique?

Answer 3

Yes. Each table alias in the FROM clause is unique.

6.5

Question 4

Why is it important to identify which entity relationship will be used by the query?

Answer 4

Because multiple relationships between the entities are possible.

(6.6)

Question 5

What does the cardinality of a relationship describe?

Answer 5

The numeric relationship between two entities.

6.6

Question 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?

Answer 6

• Zero or One
• Zero or More
• One (One exactly)
• One or More

(6.6 - 6.7)

Question 7

Should you avoid many-to-many relationships?

Answer 7

Yes

6.8

Question 8

“One to Zero or More” can be read as…

Answer 8

1-to-many

6.8

Question 9

“Zero or More to One exactly” can be read as…

Answer 9

many-to-1

6.8

Question 10

“Zero or more to Zero or More” can be read as…

Answer 10

many-to-many

6.8

Question 11

What is each type of entity represented in a relational database known as?

Answer 11

A table.

6.9

Question 12

What does the join type of a join dictate?

Answer 12

Whether or not rows in one table that have no valid matches in the other table will be in the query result.

(6.10)

Question 13

What type of join only includes rows that represent a match between the two tables?

Answer 13

The INNER JOIN (or just JOIN)

6.10

Question 14

What type of join should you use if you want your query result to only include valid matches?

Answer 14

INNER JOIN

6.10

Question 15

Why is an INNER JOIN an appropriate choice for relationship cardinalities that don’t include “Zero or…” on either side?

Answer 15

If there aren’t “Zero or…” on either side than the INNER JOIN would return everything.

(6.10)

Question 16

What rows does a LEFT OUTER JOIN keep?

Answer 16

Rows in the left table even if there is not match in the right table.

(6.11)

Question 17

In terms of relationship cardinality, when is the LEFT OUTER JOIN an appropriate choice?

Answer 17

1. When the right side is “Zero or…”
2. When the query result should include rows in the left table even if there is no match in the right table

(6.11)

Question 18

Why would you swap the order of the tables when using a LEFT OUTER JOIN?

Answer 18

To keep all rows in the right table rather than the left.

6.11

Question 19

How do you know which table is considered the left table?

Answer 19

The table listed before the join type is considered the left table.

(6.12)

Question 20

When using a LEFT OUTER JOIN, why would you put a logical expression in the JOIN condition instead of the WHERE clause?

Answer 20

When the logical expression should only affect the right table.

(6.12)

Question 21

What operator would you use when adding a logical expression in a JOIN clause?

Answer 21

AND

Ex: AND PAT_ENC_DX.PRIMARY_DX_YN = ‘Y’

(6.12-6.13)

Question 22

Which clause always maintains or reduces the number of rows in the result?

Answer 22

The WHERE clause.

6.13

Question 23

What’s the difference between putting a condition in the WHERE clause verses a LEFT OUTER JOIN?

Answer 23

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)

Question 24

How many joins does the FROM clause evaluate at a time?

Answer 24

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)

Question 25

What allows you to assign new and temporary names to tables?

Answer 25

Table aliases

6.23

Question 26

Where are table aliases defined?

Answer 26

In the FROM clause.

6.23

Question 27

Why can table aliases be used in all the clauses?

Answer 27

Because the FROM clause is evaluated first.

6.23

Question 28

Is it required to enclosed table aliases in either double quotes or square brackets?

Answer 28

The table alias must be enclosed in double quotes or square brackets if the table alias has a space in it.

(6.23)

Question 29

What keyword can you use to make the table alias explicit?

Answer 29

You can use the AS keyword in a table alias to make it explicit, but it is not required.

(6.23)

Question 30

Why would a table need to be included in the FROM clause two times?

Answer 30

If a single row of the query result needs to include information from two distinct rows.

(6.24)

Question 31

Do the table aliases have to be unique if the FROM clause references a table more than once?

Answer 31

Yes. If a table is included in the FROM clause more than one, then aliases are required to tell each copy apart.

(6.24)

Question 32

Why would a table ever be joined to itself?

Answer 32

If both entities are found in the same table.

This is also an example of when table aliases are required.

(6.25)

Question 33

What are entities represented by?

Answer 33

Tables.

6.27

Question 34

What are relationships represented by?

Answer 34

Foreign keys

6.28

Question 35

Are all relationships represented by foreign keys?

Answer 35

No. The idea that relationships are foreign keys is a rule of thumb. It does not always work.

(6.29)