Comprehensive Analysis of SQL server connections between left and right, comprehensive analysis of SQL

Comprehensive Analysis of SQL server connections between left and right, comprehensive analysis of SQL

Three Common connection resolutions for SQL SERVER databases:

 

Here is an official explanation: Left join returns records that include all records in the left table and join fields in the right table. Right join returns records that include all records in the right table and the joined fields in the left table. Inner join (equivalent join) returns only rows with equal join fields in two tables. 

 

USE [BI]
GO
Drop table bi. dbo. TABLE_ONE;
GO
Drop table bi. dbo. TABLE_TWO;
GO
Create table bi. dbo. TABLE_ONE (
[ID] [int] not null,
[NAME] [nvarchar] (50) NOT NULL
) ON [PRIMARY]
GO
Create table bi. dbo. TABLE_TWO (
[ID] [int] not null,
[SCORE] [int] NOT NULL
) ON [PRIMARY]
GO

1. If the two tables are inserted with the following information:

Insert into bi. DBO. TABLE_ONE VALUES (1, 'zhang san ');
Insert into bi. DBO. TABLE_ONE VALUES (2, 'Li si ');
Insert into bi. DBO. TABLE_ONE VALUES (3, 'wang wu ');
GO
Insert into bi. DBO. TABLE_TWO VALUES (1, 90 );
Insert into bi. DBO. TABLE_TWO VALUES (2,95 );
Insert into bi. DBO. TABLE_TWO VALUES (3,98 );
GO

 

In this case, the results of the left and right connections and the inner connections are the same:

-- Left join
SELECT TABLE_ONE.ID, TABLE_ONE.NAME, TABLE_TWO.SCORE
FROM TABLE_ONE left join TABLE_TWO
ON TABLE_ONE.ID = TABLE_TWO.ID;

-- Right join
SELECT TABLE_ONE.ID, TABLE_ONE.NAME, TABLE_TWO.SCORE
FROM TABLE_ONE right join TABLE_TWO
ON TABLE_ONE.ID = TABLE_TWO.ID;

-- Internal Connection
SELECT TABLE_ONE.ID, TABLE_ONE.NAME, TABLE_TWO.SCORE
FROM TABLE_ONE inner join TABLE_TWO
ON TABLE_ONE.ID = TABLE_TWO.ID;

 

The results are as follows:

Conclusion: if the number of records in the two tables is the same and the value of the primary key is the same, the results of connecting them are the same at this time.

 

 

2. If the two tables are 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, 'Li si ');
Insert into bi. DBO. TABLE_ONE VALUES (3, 'wang wu ');
Insert into bi. DBO. TABLE_ONE VALUES (4, 'Liu liu ');
GO
Insert into bi. DBO. TABLE_TWO VALUES (1, 90 );
Insert into bi. DBO. TABLE_TWO VALUES (2,95 );
Insert into bi. DBO. TABLE_TWO VALUES (3,98 );
Insert into bi. DBO. TABLE_TWO VALUES (5, 99 );
GO

-- Left join
SELECT TABLE_ONE.ID, TABLE_ONE.NAME, TABLE_TWO.SCORE
FROM TABLE_ONE left join TABLE_TWO
ON TABLE_ONE.ID = TABLE_TWO.ID;

Four records are returned from the left table. The ID = 4 on the left is connected to the right table, and the SCORE value of all the right tables returns NULL.

-- Right join
SELECT TABLE_ONE.ID, TABLE_ONE.NAME, TABLE_TWO.SCORE
FROM TABLE_ONE right join TABLE_TWO
ON TABLE_ONE.ID = TABLE_TWO.ID;

 

1	Zhang San	90
2	Li Si	95
3	Wang Wu	98
NULL	NULL	99

 

The returned result is the right table. TABLE_TWO records corresponding to the four records. TABLE_TWO records with ID = 5, but not in TABLE_ONE, NULL is returned.

-- Internal Connection
SELECT TABLE_ONE.ID, TABLE_ONE.NAME, TABLE_TWO.SCORE
FROM TABLE_ONE inner join TABLE_TWO
ON TABLE_ONE.ID = TABLE_TWO.ID;

 

ID	NAME	SCORE
1	Zhang San	90
2	Li Si	95
3	Wang Wu	98

Returns the IDs of both the left and right tables: 1, 2, and 3. Therefore, three records are returned.

 

Conclusion: if the two tables are connected by the primary key, the records returned from the left join must be equal to the number of records returned from the left table; if the right join clause is used, the record set must be equal to the number of records returned from the right table. If the inner join clause is used, the records of both tables are returned.

 

The above conditions are all connected by the primary key. For the Left or Right join, the resulting connection result set must be equal to the number of records in the connected primary table (left or right table. For inner join, the number of records with the intersection in both tables is returned. Next we will introduce the number of records that are not connected by the primary key. In this case, the worst number of records is the number of records accumulated by Descartes.

3. If the two tables are 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, 'Li si ');
Insert into bi. DBO. TABLE_ONE VALUES (3, 'wang wu ');
Insert into bi. DBO. TABLE_ONE VALUES (4, 'Liu liu ');
GO
Insert into bi. DBO. TABLE_TWO VALUES (1, 90 );
Insert into bi. DBO. TABLE_TWO VALUES (1, 95 );
Insert into bi. DBO. TABLE_TWO VALUES (1, 98 );
Insert into bi. DBO. TABLE_TWO VALUES (1, 99 );
GO

 

