CODEWITHSUNDEEP
sqlpostgresql
Janothan
Janothan

Reputation: 476

Add On-Condition of Full Outer Join to Selection

Assume I have tables employees (employee_id, employee_name, department_id) and departments (department_id, department_name). I want the full outer join on both tables with the department_id which happens to be the on condition. It shall always be filled (either with the value from the employee table or with the value of the department table - whichever value is not null). 

SELECT
   employee_name,
   department_name
FROM
   employees e
FULL OUTER JOIN departments d ON d.department_id = e.department_id;

How would I integrate the department_id into the query above? The syntax is in postgresql. (I do know I could join it - but actually the information concerning the department_id should be available during query processing.)

Upvotes: 0

Views: 45

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You can use the using clause:

SELECT department_id, e.employee_name, e.department_name
FROM employees e FULL OUTER JOIN
     departments d
     USING (department_id);

Note that this requires that the foreign key have the same name as the primary key and you do not qualify the column in the SELECT.

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95080

You would typically use a USING clause in a full outer join:

SELECT
   department_id,
   e.employee_name,
   d.department_name
FROM employees e
FULL OUTER JOIN departments d USING (department_id);

Upvotes: 1

Ben
Ben

Reputation: 5208

Try this...

SELECT
   employee_name,
   department_name,
   COALESCE (e.department_id, d.department_id)
FROM
   employees e
FULL OUTER JOIN departments d ON d.department_id = e.department_id;

Upvotes: 2

Related Questions