SQL join Summary (Instance)

Join is one of the important operations of the relational database system. Common Join Operations in SQLServer include internal Join, external Join, and cross Join.

Join is one of the important operations of the relational database system. Common Join Operations in SQL Server include: inner Join, outer Join, and cross Join.

1.1.1 Summary
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:

1.1.2 text
First, we define three tables in tempdb: College, Student, and Apply. The specific SQL code is as follows:
The 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 is one of the most common join types. It queries data that meets the join predicates.

Suppose we want to query the information related to the applied School in the application form. We cannot predict that the Apply table contains the school name, so we can use the cName to join the school name (Inner join) the College and Apply tables are used to find the school information contained in the Apply table.

The specific SQL code is as follows:
The Code is as follows:

---- Gets college information from college table ---- bases on college name. SELECT DISTINCT College.cName, College.enrollment FROM College INNER JOIN Apply ON College.cName = Apply.cName

---- Gets all college information SELECT College.cName, College.state, College.enrollment, Apply.cName, Apply.major, Apply.decision FROM College LEFT OUTER JOIN

The Code is as follows:

--- Gets all information from college and apply table. SELECT College.cName, College.state, College.enrollment, Apply.cName, Apply.major, Apply.decision FROM College FULL OUTER JOIN Apply ON College.cName = Apply.cName

The Code is as follows:

---- College Cross join Apply. SELECT College.cName, College.state, College.enrollment, Apply.cName, Apply.major, Apply.decision FROM College CROSS JOIN Apply

---- Creates a function to get data from Apply base on sID. 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:
The Code is as follows:

---- Student INNER JOIN Apply bases on sID. SELECT Student.sName, Student.GPA, Student.sizeHS, cName, major FROM Student INNER JOIN [Apply] ON Student.sID = [Apply].sID

The Code is as follows:

---- Student outer apply function fn_Apply. SELECT Student.sName, Student.GPA, Student.sizeHS, cName, major FROM Student OUTER APPLY dbo.fn_Apply([sID])

---- Student cross apply function fn_Apply. 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 ms, occupied time = 11 ms.

Inner join query execution time:

CPU time = 0 ms, occupied time = 4 ms.

---- Student Semi-join Apply base on sID. 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 commonly used EXISTS clause was originally implemented through Left Semi Join. Therefore, Semi-join is widely used in SQL Server.

---- Gets student still not apply for school. 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 Common Implementation of the not exists clause is through Anti-semi-join. Through the execution plan, we find that when the query matches the sID, the SQL statement uses Left Anti Semi Join for query.