Detailed SQL Server connections (internal, external, cross-connect)

When querying multiple tables, we often use a "connection query". Connection is the main feature of relational database model, and it is also a sign that distinguishes it from other types of database management system.

What is a connection query?

Concept: Query data from these tables based on the relationship between the columns of two tables or multiple tables.

Objective: To implement multiple table query operations.

When you know the concept of a connection query, when do you use a connection query?

Typically used when associating two or more tables of data. Look a bit abstract, let's give an example, two tables: Student table (t_student) and Class table (T_class).

T_student T_class

Connection standard syntax format:

The connection syntax format for the FROM clause defined by the SQL-92 standard is:

From Join_table join_type Join_table[on (join_condition)]

Where join_table indicates the name of the table participating in the JOIN operation, the connection can operate on the same table, or on a multi-table operation, the connection to the same table operation is also called self-connected. Join_type indicates the connection type. Join_condition refers to the connection condition.

Connection type:

The connection is divided into three kinds: inner connection, outer connection, cross connection.

Internal connection (INNER join)

Use comparison operators (including =, >, <, <>, >=, <=,!>, and!<) for comparison between tables to query data that matches the join criteria. According to the comparison operator, the inner connection is divided into two kinds of equivalent connections and unequal connections.

1. Equivalent connection

Concept: Use the equals sign (=) operator in the join condition, which lists all the columns in the joined table, including the repeating columns, in the query results.

[SQL]View PlainCopy

1. <span style="FONT-SIZE:18PX;" ><span style="Font-family:system;" >
2. SELECT * from t_student s,t_class c where s.classid = C.classid
3. Equals
4. SELECT * from t_student s inner joins T_class c on s.classid = c.classid</span></span>

The result is:

2, unequal connection

Concept: Use operators other than equals in join conditions (>, <, <>, >=, <=,!>, and!<)

[SQL]View PlainCopy

1. <span style="FONT-SIZE:18PX;" ><span style="Font-family:system;" >
2. SELECT * from t_student s inner joins T_class C on s.classid <> C.CLASSID</SPAN></SPAN&G  T

The result is:

External connection

An outer join is either a left join or a left-OUTER join, a right join, or a right-OUTER join, an all-connected (full join), or an all-out connection (full OUTER join). We simply call it: Left JOIN, right connection and full connection.

1, left JOIN connect:

Concept: Returns all rows in the left table, and if rows in the left table do not have matching rows in the right table, the columns in the right table in the result return null values.

[SQL]View PlainCopy

1. <span style="FONT-SIZE:18PX;" ><span style="Font-family:system;" >
2. SELECT * from t_student s left joins T_class c on s.classid = c.classid</span></span>

The result is:

Summary: Left JOIN displays all rows of the left table, and the right table is the same row as the left table.

2. Right Connection:

Concept: Just as opposed to left join, returns all the rows in the right table, and if the rows in the right table have no matching rows in the left table, the columns in the left table in the result return null values.

[SQL]View PlainCopy

1. <span style="FONT-SIZE:18PX;" ><span style="Font-family:system;" >
2. SELECT * from t_student s right join T_class c on s.classid = c.classid</span></span>

The result is:

Summary: The right connection is exactly the opposite of left join, showing all rows of the right table, and the same row as the left table and the right table.

3. Full connection:

Concept: Returns all rows in the left and right tables. When a row does not have a matching row in another table, the columns in the other table return a null value

[SQL]View PlainCopy

1. <span style="FONT-SIZE:18PX;" ><span style="Font-family:system;" >
2. SELECT * from t_student s full join T_class c on s.classid = c.classid</span></span>

The result is:

Summary: Returns all rows from the left and right tables.

Cross join: Also known as Cartesian product

Concept: Without a WHERE clause, it will return the Cartesian product of the two tables connected, and the number of rows returning the result is equal to the product of two table rows (for example: T_student and T_class, Return 4*4=16 Records), and if with where, the number of matching rows is returned or displayed.

1. Do not take where:

[SQL]View PlainCopy

1. <span style="FONT-SIZE:18PX;" ><span style="Font-family:system;" >
2. SELECT * fromt_student cross join T_class
3. ' Equals
4. SELECT * fromt_student, t_class</span></span>

The result is:

Summary: Equivalent to the Cartesian product, the left table and the right table combination.

2. There is a WHERE clause, which is usually the data table that is the product of two table row numbers, and is then selected from the Where condition. [SQL]View PlainCopy

1. SELECT * from t_student s cross join T_class c where s.classid = C.classid
2. (Note: after cross join conditions can only be used where, not on )

The result of the query is the same as the result of the equivalent connection. Connection query is very simple, just need to practice in the project, keep summarizing. Reprinted from: 10474221

Detailed SQL Server connections (internal, external, cross-connect)