Shulou(Shulou.com)06/01 Report--

SQL provides several types of joins, and the difference between them is that different methods are used to select rows for joins from different data sets that overlap each other. A. Internal connection

Internal connections, the most common equivalent connections. (restrictions on watches on both sides)

B. external connection

The outer connection is divided into left outer connection, right outer connection and full external connection.

Left outer join: the left table is unrestricted, the data of the left table is retained, the right table is matched, and the columns in the rows that do not match in the right table are displayed as null. (the left outer join is to add the unmatched data in the main table to the equivalent join.)

Right outer connection: there is no restriction on the right table and the data in the right table is retained. Matches the left table, and the columns in the rows that do not match in the left table are displayed as null. (the right outer join is to add the mismatch data of the matching table on the basis of the equivalent join.)

Full external join: there are no restrictions on the left and right tables. That is, the result is: the data matched by the left and right tables + the data not matched by the left table + the data not matched by the right table. (the full outer join is to add the unmatched data of the left table and the right table on the basis of the equivalent join.)

The syntax of the connection: (usually the outer join omits the outer keyword)

Left/right/full outer join... on

Left/right/full join... on

The function of the (+) sign: the + sign can be understood as a supplement, that is, which table has a plus sign, this table is the matching table. Added to the column of the right table represents the right table as a supplement, for the left outer join. Added to the column of the left table represents the left table as a supplement, for the right outer join.

Note: full external connection does not support (+) writing.

Create two tables to generate test data:

CREATE TABLE TQA (

Id number

Name VARCHAR2 (10)

);

CREATE TABLE TUB (

Id number

Name VARCHAR2 (10)

);

INSERT INTO TQA VALUES (1)

INSERT INTO TQA VALUES (2 not QB')

INSERT INTO TQA VALUES (3 recorder QC')

INSERT INTO TQA VALUES (4)

INSERT INTO TQA VALUES (5 minutes QE')

INSERT INTO TUB VALUES (1 million UA')

INSERT INTO TUB VALUES (1 recording UB')

INSERT INTO TUB VALUES (2)

INSERT INTO TUB VALUES (1)

INSERT INTO TUB VALUES (7)

Left outer connection

Select from TQA a left join TUB b on a.id=b.id

Select from TQA a dint TUB b where a.id=b.id (+)

Right outer connection

Select from TQA a right join TUB b on a.id = b.id

Select from TQA amam TUB b where a.id (+) = b.id

Full external connection

Select from TQA a full join TUB b on a.id=b.id

Equivalent connection (internal connection can also omit the keyword inner and write directly as join)

Select from TQA a,TUB b where a.id=b.id

Select * from TQA a join TUB b on a.idladb.idscape ~ equivalent connection can also be written in this way

Note: there is a difference between an equivalent connection and a full external connection. An equivalent join is to connect only the rows of two tables that meet the criteria and then display them. A full outer join displays rows that match the query criteria, rows that are not matched in the left table, and rows that are not matched in the right table.

Summary

SQL connections (inner/outer join) include the following:

Inner join (tables on both sides are restricted)-- [inner] join

Left outer join (unrestricted table on the left)-- left [outer] join

Right outer join (unrestricted table on the right)-- right [outer] join

Full external join (left and right tables are not restricted)-- full [outer] join

Both the left outer join and the right outer join are based on the main table, and all the contents of the table are displayed, followed by the matching of the main table and the matching table. If the data of the master table is not recorded in the matching table, the column is displayed as a null value (null) in the associated result set row.

Internal connection, you can use "(+)", but must be omitted. That is, both tables are "master tables" and neither is a matching table.

For external connections, you can also use "(+)" to indicate.

Some considerations about the use of external connections (+):

The (+) operator can only appear in the where clause and cannot be used in conjunction with outer join syntax. When using the (+) operator to perform an outer join, if there are multiple conditions in the where clause, you must include the (+) operator in all conditions. The (+) operator applies only to columns, not to expressions. The (+) operator cannot be used with or and in operators. The (+) operator can only be used for left and right outer joins, not for full outer joins.

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.