PostgreSQL join query issue

Question

I am learning PostgreSQL and was practicing questions.

I am stuck at this question here

My Solution does not seem to work and I am not sure why:

SELECT first_name, last_name, department_id, department_name 
FROM employees 
JOIN departments ON departments.department_id = employees.department_id;

Their solution:

SELECT first_name, last_name, department_id, department_name 
FROM employees 
JOIN departments USING (department_id);

I'd really appreciate the help.

What is your question? You have a solution that works in the question. — Gordon Linoff
– Gordon Linoff, Commented May 18, 2021 at 1:54

Laurenz Albe · Accepted Answer · 2021-05-18 02:25:46Z

2

Your solution does not work because the field names are ambiguous. Try prefixing like this

SELECT 
  employees.first_name, 
  employees.last_name, 
  departments.department_id, 
  departments.department_name 
FROM employees 
JOIN departments ON departments.department_id = employees.department_id;

The problem does not appear when you use USING, because then there will be only a single department_id column in the join result.

edited May 18, 2021 at 2:25

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

answered May 18, 2021 at 1:08

Aadmaa

9427 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Aadmaa Over a year ago

you might also have to specify the join type. e.g., "INNER JOIN" or "LEFT JOIN" instead of just JOIN.

Karan Kumar Over a year ago

Thanks alot. Since the website does not show the error, I could not figure it out. One Question , isn't JOIN by default mean INNER JOIN ?

Aadmaa Over a year ago

JOIN might well be INNER JOIN in Postgres. That's allowed in some SQL variants for sure; I'm just never sure which.

Gordon Linoff Over a year ago

@Aadmaa . . . JOIN means INNER JOIN. The INNER is totally redundant.

Aadmaa Over a year ago

Thx @GordonLinoff - I didn't realize it was the actual SQL standard. But I confirmed here: contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt "If a <qualified join> is specified and a <join type> is not specified, then INNER is implicit."

Collectives™ on Stack Overflow

PostgreSQL join query issue

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related