JOINS Flashcards
What does a CROSS JOIN do?
It performs a Cartesian Product between two inpute tables.
SQL Server 70-461 04-01
What is the syntax for a CROSS JOIN?
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
What do you need to do in a self-join?
- 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
What word can be ommitted from INNER JOIN and still produce the same result?
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
What is the difference between a primary key and a foreign key w/ regard to indexes?
- 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
How does LEFT OUTER JOIN work?
- 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
how does a RIGHT OUTER JOIN work?
Same as left, just opposite.
SQL Server 70-461 04-01
What is the equivalent of LEFT OUTER JOIN?
- LEFT JOIN
- Outer can be omitted. Same for RIGHT OUTER JOIN
SQL Server 70-461 04-01
How does FULL OUTER JOIN wok and what is another equivalent?
- 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
How does a multi join query work?
- 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
What is the older syntax for an INNER JOIN and should the old or new syntax be used?
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
What rows does an INNER JOIN return?
Matching rows for which the predicate evaluates to true. False and unknown are discarded.
SQL Server 70-461 04-01
For an INNER JOIN, should you place your predicates in the ON or WHERE clause?
- 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
How does an outer join work?
- 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
How does the ON clause differ when used in a LEFT OUTER JOIN vs an INNER JOIN?
- 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
