SQL Server Performance

Self Join

Discussion in 'General DBA Questions' started by harsh_sr, Oct 20, 2004.

  1. harsh_sr New Member

    I've a table TestInvoice

    UID Buyer Vendor Agent
    ----------- ---------- ---------- ----------
    1 B1 V1 A1
    2 B1 V1 NULL
    3 B1 NULL A1
    4 B1 V2 A1
    5 B1 V1 A2
    6 B1 V1 A1
    7 B2 V1 A1

    I want to search for Buyer (Buyer= B1 this case) from table; in such a manner that it return only those rows where Vendors are either Same or Null and Agent is either same or Null

    UID Buyer Vendor Agent
    ----------- ---------- ---------- ----------
    1 B1 V1 A1
    2 B1 V1 NULL
    3 B1 NULL A1
    6 B1 V1 A1

    Please help.
    Thanks
    Harsh

    Harsh
  2. Adriaan New Member

    Not sure why you call this a self join: you get there with some very basic criteria ...

    SELECT T.* FROM TestInvoice AS T
    WHERE (T.Buyer = T.Vendor OR T.Vendor IS NULL)
    OR (T.Buyer = T.Agent OR T.Agent IS NULL)
  3. harsh_sr New Member

    There are multiple records for a Buyer in said table with different Agents and Vendors.
    I want to select records
    - for a particular Buyer (B1 in this case)
    - where the Vendor is either Same for all such records or is NULL
    - where the Agent is either Same for all such records or is NULL

    Hence my result should look like
    B1 V1 A1
    B1 NULL A1
    B1 V1 NULL

    Sorry if the question was confusing.

    Thanks in advance
    Harsh

    Harsh
  4. FrankKalis Moderator

    Your requirement doesn't make sense to me.
    While it is easy to identify rows that are NULL, how do you define
    "where the Vendor is either Same for all such records ..."
    "where the Agent is either Same for all such records ..."
    ?
    Is it the vendor (agent) that appears most often or the MIN() or what?
    Or am I missing something?



    -----------------------
    --Frank
    http://www.insidesql.de
    -----------------------
  5. Adriaan New Member

    Okay, the key phrase is "the same for all such records". Since your requirement is that rows with other values do not exist, why not use NOT EXISTS in the WHERE statement:

    SELECT T.* FROM TestInvoice AS T
    WHERE NOT EXISTS
    (SELECT T2.* FROM TestInvoice AS T2
    WHERE (T2.Buyer = T.Buyer AND T2.Vendor <> T.Vendor)
    OR (T2.Buyer = T.Buyer AND T2.Agent <> T.Agent))

    You don't need to handle NULLs especially, because (T2.Vendor <> T.Vendor) is false when either or both fields are NULL. If we were using EXISTS, that would mean that the row would not be returned, but since we're using NOT EXISTS there's no problem.

    I can't think of any JOINs that would return any rows in this situation, not even on derived tables.
  6. FrankKalis Moderator

    Uhoh, NULL does not compare to anything, including itself. So I do think you explicitely need to handle NULLs, or?

    -----------------------
    --Frank
    http://www.insidesql.de
    -----------------------
  7. FrankKalis Moderator

    I'm out of my head today...
    Sorry.


    set nocount on
    use tempdb
    create table #t
    (
    uid int identity
    , buyer char(2) null
    , vendor char(2)null
    , agent char(2)null
    )
    insert into #t (buyer, vendor, agent) values('B1', 'V1', 'A1')
    insert into #t (buyer, vendor, agent) values('B1', 'V1', null)
    insert into #t (buyer, vendor, agent) values('B1', null, 'A1')
    insert into #t (buyer, vendor, agent) values('B1', 'V2', 'A1')
    insert into #t (buyer, vendor, agent) values('B1', 'V1', 'A2')
    insert into #t (buyer, vendor, agent) values('B1', 'V1', 'A1')
    insert into #t (buyer, vendor, agent) values('B2', 'V1', 'A1')

    SELECT T.* FROM #t AS T
    WHERE NOT EXISTS
    (SELECT T2.* FROM #t AS T2
    WHERE (T2.Buyer = T.Buyer AND T2.Vendor <> T.Vendor)
    OR (T2.Buyer = T.Buyer AND T2.Agent <> T.Agent))


    drop table #t
    set nocount off

    uid buyer vendor agent
    ----------- ----- ------ -----
    7 B2 V1 A1
    Forgot to post this.


    -----------------------
    --Frank
    http://www.insidesql.de
    -----------------------
  8. FrankKalis Moderator

    So, fiddling around this, what about this?`


    set nocount on
    use tempdb
    create table #t
    (
    uid int identity
    , buyer varchar(2) null
    , vendor varchar(2)null
    , agent varchar(2) null
    )
    insert into #t (buyer, vendor, agent) values('B1', 'V1', 'A1')
    insert into #t (buyer, vendor, agent) values('B1', 'V1', null)
    insert into #t (buyer, vendor, agent) values('B1', null, 'A1')
    insert into #t (buyer, vendor, agent) values('B1', 'V2', 'A1')
    insert into #t (buyer, vendor, agent) values('B1', 'V1', 'A2')
    insert into #t (buyer, vendor, agent) values('B1', 'V1', 'A1')
    insert into #t (buyer, vendor, agent) values('B2', 'V1', 'A1')

    SELECT buyer, vendor, agent FROM #t AS T
    WHERE NOT EXISTS
    (SELECT T2.* FROM #t AS T2
    WHERE
    (T2.Buyer = T.Buyer AND (T.Vendor <> T2.Vendor OR T2.Vendor IS NULL))
    AND
    (T2.Buyer = T.Buyer AND (T.Agent <> T2.Agent OR T2.Agent IS NULL)))
    AND T.Buyer = 'B1'
    GROUP BY buyer, vendor, agent

    drop table #t
    set nocount off

    buyer vendor agent
    ----- ------ -----
    B1 NULL A1
    B1 V1 NULL
    B1 V1 A1



    -----------------------
    --Frank
    http://www.insidesql.de
    -----------------------
  9. Adriaan New Member

    Frank,

    If either T2.Vendor or T.Vendor contains NULL, then the expression (T2.Vendor <> T.Vendor) evaluates to false, like this:

    DECLARE @t1 VARCHAR(1), @t2 VARCHAR(1)
    SET @t1 = '1'
    SELECT @t1, @t2, CASE WHEN @t1 <> @t2 THEN 'True' ELSE 'False' END
    ---- ---- -----
    1 NULL False

    The subquery does not return a row where either T2.Vendor or T2.Agent is NULL, provided that neither field contains a non-NULL value that is different.

    NOT EXISTS means that the subquery must not return any rows at all, and these criteria are satisfied if the two fields are either identical or NULL.

    Sorry about all the double negative logic, but that's just how it works.

    Adriaan
  10. FrankKalis Moderator

    I think our posts have overlapped [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />-----------------------<br />--Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />-----------------------<br />
  11. Adriaan New Member

    Not only did our posts overlap, our queries were equivalent as well - at least your second one.[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]
  12. Adriaan New Member

    Frank,

    Oops, no they are not equivalent queries.

    Test by inserting an additional row with NULL on both Vendor and Agent:

    insert into #t (buyer, vendor, agent) values('B1', null, null)

    Now your query returns no rows, and mine still does.
  13. FrankKalis Moderator

    Well, say I did try to answer the question with the given informations. Your last insert with two NULLs wasn't mentioned in the original question, so I didn't bother to take this into account.
    However, even if your query returns a row, while mine doesn't, your statement still fails to return the desired result set.


    -----------------------
    --Frank
    http://www.insidesql.de
    -----------------------
  14. Adriaan New Member

    Frank,<br /><br />Not entirely sure what you mean, unless you refer to the Buyer = 'B1' criteria that you added. As per Harsh's original question, NULLs could be ignored for the comparison, and your query does not ignore them.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  15. FrankKalis Moderator

    Correct, since Harsh's desired resultset should be<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />Hence my result should look like<br />B1 V1 A1<br />B1 NULL A1<br />B1 V1 NULL <br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />-----------------------<br />--Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />-----------------------<br />
  16. Adriaan New Member

    Yes, but B1 NULL NULL should also be ignored, and your query does not.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />][<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />(Let's not turn this into a smiley competition.)
  17. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />Yes, but B1 NULL NULL should also be ignored, and your query does not.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />][<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />So, where do you see<br />B1 NULL NULL <br />in here<br /><br />UID Buyer Vendor Agent <br />----------- ---------- ---------- ---------- <br />1 B1 V1 A1 <br />2 B1 V1 NULL<br />3 B1 NULL A1 <br />4 B1 V2 A1 <br />5 B1 V1 A2 <br />6 B1 V1 A1 <br />7 B2 V1 A1 <br /><br />[<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />(Let's not turn this into a smiley competition.)<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Why not <img src='http://www.insidesql.de/images/cool026.gif' border='0' /><br /><br />-----------------------<br />--Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />-----------------------<br />
  18. Adriaan New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />Yes, but B1 NULL NULL should also be ignored, and your query does not.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />][<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />So, where do you see<br /><br />B1 NULL NULL in here<br /><br />UID Buyer Vendor Agent <br />----------- ---------- ---------- ---------- <br />1 B1 V1 A1 <br />2 B1 V1 NULL<br />3 B1 NULL A1 <br />4 B1 V2 A1 <br />5 B1 V1 A2 <br />6 B1 V1 A1 <br />7 B2 V1 A1 <br /><br />[<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />You're right, B1 NULL NULL was not in the sample data. I was anticipating an obvious complication that would produce unexpected results.<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />(Let's not turn this into a smiley competition.)<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Why not <img src='http://www.insidesql.de/images/cool026.gif' border='0' /><br /><br />-----------------------<br />--Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />-----------------------<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />[<img src='/community/emoticons/emotion-11.gif' alt='8)' />]
  19. FrankKalis Moderator

    Who knows anyway? [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br />In the end, I really like those discussions with you [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Honestly!<br /><br /><br />-----------------------<br />--Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />-----------------------<br />
  20. Adriaan New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Who knows anyway? [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />The milkman?<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">In the end, I really like those discussions with you [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Honestly!<br /><br /><br />-----------------------<br />--Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />-----------------------<br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />We do seem to argue a lot. Usually a sign of - no, let's not go there.
  21. FrankKalis Moderator

    Hey, I'm already married [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br />Time for lunch break here.<br /><br />-----------------------<br />--Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />-----------------------<br />
  22. Adriaan New Member

    I would lie if I said that I wasn't. What about if I don't say that I'm not?

Share This Page