Why does EXISTS clause use old join method

Question

question

Slick84 asked Jan 20, '10

Why does EXISTS clause use old join method

Hi,

I'd like to know why the EXISTS clause in SQL uses the old join method. For example.

SELECT * FROM table1
WHERE EXISTS
(
   SELECT table2.columnname
   FROM table2
   WHERE table1.columnname = table2.columnname
   AND table1.columnnameB > '01/01/2099'
)

Please provide insight. This gave me different results from using the ANSI standard INNER JOIN in the sub-query.

Thanks, S

select joins exists

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David 1 commented · Jan 23, 2010 at 04:56 PM

Just to correct what may be a misconception here: Putting the joining criteria in the WHERE clause (subquery or not) is NOT an old/obsolete/non-standard syntax. It is part of the core ISO Standard SQL and always has been. It is the Microsoft / Sybase *=/=* operator for *outer* joins that is non-standard.

0 ·

Answer 1 · 2010-01-20T20:04:55Z

Rob Farley answered Jan 20, '10

EXISTS isn't the same as an Inner Join. It's actually a Semi Join (and NOT EXISTS is an Anti Semi Join). It doesn't use an ON clause because it does use the JOIN notation.

Another example is APPLY, which is similar to JOIN but doesn't need an ON clause because the hook is done in a different way.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-01-20T19:00:40Z

Matt Whitfield answered Jan 20, '10

It's not really a join, it's a correlated sub-query. What you are asking is 'please return me all rows where the sub-query returns one or more values, given the values in the primary table'. With a join, you are asking 'please return me the cartesian product of the rows in the primary table and the rows in the secondary table, based on this condition'.

I.e. it's possible for you to return more than the number of rows in the primary table with any kind of join. With a correlated sub-query, that's not possible.

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Slick84 commented · Jan 20, 2010 at 07:15 PM

How is it possible to me to return more than the number of rows in the primary table using an INNER JOIN?

0 ·

Rob Farley commented · Jan 20, 2010 at 08:01 PM

Joining on something that matches multiple records on the right. A semi-join (exists) will only filter. Read my chapter on the FROM clause in SQL Server MVP Deep Dives, or search my blog for joins and words like duplication and elimination.

0 ·

Answer 3 · 2010-01-20T19:41:51Z

Jack Corbett answered Jan 20, '10

I think you really want your code to be:

SELECT 
    * 
FROM 
    table1 
WHERE 
    EXISTS(SELECT table2.columnname FROM table2 
          WHERE table1.columnname = table2.columnname) AND 
    table1.columnnameB > '01/01/2099'

I think this will give you the same results as an inner join. I moved the criteria that was just on Table1 outside the EXISTS

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Slick84 commented · Jan 20, 2010 at 08:57 PM

No my code is giving me the correct results. I was just annoyed at the fact that I had to use the old joining method for the EXISTS keyword. It took me 3 hours to find out something which I fixed in 2 mins. Lack of knowledge on my part. Thanks for all the input though.

0 ·

question