About SQL table joins

For example, Microsoft SQL Server 2008 supports four table operator-join,apply,pivot,unpivot in SQL2008. The Join table operator is an ANSI standard, while the other three types are T-SQL extensions to the standard. Today is primarily about working with join tables.

The join table operator operates on two input tables. There are three basic types of joins: Cross joins, Inner joins and outer junctions. The difference between the three types of joins is that they take different logical query processing steps, each of which has a set of steps. The cross join has only one step-cartesian product; There are two steps in the inner join-Cartesian product, filtration, outer coupling has three steps-cartesian product, filtering, adding external rows.

Learning to use the database is t-sqlfundamentals2008 database, database creation code share: HTTP://PAN.BAIDU.COM/S/1JGLBXM2

1. Cross Join

Logically, cross joins are the simplest of joins. A cross join implements only one logical query step (Cartesian product). This step is to manipulate the input two tables and join them to produce a Cartesian product of both. That is, each row of one input table is matched to all rows of another table. If a table has m rows and the other table has n rows, the result set of the M*n row is obtained.

Sql-server supports two standard syntax-ansi SQL-92 and ANSI SQL-89 syntax for cross joins. It is recommended to use ANSI SQL-92, which is explained later for specific reasons.

After you have downloaded the database code and created the T-SQLFUNDAMETALS2008 database, the following example cross-joins a query (using the ANSI SQL-92 syntax) for customers and employees tables in the database. Returns the CustID and Empid in the collection:

--ANSI SQL-92 syntax

Use TSQLFundamentals2008;

SELECT C.custid, E.empid

From Sales.customers as C

Cross JOIN HR. Employees as E;

--ANSI SQL-89 syntax

SELECT C.custid, E.empid

From Sales.customers as C, HR. Employees as E;

There is no logical and performance difference between the two syntaxes, both of which are an integral part of the latest SQL standard, and the latest version of SQL Server fully supports both grammars. It is recommended to use the ANSI SQL-92 syntax, which will become clear after interpreting the inner join.

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

Example: This query generates all possible combinations of employee pairings.

-Self Cross-join

SELECT

E1.empid, E1.firstname, E1.lastname,

E2.empid, E2.firstname, E2.lastname

From HR. Employees as E1

Cross JOIN HR. Employees as E2;

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.

2. Inner joins

Inner JOIN to apply two logical query processing steps: It begins with the Cartesian product of two input tables, like a cross join: then filters the results based on the user-specified predicate. As with cross joins, there are two standard syntaxes for inner joins: ANSI SQL-92 and ANSI SQL-89.

2.1:ansi SQL-92 Syntax

With the ANSI SQL-92 syntax, you specify the INNER join keyword between two table names. The inner keyword is optional because an inner join is the default join method, so you can specify only the Join keyword, but it is recommended to specify it all. The predicate used for filtering is specified in a specially set statement called an ON clause, which is also known as a join condition.

Example: Perform an inner JOIN operation on a Employees table and an Orders table in a TSQLFundamentals2008 database, matching an employee and an order record according to the predicate condition E.empid=o.empid:

--ANSI SQL-92

Use TSQLFundamentals2008;

SELECT e.empid, E.firstname, E.lastname, O.orderid

From HR. Employees as E

JOIN Sales.orders as O

on e.empid = O.empid;

Because SQL uses the three-value predicate logic (True,false,unknown), the ON clause also returns only the rows where the predicate result is true, and does not return a line where the predicate result is false or unknown, if the employee does not have an associated order, These employee-related rows are filtered out during the filtering processing phase.

2.2:ansi SQL-89 Syntax

Similar to cross joins, Inner joins can also be expressed using ANSI SQL-89 syntax. You can place a comma between table names like a cross join, and define conditions in the WHERE clause of the query, such as:

--ANSI SQL-89

SELECT e.empid, E.firstname, E.lastname, O.orderid

From HR. Employees as E, sales.orders as O

WHERE e.empid = o.empid;

Note: The ANSI SQL-89 syntax does not have an ON clause

2.3: More secure inner joins

It is strongly recommended to use the JOIN syntax for ANSI SQL-92 because he is safer in some ways. If you want to write an inner join query, but accidentally forget to specify the join condition. If the ANSI SQL-92 syntax is used at this time, the query statement will be invalid, such as:

--Inner Join Safety

