Inner join and Outer Join of SQL Server

From: http://www.williamlong.info/info/archives/50.html

 

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, we will introduce you 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, suppliers. companyName from products inner join suppliers on products. supplierid = suppliers. supplierid order by productname

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, suppliers. companyName from products left Outer Join suppliers

 

On products. supplierid = suppliers. supplierid order by productid

 

 

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!