SQL Server inner JOIN and outer join

of course join how to combine the data of different database, also depends on how you use it, there are four different ways of join, in this article we will introduce you Inner join and Outer join and its application.

In a formalized database environment, we often encounter this situation: the required information is not placed in the same table, at this time, you need to use the join.

Of course join how to combine the data of different database, also depends on how you use it, there are four different ways of join, in this article we will introduce you Inner join and Outer join and its application.

Inner Join
The Inner join should be the most commonly used join method, which will only return records that conform to the join rule, or take a look at the syntax first:
Select < fields to select > from < Main Table > <join mode > < secondary table > [on <join Rules]
Now we use MS SQL built in the north Wind database to practice! To learn more about MS SQL's built-in database, you can look at SQL Server's built-in database for this article.

Please turn on QA (Query Analyzer), to use the North Wind database, first execute using use Northwind, and then execute
Select ProductId, ProductName, SupplierId from
Take out three fields from the Products table, which are product code, product name, supplier code, but the results of the query to ensure that your boss is not satisfied, because the supplier code for humans is really meaningless, this time join can help, by join Suppli ERs This information sheet, we can find out the name of the supplier.
Select ProductId, ProductName, Suppliers.supplierid
From Products
Inner Join Suppliers
Products.suppliers = Suppliers.supplierid
The result of this query is not clear! The main spirit of Inner Join is exclusive, let it be exclusive! The information that does not match the Join rule is excluded, for example, if there is a supplier code (SUPPLIERID) in product that does not appear in the Suppliers table, then the record is excluded

Outer Join
This type of Join is less commonly used, even some SQL managers have never used, this is a sad generation, because the use of Outer join can simplify some of the work of the query, first look at the Outer join syntax Select < to query the word Section > from <left tables > <left | right> [Outer] Join <right table > on <join rules >

Outer in grammar can be omitted, for example, you can use left JOIN or right join, in essence, Outer join is inclusive, let it be inclusive! Unlike the exclusivity of the Inner join, so the query result in the left Outer join contains all the data from the left table, upside down, the right Outer join query will contain the data for all of the table, and then we'll do Some practical operations, still use the north wind database, but to do some small changes before we can achieve the results we want.

The first to take off the Products table Foreign Key, otherwise there is no application in the Products table add a SupplierId not to map to the Suppliers table records, to know the shadow of a table of Constraint you can perform SQ L built-in sp_helpconstraint, in QA execution
Sp_helpconstraint Products

Total 2 page: previous 1 [2] Next page