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

The SQL JOIN clause is used to combine rows from two or more tables based on common fields between those tables.

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 left table and Table B is the right table.

Idname1Google2 Taobao 3 Weibo 4Facebookidaddress1 USA 5 China 3 China 6 USA

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

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

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 6null USA

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

The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.

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.