Professional Web Applications Themes

using 'exist' - Microsoft SQL / MS SQL Server

GO select * from publishers where exists ( select distinct * from titles where titles.pub_id = publishers.pub_id and type = 'business' --select distinct * from titles full outer join publishers on titles.pub_id = publishers.pub_id where type = 'business' ) select * from publishers where exists ( --select distinct * from titles where titles.pub_id = publishers.pub_id and type = 'business' select distinct * from titles full outer join publishers on titles.pub_id = publishers.pub_id where type = 'business' ) --select * from titles --select p.* from publishers as p GO select t.* from titles as t , publishers as p where t.pub_id ...

  1. #1

    Default using 'exist'

    GO
    select * from publishers where exists (
    select distinct * from titles where titles.pub_id =
    publishers.pub_id and type = 'business'
    --select distinct * from titles full outer join publishers
    on titles.pub_id = publishers.pub_id where type
    = 'business'
    )

    select * from publishers where exists (
    --select distinct * from titles where titles.pub_id =
    publishers.pub_id and type = 'business'
    select distinct * from titles full outer join publishers
    on titles.pub_id = publishers.pub_id where type
    = 'business'
    )
    --select * from titles

    --select p.* from publishers as p
    GO

    select t.* from titles as t , publishers as p where
    t.pub_id = p.pub_id and t.type = 'business'

    select * from titles full outer join publishers on
    titles.pub_id = publishers.pub_id where type = 'business'
    GO

    why is there an anamoly in the result sets ??
    thx in advance.
    Arvind Guest

  2. #2

    Default Re: using 'exist'

    Arvind (parvind_15hotmail.com) writes:
    > select * from titles full outer join publishers on
    > titles.pub_id = publishers.pub_id where type = 'business'
    > GO
    >
    > why is there an anamoly in the result sets ??
    Maybe you could explain a little more in detail what results you
    expected.

    I don't know if is related to the problem you are seing, but note
    that in the SELECT above you have a condition in the WHERE clause.
    The logcial execution of A FULL OUTER JOIN B WHERE ... is that first
    SQL Server constructs a table containing all rows from A both and B,
    and where there is no matching row according to the ON conditions
    in B for a row in A, all B columns are NULL and vice versa. Once this
    is done, SQL Server applies the WHERE clause. So if the where clause
    says A.col = 'Value', this means that all columns with NULL A.col
    are filtered away, which means that all rows in B with no matching
    rows in A are filtered. If you move the condition in the WHERE
    clause to the ON clause, you get a different result.

    Note: this is a logical description of how SQL Server evaluates the
    query. The actual query may be different, as long as the result is the
    same.


    --
    Erland Sommarskog, SQL Server MVP, [email]sommaralgonet.se[/email]

    Books Online for SQL Server SP3 at
    [url]http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/url]
    Erland Sommarskog Guest

Similar Threads

  1. Can MX6.1 and MX7 co-exist on a server?
    By prakash.mishra in forum Coldfusion Server Administration
    Replies: 2
    Last Post: October 21st, 03:42 PM
  2. cfencoder does it exist for 6.1?
    By SilentBob'secretfusion in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: March 10th, 09:10 PM
  3. Does this exist?
    By Deb in forum Macromedia Freehand
    Replies: 3
    Last Post: January 16th, 04:54 PM
  4. Do Else Unless Statements Exist?
    By Dan Anderson in forum PERL Beginners
    Replies: 2
    Last Post: January 12th, 09:07 AM
  5. Replies: 1
    Last Post: July 29th, 04:31 PM

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