The Developer Space

5 0

In our previous articles, we explored the basics of SQL and moved on to more advanced queries. Now, it’s time to dive deeper into one of the most powerful features of SQL: JOINS. Understanding how to join tables is essential for retrieving meaningful data from relational databases. In this guide, we’ll cover different types of JOINs with easy-to-understand explanations and examples.

Articles in the Series

Before reading this article, I recommend that you read the previous articles in this series to understand the basics and to set up the Sample database that we’ll be using for the examples in the article.

SQL for Beginners: Learn the Basics with Examples

SQL for Beginners – Part 2: SELECT Commands with Examples

SQL for Beginners – Part 3: JOINS

SQL for Beginners – Part 4: Mastering Subqueries

Setting up the Sample Database

The examples in these article series were created using PostgreSQL (Database) and DbStudio (IDE). However, you can try out these examples in any Relational Database. You may have to tweak the syntax to make it work with your target database platform.

We created the Users table in the previous article. We’ll reuse this table and create a new Orders table that stores the orders for the user.

All code samples in this article can be accessed here: https://github.com/The-Developer-Space/CodeSamples/blob/main/SQL_for_Beginners

Users Table

The script for the Users table can be found here.

Orders Table

This table has intentionally been made simple and denormalized for explaining joins in this article. Ideally, we should have a separate Products table and store the ProductID in the Orders table. Orders without a UserID wouldn’t exist in a real-world scenario, but it’s shown here as an example to understand how RIGHT JOIN works.

--Create the Orders table
CREATE TABLE Orders (
    OrderID BIGSERIAL PRIMARY KEY,
    UserID INT,
    Product VARCHAR(50),
    Amount DECIMAL(10,2)
);

--Insert Sample Orders
INSERT INTO Orders 
    (UserID, Product, Amount) 
VALUES
((SELECT UserID FROM Users WHERE FirstName='John' AND LastName='Doe'), 'Keyboard', 50),
((SELECT UserID FROM Users WHERE FirstName='Jane' AND LastName='Smith'), 'Mouse', 30),
((SELECT UserID FROM Users WHERE FirstName='John' AND LastName='Doe'), 'Monitor', 150),
((SELECT UserID FROM Users WHERE FirstName='Bob' AND LastName='Brown'), 'Webcam', 70),
((SELECT UserID FROM Users WHERE FirstName='Olivia' AND LastName='Martin'), 'Laptop', 900),
((SELECT UserID FROM Users WHERE FirstName='James' AND LastName='King'), 'Desktop', 1200),
((SELECT UserID FROM Users WHERE FirstName='Amelia' AND LastName='Gonzalez'), 'Digicam', 200),
((SELECT UserID FROM Users WHERE FirstName='Amelia' AND LastName='Gonzalez'), 'Tripod', 30),
(NULL, 'External Drive', 100),
(9999, 'Speaker', 50),
(NULL, 'Microphone', 25);

Once created, you should see the table like this:

SELECT * FROM Orders;

What is a JOIN?

In SQL, a JOIN clause is used to combine rows from two or more tables based on a related column between them. Since relational databases are designed to store data in multiple tables, JOINs help bring that data together to form a complete picture. JOINs help us normalize databases and keep only the data that is required in each individual tables without the need to duplicate them.

The examples in this article discuss joins between two tables. However, you can join as many tables as you want in a single query.

Syntax of JOIN

The basic format begins with a SELECT statement, specifying the columns you want to retrieve, followed by the FROM clause naming the primary table. Then, you use a specific JOIN keyword—such as INNER JOIN, LEFT JOIN, RIGHT JOIN, or FULL JOIN—to indicate the type of join operation. This is followed by the secondary table you want to join with, and an ON clause that defines the matching condition, usually involving primary and foreign keys (e.g., ON Users.UserID = Orders.UserID).

Types of JOIN

There are different types of joins that return different set of results. Let’s take a look at the most common ones

• INNER JOIN
• LEFT (OUTER) JOIN
• RIGHT (OUTER) JOIN
• FULL (OUTER) JOIN
• CROSS JOIN

