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

This article introduces the relevant knowledge of "the difference between left join, right join and inner join in SQL sentences". In the operation of actual cases, many people will encounter such a dilemma, 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!

Today, I chatted with a friend and talked about a small problem in their company. As follows:

Table An equipment table, storing MAC address, province, city, district.

Table B software table, store MAC address, software name.

The function is to query the list of software by province, city, or district.

What do you think it does now?

It gets the MAC address by province, city, or district, and then queries table B with in.

This is obviously unreasonable, to deal with this many-to-many relationship, why not multi-table check?

There are three common methods of linked list: (inner) join internal equivalent join, left join left join and right join right join.

What's the difference? How do I use it? Here is an article by copy:

Left join (left join) returns records that include all records in the left table and records that are equal to join fields in the right table

Right join (right join) returns records that include all records in the right table and records that are equal to join fields in the left table

Inner join (equivalent join) returns only rows with equal join fields in two tables.

Examples are as follows:

Table An is recorded as follows:

The copy code is as follows:

AID aNum

1 a20050111

2 a20050112

3 a20050113

4 a20050114

5 a20050115

Table B is recorded as follows:

The copy code is as follows:

BID bName

1 2006032401

2 2006032402

3 2006032403

4 2006032404

8 2006032408

1.left join

The sql statement is as follows:

The copy code is as follows:

Select * from A left join B on A.aID = B.bID

The results are as follows:

The copy code is as follows:

AID aNum bID bName

1 a20050111 1 2006032401

2 a20050112 2 2006032402

3 a20050113 3 2006032403

4 a20050114 4 2006032404

5 a20050115 NULL NULL

(the number of rows affected is 5)

The results show that:

Left join is based on the record of table A, A can be regarded as the left table, B can be regarded as the right table, and left join is based on the left table.

In other words, the records in table An on the left will be fully represented, while those in table B on the right will only show records that meet the search criteria (in this case, A.aID = B.bID).

The deficiency in table B is NULL.

2.right join

The sql statement is as follows:

The copy code is as follows:

Select * from A right join B on A.aID = B.bID

The results are as follows:

The copy code is as follows:

AID aNum bID bName

1 a20050111 1 2006032401

2 a20050112 2 2006032402

3 a20050113 3 2006032403

4 a20050114 4 2006032404

NULL NULL 8 2006032408

(the number of rows affected is 5)

The results show that:

If you take a closer look, you will find that, contrary to the result of left join, this time it is based on table (B) on the right, and the deficiency in Table An is filled with NULL.

3.inner join

The sql statement is as follows:

The copy code is as follows:

Select * from An innerjoin B on A.aID = B.bID

The results are as follows:

The copy code is as follows:

AID aNum bID bName

1 a20050111 1 2006032401

2 a20050112 2 2006032402

3 a20050113 3 2006032403

4 a20050114 4 2006032404

The results show that:

Obviously, only the record of A.aID = B.bID is shown here. This shows that inner join is not based on anyone, it only displays records that meet the criteria.

Note:

The LEFT JOIN operation is used to combine the records of the source table in any FROM clause. Use the LEFT JOIN operation to create a left outer join. The left outer join will contain all the records in the two tables starting from the first (left), even if there are no records with corresponding values in the second (right) table.

Syntax: SELECT FROM table1 LEFT JOIN table2 ON table1.field1 compopr table2.field2

Description:

The table1, table2 parameter is used to specify the name of the table in which records are to be combined.

The field1, field2 parameter specifies the name of the field to be joined. And these fields must have the same data type and contain the same type of data, but they do not need to have the same name.

The compopr parameter specifies the relational comparison operator: "=", "", "=" or "".

If you join fields that contain Memo data type or OLE Object data type data in an INNER JOIN operation, an error will occur.

So, as far as I understand it, sql should write:

Select software from software table inner join device table on software table. Mac = equipment table. Mac where device table. Pro = 'xxx' and device table. City =' xxx'

This is the end of the content of "the difference between left join, right join and inner join 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.