Join has a total of four internal and external
Join inner join
Left join Outer Join
Right join Outer Join
Full join Outer Join
Example:
Table
1
2
Table B
1
3
A join B
1 1
A left join B
1 1
2 null
A right join B
1 1
Null 3
A full join B
1 1
2 null
Null 3
Join = Inner join
Join is one of the important operations of the relational database system. Common Join Operations in SQL Server include internal join, external join, and cross join. If we want to obtain data from two or more tables that matches rows in one table with rows in another table, we should consider using join, because join queries a specific joined table or function
This article will introduce the features and usage of various common joins in SQL through examples:
Directory
1.1.2 text
First, we define three tables in tempdb: College, student, and apply. The specific SQL code is as follows:
USE tempdb---- 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(Inner join) is one of the most common join types. It queries data that meets the join predicates.
Suppose we want to query the information about the applied School in the application form. Because the apply table contains the school name, we cannot predict it, so we can use the cnameInner join(Inner join) The College and apply tables to find the school information contained in the apply table.
The specific SQL code is as follows:
---- Gets college information from college table---- bases on college name.SELECT DISTINCT College.cName, College.enrollmentFROM 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 college table
As shown in 1, we have queried the school information contained in the apply table. As Harvard is not found, we know that no student has applied for Harvard.
Inner join(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 the school information; even for schools that have not been applied for (such as Harvard), we can useExternal join(Outer Join. BecauseExternal joinStores all rows in one or two input tables, even if the rows matching the join predicates cannot be found.
The specific SQL code is as follows:
---- Gets all college informationSELECT College.cName, College.state, College.enrollment,Apply.cName, Apply.major, Apply.decisionFROM College LEFT OUTER JOIN Apply ON College.cName = Apply.cName
Figure 2 left join query results
As shown in figure 3: No student applies for Harvard in the apply table, but we passLeft join(Left Outer Join) queries all school information.
BecauseLeft join(Left Outer Join) generates the full set of the college table, while the apply table matches with values, and the unmatched values are replaced by null values, so we know that no student applies for Harvard in the apply table.
PassLeft joinWe can query the full set of the college. If we want to obtain both the full set of the College and the full set of apply, we can consider usingComplete External Connection(Full outer join ). UseComplete External Connection, We can query all schools, whether or not they match the join predicates:
---- Gets all information from college and apply table.SELECT College.cName, College.state, College.enrollment,Apply.cName, Apply.major, Apply.decisionFROM College FULL OUTER JOIN Apply ON College.cName = Apply.cName
Figure 3 complete external join query results
Now we have obtained the complete dataset of college and apply, and there is a value for matching in the table, even if no matching cname is found, it will be replaced by null.
The following table showsExternal join(Outer Join) retained data rows during Matching:
Join type |
Retain 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 join reserved data rows
Complete External Connection(Full outer join) satisfies the exchange law: "A full outer join B" and "B full outer join a" are equal.
Cross join
Cross join(Cross join) executes the Cartesian product of two tables (that is, the combination of data in tables A and B in a n * m ). That is to say, it matches each row in one table and the other. We cannot use the on clauseCross joinSpecify the predicate. Although we can use the WHERE clause to implement the same result, this isCross joinBasically asInternal Connection.
Cross joinRelativeInternal ConnectionLow usage, and two large tables should not be usedCross joinBecause it will lead to a very expensive operation and a very large result set.
The specific SQL code is as follows:
---- College Cross join Apply.SELECT College.cName, College.state, College.enrollment,Apply.cName, Apply.major, Apply.decisionFROM CollegeCROSS JOIN Apply
Figure 4 number of rows in the College and apply tables
Figure 5 cross join
Now we perform a cross join on the college and apply tables and generate a Cartesian product of the number of rows in the Data behavior College and apply tables, that is, 5*20 = 100.