Murach's SQL Server for developers 2022: Chapter 4 terms

join

An operation that combines rows from two or more tables into a single result set based on a relationship between them.

join condition

The expression in the ON clause that specifies how rows in one table match rows in another, usually comparing a primary key to a foreign key.

outer join

A join that returns all rows meeting the join condition plus unmatched rows from one or both tables, with NULLs filling the columns from the missing side.

inner join

A join that returns only the rows where the join condition is true in both tables. Unmatched rows are excluded. It is the default when you write JOIN without a type.

ad hoc relationship

A relationship between tables created on the fly in a query's join condition rather than defined by a primary key/foreign key constraint in the database.

qualified column name

A column name prefixed with its table name or alias (such as Vendors.VendorID) to identify which table it comes from. Required when the same column name exists in more than one table in the query.

explicit syntax

The ANSI SQL-92 join syntax that uses the JOIN keyword in the FROM clause and an ON clause to specify the join type and join condition.

table alias

A temporary name assigned to a table in the FROM clause to shorten code and to distinguish tables, required in self joins. Also called a correlation name.

correlation name

Another term for a table alias: a temporary name given to a table within a query.

fully-qualified object name

The complete name of a database object, including all four parts: server.database.schema.object.

partially-qualified object name

An object name that omits one or more of the leading parts (such as schema.object), so the omitted parts default to the current server, database, or schema.

self join

A join of a table to itself using two different aliases, used to compare rows within the same table (for example, employees to their managers).

implicit syntax

The older join syntax that lists tables separated by commas in the FROM clause and puts the join condition in the WHERE clause.

left outer join

An outer join that returns all rows from the left table plus matching rows from the right table. Right-side columns are NULL when there is no match.

right outer join

An outer join that returns all rows from the right table plus matching rows from the left table. Left-side columns are NULL when there is no match.

full outer join

An outer join that returns all rows from both tables, matched where possible. Columns from the side with no match are NULL.

cross join

A join that pairs every row in the first table with every row in the second table and uses no join condition.

cartesian product

The result of combining every row of one table with every row of another. The row count equals the product of the two tables' row counts.

union

A set operator that combines the results of two or more SELECT statements into one result set and removes duplicate rows (UNION ALL keeps them).

set operator

An operator that combines the result sets of two or more queries into one, such as UNION, INTERSECT, and EXCEPT. The queries must return the same number of columns with compatible data types.