MSDN Documentation

Advanced Relational Database Queries

Welcome to the advanced section of our relational database tutorials. Here, we will dive into more complex query techniques that allow for sophisticated data retrieval and manipulation.

Subqueries

A subquery, also known as a nested query or inner query, is a query embedded within another SQL query. Subqueries can be used in various parts of a SQL statement, such as the WHERE clause, FROM clause, or SELECT list.

They are particularly useful for performing operations that require a set of data to be compared against or used in another query.

Example: Finding employees with salaries above the average salary.

SELECT employee_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Advanced Joins

While basic joins (INNER JOIN, LEFT JOIN, RIGHT JOIN) are fundamental, advanced scenarios might require more specialized join types or complex join conditions.

• FULL OUTER JOIN: Returns all rows when there is a match in either the left or the right table.
• CROSS JOIN: Returns the Cartesian product of the rows from the tables being joined. Use with caution as it can produce very large result sets.
• Self-Joins: Joining a table to itself to compare rows within the same table.

Example: Finding employees and their direct managers.

SELECT e.employee_name AS Employee, m.employee_name AS Manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

Window Functions

Window functions perform calculations across a set of table rows that are somehow related to the current row. This is similar to the type of calculation that can be done with an aggregate function, but it returns an aggregate value for each row rather than for a group of rows.

Common window functions include:

• ROW_NUMBER(): Assigns a unique sequential integer to each row within a partition.
• RANK(): Assigns a rank to each row within a partition of a result set. Rows with equal values receive the same rank, and the next rank is skipped.
• DENSE_RANK(): Similar to RANK() but does not skip ranks for tied values.
• LAG() and LEAD(): Access data from a previous or next row within the partition.
• Aggregate functions like SUM(), AVG(), COUNT(), MIN(), MAX() used as window functions.

Example: Ranking employees by salary within each department.

SELECT
    employee_name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank
FROM employees;

Common Table Expressions (CTEs)

A Common Table Expression (CTE) is a temporary, named result set that you can reference within a single SQL statement. CTEs can simplify complex queries by breaking them into more manageable logical units.

They are especially useful for:

• Recursive queries (e.g., hierarchical data).
• Breaking down complex aggregations.
• Improving readability.

Example: Using a CTE to find departments with more than 5 employees.

WITH DepartmentCounts AS (
    SELECT department, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department
)
SELECT department, employee_count
FROM DepartmentCounts
WHERE employee_count > 5;

Performance Tuning for Complex Queries

As queries become more advanced, performance can become a critical concern. Here are some key considerations:

• Indexing: Ensure appropriate indexes are created on columns used in WHERE clauses, JOIN conditions, and ORDER BY clauses.
• Query Plans: Analyze the execution plan of your queries using tools like EXPLAIN (or similar commands depending on your database system) to identify bottlenecks.
• Avoid `SELECT *`: Specify only the columns you need to reduce data transfer and processing.
• Optimize Subqueries: Sometimes, subqueries can be rewritten as joins or CTEs for better performance.
• Database Statistics: Keep database statistics up-to-date so the query optimizer can make informed decisions.