Chapter three joins the query T-SQL Language Foundation

Join query

SQL Server 2008 supports the Join,apply,pivot,unpivot of four table operators----.

The Join table operator is an ANSI standard, and Apply,pivot,unpivot is a T-SQL extension to the standard

Join table operator with three basic types of joins: Cross joins, Inner joins, and outer joins.

Cross joins have only one step: Cartesian product; There are two steps in the inner join: Cartesian product and filtering; outer joins have three steps: Cartesian product, filtering, adding external rows.

(The difference between logical query processing and physical query processing!)

3.1 Crossover join (cross join)

The simplest join, which only implements a logical query step (Cartesian product)

3.1.1 ANSI SQL-92

The general standard is to use ANSI-SQL 92

-- ANSI SQL-92  Use TSQLFundamentals2008; SELECT C.custid, E.empid  from  as C    Cross JOIN  as E

3.1.2 ANSI SQL-89

-- ANSI SQL-89 SELECT C.custid, E.empid  from  as  as E

3.1.3 Self-cross Join

Multiple instances of the same table can also be joined, a function known as a self-join, where all basic join types (cross joins, Inner joins, and outer joins) support self-joins.

-- Self cross-join SELECT   e1.empid, E1.firstname, E1.lastname,  e2.empid, E2.firstname, E2.lastnamefrom as E1     Cross JOIN  as E2; GO -- in a self-join, you must alias the table. If you do not specify an alias for the table, the column names in the join result will be ambiguous. -- the Cartesian product of the self-join is different from the Cartesian product that is not a self-join!

111

Chapter Three joins the query T-SQL Language Foundation