Which query is the correct way?

问题:

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

回答1:

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:

• Bad habits to kick : using old-style JOINs.

• Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3).

• SQL JOIN: is there a difference between USING, ON or WHERE?.