Left join right join in SQL

The following are their commonalities:
1. Concepts about left and right tables. The left table refers to the table on the left of left join in the SQL statement, and the right table refers to the table on the right of left join.
2. In a large table, the left table is placed on the left, and the right table is placed on the right.
3. For the on condition statement, it is best to use the = sign to connect the corresponding primary and Foreign keys of the two tables. Of course, you can also use other operators, such as >,<, to connect any field in the two tables. The relationship between the two fields is very complex, and the number of records after the join operation becomes uncertain. If this method is required in some special scenarios, it must be confirmed by a simple instance. Otherwise, the connection result may not be what we want!
4. The on condition statement cannot be omitted.
5. join can be chained. Each join Operation connects another table to the current table or join result.
The following two tables are used: "department" table and "Organization" table. The "department" table has a foreign key named "organization number, point to the primary key number in the "Organization" table ".
Inner join
Format: select * from department inner join organization on department. Organization No. = Organization. No.
Purpose: To retrieve all records that meet the on condition in the two tables.
Rule:
1. The records of the assembled large table are not added.
2. if the relationship between the left table and the right table is one-to-many, if multiple records in the right table correspond to the selected records, the connected left table, the primary key is no longer unique.
Typical application: Place referenced tables with multiple relationships in the left table, place referenced tables with one link in the right table, and connect the primary and Foreign keys using the = sign, set filtering conditions for the right table to select the corresponding left table record with the unique primary key.
Note: inner join is the default join method, which can be abbreviated as join.
Convert to where clause:
Select * from department, organization where department. Organization number = Organization. Number
Left outter join
Format: select * from department left join organization on department. Organization No. = Organization. No.
Format: select * from organization left join Department on organization. No. = Department. Organization No.
Purpose: to list all records in the left table. If the on condition is met in the right table and the records in the left table are combined, if the conditions are not met, enter a null value.
Rule:
1. Select all left tables that meet the conditions. If the relationship between the left and right tables is one-to-one, the assembled large table records will not change.
If the relationship between the left table and the right table is multiple-to-one, the merged large table record will not change.
If the relationship between the left table and the right table is one-to-many, the combined large table records will increase. For each left table record with a one-to-many relationship, if the left table 1: N corresponds to the right table, there will be more N-1 records. For example, if the first record in the left table is corresponding to the right table, there are two more records. If the second record of the Left table is corresponding to the right table, one more record is displayed. In this way, three more records are generated. And so on.
2. if the relationship between the left table and the right table is one-to-many, if multiple records in the right table correspond to the selected records, the connected left table, the primary key is no longer unique.
3. If the relationship between the left table and the right table is one-to-many, for any record in the left table, if there is no record corresponding to it in the right table, fill in all null values.
Typical application: Place referenced tables with multiple relationships in the left table, place referenced tables with a link in the right table, and set filtering conditions for the right table, select the corresponding left table record with the unique primary key.
Note: left outter join can be replaced by left join. In some databases, such as HSqlDb, you can only use left join instead of left outter join.
Convert to where clause:
Select * from department, organization where department. Organization number = Organization. Number
Right outter join
Format: select * from department right join organization on department. Organization No. = Organization. No.
Format: select * from organization right join Department on department. Organization No. = Organization. No.
Purpose: To list all records in the right table. If the on condition is met in the left table and the records in the right table are combined, if the conditions are not met, enter a null value.
Rule: (opposite to left outter join)
Typical application: It can be converted into left outter join. For example
Select * from organization right join Department on department. Organization No. = Organization. No.
And
Select * from department left join organization on department. Organization No. = Organization. No.
The same effect