INNER JOIN

An INNER JOIN returns only the rows where there is a match in both tables based on the specified join condition. It’s the most commonly used join and is ideal when you want to retrieve data that exists in both related tables. For example, if you join a Users table with an Orders table using Users.UserID = Orders.UserID, the result will include only users who have written posts.

SELECT Users.FirstName, Users.LastName, Orders.Product, Orders.Amount
FROM Users
INNER JOIN Orders ON Users.UserID = Orders.UserID;

Output:

Notice that not all Users are listed. Users without a UserID in the Orders table won’t be listed. And Orders with NULL UserIDs and UserIDs that are not in the Users table won’t be listed as well. For example, Order with UserID 9999 is not returned as the user with that ID does not exist in the Users table.

LEFT JOIN (or LEFT OUTER JOIN)

A LEFT JOIN returns all records from the left (first) table and the matching records from the right (second) table. If there is no match, NULL values are returned for columns from the right table. This is useful when you want to see all records from one table regardless of whether they have related records in the second table. For instance, showing all users along with any posts they may have created.

SELECT Users.FirstName, Users.LastName, Orders.Product, Orders.Amount
FROM Users
LEFT JOIN Orders ON Users.UserID = Orders.UserID;

Output:

RIGHT JOIN (or RIGHT OUTER JOIN)

A RIGHT JOIN works the opposite of a LEFT JOIN: it returns all records from the right (second) table and any matching records from the left (first) table. If there is no match, NULLs are returned for the left table’s columns. It’s helpful when the focus is on the right table, such as retrieving all posts and the corresponding user information, even if some posts don’t have an associated user entry.

SELECT Users.FirstName, Users.LastName, Orders.Product, Orders.Amount
FROM Users
RIGHT JOIN Orders ON Users.UserID = Orders.UserID;

Output:

FULL JOIN (or FULL OUTER JOIN)

A FULL OUTER JOIN returns all records when there is a match in either the left or right table. If there is no match, the result will contain NULLs in the place of missing matches from either table. This join is ideal for capturing a complete picture of the data from both tables, including unmatched records on both sides. For example, it shows all users and all orders, regardless of whether they are linked.

SELECT Users.FirstName, Users.LastName, Orders.Product, Orders.Amount
FROM Users
FULL JOIN Orders ON Users.UserID = Orders.UserID;

Note that not all Databases support full joins and the syntax may vary significantly between different database platforms. If your database does not support the FULL JOIN keyword, the same results can be achieved using a UNION of LEFT and RIGHT JOINS.

A UNION simply returns the combined unique results of two separate SELECT statements.

SELECT Users.FirstName, Users.LastName, Orders.Product, Orders.Amount
FROM Users
LEFT JOIN Orders ON Users.UserID = Orders.UserID
UNION
SELECT Users.FirstName, Users.LastName, Orders.Product, Orders.Amount
FROM Users
RIGHT JOIN Orders ON Users.UserID = Orders.UserID;

Output:

CROSS JOIN

A CROSS JOIN returns the Cartesian product of two tables, meaning it pairs every row from the first table with every row from the second table. If the first table has m rows and the second table has n rows, the result will contain m × n rows. CROSS JOINs are rarely used in typical queries unless you specifically need all possible combinations—for example, generating a list of every possible product and color combination from two tables: Users and Orders. Since it doesn’t require a join condition, it’s often used for matrix-style data generation, testing, or combinatorial logic. For example, if there are 25 users and 11 orders, the result will have 25 x 11 = 275 rows.

SELECT Users.FirstName, Users.LastName, Orders.Product, Orders.Amount
FROM Users
CROSS JOIN Orders;

Output:

Conclusion

JOINS are essential for working with relational databases. Whether you’re displaying user orders, combining customer and transaction data, or analyzing relationships across tables, mastering JOINs will give you the power to build more insightful and dynamic applications.

In the next part of this series, we’ll explore subqueries, views, and set operations. Stay tuned and keep practicing!

Enjoyed this article?
Subscribe to The Developer Space Newsletter for more articles like this that explore tools, technologies, and best practices for modern software development.