1

I have 3 query that have same result, But i don't know what is difference and which way that i should use?

-- Query 1

select u.firstName ,ag.[homepage]
from [User] u
CROSS APPLY (
SELECT  [homepage] as  homepage 
FROM [Agent] a
WHERE a.Id = u.Id
) ag

-- Query 2

select u.firstName , a.[homepage] 
from [User] u , [Agent] a
where a.Id = u.Id

-- Query 3

select u.firstName , a.[homepage] 
from [User] u 
inner join  
[Agent] a
on a.Id = u.Id
uopeydel
  • 431
  • 4
  • 22
  • 2
    Simply use Q 3. – jarlh Jun 13 '18 at 08:13
  • 2
    query 2 and 3 are the same, except that 2 uses the old style join, I wouldn't recommend using the older join style as it is more standard to use the newer style (query 3) – WhatsThePoint Jun 13 '18 at 08:14
  • 2
    To back up what @WhatsThePoint said on Query 2: [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) – Thom A Jun 13 '18 at 08:17
  • Difference between 1 and 2/3: https://stackoverflow.com/questions/1139160/when-should-i-use-cross-apply-over-inner-join – EzLo Jun 13 '18 at 08:29
  • If you enter all three queries into SQL Server Management Studio, with "GO" between each block, then click "Display Estimated Execution Plan" it will show you the "cost" of each block as a percentage (you can ignore the rest of the information for now). Pick the one that has the lowest percentage, which will probably be Query 3? – Richard Hansell Jun 13 '18 at 08:44
  • It all depends on when the "Filter" is applied, and in what sequence the tables are accessed. For inner-joins the result set itself would be the same. Now should you test out Outer-joins, then the result sets would be different, as the filter would be applied at different stages in the query sequence. It is better to not use the Q2 format, as it's less readable and I'm not sure it's compliant to all databases (Q3 is). Q1 vs Q3 is completely dependent on the tables structures and indexing. Q3 however would be the most common use. – Amir Pelled Jun 13 '18 at 08:50

1 Answers1

0

If you don't know which to use, then presumably you area learning SQL. If so, the place to start is with the third one:

select u.firstName, a.[homepage] 
from [User] u inner join  
     [Agent] a
     on a.Id = u.Id;

Why? The primary reason is that your intention is to "join" two tables. The ANSI standard syntax for joining uses the JOIN keyword with the ON clause. This is the proper, accepted syntax for a JOIN.

JOIN is a fundamental part of SQL, because the join operation (or equivalents) is a fundamental part of relational algebra, which is sort of the theoretical foundation of SQL. It is usually among the first things taught in SQL.

That does not make the other two versions "wrong", just misleading. APPLY implements something called a "lateral join". This is a super-powerful part of the SQL language. However, it is not necessarily the place to begin.

Although you can express all joins using APPLY, I find that misleading. Perhaps I am old-fashioned and one day, APPLYwill supplant JOIN. However, SQL has been centered on "regular" joins for decades. And I find them to be useful for expressing relationships between tables.

As for the second version, it is simply archaic. When I see commas in the FROM clause, I think . . . Gosh that person is old, learned SQL a long, long time ago, and hasn't learned the more powerful, standard, explicit JOIN syntax. That person probably doesn't even know what an outer join is. How sad.

Don't get confused by execution plans. That is the role of the SQL optimizer. Focus on the query that best represents your intention. If you then have problems with performance, you can work on performance after you have the query that does what you want.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786