Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Unanswered: Left Anti Semi Join Syntax

    Anyone has an example or the exact syntax for a Left Anti Semi Join ? Could not find it in the doc or on Microsoft' KB.

    Thanks!

    BTW using Microsoft SQL Server 2000

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    look up left outer join in Books Online.
    Last edited by Paul Young; 03-28-03 at 17:05.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i've been working with sql (microsoft as well as other databases) for many years and have never heard of an anti join, a semi join, or an anti semi join

    what exactly does it do?


    rudy

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    look up left semi join in bol

    I am assuming that if we have left outer joins and left semi join, in TSQL, then a left anti semi join = left outer join.
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: Left Anti Semi Join

    Originally posted by Paul Young
    look up left outer join in Books Online.
    No! Not a left outer join! I want the same thing as EXCEPT (DB2) or MINUS (Oracle).

    All there is on the subject is:
    http://msdn.microsoft.com/library/de...tun_1_9n3i.asp

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, i know what EXCEPT is supposed to do (it's part of the sql standard), and that microsoft page sure is useful, isn't it? i still don't know what "anti semi" means, but i can do an EXCEPT without using that syntax -- the need for this type of retrieval has always existed, but we used to do it the "old-fashioned" way before the sql standard provided the syntax

    basically, use a LEFT OUTER join, and check a column from the right table to see when it's null -- and it is best to use a join column for this purpose

    select a.foo, b.bar
    from table1 a
    left outer
    join table2.b
    on a.foo = b.foo
    where b.foo is null

    rudy

  7. #7
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Left Anti Semi Join

    Originally posted by r937
    well, i know what EXCEPT is supposed to do (it's part of the sql standard), and that microsoft page sure is useful, isn't it? i still don't know what "anti semi" means, but i can do an EXCEPT without using that syntax -- the need for this type of retrieval has always existed, but we used to do it the "old-fashioned" way before the sql standard provided the syntax

    basically, use a LEFT OUTER join, and check a column from the right table to see when it's null -- and it is best to use a join column for this purpose

    select a.foo, b.bar
    from table1 a
    left outer
    join table2.b
    on a.foo = b.foo
    where b.foo is null

    rudy
    Well kinda knew that but I thought there was a "shorter" way to do it... To bad they don't support EXCEPT!

  8. #8
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    I read all BOL and I also found these "Left Semi" and "Left Anti Semi" joins.
    At first I tried to

    select a.foo, b.foo
    from table1 a
    left semi join table2 b on a.foo = b.foo

    Of course, it didn't work.

    At second I read a description. And it is normal:

    select a.foo
    from table1 a
    where exists( select * from table2 b where a.foo = b.foo )

    select a.foo
    from table1 a
    where not exists( select * from table2 b where a.foo = b.foo )


    r937's query is a replacement of "not exists"

    select a.foo
    from table1 a
    left outer join table2 b on a.foo = b.foo
    where b.foo is null

    but "not exists" is faster.
    There is a bug in QA-Display Estimated Execution Plan.
    Sometimes it shows 100% for "not exists" and 0% for "left join".
    But in reality "left join" has higher Duration, CPU and Reads.

    My replacement of "exists"

    select a0.foo
    from table1 a0
    join
    (
    select distinct a.foo
    from table1 a
    join table2 b on a.foo = b.foo
    ) x on a0.foo=x.foo

    is much slower

    Test data
    --------------------------------
    create table table1(foo int NULL)
    create table table2(foo int NULL)
    insert table1(foo) values (NULL)
    insert table1(foo) values (NULL)
    insert table1(foo) values (1)
    insert table1(foo) values (1)
    insert table1(foo) values (2)
    insert table1(foo) values (2)
    insert table2(foo) values (NULL)
    insert table2(foo) values (NULL)
    insert table2(foo) values (2)
    insert table2(foo) values (2)
    insert table2(foo) values (3)
    insert table2(foo) values (3)

Posting Permissions

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