Inner join and Outer Join of SQL Server

In a formal database environment, we often encounter this situation: the required data is not stored in the same data table. At this time, you need to use join.
Of course, how to combine the data of different databases depends on how you use it. There are four different Join methods in this article. Article We will introduce you to inner join and Outer Join and their applications.

Inner join
Inner join is the most common join method. It returns only records that comply with the join rules, let's take a look at the syntax
select from [on ]
now we use the 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
Please open QA (query analyzer), in order to use the north wind database, run use northwind first, and then
select productid, productname, supplierid from products
to retrieve three fields from the products product data table, they are product Code , product name, and supplier code, but the query results make your boss very dissatisfied, because the supplier code is meaningless to humans, join can help at this time. By joining the suppliers data table, we can query the Supplier name
select productid, productname, suppl IERS. supplierid
from products
Inner join suppliers
products. Suppliers = suppliers. supplierid
is the query result clear! 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 common users, and even some SQL managers have never used it, this is really a sad ambition, because using outer join can simplify some queries, let's take a look at the outer join syntax
select from [outer] Join On
the 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, to achieve the expected results
first, remove the foreign key of the products data table. Otherwise, a new supplierid is not added to the suppliers data table in the products data table, to understand the constraint of a data table, you can execute the SQL built-in sp_helpconstraint, execute
sp_helpconstraint products
and then delete the foreign key of fk_products_suppliers
alter table products
drop constraint fk_products_suppliers
to add a new table named products, supplierid uses 50 because it is not mapped to the records in the suppliers table

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!