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, andMINUS. - Methods for collecting and filtering ranges of data using operators such as
LIKE,IN,OR,BETWEEN,ANY,SOME, andALL.
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 theONkeyword.
- Join condition clauses (e.g., matching attributes like
- 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
FROMclause.
- All tables participating in a join are listed within the
- 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
WHEREclause to filter outer query results.
- Nested queries typically appear inside the
- 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
CUSTOMERSmust appear first, followed by the child tableORDERS.
Set Operators: Union, Intersect, and Minus
- General Purpose:
- Set operators are utilized to combine the output of or more
SELECTstatements into a single unified result set.
- Set operators are utilized to combine the output of or more
- Fundamental Structural Rules:
- Column Matching: Each constituent
SELECTstatement must project the exact same number of columns. - Datatype Compatibility: Corresponding columns across all queries must possess matching or compatible data types.
- Column Matching: Each constituent
- Heading and Projection Rules:
- Column headers in the final output are determined exclusively by the names defined in the first
SELECTstatement.
- Column headers in the final output are determined exclusively by the names defined in the first
- 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 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
SELECTstatements does not affect the final result set. - Functions similarly to a relational
FULL OUTER JOIN.
- Commutative operation: The sequence/order of the individual
- Syntax Structure:
SELECT column, column FROM tablename
UNION
SELECT column, column FROM tablename;
```
- **Associated Script Reference:** Defined within `MoreQueries.sql`.

# 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.

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 JOINorRIGHT JOIN).
- Syntax Structure:
SELECT column, column FROM tablename
MINUS
SELECT column, column FROM tablename;
```
- **Associated Script Reference:** Defined within `MoreQueries.sql`.

# Columns to Project
- **Primary Key (PK):**
- Primary keys utilize a dense 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
ORstatements. - 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:
BETWEENcan 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
AandF. - Hourly pay is within the range of £ to £.
- 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 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.
- Combined directly with simple comparison operators (
- Quantifier Definitions:
ANY: The comparison condition must evaluate to true for at least one element in the set.SOME: Exact semantic synonym forANY.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); - The
>operator can be substituted with any simple relational operator. ALLcan be swapped withANYorSOMEdepending 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:
SELECTfollowed byFROM, followed byWHERE.
- Answer:
- Question: What are the two non-negotiable structural rules for executing
UNION,INTERSECT, andMINUSoperations?- 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
SELECTstatements of a set operation each project columns, how many columns appear in the final output result set?- Answer: Exactly 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
LIKEoperator alongside the wildcard symbols%(multiple characters) and_(single character).
- Answer: The
- Question: What SQL keyword acts as an exact synonym for
ANY?- Answer:
SOME.
- Answer:
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: separate groups will run within each block.
- Duration: Exactly hour during the timetabled session.
- Attendance Requirement: Attendees must arrive early before the start of the assessment.