Join is one of the important operations of the relational database system. The commonly used Join contained in SQL Server: internal connection, external interface and cross -connection. If we want to obtain data that matches the lines in one table with the lines in one table with two or more tables, then we should consider using Join, because Join specific connection table or function to query the characteristics to query the characteristics to query the characteristics of querying characteristics to query the characteristics of querying characteristics to query the characteristics.
This article will introduce the characteristics and use of various commonly used Join in SQL through specific examples:
1.1.2 text
First of all, we define the three tables of College, Student, and Apply in Tempdb. The specific SQL code is as follows:
—- If database exists the same name datatable deletes it.
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ‘College’) DROP TABLE College;
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ‘Student’) DROP TABLE Student;
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ‘Apply’) DROP TABLE Apply;
—- Create Database.
create table College(cName nvarchar(50), state text, enrollment int);
create table Student(sID int, sName nvarchar(50), GPA real, sizeHS int);
create table Apply(sID int, cName nvarchar(50), major nvarchar(50), decision text);
Inner join
Inner Join is one of the most commonly used connection types. It querys data that meets the connection predicate.
Suppose we want to check the relevant information of the school in the application form. Since the Apply table contains the school name, we cannot predict, so we can connect to the COLLEGE and Apply according to the Inner Join table, so as to find the Apply table in the Apply table Including school information.
specific SQL code is as follows:
—- bases on college name.
SELECT DISTINCT College.cName, College.enrollment
FROM College INNER JOIN
Apply ON College.cName = Apply.cName
Figure 1 query results
| cName | state | enrollment |
| Stanford | CA | 15000 |
| Berkeley | CA | 36000 |
| MIT | MA | 10000 |
| Cornell | NY | 21000 |
| Harvard | MA | 29000 |
Table 1 data in the collection table
As shown in Figure 1 above, we found out the school information contained in the Apply table. Since Harvard was not found out, we knew that there was no student to apply for Harvard for the time being.
Inner Join satisfies the exchange law: “A Inner Join B” and “B Inner Join A” are equal.
Outer join
Suppose we want to see all school information; even those who have not applied (such as: Harvard), at this time we can use Outjoin for query. Because the external connection saves all the rows of one or two input tables, even if the lines of matching the connection words cannot be found.
specific SQL code is as follows:
SELECT College.cName, College.state, College.enrollment,
Apply.cName, Apply.major, Apply.decision
FROM College LEFT OUTER JOIN
Figure 3 Left link query results
As shown in Figure 3 above: Since there is no student applied for Harvard in the Apply table, we query all school information through the left connection.
Since the left connection (Left Outr Join) generates a complete set of tables, and the Apply table is worthy of value, and those who do not match are replaced by NULL, so we know that there is no student in the Apply table to apply for Harvard.
Inquiry through the left couplet, we can get the complete collection of College. Suppose we are now to get the complete set of College and get the full episode of Apply. Then we can consider using Full Outrin. Using a complete external connection, we can query all schools, whether they are matched with the predicate words:
SELECT College.cName, College.state, College.enrollment,
Apply.cName, Apply.major, Apply.decision
FROM College FULL OUTER JOIN
Apply ON College.cName = Apply.cName
Figure 3 complete external connection query results
Now we have obtained a complete data set between College and Apply, which are worthy of matching in the table. Even if it is not found, it is replaced by NULL.
below shows the situation of the data line when each external connection is matched:
|
connection type |
Reserve data rows |
|
A left outer join B |
all A rows |
|
A right outer join B |
all B rows |
|
A full outer join B |
all A and B rows |
Table 2 External connection Reserve data row
FULL OUTER JOIN meets the exchange law: “A Full Outr Join B” and “B Full Outr Join A” are equal.
Cross join
Cross Join performs two tables of Descartes (that is, combine the data of Table A and Table B for a combination of N*M). In other words, it matches every line of one table and another table; we cannot specify the predicate words by using the ON clause, although we can use the where clause to achieve the same result. As an internal connection.
The
cross -connection is low compared to the internal connection, and the two large watches should not be cross -connect, because this will lead to a very expensive operation and a very large result set.
specific SQL code is as follows:
SELECT College.cName, College.state, College.enrollment,
Apply.cName, Apply.major, Apply.decision
FROM College
CROSS JOIN Apply
Figure 4 College table and Apply table
Figure 5 cross -connect
Now we cross -connect the College and Apply tables, and the Discial of the College and Apply tables that generate data behaviors are 5 * 20 = 100.
Cross apply
provides the Cross Apply in SQL Server 2005 to make the table and the table value function (Table-Valued Functions TVF ‘S) results for Join query. For example, now we want to query through the results of the function and table Student. At this time, we can use the Cross Apply to query:
CREATE FUNCTION dbo.fn_Apply(@sID int)
RETURNS @Apply TABLE (cName nvarchar(50), major nvarchar(50))
AS
BEGIN
INSERT @Apply SELECT cName, major FROM Apply where [sID] = @sID
RETURN
END
—- Student cross apply function fn_Apply.
SELECT Student.sName, Student.GPA, Student.sizeHS,
cName, major
FROM Student CROSS APPLY dbo.fn_Apply([sID])
We can also use the internal connection to implement the same query function as Cross Apply. The specific SQL code is as follows:
SELECT Student.sName, Student.GPA, Student.sizeHS,
cName, major
FROM Student INNER JOIN [Apply]
ON Student.sID = [Apply].sID
Figure 6 Cross Apply query
Outer apply
After introducing Cross Apply and OUTER JOIN, it is not difficult to let us understand the Out Apply. Outre Apply can make the table with the table value function (Table-Valued Functions TVF’s). With value, it is represented by null without finding the matching value.
SELECT Student.sName, Student.GPA, Student.sizeHS,
cName, major
FROM Student OUTER APPLY dbo.fn_Apply([sID])
Figure 7 OUTER Apply Query
The difference between
Inner Join and Cross Apply
First of all, we know that Inner Join is a connection query of tables and tables, and Cross Apply is a connection query of table and table value function. In the previous Cross Apply example, we can also implement the same query through Inner Join.
SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SELECT Student.sName, Student.GPA, Student.sizeHS,
cName, major
FROM Student CROSS APPLY dbo.fn_Apply([sID])
SET STATISTICS PROFILE OFF
SET STATISTICS TIME OFF
—- Student INNER JOIN Apply base on sID.
SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SELECT Student.sName, Student.GPA, Student.sizeHS,
cName, major
FROM Student INNER JOIN [Apply]
ON Student.sID = [Apply].sID
SET STATISTICS PROFILE OFF
SET STATISTICS TIME OFFCross apply
Query execution time:
CPU time = 0 milliseconds, occupying time = 11 milliseconds.
Inner Join query execution time:
CPU time = 0 milliseconds, occupying time = 4 milliseconds.
Figure 8 Execution Plan
As shown in Figure 8: Cross Apply first executes TVF (Table-Valued Functions), then scan the table Studnet throughout the table, and then find the matching value by traversing SID.
Inner Join scan the table Student and Apply, and then find the matching SID value through Hazha matching.
Through the above SQL execution time and execution plan, can we say that Inner Join is better than Cross Apply? The answer is no. If the amount of data of the table is large, then the full table scanning of Inner Join takes time and the CPU resources will increase (testing can be tested with a large amount of data).
Although most of the queries implemented by Cross Apply can be implemented through Inner Join, Cross Apply may produce better execution plans and better performance because it can be added before the connection execution.
SEMI-JOIN and Anti-SEMI-JOIN
SEMI-JOIN The row returned from one table with the data row in the table incompletely connected inquiries (returned to the matching data row, and no longer searches).
Anti-SEMI-JOIN The row returned from one table with the data row in the table incompletely connected inquiries, and then returned the data that did not match.
Different from other connection operations, Semi-Join and Anti-SEMI-JOIN have no clear syntax to implement, but Semi-Join and Anti-SEMI-JOIN have various applications in SQL Server. We can use EXISTS to achieve SEMI-JOIN query, not exists to achieve Anti-SEMI-JOIN. Let’s explain it through specific examples now!
Assuming that we ask us to find the student information of SID matching in the Apply and Student table, which is the same as the previous Inner Join query result. The specific SQL code is as follows:
SELECT Student.sName, Student.GPA, Student.sizeHS
—-[Apply].cName, [Apply].major
FROM Student
WHERE exists (
SELECT *
from [Apply]
where [Apply].sID = Student.sID
)
We found that the common Exists clauses were achieved through LEFT SEMI JOIN, so SEMI-JOIN has many use occasions in SQL Server.
Figure 9 query results
Figure 10 execution plan
Now I ask us to find the student information that has not yet applied for the school. At this time, we immediately responded that we can use Not ExistS clauses to achieve the query. The specific SQL code is as follows:
SELECT Student.sID, Student.sName, Student.GPA, Student.sizeHS
—-[Apply].cName, [Apply].major
FROM Student
WHERE NOT EXISTS (
SELECT *
FROM [Apply]
WHERE [Apply].sID = Student.sID
)
In fact, the implementation of the NOT EXISTS clauses we often use is through Anti-SEMI-JOIN. By executing the plan, we find that when finding matching SID, SQL uses Left Anti Semi Join to query.
Figure 11 query results
Figure 12 execution plan
1.1.3 Summary
This article introduces the connection query method in SQL: Inner Join, OUTER JOIN, Cross Join, and Cross Apply.