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

This article mainly introduces "the use of sql join on". In the daily operation, I believe that many people have doubts about the use of sql join on. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts about the use of sql join on. Next, please follow the editor to study!

The number of records connected by A left join B is the same as that in Table A.

The number of records connected by A right join B is the same as that in table B.

A left join B equivalent B right join A

Table A:Field_K, Field_A1 a3 b4 ctable B:Field_K, Field_B1 x2 y4 zselect a.Field_K, a.Field_A, b.Field_K B.Field_B from a left join b on a.Field_K=b.Field_KField_K Field_A Field_KField_ B-1 a 1 x 3 b NULL NULL4 c 4 z, that is, left join joins all records in the left table. If the relevant record is not found in the table2 according to the connection condition, it is displayed as null. Right join displays all the records in the table on the right. Inner join, on the other hand, only eligible records appear in the result set.

EG2 ∶ has two tables an and b, the first two fields are exactly the same: (id int,name varchar (10)...)

Id name

--

1 a

2 b

3 c

Do you know the running result of the following query statement? :

1.

Select * from a left join b on a.id=b.id where a.id=1

two。

Select * from a left join b on a.id=b.id and a.id=1

3.

Select * from a left join b on a.id=b.id and b.id=1

4.

Select * from a left join b on a.id=1

Results:

Id name id name

--

1 10 1 10

(1 row (s) affected)

Id name id name

--

1 10 1 10

2 20 NULL NULL

3 30 NULL NULL

(3 row (s) affected)

Id name id name

--

1 10 1 10

2 20 NULL NULL

3 30 NULL NULL

(3 row (s) affected)

Id name id name

--

1 10 1 10

1 10 2 20

1 10 3 30

2 20 NULL NULL

3 30 NULL NULL

(5 row (s) affected)

Train of thought:

Left join, based on the left table, scans the records that match the table on the right.

First, the first record of the left-hand table

1 a

Scan the records of the table on the right by conditional a.id=1

For each record in the table on the right, it is obvious that the a.id=1 condition is true, so the matching result of the first record is:

1 a 1 a

1 a 2 b

1 a 3 c

-

Then scan the second record.

2 b

For conditional a.id=1, there is no matching record in the edge table, so the right table is NULL

So the result of the second record match is

2 b NULL NULL

-

The third record is the same as the second record, and the matching result is

3 c NULL NULL

-

So the end result is five records.

1 a 1 a

1 a 2 b

1 a 3 c

2 b null null

3 c null null

Inner join (a typical join operation that uses a comparison operator such as = or). It includes equal connection and natural connection.

The inner join uses the comparison operator to match rows in two tables based on the values of the columns common to each table. For example, retrieve all rows with the same student identification number in the students and courses tables.

Outer join, which can be left outer join, right outer join, or full outer join.

When an outer join is specified in the FROM clause, it can be specified by one of the following sets of keywords:

LEFT JOIN or LEFT OUTER JOIN.

The result set of the left outer join includes all rows of the left table specified in the LEFT OUTER clause, not just the rows matched by the join column. If a row of the left table does not match in the right table, all select list columns of the right table in the associated result set rows are null.

RIGHT JOIN or RIGHT OUTER JOIN.

The right outer join is the reverse join of the left outer join. All rows of the right table are returned. If a row of the right table does not match in the left table, a null value is returned for the left table.

FULL JOIN or FULL OUTER JOIN.

The full outer join returns all rows in the left and right tables. When a row does not match a row in another table, the select list column of the other table contains null values. If there are matching rows between tables, the entire result set row contains the data values of the base table.

Cross-connect.

The cross join returns all rows in the left table, and each row in the left table is combined with all rows in the right table. Cross joins are also called Cartesian products. At this point, the study of "how to use sql join on" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.