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:

-- This SQL query retrieves the first name, last name, department ID, and department name for employees using a join operation.

SELECT first_name, -- Selects the first_name column
       last_name, -- Selects the last_name column
       department_id, -- Selects the department_id column
       department_name -- Selects the department_name column
FROM employees -- Specifies the first table from which to retrieve data, in this case, the employees table
JOIN departments USING (department_id); -- Joins the employees table with the departments table using the department_id column

Explanation:

• This SQL query retrieves the first name, last name, department ID, and department name for employees using a join operation.
• The SELECT statement selects the first_name, last_name, department_id, and department_name columns.
• The FROM clause specifies the first table from which to retrieve data, which is the employees table.
• The JOIN keyword is used to join the employees table with the departments table.
• The USING clause specifies the column (department_id) on which the join operation is performed, implying that both tables share the same column name for joining.
• The result set will contain the first name, last name, department ID, and department name for each employee, where department information is obtained by joining the employees and departments tables based on the 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.