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

This article is to share with you the content of the sample analysis of various connection Join in SQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

The most common JOIN types are SQL INNER JOIN (simple JOIN), SQL LEFT JOIN, SQL RIGHT JOIN, and SQL FULL JOIN, with the first one being inner links and the last three being outer links.

Suppose we have two tables, Table An is the upper table and Table B is the lower table.

Table A

Idname1Google2 Taobao 3 Weibo 4Facebook

Table B

Idaddress1 USA 5 China 3 China 6 USA

1. INNER JOIN

An inner join is the most common type of connection, connecting only matching rows.

Inner join syntax

Select column_name (s) from table 1INNER JOIN table 2ONtable 1.column_name=table 2.column_name

Note: INNER JOIN is the same as JOIN

The result set produced by INNER JOIN is the intersection of 1 and 2.

Select * from Table An inner join Table Bon Table A.id=Table B.id

The output of executing the above SQL is as follows:

Idnameaddress1Google USA 3 Weibo China

II. LEFT JOIN

LEFT JOIN returns all rows of the left table and rows of the right table that meet the ON condition. If the rows of the left table do not match in the right table, then the corresponding data in the right table of this row is replaced by NULL.

LEFT JOIN syntax

Select column_name (s) from table 1LEFT JOIN table 2ON table 1.column_name=table 2.column_name

Note: in some databases, LEFT JOIN is called LEFT OUTER JOIN

LEFT JOIN produces the complete set of Table 1, while those in Table 2 have values, and those that do not match are replaced by null values.

Select * from Table A left join Table Bon Table A.id=Table B.id

The output of executing the above SQL is as follows:

Idnameaddress1Google USA 2 Taobao null3 Weibo China 4Facebooknull

III. RIGHT JOIN

RIGHT JOIN returns all the rows of the right table and the rows of the left table that meet the ON condition. If the rows of the right table do not match in the left table, then the corresponding data in the left table of this row is replaced by NULL.

RIGHT JOIN syntax

Select column_name (s) from table 1RIGHT JOIN table 2ON table 1.column_name=table 2.column_name

Note: in some databases, RIGHT JOIN is called RIGHT OUTER JOIN

RIGHT JOIN produces the complete set of Table 2, while those in Table 1 have values, and those that do not match are replaced by null values.

Select * from Table A right join Table Bon Table A.id=Table B.id

The output of executing the above SQL is as follows:

Idnameaddress1Google US 5null China 3 Weibo China 6

IV. FULL OUTER JOIN

FULL JOIN returns all rows from the left and right tables. If the data rows of one table do not match in the other table, then the data on the opposite side is replaced by NULL

FULL OUTER JOIN syntax

Select column_name (s) from table 1FULL OUTER JOIN table 2ON table 1.column_name=table 2.column_name

FULL OUTER JOIN produces the union of 1 and 2. It is important to note, however, that for records that do not match, null is used as the value.

Select * from Table A full outer join Table Bon Table A.id=Table B.id

The output of executing the above SQL is as follows:

Idnameaddress1Google USA 2 Taobao null3 Weibo China 4Facebooknull5null China 6null USA Thank you for your reading! This is the end of this article on "sample analysis of various connections Join in SQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.