SQL Server connection query details, SQL Server Query details

SQL Server connection query details, SQL Server Query details

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

What is connection query?

Concept: query data from two or more tables based on the relationship between columns.

Objective: To query multiple tables.

After knowing the concept of connection query, when can I use connection query?

It is generally used to associate two or more data tables. It looks a little abstract. Let's take two tables as an example: the student table (T_student) and the class table (T_class ).

T_student

T_class

Standard connection 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)]

Join_table indicates the name of the table involved in the join operation. The join operation can be performed on the same table or on multiple tables. The Join Operation on the same table is also called a self-Join Operation. Join_type indicates the connection type. Join_condition indicates the connection condition.

Connection Type:

There are three types of connections:Internal Connection, external connection, and cross connection.

INNER JOIN)

Use comparison operators (including =,>, <, <>, >=, <=, And!> And! <) Compare tables to query data that matches the connection conditions. According to the comparison operator, inner join is divided into equal join, natural join, and unequal join.

1. equijoin

Concept: Use the equal sign (=) operator in the connection condition. All columns in the connected table, including duplicate columns, are listed in the query results.

select * from T_student s,T_class c where s.classId = c.classId 

Equal

 select * from T_student s inner join T_class c on s.classId = c.classId 

The result is:

2. Unequal connections

Concept: Use operators except equal signs (>,<, <>,>,>=, <=, and!> in connection conditions And! <)

select * from T_student s inner join T_class c on s.classId <> c.classId

The result is:

3. natural connection

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

The query statement is basically the same as the equivalent connection:

 select s.*,c.className from T_student s inner join T_class c on s.classId = c.classId

Compared with the equijoin, the result is that one column of classId is missing:

Conclusion: only matching conditions are displayed for internal connections!

External Connection

Outer join is divided into left join, left outer join, right join, right outer join, and full join) or full outer join ). We simply call left join, right join, and full join.

1. Left join:

Concept: return all rows in the left table. If the row in the left table does not match the row in the right 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 is:

Summary: The left join shows all rows in the left table, and the right table is the same as the left table.

2. Right join:

Concept: returns all rows in the right table. If the row in the right table does not match the row in the left table, the column in the left table in the result returns a null value.

 select * from T_student s right join T_class c on s.classId = c.classId

The result is:

 Conclusion: The right join is the opposite of the left join. All rows in the right table are displayed, and the left table and the right table are the same.

3. Full connection:

Concept: return all rows in the left and right tables. If a row does not match a 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 is:

Summary: All rows in the left and right tables are returned.

Cross join: Also called dikar Product

Concept: without the WHERE clause, it returns the Cartesian product of the two joined tables, and the number of rows returned is equal to the product of the number of rows of the two tables (for example, T_student and T_class, returns 4*4 = 16 records). If the record contains a where clause, the number of matched rows is returned or displayed.

1. Without where:

The result is:

Conclusion: It is equivalent to a combination of cartesian products, left tables, and right tables.

2. If there is a where clause, it is usually a data table with the product of the number of rows in two tables, and then selected based on the where condition.

 select * from T_student s cross join T_class c where s.classId = c.classId 

(Note: Only the where clause can be used for cross join and the on clause cannot be used)

The query result is the same as the query result of the equijoin.
Attached: The exercises I have made to facilitate myself and review:

-- Inner join: Paul includes equijoin, non-equijoin, natural join-equijoin. The equal sign operator is used in the connection conditions. In the query results, all columns in the connected table are listed, including the repeated 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-equivalent join: In the connection condition, SELECT * FROM dbo using the operator except the equal sign. territories inner join dbo. region ON dbo. region. regionID <> dbo. territories. regionID; -- natural connection -- the connection condition is the same as the equivalent connection condition, but the duplicate column select t in the table is deleted. *, R. regionDescription FROM dbo. territories as t inner join dbo. region as r on r. regionID = T. regionID; -- Outer Join: divided into left join, right join, full join, or call, left Outer Join, right Outer Join, full outer join -- left join SELECT * FROM dbo. region left join dbo. territories ON Territories. territoryDescription = Region. regionDescription; -- right join SELECT * FROM dbo. region right join dbo. territories ON Territories. territoryDescription = Region. regionDescription; -- SELECT * FROM dbo. regionFULL JOIN dbo. territoriesON Territories. territoryDescription = Region. regionDescription; -- cross join: Cartesian Product -- returns the Cartesian product of the two joined tables without the where clause. The number of rows returned is the product of the number of rows in the two tables, if 'where' is included, return or display the matched number of rows SELECT * FROM dbo. region, dbo. territories; -- or SELECT * FROM dbo. region cross join dbo. territories;

Connection query is very simple. You only need to practice a lot in the project and continue to summarize it. I hope this article will help you learn it.