PostgreSQL JOINS: Find the addresses of all the departments

Last update on October 27 2025 12:14:49 (UTC/GMT +8 hours)

1. Write a query to find the addresses, including location_id, street_address, city, state_province and country_name of all the departments.

Sample Solution:

Code:

-- This SQL query retrieves location details along with associated country and department information using natural joins.

SELECT location_id, -- Selects the location_id column
       street_address, -- Selects the street_address column
city, -- Selects the city column
       state_province, -- Selects the state_province column
       country_name, -- Selects the country_name column
       department_name -- Selects the department_name column
FROM locations -- Specifies the first table from which to retrieve data, in this case, the locations table
NATURAL JOIN countries -- Performs a natural join with the countries table to include associated country information
NATURAL JOIN departments; -- Performs a natural join with the departments table to include associated department information

Explanation:

This SQL query retrieves location details along with associated country and department information.
The SELECT statement selects various columns including location_id, street_address, city, state_province, country_name, and department_name.
The FROM clause specifies the first table from which to retrieve data, which is the locations table.
The NATURAL JOIN keywords are used to perform natural joins with the countries and departments tables.
Natural join matches the columns with the same name in both tables and includes only the matching rows from both tables.
As a result, the query combines data from the locations, countries, and departments tables based on the matching columns (country_id and department_id) without the need to specify join conditions explicitly.
The result set will contain location details along with associated country and department information for each location.

Sample table: locations

Sample table: countries

Sample table: departments

Output:

pg_exercises=# SELECT location_id, street_address, city, state_province, country_name,department_name
pg_exercises=# FROM locations
pg_exercises=# NATURAL JOIN countries
pg_exercises=# NATURAL JOIN departments;
 location_id |    street_address     |        city         | state_province |       country_name       |   department_name
-------------+-----------------------+---------------------+----------------+--------------------------+----------------------
        1800 | 147 Spadina Ave       | Toronto             | Ontario        | Canada                   | Marketing
        2700 | Schwanthalerstr. 7031 | Munich              | Bavaria        | Germany                  | Public Relations
        2400 | 8204 Arthur St        | London              |                | United Kingdom           | Human Resources
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Payroll
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Recruiting
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Retail Sales
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Government Sales
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | IT Helpdesk
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | NOC
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | IT Support
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Operations
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Contracting
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Construction
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Manufacturing
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Benefits
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Shareholder Services
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Control And Credit
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Corporate Tax
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Treasury
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Accounting
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Finance
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Executive
        1400 | 2014 Jabberwocky Rd   | Southlake           | Texas          | United States of America | IT
        1500 | 2011 Interiors Blvd   | South San Francisco | California     | United States of America | Shipping
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Purchasing
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Administration
(26 rows)

Relational Algebra Expression:

Relational Algebra Tree:

Go to:

PREV : PostgreSQL JOINS - Exercises, Practice, Solution
NEXT : 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.

Practice Online

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