Join left join right join Outer Join and flute Product

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

• Inner join
• Outer Join

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.