none
full outer join acting like right join

    Question

  • Can anyone tell me why the full outer join in the SQL below is acting like right join?

    I have a solution to my problem by creating 2 # tables and doing the full outer join on them, so there is no rush to solve this.  I just do not see why it would not work.

    SELECT distinct
    prod.Name
    , prod.CreationDate
    , prod.ModifiedDate
    , prod.Path
    , EIS.Name
    , EIS.CreationDate
    , EIS.ModifiedDate
    , EIS.Path
    FROM ReportData prod
    full outer join
    ( select distinct
    EIS.Name
    , EIS.CreationDate
    , EIS.ModifiedDate
    , EIS.Path
    from ReportData EIS
    where upper(EIS.Path) like '%EIS CENTRALIZED REPORTING%'
    and upper(EIS.Path) not like '%M3UPGRADE%'
    and EIS.ModifiedDate
    ( select max(EIS2.ModifiedDate )
    FROM ReportData EIS2
    where EIS.Name = EIS2.Name
    and upper(EIS2.Path) like '%EIS CENTRALIZED REPORTING%'
    and upper(EIS2.Path) not like '%M3UPGRADE%'
    )
    ) EIS
    on prod.Name = EIS.Name

    where upper(isnull(prod.Path,'')) not like '%EIS CENTRALIZED REPORTING%'
    and upper(isnull(prod.Path,'')) not like '%M3UPGRADE%'
    and isnull(prod.ModifiedDate,'1/1/2050')
    ( select max( isnull(prod2.ModifiedDate,'1/1/2050') )
    FROM ReportData prod2
    where isnull(prod.Name,'') = isnull(prod2.Name,'')
    and upper(isnull(prod2.Path,'')) not like '%EIS CENTRALIZED REPORTING%'
    and upper(isnull(prod2.Path,'')) not like '%M3UPGRADE%'
    )

    Wednesday, April 17, 2019 9:01 PM

Answers

  • When doing a full outer join you should always perform table-specific filtering in derived tables before the doing the join. The WHERE clause for the main query should only have filters that are related to both tables. Else you run into surprises like this.

    Here is a rewritten version of your query:

    SELECT distinct
    prod.Name
    , prod.CreationDate
    , prod.ModifiedDate
    , prod.Path
    , EIS.Name
    , EIS.CreationDate
    , EIS.ModifiedDate
    , EIS.Path
    FROM (SELECT prod.Name, Prod.CreationDate, Prod.ModifiedDate, prod.Path
          FROM   ReportData prod
          where upper(prod.Path) not like '%EIS CENTRALIZED REPORTING%'
            and upper(prod.Path) not like '%M3UPGRADE%'
            and prod.ModifiedDate = (Select max(prod2.ModifiedDate)
                                     FROM ReportData prod2                                                           where prod.Name = prod2.Name
                                       and upper(prod2.Path,'') not like '%EIS CENTRALIZED REPORTING%'
                                       and upper(prod2.Path,'') not like '%M3UPGRADE%')
          ) prod full outer join  ( select distinct EIS.Name , EIS.CreationDate, EIS.ModifiedDate, EIS.Path
                      from ReportData EIS
                      where upper(EIS.Path)like '%EIS CENTRALIZED REPORTING%'
                      and upper(EIS.Path) not like '%M3UPGRADE%'
                      and EIS.ModifiedDate= ( selectmax(EIS2.ModifiedDate )
                                             FROM ReportDataEIS2
                                             where EIS.Name= EIS2.Name
                                               and upper(EIS2.Path)like '%EIS CENTRALIZED REPORTING%'
                                               and upper(EIS2.Path)not like '%M3UPGRADE%')                   ) EIS on prod.Name = EIS.Name


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Tom Stone 98 Wednesday, April 17, 2019 10:01 PM
    Wednesday, April 17, 2019 9:52 PM

All replies

  • When doing a full outer join you should always perform table-specific filtering in derived tables before the doing the join. The WHERE clause for the main query should only have filters that are related to both tables. Else you run into surprises like this.

    Here is a rewritten version of your query:

    SELECT distinct
    prod.Name
    , prod.CreationDate
    , prod.ModifiedDate
    , prod.Path
    , EIS.Name
    , EIS.CreationDate
    , EIS.ModifiedDate
    , EIS.Path
    FROM (SELECT prod.Name, Prod.CreationDate, Prod.ModifiedDate, prod.Path
          FROM   ReportData prod
          where upper(prod.Path) not like '%EIS CENTRALIZED REPORTING%'
            and upper(prod.Path) not like '%M3UPGRADE%'
            and prod.ModifiedDate = (Select max(prod2.ModifiedDate)
                                     FROM ReportData prod2                                                           where prod.Name = prod2.Name
                                       and upper(prod2.Path,'') not like '%EIS CENTRALIZED REPORTING%'
                                       and upper(prod2.Path,'') not like '%M3UPGRADE%')
          ) prod full outer join  ( select distinct EIS.Name , EIS.CreationDate, EIS.ModifiedDate, EIS.Path
                      from ReportData EIS
                      where upper(EIS.Path)like '%EIS CENTRALIZED REPORTING%'
                      and upper(EIS.Path) not like '%M3UPGRADE%'
                      and EIS.ModifiedDate= ( selectmax(EIS2.ModifiedDate )
                                             FROM ReportDataEIS2
                                             where EIS.Name= EIS2.Name
                                               and upper(EIS2.Path)like '%EIS CENTRALIZED REPORTING%'
                                               and upper(EIS2.Path)not like '%M3UPGRADE%')                   ) EIS on prod.Name = EIS.Name


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Tom Stone 98 Wednesday, April 17, 2019 10:01 PM
    Wednesday, April 17, 2019 9:52 PM
  • Thank you Erland.

    That sort of makes sense. 

    I was thinking that the use of ISNULL on the prod table columns in the WHERE clause would do the same.

    Wednesday, April 17, 2019 10:03 PM
  • I was thinking that the use of ISNULL on the prod table columns in the WHERE clause would do the same.

    Yeah, I don't really see the flaw in your original query, but I have been through that before.
    FULL JOIN is definitely a level higher than a regular left join!


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, April 18, 2019 9:53 AM