NATURAL join and its risks in PostgreSQL - Mini Project: Build & Apply

Understanding NATURAL JOIN and Its Risks in PostgreSQL

📖 Scenario: You are working with two tables in a PostgreSQL database for a small bookstore. One table stores authors and the other stores books. Both tables have a column named author_id. You want to combine data from these tables using a NATURAL JOIN to see which books belong to which authors.However, you also want to understand the risks of using NATURAL JOIN when tables have multiple columns with the same name.

🎯 Goal: Build SQL queries step-by-step to perform a NATURAL JOIN between the authors and books tables. Then, add a second column with the same name in both tables to see how NATURAL JOIN behaves and why it can be risky.

📋 What You'll Learn

Create two tables: authors and books with specified columns

Insert exact sample data into both tables

Write a NATURAL JOIN query to combine the tables

Add a second common column to both tables and observe the effect on NATURAL JOIN

💡 Why This Matters

🌍 Real World

In real databases, tables often share multiple column names. Understanding NATURAL JOIN helps avoid bugs when combining data.

💼 Career

Database developers and analysts must write safe and clear join queries to ensure correct data results and avoid unexpected data loss or duplication.

1

Create tables and insert initial data

Create a table called authors with columns author_id (integer) and author_name (text). Create a table called books with columns book_id (integer), title (text), and author_id (integer). Insert these exact rows into authors: (1, 'Alice Walker'), (2, 'Mark Twain'). Insert these exact rows into books: (101, 'The Color Purple', 1), (102, 'Adventures of Huckleberry Finn', 2).

PostgreSQL

-- Create authors table
-- Create books table
-- Insert data into authors
-- Insert data into books

Need a hint?

Use CREATE TABLE statements for both tables. Then use INSERT INTO with the exact values given.

2

Write a NATURAL JOIN query

Write a SQL query that uses NATURAL JOIN to combine authors and books tables. Select all columns from the result. Use the exact table names authors and books.

PostgreSQL

SELECT * FROM authors
-- Add NATURAL JOIN with books below

Need a hint?

Use SELECT * FROM authors NATURAL JOIN books; to join on the common column author_id.

3

Add a second common column to both tables

Alter the authors table to add a new column called country (text). Alter the books table to add a new column called country (text). Update the authors table to set country to 'USA' for all rows. Update the books table to set country to 'USA' for all rows.

PostgreSQL

ALTER TABLE authors ADD COLUMN country TEXT;
-- Add country column to books and update values below

Need a hint?

Use ALTER TABLE to add the country column to both tables. Then use UPDATE to set the value to 'USA'.

4

Run NATURAL JOIN again and observe the risk

Write a SQL query that uses NATURAL JOIN to join authors and books again after adding the country column. Select all columns from the result. Use the exact table names authors and books. This will show how NATURAL JOIN joins on both author_id and country columns, which can cause unexpected results.

PostgreSQL

SELECT * FROM authors NATURAL JOIN books; -- Observe the join on both author_id and country

Need a hint?

Run the same NATURAL JOIN query again after adding the country column to see how it affects the join.