Chapter 4: Combining Sets

Question 1

Does a Cross Join affect the logical query processing?

Answer 1

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.

Question 2

What is a Inner Join?

Answer 2

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.

Question 3

What is a Outer Join?

Answer 3

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.

Question 4

What happens in a LEFT or RIGHT JOIN when doesn’t exist a match between two tables?

Answer 4

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.

Question 5

In a OUTER JOIN, what means a preserved side?

Answer 5

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.

Question 6

What is the principal difference between ON clause and the WHERE clause?

Answer 6

The ON clause just appear on the JOIN operations, so it is used for matching purpose. The WHERE clause is used for filtering purpose.

Question 7

What is a FULL OUTER JOIN?

Answer 7

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.

Question 8

What is the difference between the old and new syntax for cross joins?

Answer 8

The new syntax has the CROSS JOIN keywords between the table names and
the old syntax has a comma.

Question 9

What are the different types of outer joins?

Answer 9

Left, right, and full.

Question 10

What is a self contained subquery?

Answer 10

It’s a inner query that doesn’t depends to the outer query. It can runs independently.

Question 11

What is a correlated subquery?

Answer 11

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.

Question 12

What is a table expression?

Answer 12

It is a named query.

Question 13

What are the forms of table expressions?

Answer 13

(1) Derived tables.
(2) Common Table Expression (CTE).
(3) Views.
(4) Inline table-valued functions.

Question 14

What is a derived table?

Answer 14

It is an inner query in the FROM clause. For example, SELECT * FROM (SELECT * FROM TABLE). The inner query is a derived table.

Question 15

What is a Common Table Expression (CTE)?

Answer 15

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.

Question 16

How a CTE recursived can be achieved?

Answer 16

NOTE: Review this

Question 17

What’s the difference between the table expressions like derived tables and CTE and the table expressions like Views and Inline Table-Valued Functions?

Answer 17

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.

Question 18

What’s the difference between Views and Inline Table Valued Functions?

Answer 18

The former doesn’t use input parameters, whereas the latter use input parameters (it’s like a function).

Question 19

What’s the APPLY operator?

Answer 19

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.

Question 20

What’s the difference between a CROSS APPLY and an OUTER APPLY?

Answer 20

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.

Question 21

What is the difference between self-contained and correlated subqueries?

Answer 21

Self-contained subqueries are independent of the outer query, whereas correlated subqueries have a reference to an element from the table in the outer
query.

Question 22

What is the difference between the APPLY and JOIN operators?

Answer 22

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.

Question 23

What are the guidelines to use set operators?

Answer 23

• . 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.

Question 24

What are the different set operators?

Answer 24

UNION (UNION ALL also), INTERSECT and EXCEPT.

Question 25

What is the difference between an UNION and an UNION ALL?

Answer 25

UNION removes duplicates, whereas UNION ALL doesn’t remove duplicates.

Question 26

Give a brief of set operators.

Answer 26

• . 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.

Question 27

What are the table operators which support T SQL?

Answer 27

JOIN (Standard), PIVOT (No standard), UNPIVOT (No standard) and APPLY (No standard)

Question 28

What’s the rule of precedence for set operators?

Answer 28

INTERSECT is the first operation that is achieved, then UNION and EXCEPT are achieved from left to right.