Advanced SQL Queries: Set Operators, Range Filtering, and Query Optimization

Objectives

  • Comprehensive review of core SQL concepts and join clauses.
  • Introduction to set operators: UNION, INTERSECT, and MINUS.
  • Methods for collecting and filtering ranges of data using operators such as LIKE, IN, OR, BETWEEN, ANY, SOME, and ALL.

SQL Joins and Query Structure Review

  • Join Clauses Specification:
    • Join condition clauses (e.g., matching attributes like table1.attribute = table2.attribute) are specified immediately following the ON keyword.
  • Standard Joined Columns:
    • Joins are typically executed between Primary Keys (PKs) and Foreign Keys (FKs).
    • Example: customers.customer_id = orders.customer_id.
  • Table Declarations:
    • All tables participating in a join are listed within the FROM clause.
  • Join Capacity Limits:
    • There is no maximum limit to the number of tables that can be joined in a single SQL query.
  • ANSI Table Sequencing:
    • In ANSI notation, joined tables must be declared next to each other in a logical, cascading order.
  • Nested Subquery Placement:
    • Nested queries typically appear inside the WHERE clause to filter outer query results.
  • Negative Evaluation Order:
    • Statement ordering is critical when formulating negative queries (e.g., identifying customers who have not placed any orders).
    • In such queries, the parent table CUSTOMERS must appear first, followed by the child table ORDERS.

Set Operators: Union, Intersect, and Minus

  • General Purpose:
    • Set operators are utilized to combine the output of 22 or more SELECT statements into a single unified result set.
  • Fundamental Structural Rules:
    • Column Matching: Each constituent SELECT statement must project the exact same number of columns.
    • Datatype Compatibility: Corresponding columns across all queries must possess matching or compatible data types.
  • Heading and Projection Rules:
    • Column headers in the final output are determined exclusively by the names defined in the first SELECT statement.
  • Supported Types:
    • Compatible with both simple primitive data types and object types.
  • Performance and Usage Considerations:
    • Processing speed is slower compared to relational joins due to dataset comparison and deduplication overhead.
    • Set operations tend to be overused by developers who lack an understanding of relational database structures and proper join mechanics.

UNION Operator

  • Definition and Mechanism:
    • Adds 22 distinct data sets together into a combined set.
    • Automatically eliminates duplicate tuples, displaying only distinct data rows.
  • Evaluation Characteristics:
    • Commutative operation: The sequence/order of the individual SELECT statements does not affect the final result set.
    • Functions similarly to a relational FULL OUTER JOIN.
  • Syntax Structure:
  SELECT column, column FROM tablename
  UNION
  SELECT column, column FROM tablename;
  ```
- **Associated Script Reference:** Defined within `MoreQueries.sql`.

![Diagram of a UNION operation combining two datasets](https://assets.knowt.com/pdf-flow-prod/05cca977-ffca-4b61-bf38-3571fd0d4a47-figures/0.png)

# INTERSECT Operator

- **Definition and Mechanism:**
  - Evaluates both datasets and returns only the intersecting (overlapping) values present in both queries.
- **Evaluation Characteristics:**
  - Commutative operation: The order of the `SELECT` statements does not alter the output.
  - Functions similarly to a relational `INNER JOIN`.
- **Syntax Structure:**

sql SELECT column, column FROM tablename INTERSECT SELECT column, column FROM tablename;   ```

  • Associated Script Reference: Defined within MoreQueries.sql.

Diagram of an INTERSECT operation showing the overlapping region

MINUS Operator

  • Definition and Mechanism:
    • Subtracts (takes away) the result set of the second query from the result set of the first query.
  • Evaluation Characteristics:
    • Non-commutative operation: The order of the statements directly determines the output.
    • Exhibits conceptual similarity to directional outer joins (LEFT JOIN or RIGHT JOIN).
  • Syntax Structure:
  SELECT column, column FROM tablename
  MINUS
  SELECT column, column FROM tablename;
  ```
- **Associated Script Reference:** Defined within `MoreQueries.sql`.

![Diagram of a MINUS operation taking away the second set from the first](https://assets.knowt.com/pdf-flow-prod/05cca977-ffca-4b61-bf38-3571fd0d4a47-figures/2.png)

# Columns to Project

- **Primary Key (PK):**
  - Primary keys utilize a dense 1:11:1 index.
  - Inclusion ensures quicker data retrieval speeds and unambiguously identifies distinct entity rows.
- **Descriptive Information:**
  - Entities require human-interpretable, meaningful data (e.g., student or staff names) to make output readable.
- **Restricted Values:**
  - Projecting attributes that are explicitly restricted in the `WHERE` clause provides an immediate visual check to confirm filtering accuracy.

# Pattern Matching with the LIKE Operator

- **Purpose:**
  - Used within the `WHERE` clause to perform non-exact pattern matching, replacing standard equality operators (`=`).
- **Wildcard Characters:**
  - `%`: Matches zero, one, or multiple characters.
  - `_`: Matches exactly one individual character.
- **Performance Caveat:**
  - Utilizing wildcard patterns prevents the query engine from applying standard B-tree indexes, resulting in full table scans.
- **Example Syntax:**

sql SELECT student_id, firstname, surname FROM students WHERE surname LIKE 'S%';   ```

