PostgreSQL JOINS: Make a join with employees and departments table to find the name, department ID and department name

2. Write a query to make a join with employees and departments table to find the name of the employee, including first_name and last name, department ID and name of departments.

Sample Solution:

Code:

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

Sample table: employees

Sample table: departments

Output:

pg_exercises=# SELECT first_name, last_name, department_id, department_name
pg_exercises-# FROM employees
pg_exercises-# JOIN departments USING (department_id);
 first_name  |  last_name  | department_id | department_name
-------------+-------------+---------------+------------------
 Alexander   | Hunold      |            60 | IT
 Bruce       | Ernst       |            60 | IT
 David       | Austin      |            60 | IT
 Valli       | Pataballa   |            60 | IT
 Diana       | Lorentz     |            60 | IT
 Den         | Raphaely    |            30 | Purchasing
 Alexander   | Khoo        |            30 | Purchasing
 Shelli      | Baida       |            30 | Purchasing
 Sigal       | Tobias      |            30 | Purchasing
 Steven      | King        |            90 | Executive
 Neena       | Kochhar     |            90 | Executive
 Guy         | Himuro      |            30 | Purchasing
 Karen       | Colmenares  |            30 | Purchasing
 Jason       | Mallin      |            50 | Shipping
 Michael     | Rogers      |            50 | Shipping
 Ki          | Gee         |            50 | Shipping
 Hazel       | Philtanker  |            50 | Shipping
 Stephen     | Stiles      |            50 | Shipping
 John        | Seo         |            50 | Shipping
 Joshua      | Patel       |            50 | Shipping
 Trenna      | Rajs        |            50 | Shipping
...          | ...         |           ... | ...
 Susan       | Mavris      |            40 | Human Resources
 Hermann     | Baer        |            70 | Public Relations
 Shelley     | Higgins     |           110 | Accounting
 William     | Gietz       |           110 | Accounting
(105 rows)

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a query to find the addresses, including location_id, street_address, city, state_province and country_name of all the departments.
Next: Write a SQL query to make a join with three tables employees, departments and locations to find the name, including first_name and last_name, jobs, department name and ID, of the employees working in London.