SQL Syntax error:：use parentheses() after FROM clause is wrong?-CodePudding

Can you tell me why I can not use parentheses after FROM clause to treat it a whole table?

The two codes may can not run here. The difference is the appearance of one more parentheses after FROM clause.

Right:

# right
SELECT t1.emp_no, t2.manager_no, t1.emp_salary, t2.manager_salary
FROM (SELECT de.emp_no emp_no, s1.salary emp_salary, de.dept_no
      FROM dept_emp de 
      JOIN salaries s1 
      ON de.emp_no = s1.emp_no
      WHERE de.to_date = '9999=01-01'
            AND s1.to_date = '9999=01-01'
            AND de.emp_no NOT IN (SELECT emp_no FROM dept_manager)
        ) t1
      JOIN (SELECT dm.emp_no manager_no, s2.salary manager_salary, dm.dept_no
            FROM dept_manager dm 
            JOIN salaries s2
            ON dm.emp_no = s2.emp_no 
            WHERE s2.to_date = '9999=01-01'
                  AND dm.to_date = '9999-01-01'
            ) t2
      ON t1.dept_no = t2.dept_no
WHERE t2.manager_salary < t1.emp_salary

Wrong:

# wrong
SELECT t.emp_no, t.manager_no, t.emp_salary, t.manager_salary
FROM ((SELECT de.emp_no emp_no, s1.salary emp_salary, de.dept_no
      FROM dept_emp de 
      JOIN salaries s1 
      ON de.emp_no = s1.emp_no
      WHERE de.to_date = '9999=01-01'
            AND s1.to_date = '9999=01-01'
            AND de.emp_no NOT IN (SELECT emp_no FROM dept_manager)
      ) t1 
      JOIN (SELECT dm.emp_no manager_no, s2.salary manager_salary, dm.dept_no
            FROM dept_manager dm 
            JOIN salaries s2
            ON dm.emp_no = s2.emp_no 
            WHERE s2.to_date = '9999=01-01'
                  AND dm.to_date = '9999-01-01'
            ) t2
      ON t1.dept_no = t2.dept_no
      ) t
WHERE t.manager_salary < t.emp_salary

The code may can not run here. The difference is the apprence of one more parentheses after FROM clause.

CodePudding user response：

The part between the brackets in the FROM part must be a subquery. Your example has two subqueries and a join clause without the select * part. So it is not a subquery. Try adding select * from between the first two brackets.

CodePudding user response：

Seems your query have on extra brace

SELECT t.emp_no, t.manager_no, t.emp_salary, t.manager_salary
FROM (
     SELECT de.emp_no emp_no, s1.salary emp_salary, de.dept_no
     FROM dept_emp de 
     JOIN salaries s1 
     ON de.emp_no = s1.emp_no
     WHERE de.to_date = '9999=01-01'
        AND s1.to_date = '9999=01-01'
        AND de.emp_no NOT IN (SELECT emp_no FROM dept_manager)
     ) t1 
    JOIN 
    (
      SELECT dm.emp_no manager_no, s2.salary manager_salary, dm.dept_no
      FROM dept_manager dm 
      JOIN salaries s2
      ON dm.emp_no = s2.emp_no 
      WHERE s2.to_date = '9999=01-01'
               AND dm.to_date = '9999-01-01'
    ) t2
    ON t1.dept_no = t2.dept_no
  ) t
  WHERE t.manager_salary < t.emp_salary