1.4. Advanced Database Queries

In SQL, joining tables allows you to combine data from different tables. Common joins include:

• INNER JOIN: Returns records with matching values in both tables.
• LEFT JOIN: Returns all records from the left table, and matched records from the right table.
• RIGHT JOIN: Returns all records from the right table, and matched records from the left table.

Example:

SELECT orders.id, users.name
FROM orders
INNER JOIN users ON orders.user_id = users.id;

Join operations in SQL are ways to combine data from multiple tables based on a related column between them. An INNER JOIN gives you only the rows where there is a match in both tables, meaning if either table does not have a corresponding entry, that row won’t be included in the result. In contrast, a LEFT JOIN includes all records from the left table regardless of whether there's a match in the right table, while a RIGHT JOIN does the opposite, including all records from the right table.

Imagine you have a list of students and a list of their grades. If you want to know which students have grades, you would use an INNER JOIN to match students with existing grades. If you want to list all students alongside their grades, even if they don’t have one, you would use a LEFT JOIN. This is like checking attendance and noting that some students didn’t show up – they still exist on the roll call!

Aggregate functions allow you to perform calculations on multiple rows of data. Examples include:

• COUNT(): Count the number of rows.
• SUM(): Calculate the sum of a numeric column.
• AVG(): Calculate the average of a numeric column.

Example:

SELECT COUNT(*) FROM users WHERE active = true;

Aggregate functions in SQL are designed to perform calculations on groups of rows. For instance, COUNT() will tally the number of entries that meet certain criteria, such as counting all active users. Similarly, SUM() can add up numeric values in a column, while AVG() finds the mean of those values. Using these functions enables you to quickly generate insights from your data without the need for elaborate processing.

Think of aggregate functions like tallying scores in a sports event. If you want to know how many players are scoring points, you would use COUNT(). If you want to find out the total points scored by all players, you’d use SUM(). To find the average score per player, you’d apply the AVG() function. This gives you a clear statistical view of the performance.

Subqueries are queries nested inside other queries, often used in WHERE or HAVING clauses to filter results.

Example:

SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

A subquery is essentially a query within a query, used to retrieve data based on the results of another query. In the example provided, the outer query selects user names where their IDs match those of users who have placed orders over a certain amount. Subqueries are useful for breaking down complex queries into simpler pieces, allowing you to build queries incrementally.

Imagine you are trying to filter out only the top students from a larger student list based on their exam scores. You might first look for students who have specific high scores and then list those students by name. A subquery allows you to focus on the criteria first (high scores), then pull the relevant names (students) from the larger list.

--