A2-Query multiple tables by using joins

Question 1

What does the COALESCE statement return

Answer 1

COALESCE returns the first NULL value

Question 2

From where can INNER and OUTER JOINs be specified?

Answer 2

• INNER JOINS can be specified in either the FROM or WHERE clauses, FROM is however recommended even for INNER JOINS.
• While OUTER JOINS can only be specified from FROM clauses;

Question 3

Do columns used in a join condition are required to have the same name or be the same data type? If not explain.

Answer 3

Columns used in a join condition are not required to have the same name or be the same data type.

However:

1. if the data types are not identical, they must be compatible;
2. or be types that SQL Server can implicitly convert;
3. If the data types cannot be implicitly converted, the join condition must explicitly convert the data type using the CAST function;

Question 4

How can’t tables be joined?

Answer 4

Tables cannot be joined directly on ntext, text, or image columns. However, tables can be joined indirectly on ntext, text, or image columns by using SUBSTRING, DATALENGTH or other by using other functions that can make these tipes of data types comparable

EX:

1. SELECT * FROM t1 JOIN t2 ON SUBSTRING(t1.textcolumn, 1, 20) = SUBSTRING(t2.textcolumn, 1, 20)-performs a two-table inner join on the first 20 characters of each text column in tables t1 and t2;
2. WHEREDATALENGTH(p1.pr_info) =DATALENGTH(p2.pr_info)

Question 5

Which are the types of Joins?

Answer 5

• INNER JOIN a join that displays only the rows that have a match in both joined table
• LEFT OUTER JOIN- All rows from the first-named table (the “left” table, which appears leftmost in the JOIN clause) are included. Unmatched rows in the right table do not appear.
• RIGHT OUTER JOIN-All rows in the second-named table (the “right” table, which appears rightmost in the JOIN clause) are included. Unmatched rows in the left table are not included;
• FULL OUTER JOIN All rows in all joined tables are included, whether they are matched or not;
• CROSS JOIN-A join whose result set includes one row for each possible pairing of rows from the two tables.

​Remarks:

Using Joins is more efficient than using subqueries

Question 6

What are the advantages of using SQL 92 over SQL-89

Answer 6

SQL-92 syntax produces code that is easier to understand. SQL-Q separates the join conditions from the WHERE clause, which results in clearer, easier to understand Transact-SQL.

using the SQL-92 standard for join syntax is the current best practice.

SQL-92 involves a little bit more typing, but the resulting SQL is easier to understand.

There is no difference in the efficiency of the query plans from SQL-92 syntax and SQL-89 syntax.

SQL-92 syntax is less error prone. You must specify the join condition separately from the WHERE clause, which is likely to result in fewer errors.

Question 7

How are Inner Joins Used?

Answer 7

An inner join is a join in which the values in the columns being joined are compared using a comparison operator.

In the ISO standard, inner joins can be specified in either the FROM or WHERE clause. This is the only type of join that ISO supports in the WHERE clause. Inner joins specified in the WHERE clause are known as old-style inner joins.

Joins Using Operators Other Than Equal

You can also join values in two columns that are not equal. The same operators and predicates used for inner joins can be used for not-equal joins. For more information about the available operators and predicates that can be used in joins.

Joins Using the Not-equal Operator:

You can also join values in two columns that are not equal. The same operators and predicates used for inner joins can be used for not-equal joins.