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

Source: Internet
Author: User
Tags joins

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.