Home Forums SQL Server 2008 T-SQL (SS2K8) Exclude Records using join RE: Exclude Records using join

  • November 7, 2012 at 7:00 am

    #1556760

    Sony Francis @EY (11/7/2012)

    Try this

    SELECT *

    FROM TableA a

    LEFT OUTER JOIN TableB b ON a.LoanID = b.LoanID

    WHERE b.ColumnA <> 'Y'

    When we use INNER JOIN it will consider only commom records. But the user wants to consider all records in first table

    If you reference an outer-joined table column in the WHERE clause, you turn that join into an INNER join - unless the reference is to a null value [WHERE b.ColumnA IS NULL].

    If you want to filter an outer-joined table, put the filter into the join condition.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden