Also Like

Database12 SQL12
📁 last Posts
0

DB --> LEC 7 --> Topics --> 52 --> 58

Below is a detailed explanation of the SQL concepts you mentioned, focusing on their usage in MySQL. Each concept is explained with its purpose, syntax, examples, and key details.

52. SQL UNION

Purpose: The UNION operator combines the result sets of two or more SELECT statements into a single result set. It removes duplicate rows, ensuring only unique rows are returned.

Key Characteristics:

  • The SELECT statements must have the same number of columns.
  • The corresponding columns must have compatible data types.
  • The column names in the result set are taken from the first SELECT statement.
  • By default, UNION performs a DISTINCT operation to eliminate duplicates.

Syntax:

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

Example: Suppose you have two tables, employees and contractors, with similar structures.


CREATE TABLE employees1 (
    id INT,
    name VARCHAR(50)
);
INSERT INTO employees VALUES (1, 'Alice'), (2, 'KE RO'), (3, 'Charlie');

-- contractors table
CREATE TABLE employees2 (
    id INT,
    name VARCHAR(50)
);
INSERT INTO contractors VALUES (2, 'Bob'), (4, 'David');

-- UNION query
SELECT name FROM employees
UNION
SELECT name FROM contractors;

Output:

name
Alice
Bob
Charlie
David

Explanation:

  • The result combines names from both tables.
  • "Bob" appears only once because UNION removes duplicates.

Performance Note:

  • UNION involves a sorting operation to remove duplicates, which can be slower for large datasets.

53. SQL UNION ALL

Purpose: The UNION ALL operator also combines the result sets of two or more SELECT statements, but it does not remove duplicates. This makes it faster than UNION.

Key Characteristics:

  • Same requirements as UNION (same number of columns, compatible data types).
  • Retains all rows, including duplicates.
  • Faster than UNION because it skips the duplicate removal step.

Syntax:

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

Example: Using the same employees and contractors tables:

SELECT name FROM employees
UNION ALL
SELECT name FROM contractors;

Output:

name
Alice
Bob
Charlie
Bob
David

Explanation:

  • Unlike UNION, "Bob" appears twice because UNION ALL does not eliminate duplicates.

Use Case:

  • Use UNION ALL when you know there are no duplicates or when duplicates are acceptable, as it is more efficient.

54. SQL UNION vs. UNION ALL

Comparison:

Feature UNION UNION ALL
Duplicate Handling Removes duplicates Retains duplicates
Performance Slower (due to sorting) Faster (no sorting)
Result Set Unique rows only All rows, including duplicates
Use Case When unique results are needed When speed or duplicates are acceptable

Example:

-- UNION
SELECT name FROM employees
UNION
SELECT name FROM contractors;
-- Output: Alice, Bob, Charlie, David (4 rows, duplicates removed)

-- UNION ALL
SELECT name FROM employees
UNION ALL
SELECT name FROM contractors;
-- Output: Alice, Bob, Charlie, Bob, David (5 rows, duplicates retained)

Choosing Between Them:

  • Use UNION when you need a distinct result set.
  • Use UNION ALL for better performance when duplicates are not a concern.

MySQL Note:

  • MySQL optimizes UNION ALL better than UNION because it avoids the overhead of duplicate elimination.

55. The SQL INTERSECT Operator

Purpose: The INTERSECT operator returns only the rows that are common to the result sets of two SELECT statements.

Key Characteristics:

  • Like UNION, it removes duplicates from the final result.
  • The SELECT statements must have the same number of columns with compatible data types.

MySQL Note:

  • MySQL does not natively support the INTERSECT operator. However, you can achieve the same result using INNER JOIN or IN/EXISTS clauses.

56. The SQL EXCEPT Operator

Purpose: The EXCEPT operator returns rows from the first SELECT statement that are not present in the second SELECT statement.

Key Characteristics:

  • Removes duplicates from the result set.
  • Requires the same number of columns with compatible data types.

MySQL Note:

  • MySQL does not support the EXCEPT operator natively. You can simulate it using LEFT JOIN with a NULL check or NOT IN/NOT EXISTS.

57. SQL CASE

Purpose: The CASE statement provides conditional logic in SQL queries, allowing you to return different values based on conditions.

Key Characteristics:

  • Acts like an IF-THEN-ELSE structure.
  • Can be used in SELECT, WHERE, ORDER BY, and other clauses.
  • Two forms: Simple CASE (compares a single expression) and Searched CASE (evaluates multiple conditions).

Syntax (Simple CASE):

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    [ELSE resultN]
END

Syntax (Searched CASE):

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    [ELSE resultN]
END

Example (Simple CASE):

SELECT name,
       CASE id
           WHEN 1 THEN 'Senior'
           WHEN 2 THEN 'Junior'
           ELSE 'Intern'
       END AS position
FROM employees;

Output:

name    position
Alice   Senior
Bob     Junior
Charlie Intern

Example (Searched CASE):

SELECT name,
       CASE
           WHEN id <= 2 THEN 'Experienced'
           WHEN id > 2 THEN 'New'
       END AS experience
FROM employees;

Output:

name    experience
Alice   Experienced
Bob     Experienced
Charlie New

MySQL Note:

  • MySQL fully supports CASE in all clauses, and it’s commonly used for dynamic reporting or data transformation.

58. SQL CASE With ELSE

Purpose: The ELSE clause in a CASE statement specifies a default value to return when none of the WHEN conditions are met.

Key Characteristics:

  • Optional but recommended to handle unexpected cases.
  • If ELSE is omitted and no conditions match, NULL is returned.

Syntax:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

Example:

SELECT name,
       CASE
           WHEN id = 1 THEN 'Manager'
           WHEN id = 2 THEN 'Developer'
           ELSE 'Other'
       END AS role
FROM employees;

Output:

name    role
Alice   Manager
Bob     Developer
Charlie Other

Explanation:

  • "Alice" and "Bob" match specific conditions.
  • "Charlie" does not match any condition, so the ELSE clause assigns "Other".

Use Case:

  • Use ELSE to avoid NULL values in the result set and ensure all rows have a meaningful value.

MySQL Note:

  • MySQL handles CASE with ELSE efficiently, and it’s widely used in data cleaning and reporting.

Summary Table

Operator/Statement Purpose MySQL Support Key Feature
UNION Combines unique rows from multiple queries Yes Removes duplicates
UNION ALL Combines all rows, including duplicates Yes Faster, retains duplicates
INTERSECT Returns common rows No (use JOIN/IN) Finds overlapping data
EXCEPT Returns rows in first query not in second No (use LEFT JOIN/NOT IN) Finds exclusive data
CASE Conditional logic for value transformation Yes Supports simple/searched forms
CASE with ELSE Adds default value to CASE Yes Prevents NULL for unmatched cases

You may like these posts

Comments