Understanding SQL Joins: Examples with Country GDP Data

In SQL, JOIN clauses allow you to combine records from two or more tables based on a related column. In this article, we’ll create tables for years and GDP (Gross Domestic Product) data for three countries (Brazil, the United States, and Japan) and then apply different types of joins to retrieve and display the data in various ways.

Step 1: Create the Tables

We’ll start by creating a Years table and individual GDP tables for each country.

-- Create the Years table
CREATE TABLE Years (
    Year INT PRIMARY KEY
);

-- Insert data into the Years table
INSERT INTO Years (Year) VALUES (2019), (2020), (2021), (2022);

-- Create the GDP_Brazil table
CREATE TABLE GDP_Brazil (
    Year INT,
    GDP DECIMAL(5,2),
    PRIMARY KEY (Year),
    FOREIGN KEY (Year) REFERENCES Years(Year)
);

-- Insert data into GDP_Brazil
INSERT INTO GDP_Brazil (Year, GDP) VALUES
(2019, 2.1),
(2020, 1.5),
(2021, 1.9),
(2022, 2.2);

-- Create the GDP_US table
CREATE TABLE GDP_US (
    Year INT,
    GDP DECIMAL(5,2),
    PRIMARY KEY (Year),
    FOREIGN KEY (Year) REFERENCES Years(Year)
);

-- Insert data into GDP_US
INSERT INTO GDP_US (Year, GDP) VALUES
(2019, 21.4),
(2020, 20.5),
(2021, 21.0),
(2022, 22.0);

-- Create the GDP_Japan table
CREATE TABLE GDP_Japan (
    Year INT,
    GDP DECIMAL(5,2),
    PRIMARY KEY (Year),
    FOREIGN KEY (Year) REFERENCES Years(Year)
);

-- Insert data into GDP_Japan
INSERT INTO GDP_Japan (Year, GDP) VALUES
(2019, 5.2),
(2020, 5.0),
(2021, 5.1),
(2022, 5.3);

Step 2: Examples of Different SQL Joins

Now that we have the data tables, let’s explore different types of joins to see how they affect our query results.

1. INNER JOIN

The INNER JOIN combines tables by returning only the years present in all tables.

SELECT Years.Year, GDP_Brazil.GDP AS GDP_Brazil, GDP_US.GDP AS GDP_US, GDP_Japan.GDP AS GDP_Japan
FROM Years
INNER JOIN GDP_Brazil ON Years.Year = GDP_Brazil.Year
INNER JOIN GDP_US ON Years.Year = GDP_US.Year
INNER JOIN GDP_Japan ON Years.Year = GDP_Japan.Year;

Result:

2. LEFT JOIN

The LEFT JOIN returns all years from the Years table, even if there is no corresponding data in the GDP tables. For years without GDP data, the columns will show NULL.

SELECT Years.Year, GDP_Brazil.GDP AS GDP_Brazil, GDP_US.GDP AS GDP_US, GDP_Japan.GDP AS GDP_Japan
FROM Years
LEFT JOIN GDP_Brazil ON Years.Year = GDP_Brazil.Year
LEFT JOIN GDP_US ON Years.Year = GDP_US.Year
LEFT JOIN GDP_Japan ON Years.Year = GDP_Japan.Year;

3. RIGHT JOIN

The RIGHT JOIN returns all years from the GDP tables, even if there is no matching year in the Years table.

SELECT Years.Year, GDP_Brazil.GDP AS GDP_Brazil, GDP_US.GDP AS GDP_US, GDP_Japan.GDP AS GDP_Japan
FROM Years
RIGHT JOIN GDP_Brazil ON Years.Year = GDP_Brazil.Year
RIGHT JOIN GDP_US ON Years.Year = GDP_US.Year
RIGHT JOIN GDP_Japan ON Years.Year = GDP_Japan.Year;

4. FULL JOIN

The FULL JOIN returns all years from all tables and fills in NULL for non-matching records.

SELECT Years.Year, GDP_Brazil.GDP AS GDP_Brazil, GDP_US.GDP AS GDP_US, GDP_Japan.GDP AS GDP_Japan
FROM Years
FULL JOIN GDP_Brazil ON Years.Year = GDP_Brazil.Year
FULL JOIN GDP_US ON Years.Year = GDP_US.Year
FULL JOIN GDP_Japan ON Years.Year = GDP_Japan.Year;

5. CROSS JOIN

The CROSS JOIN returns the Cartesian product of two tables, meaning every possible combination of rows between the two tables. This join can be useful when analyzing data across different categories or dimensions but should be used cautiously as it can produce a large number of rows.

In this example, we’ll apply a CROSS JOIN to get all combinations of years with each country’s GDP value.

SELECT Years.Year, GDP_Brazil.GDP AS GDP_Brazil, GDP_US.GDP AS GDP_US, GDP_Japan.GDP AS GDP_Japan
FROM Years
CROSS JOIN GDP_Brazil
CROSS JOIN GDP_US
CROSS JOIN GDP_Japan;

Note: The result will include every possible combination of each year and the GDP values for all years in each table, leading to a potentially large dataset.

Summary of SQL Joins

• INNER JOIN: Returns only years present in all tables.
• LEFT JOIN: Returns all years from the Years table, even if there’s no corresponding data in other tables.
• RIGHT JOIN: Returns all years from the GDP tables, even if there’s no corresponding data in the Years table.
• FULL JOIN: Returns all years from all tables, filling in NULL for unmatched records.
• CROSS JOIN: Produces the Cartesian product of two tables, returning every possible row combination.

Using different types of joins allows for flexible combinations of data across tables depending on analysis needs.