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

This article mainly explains "Sql connection query command". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn the "Sql connection query command"!

Join query

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.

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

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. According to the different comparison methods used, the inner connection can be divided into three types: equivalent connection, natural connection and unequal connection.

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

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 rows in the first table that meet the query criteria multiplied by the number of rows in the second table that meet the query criteria.

The ON (join_condition) clause in the join operation indicates the join condition, which consists of columns in the joined table and 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.pub_id,p2.pub_id,p1.pr_info

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 equals sign (=) operator in the join condition to compare the column values of the connected columns, 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.