B1-Query data by using the subqueries and APPLY

Question 1

What does the APPLY operator do?

Answer 1

The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

There are two forms of APPLY:

• CROSS APPLY:
  
  • ​returns only rows from the outer table that produce a result set from the table-valued function.
• OUTER APPLY:
  
  • returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.

Question 2

What is a subquery?

Answer 2

A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery can be used anywhere an expression is allowed.

Many Transact-SQL statements that include subqueries can be alternatively formulated as joins. Other questions can be posed only with subqueries. In Transact-SQL, there is usually no performance difference between a statement that includes a subquery and a semantically equivalent version that does not. However, in some cases where existence must be checked, a join yields better performance

Question 3

What are Comparison Operators (Transact-SQL)

Answer 3

Comparison operators test whether two expressions are the same. Comparison operators can be used on all expressions except expressions of the text, ntext, or image data types. The following table lists the Transact-SQL comparison operators.

COMPARISON OPERATORS (TRANSACT-SQL)

OperatorMeaning

= (Equals)Equal to

> (Greater Than)Greater than

< (Less Than)Less than

>= (Greater Than or Equal To)Greater than or equal to

<= (Less Than or Equal To)Less than or equal to

<> (Not Equal To)Not equal to

!= (Not Equal To)Not equal to (not ISO standard)

!< (Not Less Than)Not less than (not ISO standard)

!> (Not Greater Than)Not greater than (not ISO standard)

Boolean Data Type

The result of a comparison operator has the Boolean data type. This has three values: TRUE, FALSE, and UNKNOWN. Expressions that return a Boolean data type are known as Boolean expressions.

Unlike other SQL Server data types, a Boolean data type cannot be specified as the data type of a table column or variable, and cannot be returned in a result set.

When SET ANSI_NULLS is ON, an operator that has one or two NULL expressions returns UNKNOWN. When SET ANSI_NULLS is OFF, the same rules apply, except for the equals (=) and not equals (<>) operators. When SET ANSI_NULLS is OFF, these operators treat NULL as a known value, equivalent to any other NULL, and only return TRUE or FALSE (never UNKNOWN).

Expressions with Boolean data types are used in the WHERE clause to filter the rows that qualify for the search conditions and in control-of-flow language statements such as IF and WHILE, for example:

Question 4

What are the some of the remarks of CROSS JOINS?

Answer 4

Remarks:

• A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.
• However, if a WHERE clause is added, the cross join behaves as an inner join.
• Cannot be used with a tabled valued function;

Question 5

IN-def, syntax, remarks

Answer 5

def: Determines whether a specified value matches any value in a subquery or a list.
syntax: test_expression [NOT] IN ( subquery | expression [,…n] )
remarks: ​Explicitly including an extremely large number of values (many thousands of values separated by commas) within the parentheses, in an IN clause can consume resources and return errors 8623 or 8632. To work around this problem, store the items in the IN list in a table, and use a SELECT subquery within an IN clause.

Question 6

EXISTS-def, syntax, remarks

Answer 6

def: Specifies a subquery to test for the existence of rows.
syntax: EXISTS ( subquery )

remarks:

• EXISTS returns all data from a query;
• Tests to see if the subquery returns a result. If it does, EXISTS equates to TRUE and returns a row;
• Because EXISTS returns TRUE each time, all data are returned not just the one matching a certain criteria.

Question 7

ALL-def, syntax, remarks

Answer 7

def: Compares a scalar value with a single-column set of values.
syntax: scalar_expression { = | <> | != | > | >= | !> | < | <= | !< } ALL ( subquery );
remarks: ALL requires the scalar_expression to compare positively to every value that is returned by the subquery. For instance, if the subquery returns values of 2 and 3, scalar_expression <= ALL (subquery) would evaluate as TRUE for a scalar_expression of 2. If the subquery returns values of 2 and 3, scalar_expression = ALL (subquery) would evaluate as FALSE, because some of the values of the subquery (the value of 3) wouldn’t meet the criteria of the expression.

For statements that require the scalar_expression to compare positively to only one value that is returned by the subquery, see SOME | ANY (Transact-SQL).

This article refers to ALL when it is used with a subquery. ALL can also be used with UNION and SELECT.

Question 8

SOME | ANY-def, syntax, remarks

Answer 8

def: Compares a scalar value with a single-column set of values. SOME and ANY are equivalent.
syntax: scalar_expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } { SOME | ANY } ( subquery )

remarks:

SOME requires the scalar_expression to compare positively to at least one value returned by the subquery. For statements that require the scalar_expression to compare positively to every value that is returned by the subquery, see ALL (Transact-SQL). For instance, if the subquery returns values of 2 and 3, scalar_expression = SOME (subquery) would evaluate as TRUE for a scalar_express of 2. If the subquery returns values of 2 and 3, scalar_expression = ALL (subquery) would evaluate as FALSE, because some of the values of the subquery (the value of 3) wouldn’t meet the criteria of the expression.

SOME compares a value to a set of values in the subquery and returns TRUE when any one row matches.

Question 9

COALESCE-def, syntax, remarks

Answer 9

def: Evaluates the arguments in order and returns the current value of the first expression that initially doesn’t evaluate to NULL. For example, SELECT COALESCE(NULL, NULL, ‘third_value’, ‘fourth_value’); returns the third value because the third value is the first value that isn’t null;
syntax: COALESCE ( expression [,…n] )
remarks: If all arguments are NULL, COALESCE returns NULL. At least one of the null values must be a typed NULL.