Left JOIN right Join_mssql in SQL

Here's what they have in common:
1. About the concept of the left-right table. Left table refers to the table on the left side of the left-hand join in the SQL statement, and the right table refers to the table that is to the right of the Ieft join.
2. In the large table, the left table is on the left and the right table is on the right.
3. On conditional statements It is best to connect the corresponding primary foreign key of the two tables with the = number. Of course, you can also use other operators, such as, to join the two tables of any field, the relationship will be very complex, the number of records after the connection becomes uncertain. If you need to use this method in some special situations, you must confirm it by a simple example, otherwise the connection result is probably not what we want!
4. On conditional statements cannot be omitted.
5. A join can be chained, each time using a join to make another table connected to the result of the current table or connection.
In the following, two tables are used, the Departments table and the organization table, where the departments table has a foreign key for organization number, pointing to the primary key number in the organization table.
INNER JOIN
Format: SELECT * FROM department inner join organization on department. Organization Number = Organization. Number
Objective: To find all records in both tables that match the on condition.
Law:
1. The large table record will not increase.
2. If the relationship between the left and the right table is a one-to-many relationship, if there is more than one record in the right table in any of the selected records, then the left table after the connection, the primary key will no longer be unique.
Typical application: The reference table with multiple relationships is placed on the left table, the referenced table with a relation is placed on the right table, the main foreign key is connected by the = number, and the right table is set up to filter the condition, then the left table record of the corresponding primary key is selected.
Note: Inner JOIN is the default connection method, which can be abbreviated to join.
Into the 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 Number = Organization. Number
Format: SELECT * from organization LEFT JOIN Department on organization. Number = Department. Organization number
Objective: To list all the records in the left table, which, if matched on conditions, are combined with the left table records and are not eligible, filled with null values.
Law:
1. Select all eligible left table, if the left and right table relationship is one-to-one relationship, then the large table records will not change.
If the relationship between the left and the right table is Many-to-many, the large table records that are spelled are not changed.
If the relationship between the left and the right table is a one-to-many relationship, the large table records that are spelled are incremented. For each left table record with a one-to-many relationship, if the left table 1:n corresponds to the right table, the N-1 record is more than one. For example, if left table the first record 1:3 corresponds to the right table, 2 more records. If left table the second record 1:2 corresponds to the right table, then 1 more records. In this way, a total of 3 more records. Other analogy.
2. If the relationship between the left and the right table is a one-to-many relationship, if there is more than one record in the right table in any of the selected records, then the left table after the connection, the primary key will no longer be unique.
3. If the relationship between the left and the right table is a one-to-many relationship, for any record of left table, if the right table does not have a record corresponding to it, complete the null value.
Typical application: The reference table with multiple relationships is placed on the left table, the referenced table with a relation is placed on the right table, and the corresponding left table record is selected by setting the filter condition on the right table.
Note: The left Outter join can be replaced with a left join. In some databases, such as HSQLDB, you can only use the left join instead of the left Outter join.
Into the 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 Number = Organization. Number
Format: SELECT * from organization right JOIN department on department. Organization Number = Organization. Number
Objective: To list all the records in the right table, as long as the on condition in the left table is combined with the right table record, not eligible, and filled with a null value.
Rule: (Contrary to the left Outter join)
Typical application: Can be converted into left Outter join. For example
SELECT * FROM organization right JOIN department on department. Organization Number = Organization. Number
And
SELECT * FROM department left join organization on department. Organization Number = Organization. Number
The same effect