Flashcards in Chapter 4 Combining Sets Deck (54)
What is database normalization?
Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency.
What is a CROSS JOIN?
This join performs a Cartesian Product of the two input tables. In other words, it performs a multiplication between the tables yielding a row for each combination of rows from both sides. If you have m rows in table T1 and n rows in table T2, the result of T1 CROSS JOIN T2 is m * n rows. CROSS JOIN doesn't use the ON clause.
In a self join, is table aliasing mandatory?
Yes. If you don't assign different aliases to the two instances of the table, you end up with an invalid result because there are duplicate column names. By aliasing the tables, you can refer to the columns in an unambiguous way, e.g. D.n vs S.n.
What is the older (equivalent) syntax for CROSS JOIN?
T1 CROSS JOIN T2 is equivalent to FROM T1, T2. Standard SQL and T-SQL support both. However, it is recommended to stick to the newer syntax.
What is an INNER JOIN?
The inner join returns only matching rows for which the predicate evaluates to true. Rows from either side for which the predicate evaluates to false or unknown are discarded.
For INNER JOINs, what is the difference between the ON and the WHERE clauses, and does it matter if you specify your predicate in one or the other?
For inner joins, it does not matter. Since the WHERE is evaluated right after the FROM, conceptually, it is equivalent to concatenating the predicates with an AND operator. However, it's more intuitive to match columns from both sides in the ON clause and predicates that filter columns from only one side in the WHERE clause.
What is the older (equivalent) syntax for INNER JOIN?
Both SQL and T-SQL support the FROM T1, T2 comma syntax for INNER JOINs and then put all predicates in the WHERE clause. If you don't include the WHERE clause with the join predicate, you could end up with an unintentional CROSS JOIN.
When using the newer syntax, is an INNER JOIN valid syntactically without an ON clause?
What happens when you leave off the "INNER" portion of an INNER JOIN?
Since INNER JOIN is the most commonly used type of join, the standard decided to make it the default in case you specify just the JOIN keyword. T1 JOIN T2 is equivalent to T1 INNER JOIN T2.
What is an outer join?
With outer joins, you can request to preserve all rows from one or both sides of the join regardless if there are matching rows in the other side.
What is a LEFT OUTER JOIN?
A LEFT OUTER JOIN (or LEFT JOIN for short) preserves the left table. The join returns what an inner join would normally return, i.e. matches (inner rows). In addition, the join also returns rows from the left table that had no matches in the right table (outer rows) with NULLs used as placeholders in the right side.
For outer joins, what is the difference between the ON and the WHERE clauses, and does it matter if you specify your predicate in one or the other?
(See pg 109) For outer joins, the ON and WHERE clauses play different roles and they are not interchangeable. The WHERE clause still plays a simple filtering role; however, the ON clause is more a matching role. A row in the preserved side will be returned whether the ON predicate finds a match for it or not. The ON is designed to find matches on the non preserved side. In other words, ON is not final with respect to the preserved side of the join. WHERE is final.
What is a RIGHT OUTER JOIN?
A RIGHT OUTER JOIN (or RIGHT JOIN for short) is similar to a LEFT OUTER JOIN except it preserves the right table and returns NULLs for non matches on the left.
What is a FULL OUTER JOIN?
A FULL OUTER JOIN or FULL JOIN preserves both sides. It returns the inner rows that are normally returned from an INNER JOIN plus rows from the left that don't have matches in the right plus rows from the right that don't have matches in the left- nulls as placeholders on both sides where matches are not found.
How are multi joins handles in T-SQL?
A join in T-SQL takes place conceptually between two tables at a time. A multi-join query evaluates the joins conceptually from left to right. The result of one join is used as the left input to the next join. For INNER JOINs and CROSS JOINs, order is not important; however when outer joins are involved, you need to be careful.
How can you handle the logical bugs that come up with multi-joins?
You can separate some of the joins to their own independent logical phase using parenthesis: SELECT .. FROM Production.Suppliers AS S LEFT OUTER JOIN (Production.Products AS P INNER JOIN Production.Categories AS C ON C.categoryid = P.category.id) ON S.suppplierid = P.supplierid.
What are the shortened forms for INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN?
JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
What is a self-contained subquery?
A subquery that has no dependency on the outer query. If you want, you can highlight the inner query and run it independently.
What are the different types of results a subquery can return?
A subquery can return a single value, multiple values, or even an entire table result.
What happens when a scalar subquery returns more than one value?
The code fails at runtime.
What happens when a scalar subquery returns an empty set?
The empty set is converted to NULL.
What's an example of how a scalar subquery can be used?
It can be used where a single value expression is expected like in a one side comparison. WHERE unitprice = (SELECT MIN(unitprice) FROM Production.Products);
What's an example of how a multi-valued subquery can be used?
It can be used where a multi-valued result is expected such as when using the IN or NOT IN predicate, e.g. WHERE supplierid IN (SELECT supplierid FROM Production.Suppliers WHERE country='Japan')
What are the two types of subqueries?
Self contained and correlated.
What is a correlated subquery?
A subquery where the inner query has a reference to a column from the table in the outer query. Unlike the self-contained subquery, you can't simply highlight the inner portion and run it independently.
What is the EXISTS predicate?
The EXISTS predicate accepts a subquery as input and returns true when the subquery returns at least one row and false otherwise. Because it only returns true or false, the SQL Server Query Optimizer ignores the SELECT list of the subquery. Whatever you specify there will not affect optimization choices like index selection.
What is a table expression?
A table expression is a named query. It has three main parts: You write an (1) inner query, (2) name it, and (3) query it from an outer query. T-SQL supports 4 forms of table expressions (1) derived tables, (2) common table expressions (CTEs), (3) views, (4) inline table-valued functions.
What is a derived table?
A named table expression that is visible only to the statement that defines it. A derived table's inner query is defined in parenthesis in the FROM clause of the outer query. The name is specified after the parenthesis. It has the form FROM () AS Name;
What is a common table expression (CTE)?
A named table expression that is visible only to the statement that defines it. Has the following form: WITH AS () ;