Interview SQL, SQL Indexes - Yash-777/MyWorld GitHub Wiki
In SQL, UNION and UNION ALL are used to combine the results of two or more SELECT statements.
They look similar, but they behave differently when it comes to duplicate rows. onecompiler Example
🔹 UNIONUse UNION → When you need unique results |
🔹 UNION ALLUse UNION ALL → When duplicates are acceptable and performance matters |
|---|---|
🔹 UNION
* Combines results of multiple SELECT queries
* Removes duplicate rows
* Performs an implicit DISTINCT
* Slightly slower because it checks for duplicates |
🔹 UNION ALL
* Combines results of multiple SELECT queries
* Keeps duplicate rows
* Faster (no duplicate checking) |
⚠️ Important Rules for Both
* Each SELECT must have the same number of columns
* Columns must have compatible data types
* Column names in result come from the first SELECT
-- create -- insert
CREATE TABLE customers ( INSERT INTO customers VALUES
id INT, (1, 'Alice', 'New York'),
name VARCHAR(50), (2, 'Bob', 'Chicago'),
city VARCHAR(50) (3, 'Charlie', 'New York');
);
CREATE TABLE suppliers ( INSERT INTO suppliers VALUES
id INT, (1, 'SupplyCo', 'HYD'),
name VARCHAR(50), (2, 'ABC Corp', 'Chicago'),
city VARCHAR(50) (3, 'SupplyCo', 'New York'),
); (4, 'XYZ Ltd', 'Los Angeles'); |
|
-- 🔹 Using UNION (Removes Duplicates)
SELECT city FROM customers
UNION
SELECT city FROM suppliers;
-- 👉 Even though New York appears multiple times, it shows only once.
-- Output:
+-------------+
| city |
+-------------+
| New York |
| Chicago |
| HYD |
| Los Angeles |
+-------------+
SELECT id, city FROM customers
UNION
SELECT id, city FROM suppliers;
-- Output:
+------+-------------+
| id | city |
+------+-------------+
| 1 | New York |
| 2 | Chicago |
| 3 | New York |
| 1 | HYD |
| 4 | Los Angeles |
+------+-------------+ |
-- 🔹 Using UNION ALL (Keeps Duplicates)
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;
-- Output:
+-------------+
| city |
+-------------+
| New York |
| Chicago |
| New York |
| HYD |
| Chicago |
| New York |
| Los Angeles |
+-------------+
SELECT id, city FROM customers
UNION ALL
SELECT id, city FROM suppliers;
-- Output:
+------+-------------+
| id | city |
+------+-------------+
| 1 | New York |
| 2 | Chicago |
| 3 | New York |
| 1 | HYD |
| 2 | Chicago |
| 3 | New York |
| 4 | Los Angeles |
+------+-------------+ |
MySQL can create composite indexes (that is, indexes on multiple columns). An index may consist of up to 16 columns. For certain data types, you can index a prefix of the column (see Section 10.3.5, “Column Indexes”).
The order of columns in a MySQL multi-column index is crucial for query performance due to the "leftmost prefix" principle. This principle dictates that MySQL can utilize a composite index for queries that filter or sort on the leading (leftmost) column(s) of the index.
Key considerations for ordering columns in a multi-column index:
Leftmost Prefix Matching:
MySQL can use the index if the query's WHERE clause or ORDER BY clause starts with the first column(s) defined in the index. For example, an index on (col1, col2, col3) can be used for queries filtering on (col1), (col1, col2), or (col1, col2, col3).
SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;It cannot directly use the index for queries filtering only on col2 or col2, col3 independently.
SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;