How to add subquery as a column in SQL

Question

How do I add a subquery as a column in my SQL script?

e.g.

Select emp_no, name,gender ,
    (select department_name from departments where employees.emp_no = departments.emp_no)
from employees

PS: I'm using oracle 8

Solution 1

left join is the best-practice, and should be faster in performance:

Select e.emp_no, e.name, e.gender , d.department_name
from employees e left join departments d on e.emp_no = d.emp_no;

Solution 2

That looks reasonably sound, I would suggest some (possible typos) cleaning up: add a comma after "gender" and declare the table names, also set the subquery alias

Select employees.emp_no, employees.name, employees.gender,  
    (select departments.department_name from departments where employees.emp_no = departments.emp_no) as dept_name
from employees

Alternatively, a nice join would would work too, if the other data is feasible:

Select employees.emp_no, employees.name, employees.gender,  departments.department_name
from employees
inner join departments on employees.emp_no = departments.emp_no

Solution 3

Going by the semantics, what I understand is that you want an employee's department name to be shown alongside his/her other information. I would suggest you do a join instead:

Select emp_no, name, gender, department_name
from employees emp, departments dept
where emp.emp_no = dept.emp_no;

Comments

[zerkms]

1. quote after gender 2. If it returns a single row - it should work

[rjmcb]

comma isn't the issue, I only mistyped this query.. this query results to an error "ORA-0936 Missing Expression" on the subquery SELECT part

[SS781]

good point here, doesn't answer question, but I overlooked fact that subquery is not even best way to go here...

[rjmcb]

no, this is just a dummy query so just you would understand my problem.

[rjmcb]

The first one is just the one I just did, but it shows an error... "Missing Expression"

[JBC]

If you are receiving "Missing Expression" on the subquery select, it means you have null/no matching rows. Basically, you have Emp_No 1,2,3,4,5 but only 1,2,4,5 return department_name. You have bad data for a subquery select and thus are returning an error because not all rows from the root table have values in the subquery. Try the join and see if you get the error, I figure you won't, but you WILL have fewer rows than expected. Otherwise, for more detailed help, post some sample data