INNER JOIN in SQL

Purpose of inner join on in sql: (equivalent connection)

SELECT * FROM A inner join B on a.no=b.no;

A record of all numbers equal to B is queried, which is equivalent to: SELECT * from A a, where a.no=b.no;

Multi-Table query:

SELECT * FROM (table 1 inner JOIN table 2 on table 1.no= table 2.no) INNER JOIN table 3 on table 1.no= table 3.no) INNER JOIN table 4 on member.no= table 4.no;

(above three tables using member)

Purpose of the LEFT join in sql:

SELECT * from A LEFT join B on a.no=b.no;

All records with a number equal to B are queried, and a value not in a is added to the result set, that is, all fields of B are empty.

The left join is based on the records of Table A, a can be regarded as the right table, and B can be regarded as left table.
In other words, the records of the left table (A) will all be represented, and the right table (B) will only display records that match the search criteria (in the example: A.aid = b.bid).
The low-record of table B is null.

Purpose of Right join in SQL:

SELECT * from A right join B on a.no=b.no;

The defaults of a is considered null when a record of a is equal to the B mark is queried and a record in B is added to the result set.
Equivalent:

SELECT * from B LEFT join A on a.no=b.no;

When using left Jion, the difference between on and where conditions is as follows:

1. On condition is the condition used when generating a temporary table, which returns records from the left table regardless of whether the condition on is true.

2. Where condition is the condition that the temporary table is filtered after the temporal table has been generated. At this point there is no left join meaning (must return the record of the table on the right), the condition is not true all filter out.

Suppose there are two tables:

Table 1 TAB1:

ID size

1 10

2 20

3 30

Table 2 TAB2:

Size Name

Ten AAA

BBB

CCC

Two sql:
1. SELECT * Formtab1 left JOIN tab2 on (tab1.size = tab2.size) where tab2.name= ' AAA '
2. SELECT * Formtab1 left JOIN tab2 on (tab1.size = tab2.size and Tab2.name= ' AAA ')

The first SQL procedure:

1. Intermediate table
On condition:
Tab1.size = Tab2.size

Tab1.id tab1.size tab2.size Tab2.name

1 AAA

2 BBB

2 CCC

3 (NULL) (NULL)

2, then the intermediate table filter
Where Condition:
Tab2.name= ' AAA '

Tab1.id tab1.size tab2.size Tab2.name

1 AAA

The second SQL procedure:

1. Intermediate table
On condition:
Tab1.size = tab2.size and tab2.name= ' AAA '
(The condition is not true also returns the records in the left table)

Tab1.id tab1.size tab2.size Tab2.name

1 AAA

2 (NULL) (NULL)

3 (NULL) (NULL)

In fact, the key reason for the above results is the particularity of the left Join,right Join,full join, regardless of whether the on condition is true will return the records in the left or right table, and full has a set of attributes of left and right. and inner jion does not have this particularity, the condition is placed in and where, the result set returned is the same.

INNER JOIN in SQL