哪种查询是正确的方法? [英] 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.

谢谢

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

推荐答案

两个查询之间的区别是JOIN语法:

The difference between the two queries is the JOIN syntax:

• 第一个使用ANSI-92 JOIN语法.
• 第二个使用旧的ANSI-89 JOIN语法.

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

哪个更好?

Which is better?

MySQL和SQL Server以及其他RDBMS都支持这两种语法，并且您不应该期望两者之间的任何性能差异.他们是一样的.

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.

但是，建议使用第一个.在许多方面它是更安全的.例如，如果要执行INNER JOIN，请使用第二种语法:

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;

如果您忘记了WHERE t1.id = t2.id，查询将不会失败.但是它将产生交叉连接，并且很难发现该错误.但是使用第一种语法:

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;

如果您忘记了ON条件，则会收到语法错误.即使您打算执行CROSS JOIN，其他人也可以更容易理解:

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 

比其他语法.

@Gareth 指出的另一个好处(请参见下面的评论):

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

另一个巨大的好处是可以轻松地在INNER和 OUTER联接而不必在WHERE子句中处理NULL.

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:

• 坏习惯:使用老式的JOIN.

SQL JOIN:在USING，ON或WHERE之间有区别吗? .

这篇关于哪种查询是正确的方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！