About Jion,inner join in SQL Server, left joins, left outer joins, right join,right outer join tips

Usually we do the association, generally are a table, not too concerned about such a complicated way of writing, then today we look at these writing

For these three kinds of things to say, let's talk about the main points: on the following conditions can be put a few? When is it combined with the Where condition?

You can first look at this post, the name of the post is:SQL Server left join in on how to add multiple query conditions??

Links: http://bbs.csdn.net/topics/270023422

Let's start our introduction.

First, the join is divided into three blocks:

1. Join, join inner (inline)

Our usual wording is:

<span style= "FONT-SIZE:14PX;" >select * from A JOIN B on A.id=b.id </span>

Write the join directly, this is actually the inner join,

On the back is what we're talking about, which can be followed by multiple conditions.

SELECT TOP * from  [db0208].[ DBO]. [Orders] o  JOIN [db0208].[ DBO]. [Baseorderstatus] B on  O.status=b.statuscode and b.statuscode=8 and  o.productid= ' 660016 '

The wording is correct, and of course you can put the conditions in where

The following wording:

SELECT TOP * from  [db0208].[ DBO]. [Orders] o   JOIN [db0208].[ DBO]. [Baseorderstatus] B on  o.status=b.statuscode   WHERE  o.productid= ' 660016 ' and  b.statuscode=8

Query Result:

2. Left JOIN, outer join

The left join is called the LEFT join, and the ieft outer join is called an outer connection, which is actually a

After on, if there is no where condition, on can be followed only by an association condition, the following conditions are added to where

Left join: Returns records that include all records in the left table and the equivalent of the junction fields in the right table

Look at the following wording:

SELECT TOP * from  [db0208].[ DBO]. [Orders] o left  JOIN [db0208].[ DBO]. [Baseorderstatus] B on  O.status=b.statuscode and b.statuscode=8 and  o.productid= ' 660016 '

This is a mistake, because the second condition on has no effect, the query result:

The correct wording:

  SELECT TOP * from  [db0208].[ DBO]. [Orders] o  JOIN [db0208].[ DBO]. [Baseorderstatus] B on  o.status=b.statuscode   WHERE b.statuscode=8 and o.productid= ' 660016 '

Of course, you can also write this:

  SELECT TOP * from  [db0208].[ DBO]. [Orders] o  JOIN [db0208].[ DBO]. [Baseorderstatus] B on  O.status=b.statuscode and <span style= "font-family:arial, Helvetica, Sans-serif;" >b.statuscode=8 and</span>  WHERE  o.productid= ' 660016 '

that is, there is a where condition control, and on can be followed by multiple conditions,the query results are the same as the first one.

3. Right Join,right outer JOIN

Right join is called to connect, the right outer join is called an outer join, in fact is a kind of,

The back on, can only be associated with an association condition, and the left join is the same.

Right join returns records that include all records in the right table and the junction fields in the left table

3 and 2 are similar, do not repeat it.

-------------------------------------

These are a small colleague asked me, has always wanted to tidy up, now write it out, let everyone reference, after the new people ask related questions, directly to his link is good, the thing lies in fine, study once, in the later repeated practice can master.

About Jion,inner join in SQL Server, left joins, left outer joins, right join,right outer join tips