Advanced SQL Query Techniques

1. Combining Data with JOINs

At the heart of any relational database are multiple tables that relate to one another. To get a complete picture, you almost always need to combine them. The JOIN clause is your primary tool for this. While you have some familiarity with JOINs, we'll quickly cover the essential types to ensure you have a solid foundation.

The video below gives an excellent overview of the different types of JOINs and how they work.

To summarize the key ideas from the video:

2. Deeper Analysis with Subqueries

Now that you can combine tables, the next step is to perform more complex filtering and calculations. Subqueries (also called inner or nested queries) are queries placed inside another SQL query. They are incredibly powerful because they allow you to perform analysis in multiple steps within a single statement.

The following video provides an excellent conceptual breakdown of what subqueries are, how the database processes them, and the different types you'll encounter. Given your computer science background, you'll likely find the explanation of the execution flow particularly insightful.

Subqueries can appear in several parts of a main query, most commonly:

• In the WHERE clause: To filter rows based on a value or a list of values returned by the subquery.
• In the FROM clause: To create a temporary, "derived" table that you can then join with other tables.
• In the SELECT clause: To return a single, calculated value as a new column.

For a more structured textual reference on these patterns, the following article is a great resource.

3. Improving Readability with Common Table Expressions (CTEs)

As your queries become more complex, with multiple subqueries nested inside each other, they can become difficult to read and debug. Common Table Expressions (CTEs) are a modern SQL feature that helps solve this problem.

A CTE allows you to define a temporary, named result set that you can reference within your main SELECT, INSERT, UPDATE, or DELETE statement. They make your queries more modular and readable. You define a CTE using the WITH clause.

You can even define multiple CTEs and chain them together, where a later CTE references an earlier one.

The next video segment explains how to transform a complex nested subquery into a much cleaner query using the WITH clause.

Practice: Tying It All Together

Let's apply these concepts to a common business problem.

Scenario: Find all employees who earn more than the average salary within their own department.

To solve this, you need to compare each employee's salary to a value that changes depending on their department. This is a perfect use case for a correlated subquery.

Here is one way to write the query:

SELECT
    e.name,
    e.salary,
    d.dept_name
FROM
    employees e
JOIN
    departments d ON e.dept_id = d.dept_id
WHERE
    e.salary > (
        SELECT AVG(salary)
        FROM employees
        WHERE dept_id = e.dept_id -- This correlation is key!
    );

How it works:

1. The outer query iterates through each employee in the employees table.
2. For each employee, the correlated subquery in the WHERE clause is executed.
3. Crucially, the subquery calculates the average salary only for the department of the current employee being processed by the outer query (WHERE dept_id = e.dept_id).
4. The outer query then compares the current employee's salary to this department-specific average and includes them in the result if their salary is higher.

This pattern is powerful, but as we saw, CTEs can often make such logic even clearer, especially as more steps are added. In a future lesson on window functions, we'll see an even more efficient way to solve this same problem.

Conclusion

In this lesson, you've built a strong foundation for writing advanced SQL queries. You are now equipped to tackle complex data retrieval tasks that are common in data science.

Here are the key takeaways:

• JOINs are used to combine rows from two or more tables based on a related column.
• Subqueries are queries nested inside another query. They are powerful for multi-step data transformations and filtering but can become hard to read.
• Correlated Subqueries are a special type of subquery that references the outer query, making them useful for row-by-row comparisons.
• Common Table Expressions (CTEs), defined with the WITH clause, allow you to break down complex queries into logical, readable, and reusable steps.

In our next lesson, we will explore window functions. These functions allow you to perform calculations across a set of table rows that are related to the current row, enabling you to compute running totals, moving averages, and rankings with ease.