Professional Web Applications Themes

what join to emulate Not In clause? - Microsoft SQL / MS SQL Server

beware, those replies were just too swift! your original query (though not working) is not equivalent with the outer join solutions. you did not specify if Item columns were actually keys (unique)! always post DDL (including keys) and sample data + sample output if you want correct responses. if Item is not key you might get duplicates in the join solution where the NOT IN will not produce these. HTH, </wqw> "Rich" <com> wrote in message news:105d01c360e6$87221c10$gbl...  > plus  > suggestions  > (If [/ref]...

  1. #1

    Default Re: what join to emulate Not In clause?

    beware,

    those replies were just too swift! your original query (though not working)
    is not equivalent with the outer join solutions. you did not specify if Item
    columns were actually keys (unique)! always post DDL (including keys) and
    sample data + sample output if you want correct responses.

    if Item is not key you might get duplicates in the join solution where the
    NOT IN will not produce these.

    HTH,
    </wqw>


    "Rich" <com> wrote in message
    news:105d01c360e6$87221c10$gbl... 
    > plus 
    > suggestions 
    > (If [/ref]


    Vlad Guest

  2. #2

    Default Re: what join to emulate Not In clause?

    Vlad,

    A warning on the use of OUTER JOIN for mismatch queries is appropriate. But
    the key, as it were, is in the nullability of the column specified in the
    WHERE clause. For example:

    CREATE TABLE countries (
    country_cd CHAR(2) NOT NULL PRIMARY KEY,
    country_name VARCHAR(50) NOT NULL UNIQUE,
    population INT NOT NULL,
    king VARCHAR(50) NULL);

    CREATE TABLE men (
    full_name VARCHAR(50) NOT NULL PRIMARY KEY,
    baldness_index DECIMAL (2,2) NOT NULL,
    country_cd CHAR(2) NOT NULL REFERENCES countries(country_cd));

    INSERT INTO countries VALUES ('US','United States',280000000,NULL);
    INSERT INTO men VALUES ('Rich Dillon',0.60,'US');

    SELECT
    full_name
    FROM
    men AS m LEFT JOIN
    countries AS c ON m.country_cd=c.country_cd
    WHERE
    king IS NULL;

    Dispite the fact that a match exists, I would be shown here as a man without
    a country :) On the other hand, "WHERE c.population IS NULL" would have
    worked correctly, because <population> cannot otherwise be NULL in the
    result.

    As for duplication, I might indeed get duplicates by writing "SELECT
    baldness_index FROM.." as it isn't a key. But only in the same way that I
    would with NOT IN or NOT EXISTS. The solution to that duplication in all
    cases is the DISTINCT keyword.

    Rich


    "Vlad Vissoultchev" <myrealbox.com> wrote in message
    news:#phx.gbl... 
    working) 
    Item 
    > > plus 
    > > suggestions 
    > > (If [/ref]
    >
    >[/ref]


    Rich Guest

  3. #3

    Default Re: what join to emulate Not In clause?

    :-))))

    my warning concerned duplicates introduced from tbl2 (as per original post)

    suppose tbl1.Item is key for tbl1 but tbl2.Item is FK from tbl2 to tbl1.
    joining both tables on tbl1.Item = tbl2.Item might multiply entries in tbl1
    if there are rows with many-to-one matches. a straight NOT IN will filter
    (as seen in execution plan) these gracefully.

    my idea is that the equivalent of NOT IN is more like tbl1 JOIN (SELECT
    DISTINCT Item FROM tbl2)

    cheers,
    </wqw>

    "Rich Dillon" <com> wrote in message
    news:phx.gbl... 
    But 
    without 
    > working) 
    > Item [/ref]
    and [/ref]
    the 
    > >
    > >[/ref]
    >
    >[/ref]


    Vlad Guest

  4. #4

    Default Re: what join to emulate Not In clause?

    Ah. I think this is a good example of how a question can be murky without
    DDL (as you've suggested also). I still see an issue here. But if I've
    misread you again, I apologize and will say no more.

    CREATE TABLE tbl1 (
    Item CHAR(1) NOT NULL PRIMARY KEY);

    CREATE TABLE tbl2 (
    SometingElse INT NOT NULL PRIMARY KEY,
    Item CHAR(1) NOT NULL REFERENCES tbl1(Item));

    INSERT INTO tbl1 VALUES ('A');
    INSERT INTO tbl1 VALUES ('B');
    INSERT INTO tbl2 VALUES (1,'A');
    INSERT INTO tbl2 VALUES (2,'A');

    Considering the case of IN vs JOIN, duplication *is* an issue to be aware
    of.

    SELECT Item
    FROM tbl1
    WHERE Item IN (SELECT Item FROM tbl2);

    SELECT tbl1.Item
    FROM tbl1 JOIN tbl2 ON tbl1.Item=tbl2.Item;

    SELECT DISTINCT tbl1.Item
    FROM tbl1 JOIN tbl2 ON tbl1.Item=tbl2.Item;

    The result in the first case will be ('A'), in the second ('A','A'), in the
    third ('A'). The second and third are equivalent so long as no columns from
    tbl2 appear in the SELECT list. But the second does indeed produce
    duplicates. Your example of "tbl1 JOIN (SELECT DISTINCT Item FROM tbl2)" is
    actually the equivalent of IN.

    Considering the case of "NOT IN" vs "LEFT JOIN ... WHERE tbl2.Item IS NULL".
    The negation changes everything.

    SELECT Item
    FROM tbl1
    WHERE Item NOT IN (SELECT Item FROM tbl2);

    SELECT tbl1.Item
    FROM tbl1 LEFT JOIN tbl2 ON tbl1.Item=tbl2.Item
    WHERE tbl2.Item IS NULL;

    The result in the first case will be ('B'), in the second ('B'). This time,
    including columns from tbl2 in the SELECT list changes nothing. Since
    <tbl2.Item> is the column on which we're joining and can be NULL in the
    result of the JOIN only in the case of a mismatch, and since, where there
    isn't a match, multiplication of rows from the JOIN can't happen, these two
    queries are equivalent.

    best regards,
    Rich


    "Vlad Vissoultchev" <myrealbox.com> wrote in message
    news:phx.gbl... 
    post) 
    tbl1 
    > But [/ref]
    the 
    > without [/ref]
    I [/ref]
    all 
    > > working) [/ref][/ref]
    if [/ref]
    > and [/ref]
    > the 
    > >
    > >[/ref]
    >
    >[/ref]


    Rich Guest

  5. #5

    Default Re: what join to emulate Not In clause?

    ok, right, my mistake then :-))

    so nothing to worry about if filtering WHERE tbl1.Item IS NULL

    cheers,
    </wqw>

    "Rich Dillon" <com> wrote in message
    news:phx.gbl... 
    the 
    from 
    is 
    NULL". 
    time, 
    two 
    > post) 
    > tbl1 [/ref]
    filter [/ref][/ref]
    appropriate. [/ref]
    > the 
    > > without [/ref][/ref]
    have [/ref][/ref]
    that [/ref]
    > all [/ref]
    > if [/ref][/ref]
    keys) [/ref][/ref]
    where 
    > >
    > >[/ref]
    >
    >[/ref]


    Vlad Guest

  6. #6

    Default Re: what join to emulate Not In clause?

    Actually, I worded my question incorrectly. It turns out that the list
    of items from tbl1 is unique. ID in my example, is the item. So I
    ended up using Not In (Select Item...). For my purposes this worked out
    the best.

    Thank you all for your replies.

    Rich

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Rich Guest

Similar Threads

  1. Replies: 2
    Last Post: September 18th, 09:59 PM
  2. Emulate a explorer window
    By Angel in forum ASP.NET Building Controls
    Replies: 2
    Last Post: February 4th, 12:36 PM
  3. outer join, cross join, union? DDL/sample data incl.
    By Kevin in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 5th, 08:01 PM
  4. Rewrite Query using Inner Join, Left Join e.t.c
    By Vishal in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 23rd, 08:02 AM
  5. help on join in from clause
    By Jen in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 3rd, 06:16 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139