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

This article introduces the relevant knowledge of "the use of JOIN ON in sql sentences". Many people will encounter such a dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

JOIN connections combine field records from two tables, including three types:

INNER JOIN expression: join combines field records in two tables.

LEFT JOIN expression: join combines the field records in two tables and will contain all the records in the table on the left side of LEFT JOIN.

RIGHT JOIN expression: join combines the field records in two tables and will contain all the records in the table on the right side of RIGHT JOIN.

INNER JOIN sets expressions for the correlation of two tables to join and combine field records in two tables.

The INNER JOIN syntax is as follows:

FROM Table 1 INNER JOIN Table 2 ON Table 1. Field 1 compares operators table 2. Field 2

Fields joined by two tables, such as [Table 1. Field 1 = Table 2. Field 2], must have the same field type, but the field name does not need to be the same.

For example, the auto-numbering field type can connect to the field type of Long, but the single-finishing field type cannot connect to the field type of double precise integers.

The comparison operator can be =, or.

Fields for JOIN connections cannot contain MEMO field types or OLE object types, otherwise an error will occur.

In a JOIN expression, you can join multiple ON clauses:

SELECT fields

FROM Table 1 INNER JOIN Table 2

ON Table 1. Field 1 compares operators table 2. Field 1 AND

ON Table 1. Field 2 comparison operator Table 2. Field 2) OR

ON Table 1. Field 3 comparison operator Table 2. Field 3)

In JOIN expressions, it can be nested:

SELECT fields

FROM Table 1 INNER JOIN

(table 2 INNER JOIN [(] Table 3)

[INNER JOIN [(] Table x [INNER JOIN...)]

ON Table 3. Field 3 comparison operator table x. Field x)]

ON Table 2. Field 2 comparison operator Table 3. Field 3)

ON Table 1. Field 1 compares operators table 2. Field 2

A nested LEFT JOIN or RIGHT JOIN can be included in an INNER JOIN, but a nested INNER JOIN cannot be included in a LEFT JOIN or RIGHT JOIN.

Condition: suppose you join the authors table and the publishers table on the city column.

Use the left outward join:

Sql statement: SELECT a.au_fname, a.au_lname, p.pub_name

FROM authors a LEFT OUTER JOIN publishers p

ON a.city = p.city

Features: regardless of whether there is matching data in the second table, the result will contain all rows in the first table.

Use the right outward join:

Sql statement: SELECT a.au_fname, a.au_lname, p.pub_name

FROM authors a RIGHT OUTER JOIN publishers p

ON a.city = p.city

Features: regardless of whether there is matching data in the first table, the result will contain all rows in the second table.

Use a full outer join:

Sql statement:

SELECT a.au_fname, a.au_lname, p.pub_nameFROM authors a FULL OUTER JOIN publishers pON a.city = p.city feature: show all rows in two tables. If they match, write them together. If they don't match, write null separately.

-

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_A

1 a

3 b

4 c

Table B:

Field_K, Field_B

1 x

2 y

4 z

Select a.Field_K, a.Field_A, b.Field_K, b.Field_B from a left join b on a.Field_K=b.Field_K

Field_K Field_A Field_K Field_B

--

1 a 1 x

3 b NULL NULL

4 c 4 z

Select a.Field_K, a.Field_A, b.Field_K, b.Field_B from a right join b on a.Field_K=b.Field_K

Field_K Field_A Field_K Field_B

--

1 a 1 x

NULL NULL 2 y

4 c 4 z

Like this

Table1 table2

Id,sex1 id sex2

A 1 a 4

B 0

Select id,sex1,sex2 from table1 left join table2 on table1.id=table2.id

Then:

Id sex1 sex2

A 1 4

B 0 null

In other words, all records in the table on the left side of the join will appear in left join, and will be displayed as null if the relevant record cannot be found in table2 according to the join condition.

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.

This is the end of the introduction to "the use of JOIN ON in sql sentences". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.