Table connection similarities and differences (equivalent, no index)

I. Description:

1. cainiao hydrological... please correct me

2. In this document, the mass test data is generated by SQL data generator 1 (red gate ).

3. Due to space limitations, table connections in this article are equivalent connections.

4. the table in this article has no indexer (deleted by the blogger)

5. the table in this article does not have a primary-foreign key relationship (avoid affecting the test)

6. This article focuses on comparing the similarities and differences between inner connections, outer connections, and cross connections.

2. Terms

1. matching data and non-matching data

A table connection is like a line (ing) between tables. Such a line (ing) can be a one-to-many, one-to-one, and many-to-many relationship, of course, the left and right tables are not connected. The blogger (I am the only one, don't mislead everyone) calls the data with connections as matched data, and the data with no connections as non-matched data.

2. Functions

When the same test data and the data queried by different table connections are the same, the table connection function is the same, and vice versa. For example, internal connections and cross connections

Same, the functions of the left and right connections are different (the data given below is the test data ).

Iii. Table connection types

1. Internal Connection

2. External Connection

1) left Link

2) Right join

3) full connection

3. Cross join

Iv. View (SQL dependency tracker (Red gates ))

Note: The teacher (Instructor) table is the left table by default, and the coures (course) table is the right table. SQL script at the end of the article

V. Data

Note: The data is inserted into the database (not generated) to compare the similarities and differences between various table connections ).

Vi. Table join

1. Internal Connection

 

View code

 

We can see that the internal connection only filters matching data.

2. Left join

View code

In addition to filtering the matching data, the left join filters out non-matching data in the left (teacher) table.

3. Right join

View code

Right join and vice versa .....

4. Full connection

View code

 

Full connection combines the data set filtered by links with the data filtered by right connections for computation.

Because the image is not an image, you simply do not need to draw it. If you are interested, you can try it yourself. The script will be provided at the end of the article.

5. Cross join

View code

Cross-join and interesting. It is the same as the data filtered by the inner connection, that is, the function is the same.

VII. Table join Similarities and Differences

Similarities:

1. matching data may be filtered out regardless of the internal connection, external connection, or cross-connection.

2. The functions of inner connection and cross connection are the same.

Differences:

1. The functions of external and internal connections are different.

2. The functions of external connections and cross-connections are different.

3. The functions of left join, right join, and full join are different.

VIII. Performance Testing

1. Statement

1) it is meaningless to test the performance of table connections with different functions. Only table join tests with the same functions of inner join and outer join make sense. Therefore, the test focuses on internal connections and external connections.

2) the test data generated each time contains non-matching data.

3) This test is only available on my PC and only on SQL Server 2008. Only these two tables are available at that time. The blogger wants to say that the query time of the test data is relative, not absolute.

2. Is the internal connection more efficient than external connections?

It is said that the internal connection is more efficient than the external connection. From this test, this is the truth.

However, if your software does not produce a large amount of data, it doesn't matter if the internal connection is a cross connection. Even so, the data still shows that internal connections are more efficient than cross connections.

Tip: If you encounter a problem during the interview, you can write a cross connection or an internal connection question, you must write an internal connection. During the interview in Beijing in May, the performance of internal connections and cross-connections was unclear. The result was a tragedy when I interviewed a particularly favorite company. Finally, I was forced to develop in Yantai. These are the lessons of blood and tears from bloggers.

3. Use scalar functions in a timely manner.

If you only want to obtain the scalar of the cluster, it is recommended that you use the DB built-in scalar function.

I believe that Boyou have also met the code of a wonderful colleague, which extracts data from the database and stores it in the memory (Dataset), and then calculates the number of data. Aside from the database query statement time, this is a huge waste of resources, and this problem is relatively low.

4. Is the select * efficiency low?

Is select * inefficient when all data is filtered out? At least the answer to this test is no.

Intranet connection as an Example

SELECT * FROM dbo.Teacher T    INNER JOIN dbo.Course C　　ON T.T#=C.T#

And

SELECT T.Name,        T.T#,        C.C#,        C.Name,        C.T#     FROM dbo.Teacher T    INNER JOIN dbo.Course C    ON T.T#=C.T#

When the test data volume and matching gauge REACH, the query time is 11 seconds. Assume that the second SQL statement is changed.

 

SELECT T.Name,T.T#     FROM dbo.Teacher T    INNER JOIN dbo.Course C　　ON T.T#=C.T#

 

The query time is 8 seconds.

Even so, when querying all fields, I still recommend that you write each field name, that is, the second SQL in this issue, because it is more conducive to SQL maintenance.

5. Special Cases

In special cases, you may need to retrieve matching data from the left table. For example, you can find information about instructors with course schedules.

That is:

 

Such as the data circled by the red line. In this case, connection queries are less efficient.

Intranet connection as an Example

SELECT T.Name,T.T#     FROM dbo.Teacher T    INNER JOIN dbo.Course C　　ON T.T#=C.T#

And

SELECT T#,Name    FROM dbo.Teacher    WHERE T# IN     (        SELECT T#             FROM dbo.Course            WHERE dbo.Course.T#=dbo.Teacher.T#　　)

When the test data volume and matching measuring tool REACH, the query time of the inner connection is 8 seconds, while that of the in query is 6 seconds.

Is the built-in exists function more efficient?

 

SELECT T#,Name    FROM dbo.Teacher    WHERE EXISTS     (        SELECT T#             FROM dbo.Course            WHERE dbo.Course.T#=dbo.Teacher.T#　　)

 

The answer is yes. The query time using the exists function is 5 seconds.

6. Summary

P (*)> P (^ *)

With the same functions

P (exists)> P (in)> P (inner join)> P (cross join)

9. SQL script

 

View code

Create Database testgouse testcreate table [Teacher] ([T #] int primary key, [name] nvarchar (50) not null ,); gocreate table [course] ([C #] int primary key, [name] nvarchar (50) not null, [T #] INT); goinsert DBO. teacher (T #, name) select 1, -- T #-int 'zhang san' -- name-nvarchar (50) Union all select 2, 'Li si' Union all select 3, 'wang 5' Union all select 4, 'zhao liu' insert DBO. course (C #, name, T #) select 1, -- C #-int 'high', -- name-nvarchar (50) 1 -- T #-intunion allselect 2, 'Data structuring ', 2 Union allselect 3, 'Social etiquette', 3 Union allselect 4, 'Love culture ', null