Chapter three joins the query T-SQL Language Foundation

Source: Internet
Author: User

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!


Chapter Three joins the query T-SQL Language Foundation

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.