how to change the syntax of this sql query to 92's explicit join systax

How can i rewrite the below query in SQL '92's Explicit join syntax using only inner or outre joins

SELECT  e.emp_id ,
( SELECT    AVG(salary)
  FROM      #salary d
 开发者_StackOverflow社区 WHERE     d.emp_id = e.emp_id )
FROM    #emp e

I'm not even sure why you need the outer select; The employee id is present in both tables, you don't need a join at all:

SELECT salary.emp_id, AVG(salary.salary)
FROM salary
GROUP BY salary.emp_id

Well, maybe some employees aren't salaried, and you just want a NULL (is AVG() strict on your DBMS?)

SELECT e.emp_id, AVG(d.salary)
FROM employee e
LEFT OUTER JOIN salary d ON e.emp_id = d.emp_id
GROUP BY e.emp_id

Good point byTokenMacGuy about not needing the #emp table. However, let's say that you only want to query those records in #salary that are also in #emp. In that case, you'll need to move your WHERE clause to the ON clause of a join, and add a GROUP BY clause:

SELECT  e.emp_id, AVG(d.salary)
FROM      #salary d
INNER JOIN #emp e  ON d.emp_id = e.emp_id 
GROUP BY e.emp_id