sql server 2012 - Understanding Inner Joins

Posted on 2012-09-11
Last Modified: 2012-09-14
Hi experts i am reading about:Understanding Inner Joins
but i do not understand:
Why filter in ON clause?
Logical separation between filtering for purposes of join and filtering results in WHERE

can explain me with an example, like hands on lab
Question by:enrique_aeo
    LVL 10

    Expert Comment

    by:Umar Topia
    When you use INNER JOIN, you have to provide the ON clause for your query, which is equivalent to provide WHERE clause.


    SELECT Column1,Column2 FROM Tbl1 INNER JOIN Tbl2
    ON Tbl1.MyId = Tbl2.MyId

    It is equivalent to

    SELECT Column1,Column2 FROM Tbl1, Tbl2
    WHERE Tbl1.MyId = Tbl2.MyId

    Author Comment

    i undertstand, but what the mean?
    Logical separation between filtering for purposes of join and filtering results in WHERE
    LVL 13

    Accepted Solution

    I think (and apologize if not completely accurate) it means that in order to keep your "logic" cleaner, it's advisable to separate your filter (WHERE clause) from your join (INNER/LEFT/RIGHT).

    Since in your join you could something like:

    SELECT a.col1, b.col1
    a.col2=b.col2 AND a.col3<=100

    In that case you're mixing up a "filter" with your join.

    That separation would mean that the same statement should be rewritten as:

    SELECT a.col1, b.col1
    WHERE a.col3<=100

    In that case you're separating the record filter and the joining of the tables.
    Hope it helps.
    LVL 68

    Assisted Solution

    LIONKING is exactly right.

    Note that this allows you to copy the JOIN logic for table a to table b and "re-use" it, since the specific condition(s) for that query ("col3 <= 100") are not in the JOIN itself.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now