What is the difference between these two SQL?

Question

select a.name from student a, student b
where a.id = b.id and a.id = 1;

vs

select a.name from student a
inner join student b
on a.id = b.id and a.id = 1;

Are they actually the same?

The former is written by web developers who don't know any better, the latter is written by people who want explicit, readable and maintainable code. *[Just to be very slightly contentious.]* — MatBailie, May 19 '14 at 13:09

score 0 · Answer 1 · answered May 19 '14 at 12:51

0

Obviously they are not the same syntactically, but they are semantically (they return the same). However, I'd write the second one as:

select a.name from student a
inner join student b on a.id = b.id 
where a.id = 1;

answered May 19 '14 at 12:51

Thorsten Dittmar

55,956
8
91
139

So is there any performance difference among them? – Deqing May 19 '14 at 12:58
Do you mean the samples you provided or between the last sample you provided and my "improved" version? If you ask whether the `JOIN` performs better over the `WHERE`, @BaconBits answer provides information on possible optimization benefits. – Thorsten Dittmar May 19 '14 at 13:02

score 0 · Answer 2 · answered May 19 '14 at 12:53

0

The join you're doing is pointless because you're giving the same table different alias and joining on the same field i.e. id.

Your select statement should simply be as follows:

SELECT name FROM stud WHERE id = 1

answered May 19 '14 at 12:53

SamoanProgrammer

954
4
13
27

While that is true, it isn't really the point here. The question is about the two ways of inner joining two tables. – Thorsten Dittmar May 19 '14 at 12:54
True, I guess that is why my reputation is only 39 and your one is over 26k :) – SamoanProgrammer May 19 '14 at 12:59
That has nothing to do with reputation and I was not saying your answer was entirely wrong :-) I didn't even realise the OP joined the same table until I read your answer... – Thorsten Dittmar May 19 '14 at 13:01
Sorry, not a very good example. @ThorstenDittmar, actually I'm learning the concept about `self join` :) – Deqing May 19 '14 at 13:01

score 0 · Answer 3 · answered May 19 '14 at 12:54

0

the latter is per ansi standard and the formeris conventionally supported..they are functionally equivalent..if you use the latter, there is greater chance of your SQL being portable across vendors

answered May 19 '14 at 12:54

Aadith Ramia

10,005
19
67
86

score 0 · Accepted Answer · edited May 23 '17 at 12:27

They are the same as far as the query engine is concerned.

The first type, commonly called a comma join, is an implicit inner join in most (all?) RDBMSs. The syntax is from ANSI SQL 89 and earlier.

The syntax of the second join, called an explicit inner join, was introduced in ANSI SQL 92. It is considered improved syntax because even with complex queries containing dozens of tables it is easy to see the difference between join conditions and filters in the where clause.

The ANSI 92 syntax also allows the query engine to potentially optimize better. If you use a something in the join condition, then it happens before (or as) the join is done. If the field is indexed, you can get some benefit since the query engine will know to not bother with certain rows in the table, whereas if you put it in the WHERE clause, the query engine will need to join the tables completely and then filter out results. Usually the RDBMS will treat them identically -- probably 999 cases out of 1000 -- but not always.

What is the difference between these two SQL?

4 Answers4