PHP MySQL Unions: Combining Results from Multiple Queries

In the world of database management and PHP programming, there often arises a need to combine results from multiple queries. This is where the MySQL UNION operator comes into play, offering a powerful tool for merging result sets from two or more SELECT statements. In this comprehensive guide, we'll dive deep into the concept of UNION in PHP MySQL, exploring its syntax, use cases, and best practices.

Table of Contents

Understanding MySQL UNION

The UNION operator in MySQL allows you to combine the result sets of two or more SELECT statements. It's particularly useful when you need to retrieve data from multiple tables with similar structures or when you want to combine different subsets of data from the same table.

🔑 Key points about UNION:

Each SELECT statement within the UNION must have the same number of columns
The columns in each SELECT statement must have similar data types
The columns in every SELECT statement must be in the same order

Let's start with a basic example to illustrate how UNION works.

Basic UNION Example

Imagine we have two tables in our database: employees and customers. We want to create a list of all names and email addresses from both tables.

<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT name, email FROM employees
        UNION
        SELECT name, email FROM customers";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<table border='1'><tr><th>Name</th><th>Email</th></tr>";
    while($row = $result->fetch_assoc()) {
        echo "<tr><td>".$row["name"]."</td><td>".$row["email"]."</td></tr>";
    }
    echo "</table>";
} else {
    echo "0 results";
}

$conn->close();
?>

In this example, we're selecting the name and email columns from both the employees and customers tables. The UNION operator combines these results into a single result set.

📊 Output might look like this:

Name	Email
John Doe	[email protected]
Jane Smith	[email protected]
Acme Corp	[email protected]
XYZ Inc	[email protected]

Notice how the results from both tables are seamlessly combined into a single list.

UNION vs. UNION ALL

While UNION removes duplicate rows from the result set, UNION ALL includes all rows, including duplicates. This can be useful when you want to preserve all data, even if it's repeated.

Let's modify our previous example to use UNION ALL:

<?php
// ... (connection code remains the same)

$sql = "SELECT name, email FROM employees
        UNION ALL
        SELECT name, email FROM customers";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<table border='1'><tr><th>Name</th><th>Email</th></tr>";
    while($row = $result->fetch_assoc()) {
        echo "<tr><td>".$row["name"]."</td><td>".$row["email"]."</td></tr>";
    }
    echo "</table>";
} else {
    echo "0 results";
}

$conn->close();
?>

Now, if there are any duplicate names or email addresses between the employees and customers tables, they will all be included in the result set.

💡 Pro tip: Use UNION ALL when you're certain there are no duplicates or when you specifically want to include all rows. It's generally faster than UNION because it doesn't need to perform the extra step of removing duplicates.

Ordering UNION Results

When working with UNION, you might want to order the combined results. You can do this by adding an ORDER BY clause at the end of the UNION statement.

<?php
// ... (connection code remains the same)

$sql = "SELECT name, email, 'Employee' as type FROM employees
        UNION
        SELECT name, email, 'Customer' as type FROM customers
        ORDER BY name";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<table border='1'><tr><th>Name</th><th>Email</th><th>Type</th></tr>";
    while($row = $result->fetch_assoc()) {
        echo "<tr><td>".$row["name"]."</td><td>".$row["email"]."</td><td>".$row["type"]."</td></tr>";
    }
    echo "</table>";
} else {
    echo "0 results";
}

$conn->close();
?>

In this example, we've added a 'type' column to distinguish between employees and customers, and we're ordering the results by name.

📊 The output might look like this:

Name	Email	Type
Acme Corp	[email protected]	Customer
Jane Smith	[email protected]	Employee
John Doe	[email protected]	Employee
XYZ Inc	[email protected]	Customer

Complex UNION Example

Now, let's look at a more complex example where we combine data from three different tables: employees, customers, and suppliers.

<?php
// ... (connection code remains the same)

