SQL Server Performance

Left Outer Joins and querying null values.

Discussion in 'SQL Server 2005 General Developer Questions' started by kursal, Jul 18, 2008.

  1. kursal New Member

    Hi all,
    Before I start I'd like to say thank you to anyone who can help me with this as I'm pulling my hair out at the moment.
    The Code
    The below query is meant to tell if a user has baught from us over a certain 3 day period. It also checks to see if that user has an entry in a Subscriptions table and only returns the entry if they are not active (if the Status field is not set to ACTIVE)
    -----
    SELECT DISTINCT TOP (100) PERCENT derivedtbl_1.cardno, derivedtbl_1.Value, subTab.Status
    FROM (SELECT DISTINCT cardno, Value, CustomerID
    FROM dbo.Transactions
    WHERE (Transtype = N'R') AND (DT >= CONVERT(DATETIME, '2008-06-25 00:00:00', 102)) AND (DT < CONVERT(DATETIME, '2008-06-26 00:00:00',
    102))) AS derivedtbl_1 INNER JOIN
    (SELECT DISTINCT cardno, Value
    FROM dbo.Transactions AS Transactions_2
    WHERE (Transtype = N'R') AND (DT >= CONVERT(DATETIME, '2008-06-24 00:00:00', 102)) AND (DT < CONVERT(DATETIME, '2008-06-25 00:00:00',
    102))) AS derivedtbl_2 ON derivedtbl_1.cardno = derivedtbl_2.cardno INNER JOIN
    (SELECT DISTINCT cardno, Value
    FROM dbo.Transactions AS Transactions_1
    WHERE (Transtype = N'R') AND (DT >= CONVERT(DATETIME, '2008-06-23 00:00:00', 102)) AND (DT < CONVERT(DATETIME, '2008-06-24 00:00:00',
    102))) AS derivedtbl_3 ON derivedtbl_2.cardno = derivedtbl_3.cardno LEFT OUTER JOIN
    (SELECT Status, CustomerID
    FROM dbo.Subscriptions) AS subTab ON derivedtbl_1.CustomerID = subTab.CustomerID
    WHERE (subTab.Status <> N'ACTIVE')
    ORDER BY derivedtbl_1.cardno
    -----
    The Problem
    This is all working fine if the user has at some point subscribed. Not all users have and I also need to pull out those users who do not exist in the subscriptions table. Does anyone have any suggestions or solutions on how this could be achieved?
    As always, all help is greatly appreciated.
    All the best,
    K
  2. martins New Member

    Hi,
    The problem you have is that your left outer join produces some null values...and null values are not comparable to anything and you therefore have to use the isnull() function. All the derived tables are also not necessary, and I would leave out the "N" prefix unless you are using unicode.
    See if this works...SELECT DISTINCT tr
    .cardno,
    tr.Value, subTab.Status
    FROM
    dbo.Transactions trLEFT
    OUTER JOIN dbo.Subscriptions subTab ON tr.CustomerID = subTab.CustomerIDWHERE tr.Transtype = 'R'
    and isnull(subTab.Status,'NONE') <> 'ACTIVE'ORDER
    BY tr.cardno
  3. martins New Member

    My apologies...left some details out. Here's the query again:SELECT DISTINCT tr
    .cardno,
    tr.Value,
    subTab.Status FROM
    dbo.Transactions trLEFT
    OUTER JOIN dbo.Subscriptions subTab ON tr.CustomerID = subTab.CustomerIDWHERE tr.Transtype = 'R'
    and DT between CONVERT(DATETIME, '2008-06-23 00:00:00', 102) and CONVERT(DATETIME, '2008-06-26 00:00:00', 102)
    and isnull(subTab.Status,'NONE') <> 'ACTIVE'ORDER
    BY tr.cardno
  4. kursal New Member

    Thanks for that, great info on the isnull command. I did try that but didn't get it working but I can see where I went wrong.
    One thing, my current statement only returns a value if there are three seperate records in the transactions table but this one won't do that will it? I know I can get the isnull working with my current statement but it would be handy to know if there is a better way of doing it.
    Once again, thanks for all your help [:D]
  5. martins New Member

    Thats right, the query I posted will return records if there were a transaction in any of the 3 days.
    Null values are always tricky. Remember that null is not a value and therefore not equal to anything. The safest way to check for a null value is with the isnull function, so be sure to use that whenever you might have nulls in a column and you want to use it in a where clause or join.
    Hope this helps [:)]
  6. Adriaan New Member

    [quote user="martins"]Null values are always tricky. Remember that null is not a value and therefore not equal to anything. The safest way to check for a null value is with the isnull function, so be sure to use that whenever you might have nulls in a column and you want to use it in a where clause or join.[/quote]Do not use call special functions in T-SQL when there is a standard SQL equivalent - always use the IS NULL and IS NOT NULL syntax.
  7. kursal New Member

    [quote user="Adriaan"]
    [quote user="martins"]Null values are always tricky. Remember that null is not a value and therefore not equal to anything. The safest way to check for a null value is with the isnull function, so be sure to use that whenever you might have nulls in a column and you want to use it in a where clause or join.[/quote]Do not use call special functions in T-SQL when there is a standard SQL equivalent - always use the IS NULL and IS NOT NULL syntax.
    [/quote]
    Ah, thanks for that, very useful.
    -K
  8. moh_hassan20 New Member

    create view that include all your inner join , re-use the view in your outer join

    Or
    if you use sql 2005 , you can benefit of CTE for inner join , re-use CTE for outer join

Share This Page