Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: Set my mind at ease on EXISTS

    SELECT
    [List1].[TITLE],
    [List1].[FORENAME],
    [List1].[SURNAME],
    [List1].[JOB]
    FROM List1
    WHERE EXISTS(
    SELECT [List2].[forename] FROM [List2]
    WHERE [List2].[forename] = [List1].[FORENAME]
    AND [List2].[surname] = [List1].[SURNAME]
    AND [List2].[email] = [List1].[EMAIL]
    )


    I am on the right track here, aren't I?

    That query will.. sort of... loop through List1, get the "current" records firstname, surname and email, and then go and see if there's any single record in List2 that contains exactly that combination of values in those fields. It's a stupid question but I'm not the brightest of sparks and BOL leaves me even more confused. I'm just worried in case it's going "well, record 34 matches the current firstname and record 96 matches the current email, so EXISTS returns true".

    Assuming I've got it right, though - many of the records that I know to be duplicates have a NULL email address. And the query above seems to be automatically removing them. If there's a record in both List1 and List2 with identical firstname and surname, and both have NULL email addresses - should the query above pick it up? If not, how can I include those records?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yeah - that's fine. Ideally you should use
    Code:
    ...EXISTS(SELECT *
    FROM...
    (the one time SELECT * is recommended) as the optimiser will then pick the best field for the job.

    One minor thing - you could of course just write this as a nice, familar join (probably in combination with DISTINCT) instead and put your mind totally at ease.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, your query will do what you are expecting, but it is more acceptable to use a standard JOIN in cases such as this:
    Code:
    SELECT	[List1].[TITLE],
    	[List1].[FORENAME],
    	[List1].[SURNAME],
    	[List1].[JOB]
    FROM	List1
    	INNER JOIN [List2]
    		on [List1].[FORENAME] = [List2].[forename]
    		and [List1].[SURNAME] = [List2].[surname]
    		and [List1].[email] = [List2].[email]
    You cannot handle equalities with nulls directly, as NULLs are not values and thus never "equal" anything, even other NULLs. Here are two of the many possible ways around this:
    Code:
    SELECT	[List1].[TITLE],
    	[List1].[FORENAME],
    	[List1].[SURNAME],
    	[List1].[JOB]
    FROM	List1
    	INNER JOIN [List2]
    		on [List1].[FORENAME] = [List2].[forename]
    		and [List1].[SURNAME] = [List2].[surname]
    		and ([List1].[email] = [List2].[email]
    			or ([List1].[email] is null and [List2].[email] is null))
    ...or:
    Code:
    SELECT	[List1].[TITLE],
    	[List1].[FORENAME],
    	[List1].[SURNAME],
    	[List1].[JOB]
    FROM	List1
    	INNER JOIN [List2]
    		on [List1].[FORENAME] = [List2].[forename]
    		and [List1].[SURNAME] = [List2].[surname]
    		and COALESCE([List1].[email], 'NULL') = COALESCE([List2].[email], 'NULL')
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    AH - just seen your last bit. Yes - NULL <> NULL (unless you turn ANSI_NULLS off).

    Perhaps this would do it:
    Code:
    SELECT TITLE,
     FORENAME,
     SURNAME,
     JOB
    FROM  dbo.List1
    WHERE  EXISTS (
      SELECT *
      FROM dbo.List2
      WHERE  List2.forename = List1.FORENAME
       AND List2.surname = List1.SURNAME
       AND 
        (List2.email = List1.email
        OR 
        (List2.email IS NULL AND List1.email IS NULL))
      )
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Damn phone calls....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jan 2002
    Posts
    189
    Thanks for the speedy replies

    I now, however, have two queries which - if I understand correctly - should return pretty identical results. And... they don't.

    Code:
    SELECT COUNT(*)
    FROM	[List1]
    	INNER JOIN [List2]
    		on [List1].[FORENAME] = [List2].[forename]
    		and [List1].[SURNAME] = [List2].[surname]
    		and ([List1].[DMCEMAIL] = [List2].[dmcemail]
    			or ([List1].[DMCEMAIL] is null and [List2].[dmcemail] is null))
    WHERE	1=1
    	AND [List1].[EMPNATN] > 49 AND [List1].[EMPNATN] < 501
    	AND EXISTS (
    		SELECT [england_counties].[county] FROM [england_counties] WHERE [england_counties].[county] = [List1].[COUNTY]
    	)
    returns 3660

    Code:
    SELECT 
    	COUNT(*)
    FROM [List1]
    WHERE	1=1
    	AND EXISTS (
    		SELECT * FROM [List2]
    		WHERE 1=1
    		AND [List2].[forename] = [List1].[FORENAME]
    		AND [List2].[surname] = [List1].[SURNAME]
    		AND (
    			[List2].[dmcemail] = [List1].[DMCEMAIL]
    			OR(
    				[List2].[dmcemail] IS NULL AND [List1].[DMCEMAIL] IS NULL
    			)
    		)
    	)
    	AND [List1].[EMPNATN] > 49 AND [List1].[EMPNATN] < 501
    	AND EXISTS (
    		SELECT [england_counties].[county] FROM [england_counties] WHERE [england_counties].[county] = [PrefixList1].[COUNTY]
    	)
    returns 3485


    So - couple of things. Yes, there's a couple more WHERE clauses in there. They're identical between the two queries though and I can't see how they affect the results. And my email field is, in fact, called "dmcemail". I don't know why, it makes no sense to me either but I got fed up with renaming the thing for readability's sake when I'm posting it up here.

    Is the discrepancy explainable? Is it explainable by my own dimness? Which method would you suggest is the more 'accurate'?

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Are you building these up dynamically? If not - why the 1 = 1 stuff?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh - and these queries are not the same

    There is actually a clue in my first post....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Where 1=1?
    Smells like some coder is being paid by the line...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Jan 2002
    Posts
    189
    Aha - the DISTINCT

    And yeah, the 1=1 thing is force of habit really. I usually only write SQL in the context of ASP pages or whatever, and I just chuck it in so I can have all my WHERE's beginning with AND. I'm not paid by the line

    Thanks for all your help.

Posting Permissions

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