List Membership and Logical Conditions: IN and OR

  • The IN Operator:
    • Evaluates whether an attribute value matches any literal or subquery result within a defined set.
    • Operates as a cleaner, concise alternative to writing chained OR statements.
    • Example: sql SELECT staff_id, town, county FROM staff WHERE county IN ('BEDS', 'BUCKS', 'HERTS');     
  • The OR Operator:
    • Allows independent, disjunctive conditions across different attributes.
    • Example: sql SELECT staff_id, town, county FROM staff WHERE county = 'BEDS' OR town = 'NORTHAMPTON';     

Range Filtering with the BETWEEN Operator

  • Range Retrieval:
    • Specifies an inclusive range of boundary values for numeric, string, or temporal fields.
    • Numeric Range Query: sql SELECT course_id, title, cost FROM courses WHERE cost BETWEEN 1000 AND 1200;     
  • Pattern Matching Ranges:
    • BETWEEN can be combined with string wildcards to retrieve alphabetical intervals.
    • Character Range Query: sql SELECT student_id, student_firstname FROM students WHERE student_firstname BETWEEN 'A%' AND 'B%';     
  • Associated File Reference: Defined within functions.txt.

Complex Filtering: Combined Query Examples

  • Problem Specification:
    • Retrieve all staff matching the following criteria:
    • Surname starts with any character between A and F.
    • Hourly pay is within the range of £1010 to £1515.
    • Mobile phone number is either '01234567' or '01234568'.
  • Standard Solution (Using IN):
  SELECT staff_id, surname, hourly_rate, mobile
  FROM staff
  WHERE surname BETWEEN 'A%' AND 'F%'
    AND hourly_pay BETWEEN 10 AND 15
    AND mobile IN ('01234567', '01234568');
  ```
  - *Design Note:* `staff_id` is projected because primary keys provide a dense 1:11:1 index, speeding up retrieval and guaranteeing row uniqueness.
- **Alternative Solution (Using OR and Modified Range):**

sql SELECT staff_id, surname, hourly_rate, mobile FROM staff WHERE surname BETWEEN 'A%' AND 'F%' AND hourly_rate BETWEEN 8 AND 10 AND mobile = '01234567' OR mobile = '01234568';   ```

Quantified Comparison Operators: ANY, SOME, and ALL

  • Application:
    • Combined directly with simple comparison operators (>, <, =, !=, >=, <=) to evaluate scalar values against a multi-valued set.
  • Quantifier Definitions:
    • ANY: The comparison condition must evaluate to true for at least one element in the set.
    • SOME: Exact semantic synonym for ANY.
    • ALL: The comparison condition must evaluate to true for every single element in the set.
  • Syntax Structure:sql SELECT attribute, attribute FROM tablename WHERE attribute > ALL (value_1, value_2); &nbsp;&nbsp;
  • The > operator can be substituted with any simple relational operator.
  • ALL can be swapped with ANY or SOME depending on the logic required.

Questions & Discussion

  • Question: How many columns should appear in the second statement of an intersect operation?
    • Answer: The exact same number of columns that appear in the first statement.
  • Question: What is the fundamental order of keywords in a standard SQL query?
    • Answer: SELECT followed by FROM, followed by WHERE.
  • Question: What are the two non-negotiable structural rules for executing UNION, INTERSECT, and MINUS operations?
    • Answer: Both statements must project the same number of columns, and corresponding columns must share identical or compatible data types.
  • Question: If the first and second SELECT statements of a set operation each project 33 columns, how many columns appear in the final output result set?
    • Answer: Exactly 33 columns; the values from matching positions are projected together under a single set of column headings established by the first query.
  • Question: What SQL keywords and symbols are employed to locate data that does not match an exact string?
    • Answer: The LIKE operator alongside the wildcard symbols % (multiple characters) and _ (single character).
  • Question: What SQL keyword acts as an exact synonym for ANY?
    • Answer: SOME.

Practical Activity and Assessment Guidelines

  • More Queries Activity:
    • Access and read the assigned activity instructions thoroughly.
    • A primary challenge of the exercise involves analyzing schemas to determine which tables are relevant.
    • Complete all queries through to the end of the worksheet.
  • Time Constrained Assessment (TCA) Details:
    • Format: Strictly individual.
    • Materials: Closed book; external materials are prohibited.
    • Hardware: Restricted exclusively to University Machines.
    • Scheduling: Occurs during the official timetabled block.
    • Cohort Division: 22 separate groups will run within each block.
    • Duration: Exactly 11 hour during the timetabled session.
    • Attendance Requirement: Attendees must arrive early before the start of the assessment.