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

This article mainly explains "the use of left connection, right connection and full connection of database". The explanation content in this article is simple and clear, easy to learn and understand. Please follow the idea of Xiaobian to study and learn "the use of left connection, right connection and full connection of database" together.

Joins can be established in the FROM clause or WHERE clause of a SELECT statement, and specious indications of a join in the FROM clause help distinguish the join operation from the search condition in the WHERE clause. Therefore, this method is recommended in Transact-SQL.

The join syntax 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 participating in the join operation. Joins can operate on the same table or on multiple tables. Joins operating on the same table are also called self-joins. join_type indicates the type of join, which can be divided into three types: inner join, outer join, and cross join. INNER JOIN uses comparison operators to compare column (s) of data between tables and lists rows of data in those tables that match the join condition. According to the different comparison methods used, inner join can be divided into three types: equivalent join, natural join and unequal join.

There are three types of outer joints: LEFT OUTER JOIN or LEFT JOIN, RIGHT OUTER JOIN or RIGHT JOIN and FULL OUTER JOIN. Unlike inner joins, outer joins list not only rows that match the join criteria, but all rows that match the search criteria in the left table (when left outer joins), right table (when right outer joins), or both tables (when all outer joins).

CROSS JOIN has no WHERE clause and returns the Cartesian product of all rows of data in the joined tables, with the number of rows of data in the result set equal to the number of rows of data in the first table that match the query multiplied by the number of rows of data in the second table that match the query.

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

Either join does not allow direct joins to text, ntext, and image data type columns, but indirect joins can be made to all three. 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)

(i) Internal connections

The inner join query operation lists the rows of data that match the join condition and compares the column values of the joined columns using comparison operators. There are three types of inner connections:

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

Unequal join: Compare the column values of the connected columns using comparison operators other than the equal operator 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.