This is the 20th day of my participation in Gwen Challenge.
Star: github.com/Jackson0714… Personal website: www.passjava.cn
This article is a summary of the basics of multi-table queries.
The FROM clause of a query statement is logically the first clause to be processed, and the table operator can be used to operate on the input table within the FROM clause.
SQL Server 2008 supports four table operators: JOIN, APPLY, PIVOT, and UNPIVOT
1. Cross connection
1. What is cross join
(1) Operate the two input tables, join them, and generate the Cartesian product of the two.
(2) Match each row of an input table with all rows of another table.
(3) If one table has M rows and the other table has N rows, an m* N result set will be obtained.
2. Grammar
Start by creating two tables A and C, as shown below
(1) ANSI SQL-92 syntax
The following query is A cross-join query against tables A and C
SELECT A.a,C.c FROM A
CROSS JOIN C
Because table A has 4 rows and table C has 5 rows, this query produces A result set with 4*5=20 rows of data.
Using ANSI SQL-92 syntax, you need to use the “Cross JOIN” keyword between the two tables participating in the JOIN
(2) ANSI SQL-89 syntax
Place a comma between the table names of FROM
SELECT A.a,C.c FROM A,C
Copy the codeThere is no logical or performance difference between the two grammars.
3. Self-cross connection
A join to the same table is a self-join. Cross join, inner join, outer join all support self – join.
SELECT A1.a,A2.a FROM A AS A1
CROSS JOIN A AS A2
Copy the codeTable A has 4 rows, and the query produces A result set with 4*4=16 rows of data
In a self join, the table must be aliased. If you do not specify an alias for the table, the column names in the join result will be ambiguous.
For example, in this case, the aliases are A1,A2.
Two, internal connection
1. What is inner join
Cartesian product is performed on the two input tables, and the resulting rows are filtered according to the specified predicate.
2. Grammar
(1) ANSI SQL-92 syntax
A. Specify the INNER JOIN keyword between the two table names
The b.inner keyword is optional because inner join is the default join
C. The predicate that filters rows is specified in a clause called ON, also known as a join condition
For example, select table A and table C, perform an inner join, match table A and table B according to predicate condition A.id=C.id:
SELECT A.id ,
A.a ,
C.id ,
C.c
FROM dbo.A
INNER JOIN C ON C.id = A.id
Copy the code
How to understand inner join:
The easiest way to understand inner joins is to think that each row in table A is compared to all the rows in table C, and if the ids in table A are equal to those in table C, the match is successful.
Another, more formal approach is to consider inner joins on A relational algebraic basis. The join operation first takes A Cartesian product of two tables (4 rows of A records * 5 rows of C records =20 rows of records), then filters the rows according to the condition C.ID =A.id, and returns 16 rows.
Note:
Like the WHERE and HAVING clauses, the ON clause returns only the line that makes the predicate TRUE, not the line that makes the result FALSE or unknown.
(2) ANSI SQL-89 syntax
Like a cross join, an inner join can be represented by “, “between table names, and then the join conditions can be defined in the WHERE clause.
SELECT A.id ,
A.a ,
C.id ,
C.c
FROM dbo.A ,
dbo.C
WHERE A.id = C.id
Copy the codeThe results and performance of the two syntax for inner joins are the same, but the ANSI SQL-92 syntax is recommended. Because ANSI SQL-92 syntax is more secure.
There are two reasons:
A. Because if you use an inner join query, but forget to write ON condition, then the parser will report an error, execution invalid;
B. Using the ANSI SQL-89 standard, it is possible to forget the join in the WHERE sentence, but the parser does not report an error and the execution succeeds.
ANSI SQL-92 syntax is also recommended for cross joins.
There are two reasons:
A. Ensure consistency and use ANSI SQL-92 syntax
B. If a developer intended to write an inner join query using ANSI SQL-89 syntax, but forgot to include the join condition in the WHERE clause, the type of join is the same as a cross join. The other developer, looking back, cannot tell whether the previous developer wants to inline join the query or cross join it.
3. Special join instances
(1) Combined connection
A composite join is a query whose join conditions involve multiple columns on both sides of the join. A combined join is usually used when two tables need to be joined based on a primary key-foreign key relationship and the primary foreign key relationship is composite (that is, the relationship is based on multiple columns).
If table B defines a foreign key (ID1, ID2) and references id1, ID2 columns of table D, now write a primary foreign key relationship to join the query from the two tables.
SELECT *
FROM B
INNER JOIN D
ON D.id1 = B.id1
AND D.id2 = B.id2
Copy the code
(2) unequal connection
A join condition contains only the equal operator, called an equal join, and a join condition contains operators other than the equal operator, called an unequal join.
SELECT *
FROM A
INNER JOIN C
ON dbo.A.id < dbo.C.id
Copy the codeIf you use a cross join, the result will contain self-even pairs (for example, 1 and 1) and mirror pairs (for example, 1 and 2, 2 and 1). Using an inner join and specifying in the join condition that the left key value is less than the right key value, you can eliminate both of these useless cases.
(3) multi-table join
When multiple table operators are contained in the FROM clause, the table operators are logically processed FROM left to right.
JOIN association between table A and table B yields the result set AB, which takes the input of the second table operator JOIN and joins with table C to produce ABC, and so on.
So if the FROM clause contains multiple joins, logically only the first join operates on the two underlying tables, while the other joins take the result of the previous join as input to their left.
Three, external connection
1. What is external connection
Perform cartesian product ON two tables, filter ON, and add external rows
2. Grammar
Only ANSI SQL-92 syntax
Between table names
The LEFT OUTER JOIN or
RIGHT OUTER JOIN or
Or FULL OUTER JOIN
Filter in the ON sentence
3. External connection foundation
(1) LEFT keyword indicates that the LEFT table is reserved, RIGHT keyword indicates that the RIGHT row is reserved, and FULL keyword indicates that the LEFT and RIGHT tables are reserved.
(2) The third logical query processing step of the outer join is to identify those rows in the reserved table that cannot be found in another table according to the ON condition, and then add these rows to the result table generated in the first two steps of the join.
(3) NULL is used as a placeholder for those columns in the appended outer row from the non-reserved table of the join.
Set A contains two sets, A1 and A2, and set B contains two sets, B1 and B2. The intersection of A and B is A2 and B2
Select * from LEFT OUTER JOIN A and B; select * from LEFT OUTER JOIN B; select * from LEFT OUTER JOIN B; select * from LEFT OUTER JOIN B;
Example:
The Customer ID and the Customer ID of the order associate the Customer and Orders tables and return the Customer and their order information.
Customer consists of two parts: customers with orders, customers without orders, left outer join, query results return customers with orders and customers without orders:
SELECT Cus.custid ,
o.orderid
FROM Sales.Customers AS Cus
LEFT OUTER JOIN Sales.Orders AS O
ON Cus.custid = O.custid
Copy the code
We can query customers without orders through the following query
SELECT Cus.custid ,
O.orderid ,
O.custid ,
O.orderdate ,
O.requireddate ,
O.shippeddate
FROM Sales.Customers AS Cus
LEFT OUTER JOIN Sales.Orders AS O ON Cus.custid = O.custid
WHERE o.custid IS NULL
Copy the code
-
Their ids are 22 and 57. Both customers have NULL columns in the Order table in the query result.
-
Logically, the two customer-related data rows are filtered in the second step of the join (filtering based ON the ON predicate condition), and then added as external rows in the third part.
-
If an inner join is used, these two rows will not be returned as a result. After you add these two rows, you can keep all the rows from the left table in the result.
-
The outer join result can be regarded as two kinds, the inner line and the outer line, the inner line is the condition of the ON sentence can be found ON the other side of the matching lines; External rows are those rows that can’t be found. An inner join returns only inner rows, while an outer join returns inner and outer rows.
5. The condition in the ON clause does not ultimately determine whether the middle branch of the reserved table will appear in the result. When determining which rows can match the non-reserved table, specify the join condition in the ON clause. When you want to apply an external row after generating it, you want to apply a filter, and you want the filter condition to be final. You should specify the condition in the WHERE clause.
4. Use outer join in multi-table join
-
Adjusting the order in which external joins are processed may result in different output results, so do not adjust their order arbitrarily.
-
For any outer join (left outer join, right outer join, and full outer join), if followed by an inner join or a right outer join, it cancels out the outer row of the outer join. The premise is that the join condition compares NULL values from the left side of the join with some values from the right side of the join.
Four, multi table query – a few SQL query questions
Diagram of relationships between tables
Before doing the following topic, we can first prepare the environment, the following SQL script can help you to create a database, create tables, insert data.
Download the script file: tsqlals2008.zip
1. Return customers from the US and return the total number of orders and the total number of goods traded for each customer.
This question is an external join query, need to query all customers (customers with orders and customers without orders) corresponding to the total number of orders, and then associated with the order details, query each customer corresponding to all orders of all goods total number of transactions.
Then filter out country = ‘USA’. To get the total number of orders for each customer, COUNT(DISTINCT O.oderID) is used for statistics.
SELECT C.custid ,
COUNT(DISTINCT O.orderid) AS numorders ,
SUM(CASE WHEN qty IS NULL THEN 0 ELSE qty END) AS totalqty
FROM Sales.Customers AS C
LEFT OUTER JOIN Sales.Orders AS O ON O.custid = C.custid
LEFT OUTER JOIN Sales.OrderDetails D ON D.orderid = O.orderid
WHERE country = 'USA'
GROUP BY C.custid
Copy the code2. Return information about customers and their orders, including customers who have not placed any orders.
SELECT C.custid ,
C.companyname ,
O.orderid ,
O.orderdate
FROM Sales.Customers AS C
LEFT OUTER JOIN Sales.Orders AS O
ON O.custid = C.custid
Copy the code3. Return customers who placed orders on February 12, 2007, and their orders. It also returns customers who did not place an order on February 12, 2007.
Use of ON and WHERE in a join query
(1) The filtering condition of the order date must appear in the ON sentence, not the WHERE sentence. The WHERE filter condition is applied after the external row has been added, and is final.
(2) The filtering condition of the date of the order is only used to determine whether there is a match, but it is not the final condition to determine the customer’s bank.
SELECT C.custid ,
C.companyname ,
O.orderid ,
O.orderdate
FROM Sales.Customers AS C
LEFT OUTER JOIN Sales.Orders AS O
ON O.custid = C.custid
AND O.orderdate = '20070212'
Copy the codeReferences:
SQL2008 technical Insider: T-SQL Language Basics
Welcome to follow my official account: “Wukong Chat Framework”
About the author: 8-year Veteran of Internet workplace | full stack engineer | super dad after 90 | open source practitioner | owner of public number 10,000 fans original number. Blue Bridge signed the author, the author of “JVM performance tuning practice” column, handwritten a set of 70,000 words SpringCloud practice summary and 30,000 words distributed algorithm summary. Welcome to follow my public account “Wukong Chat framework”, free access to learning materials.
I am Wukong, strive to become stronger, become super Saiya people!