connection queries in SQL Server

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 operators, there are three kinds of inner joins, such as equivalent connection, natural connection and unequal connection.

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.

Select * from t_student s,t_class c where s.classid = C.classid

Equals

SELECT * from t_student s inner joins T_class c on s.classid = c.classid

The result is:

2, unequal connection

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

Select * from t_student s inner joins T_class C on s.classid <> c.classid

The result is:

3. Natural Connection

Concept: Connection conditions and equivalent connections are identical, but duplicate columns in the Join table are deleted.

Query statements are basically the same as equivalent connections:

Select S.*,c.classname from t_student s inner joins T_class c on s.classid = C.classid

Comparison with equivalent connections: The result is one less column of ClassID:

Summary: The inner connection is only displayed to meet the criteria!

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.

SELECT * from t_student s left joins T_class c on s.classid = c.classid

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.

 

Select * from t_student s right join T_class c on s.classid = c.classid

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

SELECT * from t_student s full join T_class c on s.classid = c.classid

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:

SELECT * fromt_student cross join T_class ' equalsSELECT * fromt_student, T_class

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. SELECT * from t_student s cross join T_class c where S.classid = C.classid >

(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.

connection queries in SQL Server