SQL Multiple Join from 2 Tables to 1

Joining multiple tables in a single query can be a complex task, especially when you’re working with different types of joins. In this article, we’ll explore the concept of joining two tables to one table and provide examples of how to achieve this using SQL.

Understanding Joins

Before we dive into the details of multiple joins, let’s first understand what a join is. A join in SQL is used to combine rows from two or more tables based on a related column between them. There are several types of joins, including:

• Inner Join: Returns only the rows that have a match in both tables.
• Left Join (or Left Outer Join): Returns all the rows from the left table and the matching rows from the right table. If there’s no match, the result set will contain null values for the right table columns.
• Right Join (or Right Outer Join): Similar to a left join but returns all the rows from the right table and the matching rows from the left table.
• Full Outer Join: Returns all the rows from both tables. If there’s no match, the result set will contain null values for the column that doesn’t exist in either table.

The Problem with UNION

The original code uses the UNION operator to combine two separate queries. However, this approach has a major drawback: it can lead to duplicate rows and makes it difficult to manage complex joins.

Let’s analyze the original query:

SELECT w.*, m.vin AS 'workbook_vin'
FROM workbook w
LEFT JOIN machines m ON m.id = w.machine_id

UNION

SELECT w.*, s.full_name AS 'user_full_name'
FROM workbook w
LEFT JOIN suser s ON s.id = w.user_id

As you can see, the UNION operator is used to combine two separate queries. However, this approach has a few issues:

• Duplicate rows: When using UNION, the resulting table will contain duplicate rows. For example, if there are multiple rows in both tables that match the join conditions, the result set will contain multiple identical rows.
• Lack of control: Using UNION makes it difficult to manage complex joins. You have to carefully consider which columns to include and how to handle duplicates.

A Better Approach

As mentioned earlier, joining both tables in a single query without using UNION can be a better approach. Here’s an example:

SELECT
    w.*,
    m.vin AS 'workbook_vin',
    s.full_name AS 'user_full_name'
FROM
    workbook w
    LEFT JOIN machines m ON m.id = w.machine_id
    LEFT JOIN suser s ON s.id = w.user_id

In this example, we’re joining the machines and susers tables with the workbook table using left joins. This approach allows us to include all columns from both tables and avoid duplicate rows.

How It Works

So, how does this query work? Let’s break it down:

• The first part of the query (SELECT w.*) selects all columns from the workbook table.
• The second part of the query (LEFT JOIN machines m ON m.id = w.machine_id) joins the machines table with the workbook table using a left join. This means that we’ll include all rows from both tables, even if there’s no match between them.
• The third part of the query (LEFT JOIN suser s ON s.id = w.user_id) does the same thing as the second part but for the susers table.

Benefits

Using this approach has several benefits:

• Fewer duplicate rows: By joining both tables in a single query, we avoid the issue of duplicate rows that comes with using UNION.
• Better control: We have full control over which columns to include and how to handle duplicates.
• Easier maintenance: The resulting query is easier to maintain and modify than a complex union-based query.

Conclusion

Joining multiple tables in a single query can be a powerful technique for combining data from different sources. By using left joins and selecting all columns from both tables, we can create a robust and efficient query that avoids duplicate rows and provides better control over the results.

Last modified on 2023-06-14