$sql = "SELECT name, email, phone, 'Employee' as type
        FROM employees
        WHERE department = 'Sales'
        UNION
        SELECT company_name as name, email, phone, 'Customer' as type
        FROM customers
        WHERE credit_limit > 10000
        UNION
        SELECT company_name as name, email, phone, 'Supplier' as type
        FROM suppliers
        WHERE product_category = 'Electronics'
        ORDER BY type, name";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<table border='1'><tr><th>Name</th><th>Email</th><th>Phone</th><th>Type</th></tr>";
    while($row = $result->fetch_assoc()) {
        echo "<tr><td>".$row["name"]."</td><td>".$row["email"]."</td><td>".$row["phone"]."</td><td>".$row["type"]."</td></tr>";
    }
    echo "</table>";
} else {
    echo "0 results";
}

$conn->close();
?>

In this example, we're combining:

Sales employees
High-value customers (credit limit > 10000)
Electronics suppliers

We're also aliasing the company_name column as name in the customers and suppliers tables to match the structure of the employees table.

📊 The output might look like this:

Name	Email	Phone	Type
Jane Smith	[email protected]	555-1234	Employee
John Doe	[email protected]	555-5678	Employee
Acme Corp	[email protected]	555-2468	Customer
XYZ Inc	[email protected]	555-1357	Customer
Tech Supplies Ltd	[email protected]	555-9876	Supplier

Best Practices and Performance Considerations

When using UNION in PHP MySQL, keep these best practices in mind:

🚀 Use UNION ALL instead of UNION when you don't need to remove duplicates. It's faster because it skips the duplicate removal step.
📏 Ensure that the number and order of columns in each SELECT statement match exactly.
🔍 Use WHERE clauses in individual SELECT statements to filter data before the UNION operation. This can improve performance by reducing the amount of data that needs to be processed.
📊 When ordering results, use column names or positions that are valid for all SELECT statements in the UNION.
💾 For large datasets, consider creating temporary tables or using subqueries to optimize performance.

Error Handling in UNION Queries

When working with UNION queries in PHP, it's crucial to implement proper error handling. Let's modify our complex example to include error handling:

<?php
// ... (connection code remains the same)

$sql = "SELECT name, email, phone, 'Employee' as type
        FROM employees
        WHERE department = 'Sales'
        UNION
        SELECT company_name as name, email, phone, 'Customer' as type
        FROM customers
        WHERE credit_limit > 10000
        UNION
        SELECT company_name as name, email, phone, 'Supplier' as type
        FROM suppliers
        WHERE product_category = 'Electronics'
        ORDER BY type, name";

try {
    $result = $conn->query($sql);

    if ($result === false) {
        throw new Exception("Query failed: " . $conn->error);
    }

    if ($result->num_rows > 0) {
        echo "<table border='1'><tr><th>Name</th><th>Email</th><th>Phone</th><th>Type</th></tr>";
        while($row = $result->fetch_assoc()) {
            echo "<tr><td>".$row["name"]."</td><td>".$row["email"]."</td><td>".$row["phone"]."</td><td>".$row["type"]."</td></tr>";
        }
        echo "</table>";
    } else {
        echo "0 results";
    }
} catch (Exception $e) {
    echo "An error occurred: " . $e->getMessage();
    // Log the error or perform other error handling tasks
} finally {
    $conn->close();
}
?>

In this version, we've wrapped our query execution and result processing in a try-catch block. If the query fails, it will throw an exception with the specific error message from MySQL. This approach allows for more robust error handling and can help in debugging complex UNION queries.

Conclusion

MySQL UNION in PHP is a powerful tool for combining results from multiple queries. It allows you to merge data from different tables or even different databases, providing a flexible way to retrieve and present information. By understanding the syntax, use cases, and best practices of UNION, you can write more efficient and effective database queries in your PHP applications.

Remember, the key to mastering UNION is practice. Experiment with different scenarios, combine various types of data, and always consider the performance implications of your queries. With these skills, you'll be well-equipped to handle complex data retrieval tasks in your PHP MySQL projects.

🌟 Happy coding, and may your UNIONs always be successful! 🌟