Shulou(Shulou.com)05/31 Report--

This article is to share with you about how to achieve database link query in SQL Server, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Knowledge of how SQL Server database links are queried is the main content of this article. We know that multiple table queries can be implemented through the join operator. Connection is the main feature of relational database model, and it is also a sign that it is different from other types of database management systems. Multi-table join query is the basic operation of using Sql, but there are many ways to join. Skillfully using these join methods can simplify Sql statements and improve the efficiency of database operation.

In the relational database management system, the relationship between the data does not need to be determined when the table is established, and all the information of an entity is often stored in a table. When retrieving data, the join operation is used to query the information of different entities stored in multiple tables. The connection operation gives users a lot of flexibility, and they can add new data types at any time. Create new tables for different entities and then query through joins.

A connection can be established in the FROM clause or the WHERE clause of the SELECT statement. Paradoxically, pointing out the join in the FROM clause helps to distinguish the join operation from the search condition in the WHERE clause. Therefore, this method is recommended in Transact-SQL.

Basic grammar and methods

The join syntax format of 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 join can operate on the same table or multiple tables, and the join on the same table is also called self-join.

Join_type indicates the type of connection, which can be divided into three types: inner connection, outer connection, and cross connection. Inner join (INNER JOIN) uses the comparison operator to compare the data of certain columns between tables and lists the rows of data in those tables that match the join conditions. Depending on the method of comparison used.

1. Inner connection can be divided into three types: equivalent connection, natural connection and unequal connection.

2. The external connection can be divided into three types: left external connection (LEFT OUTER JOIN or LEFT JOIN), right external connection (RIGHT OUTER JOIN or RIGHT JOIN) and full external connection (FULL OUTER JOIN or FULL JOIN). Unlike the inner join, the outer join lists not only the rows that match the join criteria, but also all the data rows that meet the search criteria in the left table (for the left outer join), the right table (for the right outer join), or both tables (for all outer joins).

3. CROSS JOIN has no WHERE clause, it returns the Cartesian product of all data rows in the join table, and the number of data rows in the result set is equal to the number of data rows in the first table that meet the query conditions multiplied by the number of data rows in the second table that meet the query conditions.

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

No connection can be made directly to columns of text, ntext, and image data types, but these three columns can be joined indirectly. For example:

SELECT p1.PublicidFROM pub_info AS p2.PublicidMagazine p1.Princiinfo FROM pub_info AS p1 INNER JOIN pub_info AS p2 ON DATALENGTH (p1.pr_info) = DATALENGTH (p2.pr_info)

(1) Internal connection

The inner join query operation lists the rows of data that match the join condition, which uses the comparison operator to compare the column values of the joined column. There are three types of internal connections:

1. Equivalent join: use the equal sign (=) operator to compare the column values of the connected columns in the join condition, and the query results list all the columns in the joined table, including the duplicate columns.

2. Unequal join: use a comparison operator other than the equal operator to compare the column values of the connected column in the join condition. These operators include >, > =,

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.