SELECT e.empid, E.firstname, E.lastname, O.orderid

From HR. Employees as E

JOIN Sales.orders as O;

However, if you use the ANSI SQL-89 syntax and forget to specify a join condition, the query is still valid, but execution is indeed a cross-query:

SELECT e.empid, E.firstname, E.lastname, O.orderid

From HR. Employees as E, sales.orders as O;

Because the query does not fail, it may not be possible to discover the logic errors hidden in the statement at one time, resulting in the user getting the wrong results when using the application. For short query statements, programmers are less likely to forget to specify join conditions, but most of the query statements in the product application are more complex than this, involving multiple tables, filtering conditions, and other query elements. Under these complex conditions, the likelihood of forgetting to specify a join condition increases.

3. Outer Joins

3.1: Outer Connection Foundation

Unlike inner joins and cross joins, outer joins are introduced in ANSI SQL-92, so it has only one standard syntax----Specify the JOIN keyword in the middle of the table name and the join condition in the ON clause. Outer joins apply two logical steps applied to the INNER join: (Cartesian product and on filter), plus an outer join-specific third step: add an outer row.

In an outer join, to mark a table as a "reserved" table, you can use the keyword left OUTER join,right OUTER join between table names and the full OUTER join, where the OUTER keyword is optional. Left indicates that the table on the right side is reserved, and it means that the table on the other side is reserved, and full indicates that two tables are reserved for the keyword. The third step in the outer join is to identify those rows in the reserved table that do not find a match in the other table by the on condition, add the rows to the result set generated by the first two steps of the join, and for those columns from the joined non-reserved table, the columns in the appended outer row are null as placeholders.

It is a good idea to understand by example. The following query joins the customers and Orders tables based on the customer ID and customer ID of the order, and returns the customer and their order information. The join type used by the query statement is a LEFT outer join, so the query results also return customers who have not made any orders:

--Customers and their orders, including Customers with no orders

SELECT C.custid, C.companyname, O.orderid

From Sales.customers as C

Left OUTER JOIN sales.orders as O

on c.custid = O.custid;

From the angle of the outer join reservation table, it can be considered that the data row in the outer join result includes two kinds: inner row and outer row. The inner row refers to those rows that match in the other table in the join in the ON clause, and the outer row is the one that cannot be found. When an outer join is used, it is often confusing whether to specify the join condition in the ON clause of the query or in the WHERE clause. Consider this issue from the rows in the reserved table for outer joins, and the filter condition in the ON clause is not final. In other words, the condition in the ON clause does not ultimately determine whether some of the rows in the table will appear in the results, but only whether they can be matched to some rows in the table on the other side. Therefore, when a non-final condition needs to be expressed, the join condition is specified in the ON clause. When a filter is applied after the outer row is generated, and you want the filter condition to be final, you should specify the condition in the where.

It is also a good idea to understand this by example:

In the following query results, when you introduce an outreach:

--Customers and their orders, including Customers with no orders

SELECT C.custid, C.companyname, O.orderid

From Sales.customers as C

Left OUTER JOIN sales.orders as O

on c.custid = O.custid;

We found that when CustID is 22 o'clock in the result, the corresponding OrderID is null, then a condition is added, the outer join query and the CustID result is not 22.

If we specify conditions in on

SELECT C.custid, C.companyname, O.orderid

From Sales.customers as C

Left OUTER JOIN sales.orders as O

On c.custid = O.custid and c.custid<>22;

The result is the same as the result above, and the same data will appear custid=22, if you add a condition in the WHERE clause

SELECT C.custid, C.companyname, O.orderid

From Sales.customers as C

Left OUTER JOIN sales.orders as O

On c.custid = O.custid

WHERE c.custid<>22;

You will find that the data in the custid=22 is filtered out, so adding conditions to the outer join query is optional in the ON clause or in the WHERE clause, so it is recommended that only the conditions that are required for two table joins be placed in the ON clause, if you want to implement a second filter on the result set, Please use the WHERE clause.

Excerpt from SQL Server2008 Tech Insider

Although I have studied SQL before, but still feel that learning is not very thorough, here is recommended a book: Microsoft SQL Server 2008 Technology Insider T-SQL Language Foundation!!! Share Address Http://pan.baidu.com/s/1hqtplqO

After reading, you will find yourself learning a lot of things! Great God Detour, the new mutual encouragement!

About SQL table joins