Skill 1.2 Query multiple tables by using joins

Question 1

What types of joins does T-SQL support?

Answer 1

cross
inner
outer

Question 2

What is the output of a cross join?

Answer 2

A cross join yields a multiplication between the tables. The output will have a number of rows equal to the number of rows in the first table multiplied by the number of rows in the second table, giving every possible combination of rows from the two tables.
This is known as a cartesian product.

Question 3

What is an equijoin?

Answer 3

A join using a predicate with an equality operator

Question 4

What is the output of an inner join?

Answer 4

An inner join returns a combination of rows from each table where the predicate evaluates to true.

Question 5

Where is the inner join’s matching predicate specified?

Answer 5

In the ON clause

Question 6

What is the functional difference between the ON and the WHERE clause in a query using an INNER JOIN?

Answer 6

There is no difference. Both clauses serve to filter rows and only return those where the predicate evaluates to true.

Question 7

What is the output of an OUTER JOIN?

Answer 7

An outer join preserves all rows from one or both sides of the join.

Question 8

In a LEFT OUTER JOIN, what is used as a placeholder for rows where the right side has no match?

Answer 8

NULLs

Question 9

What is the difference between a LEFT OUTER JOIN and a RIGHT OUTER JOIN?

Answer 9

A left outer join preserves all rows from the left table. A right outer join preserves all rows from the right table.

Question 10

What is the functional difference between the ON and the WHERE clause in a query using an OUTER JOIN?

Answer 10

In an outer join, the WHERE clause performs a filtering function (FINAL), and the ON clause performs a matching function (NONFINAL).

Question 11

What is the output of a FULL OUTER JOIN?

Answer 11

A full outer join returns the matched rows (as an inner join would), plus all unmatched rows from the left with NULLs as placeholders on the right, and plus all unmatched rows from the right with NULLs as placeholders on the left.

Question 12

What is a composite join?

Answer 12

A composite join is a join between two tables based on multiple columns.

Question 13

What is a simple way to write a predicate in a join where values can be NULL?

Answer 13

Use ISNULL or COALESCE to substitute a value that can’t normally appear in the data on both sides
select * from table1 INNER JOIN table2
ON (
COALESCE(table1.col,-999) = COALESCE(table2.col,-999)
)

Question 14

Why is it not a good idea to simply use ISNULL or COALESCE to substitute a value that can’t normally appear in the data on both sides when writing a predicate in a join where values can be NULL?

Answer 14

Since the column values have been manipulated, SQL Server cannot trust that the values preserve the ordering behavior of the original values, and may not be able to use an index to seek matching rows (will have to scan instead)

Question 15

What’s a better option than to simply use ISNULL or COALESCE to substitute a value that can’t normally appear in the data on both sides when writing a predicate in a join where values can be NULL?

Answer 15

Switch the comparison to a three-part comparison:
select * from table1 INNER JOIN table2
ON (
table1.col = table2.col
OR
(table1.col IS NULL and table2.col IS NULL)
)

Question 16

Can you use a set operator in the ON clause of a join?

Answer 16

Yes.  You could write something like:
select * from table1 INNER JOIN table2
ON EXISTS
(
SELECT table1.col
INTERSECT
SELECT table2.col
)

Question 17

In a multi-join query, are the joins evaluated at the same time?

Answer 17

No. joins are evaluated conceptually from left to right. The ON clause ordering is what defines the logical ordering.

Question 18

Do you have to use parentheses to force SQL Server to evaluate joins in a certain order

Answer 18

No. SQL Server looks at the ON clause ordering. Parentheses may make things clearer visually, but are not required.