Do you really think of SQL? The difference between join and on for SQL

After writing for so many years of SQL, actually found that they write is wrong, is really ashamed unceasingly. Fortunately, nothing wrong.

So, decided to learn from the mistakes, correct their own wrong understanding of SQL.

We have a sql:

SELECT sys. NETBIOS_NAME0 as [computer Name], Sf.filenamefrom dbo.v_r_system as sys INNER JOIN Dbo.v_fullcollectionmembershi P as FCM on FCM. ResourceID = sys. ResourceID left joins Dbo.v_gs_softwarefile as SF on sf.resourceid = Sys.resourceidwhere FCM. CollectionID = ' SMS00004 ' and sf.filename = ' Outlook.exe '

The goal is that in all v_fullcollectionmembership, the search for CollectionID is SMS00004 's computer.

And see if these computers have outlook.exe this software installed.

The data we expect to see is this:

Computer Name FileName COMP1 OUTLOOK. EXE COMP2 COMP3 OUTLOOK. EXE COMP4 COMP5 OUTLOOK. Exe

The resulting data is this:

Computer Name FileNameCOMP1 OUTLOOK. EXECOMP3 OUTLOOK. EXECOMP5 OUTLOOK. Exe

Why did you become a inner join??

explanation :

When you use where, it means you tell SQL to return records that satisfy the Where condition. Whether you're using a inner join or a outer join. Therefore, although you choose the left join, you will actually be rewritten as a inner join by the execution plan.

In other words, the Where condition is the condition that guarantees that the final result set must be met.

So we get the results we don't want to see above.

So what should I do? Rewrite it:

select sys. Netbios_name0 as [computer name],       sf.filenamefrom  dbo.v_r_system as sys     inner join dbo.v_ FULLCOLLECTIONMEMBERSHIP&NBSP;AS&NBSP;FCM&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;ON&NBSP;FCM. Resourceid = sys. resourceid     left join dbo.v_gs_softwarefile as sf          ON sf.resourceID = sys.resourceID          AND sf.fileName =  ' Outlook.exe '  --<check it &NBSP;OUT>WHERE&NBSP;FCM. collectionid =  ' SMS00004 ' 

Is that not all the case, can be used on to solve it? No, it's not.

Look at the following example:

The execution condition of on is equivalent to the IF

When two record is compared, the condition is true, the data is returned, and the condition is false returns null

In the case of on, we can see that on is not filtering data at all.

When using outer JOIN (including left, right, full)

Will keep the data as low as possible, so there is still a problem with some tables with a particularly large amount of data.

So where is the where condition and on condition to choose??

When filtering the data, use where.

Because where is typically optimized by execution plan, the scope of the query is narrowed, making the result set small enough.

In the case of the need to retain comprehensive information, it is necessary to put a part of the conditions, write on the inside.

Summarize:

Where is used to narrow the result set, but inner join is forced

On is used to maintain full data, but all data is judged

Reference content:

http://www.tech-recipes.com/rx/47637/inner-and-left-outer-join-with-where-clause-vs-on-clause/

Http://myitforum.com/cs2/blogs/jnelson/archive/2007/11/16/108359.aspx

Do you really think of SQL? The difference between join and on for SQL