Differences between left and right connections in the database

Differences between left and right connections in the database

Differences between left and right connections in Databases

Today, someone asked me a question: What is the difference between left and right connections in the database? If there are two tables A and B, there are three data records in Table A and four data records in Table B. Through the left and right connections, what is the minimum number of data records queried? What is the maximum number?

I was asked this question, and then I asked the database developer, the results are various:

A Max 12 min 0

Maximum 12 minimum unknown B

C. The maximum unknown minimum is 3.

D. Maximum 12. Minimum 3

E unclear

1. Description

(1) left join: data can be retrieved as long as there are records in the left table,The right side has

Only records in the left table can be retrieved.

(2) Right join: The right join is to retrieve data as long as there are records in the right table.

2. Examples

Left join: SELECT * FROM t_left_tab a left join t_right_tab B ON a. 'id' = B. 'id ';

Query results:

Right JOIN: SELECT * FROM t_right_tab a left join t_left_tab B ON a. 'id' = B. 'id ';

Query results:

 

Maximum number of queries: SELECT * FROM t_left_tab a left join t_right_tab B ON 1 = 1;

Query results:

3. Summary

The difference between left and right connections of database A: The master table is different.

B connects to the right through the left connection. The minimum number of records is 3 (the number of records is smaller), and the maximum number is 12 (3 × 4)

In database operations, what is left connection and right connection? example

Assume that table a has two fields: Aid, name, and table B.
Nameid is the foreign key of Aid in table B.
Table a has the following data:
Aidname
1a
2b
3c
Table B has the following data:
Bidnameid
11
21
31
42
52
To put it bluntly, the left join is based on the table on the left. All the records in the table on the left (Table a) must appear. For example:
Select * from a left join B on a. Aid = B. nameid
The result should be:
AidnameBidnameid
1a11
1a21
1a31
2b42
2b52
3 cNULLNULL
Table a appears three times for Aid as 1, twice for Aid as 2, and once for Aid as 3, all. However, because no records corresponding to Aid 3 exist in Table B, null is used for filling.

Similarly, to put it bluntly, the right join is based on the table on the right,
Example: select * from a right join B on a. Aid = B. nameid
The result should be:
AidnameBidnameid
1a11
1a21
1a31
2b42
2b52
All the records of Table B must appear, but no records with Aid as 3 appear in Table a, because table B does not have the corresponding records, and table B is the standard at this time. You may not see this metaphor clearly.

What is the meaning of the right outer connection of the database?

FROM Student LEFT OUTER JOIN SC
ON Student. Sno = SC. Sno;
This student is selected regardless of whether the sno of the student corresponds to the sno of the SC.

FROM Student,
SC B
Where a. Sno = B. Sno;
If a student's sno does not exist in SC, the student will not be elected.