"T-SQL Basics" 02. Join Query

Overview:

This series of "T-SQL Fundamentals" is primarily a summary of the T-SQL Foundation.

"T-SQL Basics" 01. Single-Table query-several SQL query questions

"T-SQL Basics" 02. Join Query

"T-SQL Basics" 03. Subqueries

"T-SQL Basics" 04. Table expressions

"T-SQL Basics" 05. Set operation

"T-SQL Basics" 06. Perspective, inverse perspective, grouping set

"T-SQL Basics" 07. Data modification

"T-SQL Fundamentals" 08. Transactions and concurrency

"T-SQL Basics" 09. Programmable objects

This article is mainly a summary of the basis of multi-table query.

The FROM clause of the query statement is logically the first sentence to be processed, and the table operator can be used to manipulate the input table in the FROM clause.

SQL Server support four table operators:join/apply/pivot/unpivot

One, cross join1. What is a cross join

(1) The input of the two tables to operate, connect them together to generate the Cartesian product of both.

(2) match each row of one input table with all rows of another table.

(3) If a table has m rows and the other table has n rows, the result set of M*n will be obtained .

2. Syntax

Create two table A,c first , as shown in

(1)ANSI SQL-92 syntax

The following query is a cross- join query for table A and C tables

SELECT A.A,C.C from A

Cross JOIN C

Because the a table has 4 rows and theC table has 5 rows, the query generates a result set that contains the data for the 4*5=20 row.

Using the ANSI SQL-92 syntax, you need to use the "Crossjoin" keyword between the two tables participating in a join

(2)ANSI SQL-89 syntax

Add a comma between the from table names

SELECT A.A,C.C from A,c

Both of these syntaxes have no logical or performance difference.

3. self-cross join

A join to the same table is a self-join. Cross joins, Inner joins, and outer joins all support self-joins.

SELECT a1.a,a2.a from A as A1cross JOIN a as A2

A table has 4 rows, and the query generates a result set that contains the data for the 4*4=16 row

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.

In this example, for example, the alias is a1,a2.

Second, INNER join1. What is an inner join

The Cartesian product is performed on two input tables, and the resulting rows are filtered according to the specified predicate.

2. Syntax (1)ANSI SQL-92 syntax

A. specifying the INNER JOIN keyword between two table names

The B.inner keyword is optional because the inner join is the default join method

C. A predicate that filters rows is specified in a clause called an ON, which is also known as a join condition

For example , query A and C tables Perform an inner JOIN operation, and match Table A and table B According to the predicate condition A.id=c.id:

SELECT  a.id,        a.a,        c.id,        c.cfrom    dbo. A        INNER JOIN C on c.id = a.id

How to understand inner joins:

The easiest way to understand inner joins is to think that each row in table A compares to all rows in the C table, and if the ID in table A is equal to the ID in table C, the match succeeds.

