question

Slick84 avatar image
Slick84 asked

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

selectjoinsexists
1 comment
10 |1200

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

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 Likes 0 ·
Rob Farley avatar image
Rob Farley answered

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

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

Matt Whitfield avatar image
Matt Whitfield answered

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 comments
10 |1200

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

How is it possible to me to return more than the number of rows in the primary table using an INNER JOIN?
0 Likes 0 ·
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 Likes 0 ·
Jack Corbett avatar image
Jack Corbett answered

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 comment
10 |1200

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

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 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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