Joining Two Tables Without a Relationship in SQL

=====================================================

As a database developer, you’ve likely encountered situations where joining two tables without a relationship seems like an insurmountable task. However, with the right approach and understanding of SQL’s cross join feature, you can achieve your desired results.

In this article, we’ll explore how to join two tables without a direct relationship using PostgreSQL as our database management system.

Understanding Cross Joins

A cross join is an ANSI-standard SQL join operation that produces the Cartesian product of two tables. In simpler terms, it creates a new table with each row from one table combined with each row from the other table.

For example, let’s say we have two tables: Customers and Years.

-- Create the Customers table
CREATE TABLE Customers (
    ID SERIAL PRIMARY KEY,
    Name VARCHAR(255)
);

-- Insert some data into the Customers table
INSERT INTO Customers (Name) VALUES ('John Doe'), ('Jane Smith');

-- Create the Years table
CREATE TABLE Years (
    ID SERIAL PRIMARY KEY,
    year INTEGER
);

-- Insert some data into the Years table
INSERT INTO Years (year) VALUES (2015), (2016), (2017);

To get a list of all customers with their respective years, we can use a cross join:

SELECT * FROM Customers CROSS JOIN Years;

This will produce a new table with each customer paired with each year.

Joining Tables Without a Relationship Using Cross Joins

Now, let’s discuss how to join two tables without a direct relationship using cross joins. Suppose we have three tables: Orders, Customers, and Years.

-- Create the Orders table
CREATE TABLE Orders (
    ID SERIAL PRIMARY KEY,
    Customer_ID INTEGER,
    YEAR_ID INTEGER,
    FOREIGN KEY (Customer_ID) REFERENCES Customers(ID),
    FOREIGN KEY (YEAR_ID) REFERENCES Years(ID)
);

-- Insert some data into the Orders table
INSERT INTO Orders (Customer_ID, YEAR_ID) VALUES (1, 2015), (1, 2016), (2, 2017);

In this example, we have a many-to-many relationship between Orders and both Customers and Years. We want to join the Orders table with the Customers and Years tables without using any relationships.

To achieve this, we can use cross joins as follows:

SELECT 
    C.ID AS CustomerID,
    C.Name AS CustomerName,
    Y.year AS Year,
    O.ID AS OrderID
FROM Customers C
CROSS JOIN Years Y
LEFT JOIN Orders O ON C.ID = O.Customer_ID AND O.YEAR_ID = Y.ID;

This will produce a new table with each customer paired with each year, along with their respective orders if they have any.

Handling NULL Values

When using cross joins, we need to handle the possibility of NULL values in our results. In this case, since we’re left joining Orders with Customers and Years, we’ll get NULL values for customers who don’t have any orders or years that they belong to.

To fix this, we can use the COALESCE function to replace NULL values with a specific value:

SELECT 
    C.ID AS CustomerID,
    C.Name AS CustomerName,
    Y.year AS Year,
    COALESCE(O.ID, 'No Order') AS OrderID
FROM Customers C
CROSS JOIN Years Y
LEFT JOIN Orders O ON C.ID = O.Customer_ID AND O.YEAR_ID = Y.ID;

This will replace NULL values with the string 'No Order'.

Grouping Results by Customer and Year

Sometimes, we want to group our results by customer and year, rather than getting a separate row for each combination. We can achieve this using the GROUP BY clause:

SELECT 
    C.ID AS CustomerID,
    C.Name AS CustomerName,
    Y.year AS Year,
    COUNT(O.ID) AS NrOfOrders
FROM Customers C
CROSS JOIN Years Y
LEFT JOIN Orders O ON C.ID = O.Customer_ID AND O.YEAR_ID = Y.ID
GROUP BY C.ID, C.Name, Y.Year;

This will produce a new table with the number of orders for each customer and year, grouped by customer and year.

Conclusion

Joining two tables without a relationship in SQL can be achieved using cross joins. By understanding how to use cross joins and handle NULL values, we can create powerful queries that combine data from multiple tables.

In this article, we’ve discussed the basics of cross joins, how to join tables without relationships using them, and some common techniques for handling NULL values and grouping results.

Last modified on 2023-08-29