none
Return NULL values with INNER JOIN for INDEXED VIEWS

    Question

  • Hi there,

    I need to be able to return NULL values from an INDEXED VIEW and I learned that LEFT (or OUTER) JOIN is not allowed with INDEXED VIEW so I was wondering how I should go about this.

    I have three tables:

    State (state domain table)

    StateID | StateName

    Country (country domain table)

    CountryID | CountryName

    User

    UserID | UserName | StateID (nullable) | CountryID (nullable)

    Now, I would like to write a query for the view that returns ALL users with their corresponding state name and country name.  Any users with null values in StateID or CountryID must be returned as well.

    SELECT U.UserID, U.UserName, U.StateID, S.StateName, U.COuntryID, C.COuntryName

    FROM User U

    INNER JOIN State S ON S.StateID = U.UserID OR (U.StateID IS NULL)

    INNER JOIN Country C ON C.COuntryID = U.CountryID OR (U.COuntryID IS NULL)

    However, this query does not seem to work or at least return different results than using LEFT JOIN.

    SELECT U.UserID, U.UserName, U.StateID, S.StateName, U.COuntryID, C.COuntryName

    FROM User U

    LEFT JOIN State S ON S.StateID = U.UserID

    LEFT JOIN Country C ON C.COuntryID = U.CountryID

    ANy suggestions?  Thanks.


    SK


    • Edited by ace_hk Tuesday, May 14, 2013 11:47 PM
    Tuesday, May 14, 2013 11:23 PM

Answers

  • I'm not convinced that you really want an indexed view for this, but if you do, one way would be to add a row to both State and Country tables with an ID value that is not otherwise used (like -1) that you will use to match NULL ID's, and a CountryName or StateName of NULL.  Then change your view to

    Create View dbo.UserView With SchemaBinding As
    SELECT U.UserID, U.UserName, U.StateID, S.StateName, U.COuntryID, C.COuntryName
    FROM dbo.[User] U
    INNER JOIN dbo.State S ON S.StateID = IsNull(U.StateID, -1)
    INNER JOIN dbo.Country C ON C.CountryID = IsNull(U.CountryID, -1)

    That will match all the user rows with NULLs in the StateID or CountryID to the special rows in the other tables.

    Tom

    Wednesday, May 15, 2013 3:53 AM

All replies

  • In this scenario LEFT works good and returns the users list irrespective of stateid/country mappings.

    A view definition   can contain the LEFT JOIN and view can also be part of left join,

    create table State (StateID int, StateName varchar(50))
    create table Country (CountryID int, CountryName varchar(50))
    create table User_table (UserID int, UserName varchar(10), StateID int NULL, CountryID int NULL)
    go
    create view test_view 
    as 
    SELECT U.UserID, U.UserName, U.StateID, S.StateName, U.COuntryID, C.COuntryName
    
    FROM User_table U
    
    LEFT JOIN State S ON S.StateID = U.UserID
    
    LEFT JOIN Country C ON C.COuntryID = U.CountryID


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, May 14, 2013 11:34 PM
  • we can use LEFT JOINs against a VIEW,

    select * from Country C 
    left join test_view v 
    On C.CountryID=V.CountryID


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, May 14, 2013 11:35 PM
  • Oh.... sorry.  My bad.

    THe problem is that I need to index this view and OUTER JOIN not allowed in indexed views and that's why I am trying to make it work with INNER JOIN.

    Thoughts?  Thanks.


    SK

    Tuesday, May 14, 2013 11:46 PM
  • Oh.... sorry.  My bad.

    THe problem is that I need to index this view and OUTER JOIN not allowed in indexed views and that's why I am trying to make it work with INNER JOIN.

    Thoughts?  Thanks.


    SK

    fine, in that cant the tables be queried directly with LEFT JOIN, instead of VIEW?

    The reason for this restriction ,http://blog.sqlauthority.com/2010/06/29/sql-server-outer-join-not-allowed-in-indexed-views/


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, May 15, 2013 12:34 AM
  • I have a reason to use indexed views instead of putting them all together in a sproc.  The query will get too complicated and big (hard to maintain).

    So simply put, is there anyway to get the same result using INNER JOIN for LEFT JOIN?


    SK

    Wednesday, May 15, 2013 1:23 AM
  • I'm not convinced that you really want an indexed view for this, but if you do, one way would be to add a row to both State and Country tables with an ID value that is not otherwise used (like -1) that you will use to match NULL ID's, and a CountryName or StateName of NULL.  Then change your view to

    Create View dbo.UserView With SchemaBinding As
    SELECT U.UserID, U.UserName, U.StateID, S.StateName, U.COuntryID, C.COuntryName
    FROM dbo.[User] U
    INNER JOIN dbo.State S ON S.StateID = IsNull(U.StateID, -1)
    INNER JOIN dbo.Country C ON C.CountryID = IsNull(U.CountryID, -1)

    That will match all the user rows with NULLs in the StateID or CountryID to the special rows in the other tables.

    Tom

    Wednesday, May 15, 2013 3:53 AM
  • THe problem is that I need to index this view

    Why?

    I wouldn't expect any performance problems with a regular view for this query, as long as the proper indexes and foreign key constraints are in place.

    In fact, if you query the view and don't need columns from all tables, then the "non-indexed" view might even outperform the indexed view.


    Gert-Jan

    Wednesday, May 15, 2013 1:48 PM
  • Thanks for the suggestions.

    I will indeed try this but I made some other changes to get it work.

    SK


    SK

    Wednesday, May 15, 2013 7:43 PM