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

Editor to share with you sql left connection, right connection, inner connection, full external connection comparison what are the differences, I hope you will gain something after reading this article, let's discuss it together!

Difference: the left join returns all records in the left table and the records with equal join fields in the right table; the right join returns all records in the right table and records with equal join fields in the left table; the inner join only returns rows with equal join fields in the two tables; the all-outer join returns all records in the left and right tables and records with equal join fields in the left and right tables.

Left join (left join, left outer join): returns records that include all records in the left table and records with equal join fields in the right table.

Right join (right join, right outer join): returns records that include all records in the right table and equal join fields in the left table.

Inner join (equivalent join or inner join): only rows with equal join fields in two tables are returned.

Full join (full external join): returns all records in the left and right table that are equal to the join fields in the left and right table.

For example:

Table A

Id name

1 Xiao Wang

2 Xiao Li

3 Xiao Liu

Table B

Id A_id job

1 / 2 teacher

2 4 programmers

Inner join: (only rows that match 2 tables can be displayed)

Select a.name,b.job from An an inner join B b on a.id=b.A_id

You can only get one record:

Miss Xiao Li

Left join: (the table on the left is unrestricted)

Select a.name,b.job from An a left join B b on a.id=b.A_id

Three records:

Xiao Wang null Xiao Li teacher Xiao Liu null

Right join: (the table on the right is unrestricted)

Select a.name,b.job from An a right join B b on a.id=b.A_id

Two records:

Mr. Li, null programmer.

Full external connection: (there are no restrictions on the left and right tables)

Select a.name,b.job from An a full join B b on a.id=b.A_id

Four pieces of data

Xiao Wang null Xiao Li teacher Xiao Liu null null programmer

Note: in sql, l outer connection includes left connection (left join) and right connection (right join), full outer connection (full join), and equivalent connection (inner join) is also called inner connection.

After reading this article, I believe you have a certain understanding of "what are the differences between left connection, right connection, inner connection and full external connection in sql". If you want to know more about it, welcome to follow the industry information channel. Thank you for your reading!

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.