Flashcards in Chapter 4: Combining Sets Deck (28):
Does a Cross Join affect the logical query processing?
Yes, according to the main logical query processing, the FROM clause is evaluated first that the WHERE clause. But SQL Server Optimizer says let's evaluate first the WHERE clause to filter the joined tables, and then let's evaluate the FROM clause doing the MATCH in the ON clause.
What is a Inner Join?
Match rows from two tables based on a predicate, usally one that compares a primary key value in one side to a foreign key value in another side.
What is a Outer Join?
With outer joins, you can request to preserve all rows from one or both sides of the join, never
mind if there are matching rows in the other side based on the ON predicate.
What happens in a LEFT or RIGHT JOIN when doesn't exist a match between two tables?
If it is a LEFT JOIN, the column values for the right table will be placeholded with NULL. In case of a RIGHT JOIN, the column values for the left table will be placeholded with NULL.
In a OUTER JOIN, what means a preserved side?
If we indicate a LEFT JOIN, the preserved side will be the left table, eg. "T1 LEFT JOIN T2", T1 will be the preserved table. In a RIGHT JOIN, the preserved side will be the right table. Also, this means if the predicate which appears on the ON clause returns false or unknown with a specific row, the column values from the row of preserved side will be returned.
What is the principal difference between ON clause and the WHERE clause?
The ON clause just appear on the JOIN operations, so it is used for matching purpose. The WHERE clause is used for filtering purpose.
What is a FULL OUTER JOIN?
It's a JOIN operation that preserves both tables.
A full outer join 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, with NULLs used as placeholders in
the right side; plus rows from the right that don’t have matches in the left, with NULLs used as
placeholders in the left side.
What is the difference between the old and new syntax for cross joins?
The new syntax has the CROSS JOIN keywords between the table names and
the old syntax has a comma.
What are the different types of outer joins?
Left, right, and full.
What is a self contained subquery?
It's a inner query that doesn't depends to the outer query. It can runs independently.
What is a correlated subquery?
Correlated subqueries are subqueries where the inner query has a reference to a column from
the table in the outer query. They are trickier to work with compared to self-contained subqueries
because you can’t just highlight the inner portion and run it independently.
What is a table expression?
It is a named query.
What are the forms of table expressions?
(1) Derived tables.
(2) Common Table Expression (CTE).
(4) Inline table-valued functions.
What is a derived table?
It is an inner query in the FROM clause. For example, SELECT * FROM (SELECT * FROM TABLE). The inner query is a derived table.
What is a Common Table Expression (CTE)?
A common table expression (CTE) is a similar concept to a derived table in the sense that it’s
a named table expression that is visible only to the statement that defines it. Like a query
against a derived table, a query against a CTE involves three main parts:
(1) The inner query.
(2) The name you assign to the query and its columns.
(3) The outer query.
How a CTE recursived can be achieved?
NOTE: Review this
What's the difference between the table expressions like derived tables and CTE and the table expressions like Views and Inline Table-Valued Functions?
Derived Tables and CTE aren't defined in the database as objects, whereas Views and Inline Table - Valued Functions are stored in the database as objects, making both reusable.
What's the difference between Views and Inline Table Valued Functions?
The former doesn't use input parameters, whereas the latter use input parameters (it's like a function).
What's the APPLY operator?
The APPLY operator is a powerful operator that you can use to apply a table expression given
to it as the right input to each row from a table expression given to it as the left input. What’s
interesting about the APPLY operator as compared to a join is that the right table expression
can be correlated to the left table; in other words, the inner query in the right table expression
can have a reference to an element from the left table. So conceptually, the right table expression is evaluated separately for each left row. This means that you can replace the use of cursors in some cases with the APPLY operator.
What's the difference between a CROSS APPLY and an OUTER APPLY?
The CROSS APPLY operator return left rows only for which the right table expression doesn't return an empty set, whereas OUTER APPLY returns also left rows for those rows which the right table expression return an empty set.
What is the difference between self-contained and correlated subqueries?
Self-contained subqueries are independent of the outer query, whereas correlated subqueries have a reference to an element from the table in the outer
What is the difference between the APPLY and JOIN operators?
With a JOIN operator, both inputs represent static relations. With APPLY, the left side is a static relation, but the right side can be a table expression with
correlations to elements from the left table.
What are the guidelines to use set operators?
-. The number of columns in the queries has to be the same and the column types of corresponding columns need to be compatible (implicitly convertible).
-. Set operators consider two NULLs as equal for the purpose of comparison. This is quite unusual when compared to filtering clauses like WHERE and ON.
-. Just an ORDER BY clause it's accepted and it needs to be in the last query. An individual query cannot have a ORDER BY clause.
-. The column names of result columns are determined by the first query.
What are the different set operators?
UNION (UNION ALL also), INTERSECT and EXCEPT.
What is the difference between an UNION and an UNION ALL?
UNION removes duplicates, whereas UNION ALL doesn't remove duplicates.
Give a brief of set operators.
*. The UNION operator unifies the input sets, returning distinct rows.
*. The UNION ALL operator unifies the inputs without eliminating duplicates.
*. The INTERSECT operator returns only rows that appear in both input sets, returning distinct rows.
*. The EXCEPT operator returns the rows that appear in the first set but not the second, returning distinct rows.
What are the table operators which support T SQL?
JOIN (Standard), PIVOT (No standard), UNPIVOT (No standard) and APPLY (No standard)