Another more formal method is to consider the inner join on the basis of relational algebra, and the join operation first asks the Cartesian product of two tables (4 rows A records the row of C Records =20 Rows, and then filter the rows according to the condition c.id=a.id , eventually returning the lines .

Attention:

The ON clause is similar to WHERE and having words, and the on clause returns only the line that makes the predicate result true, and does not return a result that evaluates to FALSE or Unknow of the line.

(2)ANSI SQL-89 syntax

Similar to cross joins, an inner join can use "," between table names to represent joins, and then define join conditions in the WHERE clause.

SELECT  a.id,        a.a,        c.id,        c.cfrom    dbo. A,        dbo. Cwhere   a.id = c.id

Both syntax execution results and performance are the same for inner joins, but the recommendation is to use the ANSI SQL-92 syntax. Because the ANSI SQL-92 syntax is more secure.

There are two reasons:

A. because if the inner join query, but forgot to write on the condition, the parser will error, invalid execution;

B. using the ANSI SQL-89 Standard, it is possible to forget the join in the WHERE clause, but the parser does not error and the execution succeeds.

For cross joins, it is also recommended to use the ANSI SQL-92 syntax.

There are two reasons:

A. Consistency , uniform use of ANSI SQL-92 syntax

B. if the developer intended to use ANSI SQL-89 syntax to write an inner join query, but forgot to write the join condition in the WHERE clause, then the join type and the cross join of this SQL are the same. Another developer will not be able to judge whether the previous developer wanted to join the query or cross join.

3. Special examples of joins (1) combination Joint

A combined join is a query in which the join condition involves multiple columns on either side of the join. A composite join is typically used when you need to join two tables based on a primary key - foreign key relationship and the primary foreign key relationship is combined (that is, if the relationship is based on more than one column).

If table B defines a foreign key (Id1,id2), referencing the id1,id2 column of table D , now write a primary foreign key relationship to join the two-table query.  

SELECT  *from    binner JOIN DON d.id1 = B.id1and D.id2 = B.id2

(2) unequal connections

The join condition contains only the equality operator, called an equivalent join, and the join condition contains an operator other than an equal sign, called an unequal join.

SELECT    *from      ainner JOIN CON dbo. a.ID < dbo. C.id

If you use a cross join, the resulting results will contain self-pairs (for example,1 and 1), as well as mirrored pairs (for example,1 and 2,2, and 1). If you use an inner join and specify the left key value to be less than the right key value in the join condition, you can eliminate both of these useless cases.

(3) Multi-table connection

When more than one table operator is included in the FROM clause, the table operator is logically processed in left-to-right order.

Table A and table B make a join Association, get the result set ab, and AB will be the input for the second table operator join , with table C JOIN Association, get results ABC, and so on.

So if the FROM clause contains more than one connection, logically only the first join operates on two underlying tables, while the other joins the result of the previous join as input to its left.

Third, outer join1. What is an outer join

Cartesian product for two tables, on filtering, adding external rows

2. Syntax

Only ANSI SQL-92 Syntax

Use between table names

Left OUTER JOIN or

Right OUTER JOIN or

Full OUTER JOIN or

Filtering in the on clause

3. External Connection Basics

(1)The left keyword identifies the drive reservation of the table on the right, and the Word keyword indicates that the row is reserved, and the fullkeyword indicates that the rows on both sides of the table are reserved.

(2) The third logical query processing step of the outer join is to identify those rows in the reserved table that match the on condition in the other table, and then add those rows to the resulting table in the first two steps of the join.

(3) for those columns from a joined non-reserved table, these columns in the appended outer row are NULL as placeholders.

collection a contains two collections, set a1 and set a2b contains two collections , set b1b2a and ba2b2

A and b are left OUTER JOIN ,A1 and A2 are reserved, because A1 cannot find the corresponding data in B , then A1 the corresponding row needs to be persisted, added to the result table generated by the first two steps of the join, and, for non-reserved table columns, NULL is used as a placeholder in the outer row.

Example:

The customer ID and the customer ID of the order are associated with the Customers table and the orders table, and the customer and their order information are returned.

Customer includes two parts: Customers with orders, customers without orders, with left outer joins, query results returned to customers with orders and customers without orders:

SELECT  Cus.custid,        o.orderidfrom    sales.customers as Cus left        OUTER joins Sales.orders as O         on Cus.custid = O.custid

We can check out the customer without order through the following query.

SELECT  Cus.custid,        O.orderid,        O.custid,        o.orderdate,        o.requireddate,        O.shippeddatefrom    sales.customers as Cus left        OUTER JOIN sales.orders as o on cus.custid = O.custidwhere   o . CustID is NULL

1. their IDs are in . The two customers in the query result have NULL columns in the Order table .

2. Logically, these two customer-related data rows are filtered in the second step of the join (filtering based on the ON predicate condition), and in the third part the rows are added as external rows.

3. If an inner join is used, the result will not return these two lines. After you add these two lines, you can leave all the rows of the table to the left in the results.

4. The outer join result can be considered as two kinds, the inner row and the outer row, the inner line is the condition of the ON clause can find the matching rows on the other side, the outer row is unable to find those rows. Inner joins only return internal, outer joins return inner rows and external rows.

5. The condition in the ON clause does not ultimately determine whether some rows in the table will appear in the result, and when deciding which rows can match the non-reserved table, specify the join condition in the on clause. After the outer row is generated, the filter is applied after the outer row is applied, and the filter condition is expected to be final. You should specify the condition in the where clause.

4. using outer joins in a multi-table join

1. The processing order of the external connection can be adjusted, and different output results may be obtained, so it is not possible to adjust their order arbitrarily.

2. for any outer joins (left outer join Yes, right outer join, and full outer join), if followed by an inner join or right outer join, the outer joins are offset by outer rows. The precondition is that the join condition compares the NULL value from the left side of the join to some value on the right side of the join.

four, multi-table query - several SQL query questions

Inter-table Relationship diagram

Before we do the following, we can prepare the environment, the following SQL script can help you create a database, create a table, insert data.

Download script file: Tsqlfundamentals2008.zip

1. return customers from the United States and return the total number of orders and the total quantity of goods traded for each customer.

The subject is an outer join query, you need to find out the total number of orders for all customers (customers with orders and customers without orders), and then associated with the order details, to find out the total amount of all items on each order for each customer.

Then filter out country = ' USA '. To get the total number of orders per customer, count (DISTINCT O.orderid) is required for the statistics.

SELECT  C.custid,        COUNT (DISTINCT o.orderid) as Numorders,        SUM (case if qty is NULL then 0 ELSE qty END) A S totalqtyfrom    sales.customers as C left        OUTER JOIN sales.orders as O in O.custid = C.custid left        OUTER JOI N sales.orderdetails D on d.orderid = o.orderidwhere   country = ' USA ' GROUP by C.custid

2. return customers and their order information, including customers who have not placed any orders.

SELECT  C.custid,        c.companyname,        o.orderid,        o.orderdatefrom    sales.customers as C        left OUTER JOIN sales.orders as O on         O.custid = C.custid

3. return customers who have orders in the year 2 months , and their orders Kusakabe. It also returns customers who have not placed orders on the 2 -month date of the year.

This topic mainly examines the use of on and where in a join query .

(1) The filter filter for the order date must appear in the ON clause , not in the WHERE clause. The where filter is applied only after the outer row has been added, and is final.

(2) The filter for the date of the order is used only to determine whether the match, but not the final condition of the customer line.

SELECT  C.custid,        c.companyname,        o.orderid,        o.orderdatefrom    sales.customers as C        Left OUTER joins Sales.orders as O on        O.custid = C.custid and        o.orderdate = ' 20070212 '

"T-SQL Basics" 02. Join Query