JOINS

Question 1

What does a CROSS JOIN do?

Answer 1

It performs a Cartesian Product between two inpute tables.

SQL Server 70-461 04-01

Question 2

What is the syntax for a CROSS JOIN?

Answer 2

SELECT Tbl1.col1, Tbl2.col2
FROM Tbl1
CROSS JOIN Tbl2;

You don’t have to select just one column per table, you can select all if you want. For row 1 from Tbl1 a row will be returned for however many rows are in tbl2. The rows will contain data from Tbl1 row 1 and data from each row of Tbl2. This happens for every row in Tbl1.

SQL Server 70-461 04-01

Question 3

What do you need to do in a self-join?

Answer 3

• Give the table tow different aliases.
• So, if you want to join Tbl1 on itself, you could do Tbl1 as A and Tbl1 as B.

SQL Server 70-461 04-01

Question 4

What word can be ommitted from INNER JOIN and still produce the same result?

Answer 4

Since an INNER JOIN is the most common type of join, the word INNER can be ommitted. If you put JOIN only, the default meaning is INNER JOIN.

SQL Server 70-461 04-01

Question 5

What is the difference between a primary key and a foreign key w/ regard to indexes?

Answer 5

• A unique index is automatically created when you make a new primary key or unique constraint.
• When you make a new foreign key, an index is not automatically created.
• An index on a foreign key may improve the performance of a join on it. Decide on a cases by case basis which foreign keys would benefit from an index.

SQL Server 70-461 04-01

Question 6

How does LEFT OUTER JOIN work?

Answer 6

• It returns what an INNER JOIN normally would.
• It also returns rows from the left table that had no matches in the right table, with nulls used as placeholders for the right side.
• All rows on the left side of the join are returned.

SQL Server 70-461 04-01

Question 7

how does a RIGHT OUTER JOIN work?

Answer 7

Same as left, just opposite.

SQL Server 70-461 04-01

Question 8

What is the equivalent of LEFT OUTER JOIN?

Answer 8

• LEFT JOIN
• Outer can be omitted. Same for RIGHT OUTER JOIN

SQL Server 70-461 04-01

Question 9

How does FULL OUTER JOIN wok and what is another equivalent?

Answer 9

• FULL JOIN (omit OUTER)
• Returns same results you would get from an INNER JOIN.
• Also returns rows from left that don’t have a match in right, with nulls as placeholders.
• Also returns rows from right that don’t have a match in left, with nulls as placeholders.

SQL Server 70-461 04-01

Question 10

How does a multi join query work?

Answer 10

• Conceptually, a join takes place between two tables.
• A multi-join query evaluates the joins conceptually from left to right.
• The result of one join is the left input to the next join.

SQL Server 70-461 04-01

Question 11

What is the older syntax for an INNER JOIN and should the old or new syntax be used?

Answer 11

Old
- A comma between the table names in the FROM clause and the predicates in the WHERE clause.

NEW
- T1 INNER JOIN T2
- Use the new syntax. With the old syntax, if you forget to put a predicate in the WHERE clause you will create an unintentional cross join.

SQL Server 70-461 04-01

Question 12

What rows does an INNER JOIN return?

Answer 12

Matching rows for which the predicate evaluates to true. False and unknown are discarded.

SQL Server 70-461 04-01

Question 13

For an INNER JOIN, should you place your predicates in the ON or WHERE clause?

Answer 13

• The predicate that is matching both sides has to be in the ON clause. An error will occur if there is an INNER JOIN and no ON clause.
• Predicates that filter only one side can be in either but putting them in the WHERE clause is more intuitive.

SQL Server 70-461 04-01

Question 14

How does an outer join work?

Answer 14

• You choose to preserve rows from one side of the join or the other.
• Or you preserve rows from both sides

SQL Server 70-461 04-01

Question 15

How does the ON clause differ when used in a LEFT OUTER JOIN vs an INNER JOIN?

Answer 15

• On a LEFT OUTER JOIN, it plays more of a matching role rather than a filtering role.
• A row from the preserved side will be returned regardless of whether a match is found for it.
• Example: ON S.Supplierid=P.supplierid AND s.country=N’Japan will return all records from table S even though we indicated Japan be exluded

SQL Server 70-461 04-01

Question 16

What does the ON clause decide in a LEFT OUTER JOIN?

Answer 16

• You can limit what rows on the non-preserved side are matched to in the ON clause.
• It only determines which rows to return from the non-preserved side, not whether to return rows from the preserved side. ALL rows are returned for the preserved side.
• ON is not final w/ respect to the preserved side of the join.

SQL Server 70-461 04-01

Question 17

How does an INNER JOIN work?

Answer 17

• It matches rows from two tables based on a predicate.
• Remember that a predicate is an expression that evaluates to true or false and excludes unknowns.
• The predicate used in an INNER JOIN usually compares a primary key on one side to a foreign key in another side.
• Example: lefttable.keycol=righttable.keycol

SQL Server 70-461 04-01

Question 18

What is the old syntax for a CROSS JOIN and should you use the old or new syntax?

Answer 18

Old
FROM T1, T2

NEW
FROM T1
CROSS JOIN T2

Use the new version, it is less prone to errors and more consistent

SQL Server 70-461 04-01

Question 19

How is the WHERE clause different in LEFT OUTER JOIN vs INNER JOIN?

Answer 19

• It isn’t. The where clause still plays a simple filtering role. It keeps true cases and discards false or unknown.
• It is final, with respect to the preserved side of the join.

SQL Server 70-461 04-01

Question 20

What can you do if your multi-join query isn’t coming out as expected?

Answer 20

1. Change the order in which you do the joins, if possible
2. Use parenthesis to make sure the join is processed as you want it to be
3. Reposition the ON clause

SQL Server 70-461 04-01

Question 21

What is a Cartesian Product?

Answer 21

• In the context of using two tables, a Cartesian Product is on row for each combination of rows from both tables.
• Total Rows=Rows Tbl1 x Rows Tbl2
• Left table values are A, B, C
• Right table values are: B1, C1, C2, D1

Result Table
Col 1 | Col 2
A | B1
A | C1
A | C2
A | D1
B | B1
B | C1
B | C2
B | D1
C | B1
C | C1
C | C2
C | D1

SQL Server 70-461 04-01