Summary of the difference between UNION and UNION ALL in SQL

In SQL,UNIONandUNION ALLBoth are used to combine two or more result sets into one result set, but they differ significantly in processing duplicate data. Here are their detailed differences:

1. UNION

UNIONOperators are used to merge the result sets of two or more SELECT statements and automatically remove duplicate rows in the result set.

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

Go to the heavy: UNIONAutomatically remove duplicate rows and return only the unique rows.
performance: Due to the need to deduplication operation,UNIONThe performance is relatively low, especially on large data sets.
use: Used when you need to merge the result set and you do not want the result set to contain duplicate rows.

2. UNION ALL

UNION ALLOperators are used to merge the result sets of two or more SELECT statements, without removing duplicate rows, and return all results, including duplicate rows.

SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;

Go to the heavy: UNION ALLDon't remove duplicate rows and return all results.
performance: Since no deduplication operation is performed,UNION ALLThe performance is relatively high.
use: Used when you need to merge the result set and you want to include all rows, including duplicate rows.

Sample code

import ;
import ;
import ;
import ;
import ;

public class UnionExample {
    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/yourdatabase";
    private static final String JDBC_USER = "yourusername";
    private static final String JDBC_PASSWORD = "yourpassword";

    public static void main(String[] args) {
        try (Connection conn = (JDBC_URL, JDBC_USER, JDBC_PASSWORD);
             Statement stmt = ()) {

            // Sample data preparation            String createTableSQL1 = "CREATE TABLE IF NOT EXISTS table1 (id INT, name VARCHAR(255))";
            String createTableSQL2 = "CREATE TABLE IF NOT EXISTS table2 (id INT, name VARCHAR(255))";
            (createTableSQL1);
            (createTableSQL2);

            String insertDataSQL1 = "INSERT INTO table1 (id, name) VALUES (1, 'Alice'), (2, 'Bob')";
            String insertDataSQL2 = "INSERT INTO table2 (id, name) VALUES (2, 'Bob'), (3, 'Charlie')";
            (insertDataSQL1);
            (insertDataSQL2);

            // Use UNION            String unionSQL = "SELECT id, name FROM table1 UNION SELECT id, name FROM table2";
            try (ResultSet rs = (unionSQL)) {
                ("Results of UNION:");
                while (()) {
                    int id = ("id");
                    String name = ("name");
                    ("ID: " + id + ", Name: " + name);
                }
            }

            // Use UNION ALL            String unionAllSQL = "SELECT id, name FROM table1 UNION ALL SELECT id, name FROM table2";
            try (ResultSet rs = (unionAllSQL)) {
                ("Results of UNION ALL:");
                while (()) {
                    int id = ("id");
                    String name = ("name");
                    ("ID: " + id + ", Name: " + name);
                }
            }

            // Clean up sample data            ("DROP TABLE IF EXISTS table1");
            ("DROP TABLE IF EXISTS table2");

        } catch (SQLException e) {
            ();
        }
    }
}

In the above code, it is demonstrated how to execute using JDBCUNIONandUNION ALLoperate. Please adjust the database connection string, username, password, and SQL statements as needed.

Summarize

UNION: Merge the result set and remove duplicate rows.
UNION ALL: Merge the result set and keep all duplicate rows.

This is the end of this article about the difference between UNION and UNION ALL in SQL. For more information about the differences between UNION and UNION ALL, please search for my previous articles or continue browsing the related articles below. I hope you will support me in the future!