Fully parse the left and right connections of SQL SERVER

Three common connection parsing for SQL Server databases: Here's an official explanation: The LEFT join returns all the records in the left table and the records in the right table that are equal to the join fields right Join Returns records that include all records in the right table and the equivalent of the junction fields in the left table inner join (equivalent join) returns only rows with the same junction field in two tables use [Bi]godrop table BI.dbo.TABLE_ONE; Godrop TABLE BI.dbo.TABLE_TWO; Gocreate TABLE BI.dbo.TABLE_ONE ([ID] [int] Not NULL, [NAME] [nvarchar] ( -) not NULL) on [Primary]gocreate TABLE BI.dbo.TABLE_TWO ([ID] [int] Not NULL, [score] [int] not NULL) on [Primary]go1, if my two tables were inserted with the following information: INSERT INTO BI. Dbo. Table_one VALUES (1,'Zhang San'); INSERT into BI. Dbo. Table_one VALUES (2,'John Doe'); INSERT into BI. Dbo. Table_one VALUES (3,'Harry'); Goinsert into BI. Dbo. Table_two VALUES (1, -); INSERT into BI. Dbo. Table_two VALUES (2, the); INSERT into BI. Dbo. Table_two VALUES (3,98); GO in this case: in fact, the left connection and the right connection, the result of the internal connection is the same:--left connection Select Table_one.id,table_one.name,table_two. Scorefrom Table_one left JOIN table_twoon table_one.id=table_two.id;--Right-connect select Table_one.id,table_one.name,table_two. Scorefrom Table_one Right JOIN table_twoon table_one.id=table_two.id;--internal Connection Select Table_one.id,table_one.name,table_two. Scorefrom table_one INNER JOIN table_twoon table_one.id=table_two.id; The results are as follows: conclusion: If the number of records for two tables is the same, and the value of the primary key is the same, then the result of connecting them is the same. 2, if my two tables were inserted with the following information: TRUNCATE TABLE BI. Dbo. Table_one;truncate table BI. Dbo. Table_two;insert into BI. Dbo. Table_one VALUES (1,'Zhang San'); INSERT into BI. Dbo. Table_one VALUES (2,'John Doe'); INSERT into BI. Dbo. Table_one VALUES (3,'Harry'); INSERT into BI. Dbo. Table_one VALUES (4,'Liu Liu'); Goinsert into BI. Dbo. Table_two VALUES (1, -); INSERT into BI. Dbo. Table_two VALUES (2, the); INSERT into BI. Dbo. Table_two VALUES (3,98); INSERT into BI. Dbo. Table_two VALUES (5, About); GO--left connection Select Table_one.id,table_one.name,table_two. Scorefrom Table_one left JOIN table_twoon table_one.id=table_two.id: Returns 4 records of the left table, with the left ID=4A connection exists in the right table, and the value of the score corresponding to the right table returns NULL. --Right-connect select Table_one.id,table_one.name,table_two. Scorefrom Table_one Right JOIN table_twoon table_one.id=table_two.id;1Tom -2John doe the3Harry98NULL NULL Aboutreturns the right table, Table_two 4 records corresponding to the record, where Table_two has an ID=5Table_one, but returns null if none is in the log. --internal Connection Select Table_one.id,table_one.name,table_two. Scorefrom table_one INNER JOIN table_twoon table_one.id=table_two.id; ID NAME score1Tom -2John doe the3Harry98returns the IDs of the left and right two tables:1，2，3so the return is 3 common record values. Conclusion: If the two tables are connected by the primary key, the returned recordset must be equal to the number of records returned by the left table, and the recordset must be equal to the number of records returned by the right table, and the inner join returns the records that exist for the two tables. The connection result set for a left or right connection is definitely equal to the number of records of the primary table (left or right table) in the connection, with the primary key as the connection condition. For inner joins, the number of records that have intersections in two tables is returned. Here's how the worst record count is the number of Cartesian records when you're not connected by a primary key. 3, if my two tables were inserted with the following information: TRUNCATE TABLE BI. Dbo. Table_one;truncate table BI. Dbo. Table_two;insert into BI. Dbo. Table_one VALUES (1,'Zhang San'); INSERT into BI. Dbo. Table_one VALUES (2,'John Doe'); INSERT into BI. Dbo. Table_one VALUES (3,'Harry'); INSERT into BI. Dbo. Table_one VALUES (4,'Liu Liu'); Goinsert into BI. Dbo. Table_two VALUES (1, -); INSERT into BI. Dbo. Table_two VALUES (1, the); INSERT into BI. Dbo. Table_two VALUES (1,98); INSERT into BI. Dbo. Table_two VALUES (1, About); GO--left connection Select Table_one.id,table_one.name,table_two. Scorefrom Table_one left JOIN table_twoon table_one.id=table_two.id;1Tom -1Tom the1Tom981Tom About2John Doe NULL3Harry NULL4Liu Liu NULL results set record number 7 is greater than 4 records on the left side of the main table. --Right-connect select Table_one.id,table_one.name,table_two. Scorefrom Table_one Right JOIN table_twoon table_one.id=table_two.id;1Tom -1Tom the1Tom981Tom About--internal Connection Select Table_one.id,table_one.name,table_two. Scorefrom table_one INNER JOIN table_twoon table_one.id=table_two.id;1Tom -1Tom the1Tom981Tom About 4, the following is the extreme case where the Cartesian product is produced. That is, the number of records for two tables is multiplied by the number of records. TRUNCATE TABLE BI. Dbo. Table_one;truncate table BI. Dbo. Table_two;insert into BI. Dbo. Table_one VALUES (1,'Zhang San'); INSERT into BI. Dbo. Table_one VALUES (1,'John Doe'); INSERT into BI. Dbo. Table_one VALUES (1,'Harry'); INSERT into BI. Dbo. Table_one VALUES (1,'Liu Liu'); Goinsert into BI. Dbo. Table_two VALUES (1, -); INSERT into BI. Dbo. Table_two VALUES (1, the); INSERT into BI. Dbo. Table_two VALUES (1,98); INSERT into BI. Dbo. Table_two VALUES (1, About); GO--left connection Select Table_one.id,table_one.name,table_two. Scorefrom Table_one left JOIN table_twoon table_one.id=table_two.id;--Right-connect select Table_one.id,table_one.name,table_two. Scorefrom Table_one Right JOIN table_twoon table_one.id=table_two.id;--internal Connection Select Table_one.id,table_one.name,table_two. Scorefrom table_one INNER JOIN table_twoon table_one.id=table_two.id;1Tom -1Tom the1Tom981Tom About1John doe -1John doe the1John doe981John doe About1Harry -1Harry the1Harry981Harry About1Liu Liu -1Liu Liu the1Liu Liu981Liu Liu Aboutin this case, the result set is 14 records. Conclusion: Regardless of the left and right connected result set, the number of records must be greater than or equal to the number of records in the primary table, while the inner join result set can be less than, equal to, greater than the number of records in the connected table. Left, right or an important use of the INNER JOIN table: You can scale out a table and get a new table with more properties. UNION can increase the number of record rows in a table vertically. 

Fully parse the left and right connections of SQL SERVER