Chapter 04 slides

Chapter 4 Quiz

A full outer join includes rows that satisfy the join condition, plus ______

rows in both tables that don’t satisfy the join condition

A unio combines the rows form two or more ______

SELECT statements

Join

The bringing together of rows from two tables

Join Condition

Specifies how the rows of table A are to be brought together with the rows of table B

Inner Join

For an inner join (default type) only rows from table A and table B that satisfy the join condition are placed on the table C. If rows exist in table A and/or table B that do not satisfy the join condition, these rows will not be present in table C

Qualified Column Name

A fully qualified name consists of four parts: a server name, a database name, a schema name, and the name of the object itself. At times it will be necessary to qualify a column name (either partially or fully) to tell SQL server the source of the column

Explicit Syntax

The join syntax introduced in the SQL-92 standard

Correlation Name or Table Alias

Temporary table names assigned in the FROM clause

Self-Join

A join where a table is joined to itself

Implicit Syntax

Pre SQL92 join Syntax

Outer Join

A join in which all the rows of a table appear in the join results, regardless of whether they have a match in the join condition

Left Outer Join

All of the rows in the left table appear in the join results, regardless of whether they have a match in the join condition

Right Outer Join

All of the rows in the right table appear in the join results, regardless of whether they have a match in the join condition

Full Outer Join

All of the rows in both joined tables appear in the join results regardless of whether they have a match in the join condition

Cross Join (Cartesian Product)

Each row from table A is joined (concatenated ) with each row from table B

Union

Combining the results of two or more SELECTs that return the same number of corresponding columns

SELECT VendorName AS Vendor, InvoiceDate AS Date

FROM Vendors v JOIN Invoices i ON v.VendorID = i.VendorID

This type of join is called ___________

an Inner Join

When you code a union that combines two result sets, which of the following is not true?

the result sets must be derived from different tables

In most cases, the join condition of an inner join compares the primary key of one table to the ________ key of another table

foreign

If you assign an alias to one table in a join, you have to ______

use that name for the table in the query

Table aliases are temporary table names assigned in which clause?

FROM

In a join, column names only need to be qualified where?

when the same column names are used in both tables