-- Left join
SELECT TABLE_ONE.ID, TABLE_ONE.NAME, TABLE_TWO.SCORE
FROM TABLE_ONE left join TABLE_TWO
ON TABLE_ONE.ID = TABLE_TWO.ID;

1	Zhang San	90
1	Zhang San	95
1	Zhang San	98
1	Zhang San	99
2	Li Si	NULL
3	Wang Wu	NULL
4	Liu	NULL

The number of records in the result set 7 is greater than the four records on the left of the master table.

-- Right join
SELECT TABLE_ONE.ID, TABLE_ONE.NAME, TABLE_TWO.SCORE
FROM TABLE_ONE right join TABLE_TWO
ON TABLE_ONE.ID = TABLE_TWO.ID;

1	Zhang San	90
1	Zhang San	95
1	Zhang San	98
1	Zhang San	99

-- Internal Connection
SELECT TABLE_ONE.ID, TABLE_ONE.NAME, TABLE_TWO.SCORE
FROM TABLE_ONE inner join TABLE_TWO
ON TABLE_ONE.ID = TABLE_TWO.ID;

1	Zhang San	90
1	Zhang San	95
1	Zhang San	98
1	Zhang San	99

 

4. In extreme cases, cartesian products are generated. That is, the number of records in 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, 'Li si ');
Insert into bi. DBO. TABLE_ONE VALUES (1, 'wang wu ');
Insert into bi. DBO. TABLE_ONE VALUES (1, 'Liu liu ');
GO
Insert into bi. DBO. TABLE_TWO VALUES (1, 90 );
Insert into bi. DBO. TABLE_TWO VALUES (1, 95 );
Insert into bi. DBO. TABLE_TWO VALUES (1, 98 );
Insert into bi. DBO. TABLE_TWO VALUES (1, 99 );
GO

-- Left join
SELECT TABLE_ONE.ID, TABLE_ONE.NAME, TABLE_TWO.SCORE
FROM TABLE_ONE left join TABLE_TWO
ON TABLE_ONE.ID = TABLE_TWO.ID;

-- Right join
SELECT TABLE_ONE.ID, TABLE_ONE.NAME, TABLE_TWO.SCORE
FROM TABLE_ONE right join TABLE_TWO
ON TABLE_ONE.ID = TABLE_TWO.ID;

-- Internal Connection
SELECT TABLE_ONE.ID, TABLE_ONE.NAME, TABLE_TWO.SCORE
FROM TABLE_ONE inner join TABLE_TWO
ON TABLE_ONE.ID = TABLE_TWO.ID;

1	Zhang San	90
1	Zhang San	95
1	Zhang San	98
1	Zhang San	99
1	Li Si	90
1	Li Si	95
1	Li Si	98
1	Li Si	99
1	Wang Wu	90
1	Wang Wu	95
1	Wang Wu	98
1	Wang Wu	99
1	Liu	90
1	Liu	95
1	Liu	98
1	Liu	99

In this case, the result set is all 14 records.

 

Conclusion: No matter the Left or Right join, the number of records in the result set must be greater than or equal to the number of records in the master table, and the result set of the inner join can be smaller than or equal, greater than the number of records in the connected table.

An important use of left, right, or inner join tables: A table can be horizontally expanded to obtain a new table with more attributes.

UNION can increase the number of record rows in a table vertically.

How does one understand the internal connection and external connection in SQL server 2005?

For example.
Suppose there are two tables: Student table and class table.
Student table t_student
Id studentname classid
1 Zhang San 11
2 Li Si 12
3 Wang Wu 13

Class table t_class
Classid classname
11 high Sany class
12. Class 3 and Class 2

As you can see, the class table does not have the id of the class corresponding to Wang Wu in the student table.
Therefore, Wang Wu has no corresponding class.
If you use an internal connection, that is, select * from t_student s, t_class c where s. classid = c. classid or
Select * from t_student s inner join t_class c on s. classid = c. classid
In this way, no result is returned. Because there is no class whose classid is 13 in the class table.
The result should be
Id studentname classid classname
1 Zhang San 11 Gao Sany class
2. Li Si 12, high school, and second class

If an external connection is used,
Select * from t_student s left join t_class c on s. classid = c. classid
Wang Wu can be queried, but the fields corresponding to the class are blank in the query results.
The result should be
Id studentname classid classname
1 Zhang San 11 Gao Sany class
2. Li Si 12, high school, and second class
3. Wang Wu

In this example, we can simply take the inner connection as the intersection of students and classes. You can only query the data that both of them have. Otherwise, the query fails.
Outer join (for example, left join) is used to query all the information of the student table and the intersection of the two tables.

I don't know if you understand it. If you still don't understand it, send me an email mazhanjun@tom.com.

How to Write SQL server connections

Internal Connection:
Select * from
Inner join B
On condition expression

Left and right connections:
Replace inner with left/right.