mysql - 有没有更好的方法来在此 SELECT 语句中构建嵌入式 SELECT 语句？

Question

在下面的 MySQL 代码段中，我在单个 SELECT 语句中使用了两个嵌入式 SELECT 语句来从我的数据库返回一些信息。它目前工作正常，但我不确定是否正确使用了两个嵌入的 SELECT 语句。

SELECT
  E.ID AS DEBATE_GROUPS_ID,
  A.TITLE AS STARTER_PACK_TITLE,
  B.TITLE AS UNITS_TITLE,
  B.UNIT_CODE AS UNITS_CODE,
  (SELECT FIRST_NAME FROM USERS WHERE ID = A.ACADEMIC_ID) AS ACADEMIC_FIRSTNAME, 
  (SELECT LAST_NAME FROM USERS WHERE ID = A.ACADEMIC_ID) AS ACADEMIC_LASTNAME,
  D.DATE_DUE AS DEBATES_DUE_DATE
FROM
  STARTER_PACKS A,
  UNITS B,
  USERS C,
  DEBATES D,
  DEBATE_GROUPS E
WHERE
  D.ID = E.DEBATE_ID AND E.STUDENT_ID = C.ID AND C.ID = 12

因此，我的问题是...

Is what I'm doing with the embedded SELECT statements correct or is there a better alternative in terms of improving efficiency or readability?

Answer 1

是的，使用 JOIN ，虽然您的查询看起来完全错误，但您几乎没有任何连接条件!

SELECT
  E.ID AS DEBATE_GROUPS_ID,
  A.TITLE AS STARTER_PACK_TITLE,
  B.TITLE AS UNITS_TITLE,
  B.UNIT_CODE AS UNITS_CODE,
  C.FIRSTNAME AS ACADEMIC_FIRSTNAME, 
  C.LASTNAME AS ACADEMIC_LASTNAME,
  D.DATE_DUE AS DEBATES_DUE_DATE
FROM
  STARTER_PACKS A
  INNER JOIN UNITS B ON(a.Column = B.Column)
  INNER JOIN USERS C ON(C.ID = A.ACADEMIC_ID)
  INNER JOIN DEBATES D ON (SomeColumn = D.Column)
  INNER JOIN DEBATE_GROUPS E ON(D.ID = E.DEBATE_ID AND E.STUDENT_ID = C.ID)
WHERE
  C.ID = 12

请使用正确的显式连接语法，这将帮助您避免此类问题。用实际的列关系替换这些行:

  INNER JOIN UNITS B ON(a.Column = B.Column)
  INNER JOIN DEBATES D ON (SomeColumn = D.Column)