Which is more correct with SQL Functions, Join or in Where clause?

Question

I know that there are similar questions floating around (like SQL Filter criteria in join criteria or where clause which is more efficient) but as I remember SQL functions are special. Maybe? So basically which is more correct/more performant?

SELECT *
  FROM  [DBOne].[dbo].[SomeData] SD
    INNER JOIN [DBTwo].[DBOne].[SomeOtherData_GetALL]() SOD ON SD.[DataID] = SOD.[DataID]

OR

SELECT *
  FROM  [DBOne].[dbo].[SomeData] SD
  WHERE SD.[DataID] IN (SELECT [DataID] FROM [DBTwo].[DBOne].[SomeOtherData_GetALL]())

Your 2 queries are not equivalent. You are comparing apples and oranges. — sstan, Oct 08 '15 at 02:54
As far as your particular code example goes - no, table functions are nothing special or different. — Roger Wolf, Oct 08 '15 at 02:55
Even if you change from `left join` to `inner join`, they can still potentially return different results. — sstan, Oct 08 '15 at 02:56
Using the inner join, you could get duplicates from the second table and you are returning all of the values from the second table. — Steve Dowling, Oct 08 '15 at 02:58
So if my function returned `DISTINCT` then they would both return the same results — Serj Sagan, Oct 08 '15 at 03:01
It depends on your table structure and indexes. WHERE EXISTS will be quicker than either of them if the indexes aren't right and will be equally performant if they are. And it is more explicit in my opinion. — Steve Dowling, Oct 08 '15 at 04:13

score 1 · Accepted Answer · edited May 23 '17 at 12:06

1

I usually use WHERE EXISTS instead of joining. See here for an explanation.

edited May 23 '17 at 12:06

Community

1
1

answered Oct 08 '15 at 02:57

Steve Dowling

1,919
1
13
18

score 0 · Answer 2 · edited May 23 '17 at 12:14

0

Basically it is like asking JOIN vs SUB QUERY, if you are concern with the performance, i suggest to use the JOIN instead of using WHERE IN clause, you can see the difference of the performance if you use the execution plan of SQL.

To further understand, you can read this post: Join vs. sub-query

Hope this help.

edited May 23 '17 at 12:14

Community

1
1

answered Oct 08 '15 at 02:48

japzdivino

1,736
3
17
25

If this is true, then you'll get the check mark... but it seems people are saying that it doesn't matter... are you sure of this? – Serj Sagan Oct 08 '15 at 03:07
Actually using `SUB QUERY` is the simple and easiest way, but like what i've said if your concern is the performance itself, i am pretty sure it is better to use `JOIN` base on my experience in SQL. If you want to test you can use the execution plan of SQL for checking. – japzdivino Oct 08 '15 at 03:38

Which is more correct with SQL Functions, Join or in Where clause?

2 Answers2