Inner join and outer join)

Introduction to inner join and Outer Join and Their Applications
Inner join
Inner join should be the most common join method. It will only return records that comply with the join rules. Let's take a look at the syntax first.
Select <field to be selected> from <main data table>
<Join method> <secondary data table> [on <join rule>]
Now we use ms SQL built-in northwind database to practice it! If you want to know more about the built-in database of ms SQL, you can look at the built-in database of SQL Server this article
Open QA (query analyzer). To use the northwind database, run use northwind first, and then
Select productid, productname, supplierid from products
Three fields are extracted from the products product data table: product code, product name, and Supplier Code. However, the query results ensure your boss is not satisfied, because the supplier code is really meaningless to humans, join can help at this time. By joining the suppliers table, we can query the Supplier name.
Select productid, productname, suppliers. supplierid
From Products
Inner join suppliers
Products. Suppliers = suppliers. supplierid
Is the query result clearly displayed! The main spirit of inner join is exclusive. Call it exclusive! That is, materials that do not match the join rule will be excluded. For example, the supplier code of a product (supplierid) in the product is not displayed in the suppliers data table, this record will be excluded.
Outer Join
This join method is rarely used by ordinary people, and even some SQL managers have never used it. This is really a sad ambition, because using outer join can simplify some query operations, Let's first look at the outer join syntax.
Select <field to be queried> from <left table>
<Left | right> [outer] Join <right table> On <join rule>
Outer In the syntax can be omitted. For example, you can use left join or right join. In essence, outer join is inclusive! Different from the exclusive nature of inner join, the query results in left Outer Join will contain information about all left tables, right outer join queries will contain all the right data tables. Next we will do some practical operations, and we will still use the north wind database, but we need to make some small modifications first, in order to achieve our desired results
First, remove the foreign key of the products data table. Otherwise, a new supplierid in the products data table does not have a record mapped to the suppliers data table, to understand the constraint of a data table, you can execute the SQL built-in sp_helpconstraint, which is executed in QA.
Sp_helpconstraint Products
Next, delete the foreign key fk_products_suppliers.
Alter table Products
Drop constraint fk_products_suppliers
A new record is added to the products table. The supplierid uses 50 because it is not mapped to the suppliers table.
Insert into products (productname, supplierid, categoryid)
Values ('test product', '50', '1 ')
Now we will execute the first query, but change inner join to left Outer Join.
Select productid, productname, suppliers. supplierid
From Products
Left Outer Join suppliers
Products. Suppliers = suppliers. supplierid
Compare the query results of the two join modes, and you will know the difference!
Let's take a look at right Outer Join. Please add the bottom record.
Insert into suppliers (companyName)
Values ('learnasp ')
Please use right out join for query now. Let's compare the query result with that of inner join!
Search for inconsistent records
Here we will take a look at how to use out join to find records that do not match each other. It may be that there are child records but no parent records or they are reversed.
Select suppliers. companyName from products
Right join suppliers
On products. supplierid = suppliers. supplierid
Where products. supplierid is null
In the execution result, you will find a piece of information for learnasp, which already exists, but basically no product comes from this supplier, imagine how you can use an SQL command to complete the same query result without using outer join! I know how to use outer join! Run again
Select products. productname
From Products
Left join suppliers
On products. supplierid = suppliers. supplierid
Where suppliers. supplierid is null
You will find that the product test product cannot find the supplier's information!