PostgreSQL JOINS: Find the addresses of all the departments

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:

Practice Online

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

Previous: 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.