A detailed explanation of how SQL Server database links queries _mssql

The knowledge of the way SQL Server database links query is what we want to introduce in this article, we know that multiple table queries can be implemented through the concatenation operator. Connection is the main feature of relational database model, and it is also a symbol distinguishing from other types of database management system. Multi-table Join query is the basic operation of SQL, but there are many ways to connect, the skilled use of these connections can simplify the SQL statements, improve the efficiency of database operation.

In the relational database management system, the relationship between the data in the table is not determined, and all the information of an entity is stored in a table. When retrieving data, queries the information of different entities that are stored in multiple tables through a JOIN operation. Connection operations give users a lot of flexibility, and they can add new data types at any time. Create a new table for different entities, and then query through a connection.

A connection can be established in the FROM clause or a WHERE clause of a SELECT statement, and paradoxically, in the FROM clause, it helps to differentiate the join operation from the search conditions in the WHERE clause. Therefore, the use of this method is recommended in Transact-SQL.

Basic grammar and methods

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, which can be divided into three types: inner, outer, and Cross joins . The INNER join (INNER join) uses the comparison operator to compare operations of some (some) column data between tables and lists the rows of data in those tables that match the join condition. Varies according to the comparison method used.

1. The inner connection is divided into equivalent connection, natural connection and unequal connection three kinds.

2. The outer joins are divided into three kinds of left outer joins (OUTER join or Ieft join), right outer join (right-hand OUTER join) and all outer join (full OUTER join or fully join). Unlike an inner connection, the outer join lists not only the rows that match the join condition, but all the rows of data that match the search criteria in the left table (when left outer), right (when the right outer join), or two tables (when all outer joins).

3. A cross join (CROSS join) does not have a WHERE clause that returns the Cartesian product of all the rows of data in the join table, with the number of rows in the result set equal to the number of rows of data in the first table that match the query criteria multiplied by the number of rows of data in the second table that

The ON (join_condition) clause in a JOIN operation indicates the join condition, which consists of columns and comparison operators, logical operators, and so on in the connected table.

No connection can be directly connected to the text, ntext, and image data type columns, but the three types of columns may be indirectly connected. For example:

SELECT P1.pub_id,p2.pub_id,p1.pr_info from  
 
pub_info as P1 INNER JOIN pub_info as P2  
 

(i) Internal connections

The INNER JOIN query operation lists the rows of data that match the join criteria, which compares the column values of the connected columns using comparison operators. The inner connection is divided into three kinds:

1. Equivalent connection: Use the equals sign (=) operator in the join condition to compare the column values of the connected columns, whose query results list all the columns in the connected table, including the repeating columns.

2. Unequal connection: The column values of the concatenated columns are compared using comparison operators other than the equals operator in the join condition. These operators include >, >=, <=, <,!>,!<, and <>.

3. Natural connection: Use the Equals (=) operator in the join condition to compare the column values of the connected columns, but it uses a select list to indicate the columns included in the query result collection, and deletes the duplicate columns from the attached table.

For example, the following uses an equivalent connection to list authors and publishers in the same city in the authors and Publishers tables:

SELECT * FROM  
authors as a INNER JOIN publishers as P on  
a.city=p.city  
SELECT a.*,p.pub_id,p.pub_name,p.count Ry from  
authors as a INNER JOIN publishers as P on  
a.city=p.city 

Also, if you are using a natural connection, delete the repeating columns (city and state) in the authors and Publishers tables in the select list:

(ii) OUTER joins

In the query results collection, only rows that meet the query criteria (WHERE search conditions or having conditions) and join conditions are returned. When an outer join is used, it returns to the query result collection not only for rows that meet the join criteria, but also for all data rows in the left table (when the left outer join), right table (when the right outer join), or two side tables (full outer joins).

1, left JOIN or left-hand outer join (outer join)

Left OUTER join: (also called Left outer connection, give two table of the row, and with the table on the left, if there is a table on the left and no rows on the right table, the columns selected on the right table are displayed as NULL, allowing the Left datum table to correspond to multiple records of the right table, and the left is to return the matching row on the left. Do not consider whether the table on the right has a corresponding row

Select Field1,field2 from table1 left join table2 
on field1=field2 (base field, can multiple) 
where table1.field3= Table2.field3 

2, right join or right-hand outer join (outer connection)

Right connection: (also called right outer connection, give two tables of the row, and the table on the right, if there is a table on the right and the left table does not have rows, the table on the right side of the corresponding row of the selected column is displayed as NULL, allowing the right base table for the left table to meet the criteria of a number of records.

The syntax is similar to doing links!

(iii) Cross-linking

A cross join does not take a WHERE clause, which returns the Cartesian product of all rows of data in the two tables that are connected, and the number of rows returned to the result set equals the number of rows in the first table that match the query criteria multiplied by the number of rows in the second table that match the query criteria.

For example, there are 6 categories of books in the titles table, and there are 8 publishers in the publishers table, the number of records retrieved by the following cross joins equals 6*8=48 rows.

SELECT Type,pub_name from  
titles CROSS JOIN Publishers order by  
type 

About the SQL Server database link query methods related knowledge is introduced here, I hope this introduction can be harvested for you!