Chapter 10: Advanced SQL Concepts

Subqueries

A subquery is a SQL statement nested inside another SQL statement. The subquery runs first, and the outer query uses its results for further processing. You can use subqueries to simplify complex queries, to calculate from intermediate results, and to retrieve data that a single query can’t.

There are two types of subqueries: correlated and non-correlated. A non-correlated subquery runs once, and the outer query uses its results. A correlated subquery runs once for each row of the outer query. Correlated subqueries are less efficient than non-correlated ones, but they can be useful in some situations.

Here is an example of a non-correlated subquery:

SELECT customer_name, balance
FROM accounts
WHERE balance > (SELECT AVG(balance) FROM accounts);

This query retrieves the name and balance of every customer whose balance is greater than the average balance of all customers.

Views

A view is a virtual table based on the result of a SQL query. Views can simplify complex queries, restrict access to certain data, and give multiple users a consistent view of the data.

You define a view with the CREATE VIEW statement and query it just like a regular table. Depending on the underlying tables and the view’s definition, you may also be able to update, insert into, and delete from a view.

Here is an example of a view:

CREATE VIEW customer_summary AS
SELECT customer_name, SUM(balance) AS total_balance
FROM accounts
GROUP BY customer_name;

This view summarizes each customer’s balance by adding up the balances of all their accounts. You can query it like this:

SELECT * FROM customer_summary;

Triggers

A trigger is a special type of stored procedure that runs automatically in response to certain events, such as an insert, update, or delete on a table. Triggers can enforce complex business rules, maintain data integrity, and automate tasks.

You define a trigger with the CREATE TRIGGER statement. A trigger has three parts. The trigger event is the type of event that makes the trigger run. The trigger condition must be true for the trigger to run. The trigger action is the code that runs when the trigger fires.

Here is an example of a trigger:

CREATE TRIGGER update_balance_after_insert
AFTER INSERT ON accounts
FOR EACH ROW
BEGIN
  UPDATE customers
  SET balance = (SELECT SUM(balance) FROM accounts WHERE customer_id = NEW.customer_id)
  WHERE customer_id = NEW.customer_id;
END;

CREATE TRIGGER update_balance_after_delete
AFTER DELETE ON accounts
FOR EACH ROW
BEGIN
  UPDATE customers
  SET balance = (SELECT COALESCE(SUM(balance), 0) FROM accounts WHERE customer_id = OLD.customer_id)
  WHERE customer_id = OLD.customer_id;
END;

These triggers keep the balance column of the customers table equal to the total of that customer’s accounts. Many database systems, including MySQL and SQLite, allow only one event per trigger, so there’s one trigger for inserts and one for deletes.

Inside a row trigger, NEW holds the row as it is after the change and OLD holds it as it was before. An INSERT trigger only has NEW, because there was no row before, and a DELETE trigger only has OLD, because there’s no row after. That’s why the insert trigger looks up NEW.customer_id and the delete trigger looks up OLD.customer_id. An UPDATE trigger has both, and one for this table would recalculate the balance for OLD.customer_id and for NEW.customer_id, in case the account moved to a different customer. COALESCE turns the NULL that SUM returns for a customer with no accounts left into 0.