What is different between Inner join and compare operator in SQL query

Question

I have to write sql query with two different way, both are giving same output. But Still not understand

what is different between both query?
which one is fast in access?
which is more recommend?

Query # 1

SELECT PageControlDet.PageId, PageMaster.PageName, PageMaster.PageURL, 
       PageMaster.PageTitle, PageMaster.PageDescription
FROM AgentRoleAccessDet 
INNER JOIN PageControlDet ON AgentRoleAccessDet.ControlId = PageControlDet.ControlId
INNER JOIN PageMaster ON PageControlDet.PageId = PageMaster.PageId

and

Query #2

SELECT PageControlDet.PageId, PageMaster.PageName, PageMaster.PageURL, 
       PageMaster.PageTitle, PageMaster.PageDescription
FROM   AgentRoleAccessDet,  PageControlDet, PageMaster
WHERE  AgentRoleAccessDet.ControlId = PageControlDet.ControlId and
    PageControlDet.PageId = PageMaster.PageId 
ORDER BY PageControlDet.PageId

Better using the Inner Join in query – Saroop Trivedi Feb 12 '13 at 08:26 — Saroop Trivedi, Feb 12 '13 at 08:26

Damien_The_Unbeliever · Accepted Answer · 2013-02-12T08:20:26.413

1

what is different between both query?

One uses INNER JOIN, the other doesn't. Just listing all of the tables in the FROM clause, separated by commas is an older style/syntax, that's mostly deprecated these days. It's from before the JOIN syntax was introduced into the standard.

which one is fast in access?

They should perform equally well. You'd have to generate execution plans to know for sure, but the optimizer should treat them both equally.

which is more recommend?

The first. Making the joins explicit and distinct from any filtering can help others to understand the query in the future. Also, as soon as you introduce OUTER joins, you'd need to use deprecated syntax to use the second style.

edited Feb 12 '13 at 08:20

answered Feb 12 '13 at 08:06

Damien_The_Unbeliever

234,701
27
340
448

Daminen : I know first use Inner Join and second not. I want to know basic different between two. – Saroop Trivedi Feb 12 '13 at 08:16
Daminen: So better option use INNER JOIN. Correct – Saroop Trivedi Feb 12 '13 at 08:27
@SaroopTrivedi - yes, that's correct. – Damien_The_Unbeliever Feb 12 '13 at 08:30

score 0 · Answer 2 · edited May 23 '17 at 11:48

0

You should definitely use the inner join.

Specifying multiple tables in the From section is deprecated and can lead to ambiguous query interpretation. See this answer for more details.

edited May 23 '17 at 11:48

Community

1
1

answered Feb 12 '13 at 08:04

Yaakov Ellis

40,752
27
129
174

score 0 · Answer 3 · answered Feb 12 '13 at 08:19

In second example your query is equivalent to this:

SELECT PageControlDet.PageId, PageMaster.PageName, PageMaster.PageURL, 
       PageMaster.PageTitle, PageMaster.PageDescription
FROM   AgentRoleAccessDet
CROSS JOIN PageControlDet 
CROSS JOIN PageMaster
WHERE  AgentRoleAccessDet.ControlId = PageControlDet.ControlId and
       PageControlDet.PageId = PageMaster.PageId 
ORDER BY PageControlDet.PageId

Using cross join without this keyword (comma separated list of tables) is old syntax, TSQL support both (old and new with CROSS Join keyword). Cross join will produce Cartesian product. Due to your example I'll prefer first query - because it's more readable, understandable and well-formed according to the query logic. About performance: I think that MSSQL Optimizer will change your second query to first.

What is different between Inner join and compare operator in SQL query

3 Answers3