1

Can somebody please tell me which is the better way, they both produce the same results but which is more 'correct'?

Personally I feel the first query is easier to read, but I seem to read elsewhere that aliases should always be used.

Thanks

SELECT Patient.PatientSurname, Doctor.DoctorSurname
FROM Patient
JOIN Operation
ON Operation.PatientCode=Patient.PatientCode
JOIN Doctor
ON Doctor.DoctorCode=Operation.DoctorCode
WHERE Operation.OperationType='Broken Arm'
GROUP BY Patient.PatientSurname
HAVING count(Patient.PatientSurname) > 0


SELECT PatientSurname, DoctorSurname
FROM Patient as p, Operation as o, Doctor as d
WHERE o.PatientCode=p.PatientCode
AND  d.DoctorCode=o.DoctorCode
AND o.OperationType='Broken Arm'
GROUP BY p.PatientSurname
HAVING count(p.PatientSurname) > 0
Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
user1295053
  • 303
  • 1
  • 7
  • 17
  • 1
    This is a subjective question so probably not suitable for SO. However I'd recommend reading each of these articles written by [Aaron Bertrand](http://stackoverflow.com/users/61305/aaron-bertrand) --> [Using Table Aliases like a, b, c](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-table-aliases-like-a-b-c-or-t1-t2-t3), [Using Old Style Joins](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) – GarethD Nov 08 '12 at 13:26

1 Answers1

11

The difference between the two queries is the JOIN syntax:

  • The first one use the ANSI-92 JOIN syntax.
  • The second one use the old ANSI-89 JOIN syntax.

Which is better?

Both syntaxes are supported by MySQL and SQL Server and other RDBMS as well, and you shouldn't expect any performance difference between the two. They are the same.

But, it is recommended to use the first one. It is safer in number of ways. For example, if you want to do an INNER JOIN, using the second syntax:

SELECT *
FROM Table1 t1, Table2 t2
WHERE t1.id = t2.id2;

If you forgot the WHERE t1.id = t2.id, the query won't fail. But it will produce a cross join and it will be hard to find out that error. But using the first syntax:

SELECT *
FROM Table1 t1 INNER JOIN Table2 t2 ON t1.id = t2.id2;

If you forgot the ON condition, you will get a syntax error. Even if you intended to do a CROSS JOIN it will more readable for other to tell that:

SELECT *
FROM Table1 t1 CROSS JOIN Table2 t2

Than the other syntax.

Another benefit noted by @Gareth (See the comments below):

Another massive benefit is the ease of switching between INNER and OUTER joins without having to handle NULL in the WHERE clause.

For more information see the folowing:

Community
  • 1
  • 1
Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
  • Yes, I find the first to be a lot clearer about what you are doing. Instead of the JOIN conditions being mixed in the WHERE clause, they are organized with the JOIN they affect. – Marlin Pierce Nov 08 '12 at 13:21
  • @MarlinPierce - Yes it the recommended way and it is more readable and more safe. See my edit. – Mahmoud Gamal Nov 08 '12 at 13:28
  • Another massive benefit is the ease of switching between `INNER` and `OUTER` joins without having to handle `NULL` in the `WHERE` clause. – GarethD Nov 08 '12 at 13:32
  • 1
    @Eng.Fouad - Thanks my brother :) I am very glad to see Arabian user with high Rep like you. Its very rare here in SO. Keep up the good work. – Mahmoud Gamal Feb 23 '13 at 14:37