The connection query in SQL Server detailed _mssql

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

What is a connection query?

Concept: Query data from these tables based on the relationships between columns in two or more tables.

Objective: To implement multiple table query operations.

After you know the concept of the connection query, when to use the connection query?

Typically used when associating two or more tables of data. It looks a bit abstract, for example, we do two tables: The Student table (t_student) and the 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 a multiple table operation, and the connection to the same table operation is called a self connection. Join_type indicates the type of connection. Join_condition refers to the connection condition.

Connection type:

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

INNER JOIN (INNER join)

Use comparison operators (including =, >, <, <>, >=, <=,!>, and!<) for comparison between tables to query for data that matches the join condition. Depending on the comparison operator, the inner joins are divided into equivalent connections, natural joins and unequal connections of three species.

1. Equivalent connection

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

Equals

 SELECT * FROM t_student s inner join t_class c on s.classid = C.classid 

The result:

2, unequal connection

Concept: Use an operator other than the equals number in a join condition (>, <, <>, >=, <=,!>, and!<)

SELECT * FROM t_student s inner join T_class C on S.classid <> C.classid

The result:

3. Natural connection

Concept: The join condition is the same as the equivalent connection, but the duplicate columns in the attached table are deleted.

Query statements are basically the same as equivalent connections:

 Select S.*,c.classname from t_student s inner join t_class c on s.classid = C.classid

Compared to the equivalent connection: The result is one less column ClassID:

Summary: The inner join is only displayed to meet the conditions!

Outer Joins

The outer joins are divided into left joins (the left join), the right join, or the right OUTER join, the full join, or the OUTER join (full OUTER join). We simply call: Left JOIN, right connection and full connection.

1, left JOIN connect:

Concept: Returns all rows from the left table, if there is no matching row in the right table in the left table, the column in the right table in the result returns a null value.

SELECT * FROM T_student s left join T_class c on s.classid = C.classid

The result:

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

2, right Connection:

Concept: just with left join instead, return all rows in the right table, and the columns in the left table in the result return null if the row in the right table has no matching rows in the left table.

 SELECT * from t_student s right join t_class c S.classid = C.classid

The result:

Summary: The right connection is 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 column in the other table returns a null value

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

The result:

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

Cross-connect (CROSS JOIN): Also known as Cartesian product

Concept: Without a WHERE condition clause, it will return the Cartesian product of the two tables that are connected, returning the result to the product of two table rows (for example: T_student and T_class, returning 4*4=16), and if so, returns or displays the number of rows that match.

1. Do not take where:

The result:

Summary: Quite with Cartesian product, left table and right table combination.

2. There is a WHERE clause, which often makes the data table of the product of two table rows, and then selects from it according to the Where condition.

(Note: Cross join after the condition can only be used where, can not be used on)

The query result is the same as the query result of the equivalent connection.
Finally attached: I do the exercise, easy to myself, review:

 --Inner connection: Paul includes equivalent connections, unequal connections, natural connections-equivalent connections; using the equal sign operator in a join condition, the query result,-lists all the columns in the attached table, including the repeating columns SELECT * FROM dbo. Territories,dbo. Region WHERE dbo. Region.regionid=dbo.
 
 Territories.regionid; SELECT * FROM dbo. Territories INNER JOIN dbo. Region on dbo. Territories.regionid=dbo.
 
 
 Region.regionid; -Non-equivalence connection: In a join condition, a SELECT * FROM dbo with an operator other than an equal sign is used. Territories INNER JOIN dbo. Region on dbo. Region.regionid<>dbo.
 
 
 Territories.regionid; --A natural connection--the join condition and the equivalent join condition are the same, but the repeating column SELECT t.*,r.regiondescription from dbo is deleted from the table. Territories as T INNER JOIN dbo.
 
 Region as R on r.regionid = T.regionid; --Outer connection: divided into left connection, right connection, full connection, or called, left outer connection, right outer connection, all outer connection--LEFT join SELECT * FROM dbo. Region left JOIN dbo.
 
 territories on territories.territorydescription = Region.regiondescription; --Right connection SELECT * FROM dbo. Region right JOIN dbo.
 
 
 territories on territories.territorydescription = Region.regiondescription; --Full Connection SELECT * FROM dbo. Region full JOIN dbo. territories on territories.territorydescription = Region.regiondEscription; --Cross connection: Also called Cartesian product--without the WHERE condition clause, the Cartesian product that is connected to the two tables will be returned, the number of rows that returns the result is equal to the product of two table rows and, if so, returns or displays the number of rows matched SELECT * FROM dbo. Region,dbo.
 
 territories; --or SELECT * FROM dbo. Region CROSS JOIN dbo.
 territories;

Connection query is very simple, only need to practice a lot in the project, constantly summed up, I hope this article for everyone's learning help.