Lesson 4: Advanced SQL Queries

In this lesson, we will dive into more complex SQL queries, focusing on subqueries and joins. These concepts will significantly enhance your ability to interact with databases and retrieve data in sophisticated ways.

Subqueries

A subquery is a query nested inside another query. It allows you to perform operations that require multiple steps, such as filtering results based on the output of another query.

Example of a Subquery

Suppose we have two tables: employees and departments.

-- Creating the employees table
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2)
);

-- Creating the departments table
CREATE TABLE departments (
    id INT PRIMARY KEY,
    department_name VARCHAR(100)
);

-- Inserting sample data into employees
INSERT INTO employees (id, name, department_id, salary) VALUES
(1, 'Alice', 1, 70000),
(2, 'Bob', 2, 60000),
(3, 'Charlie', 1, 80000),
(4, 'David', 3, 50000);

-- Inserting sample data into departments
INSERT INTO departments (id, department_name) VALUES
(1, 'Engineering'),
(2, 'Marketing'),
(3, 'Sales');

Now, let's say we want to find all employees who earn more than the average salary in their department. We can achieve this using a subquery:

SELECT name, salary
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
);

Joins

Joins are used to combine rows from two or more tables based on a related column between them. The most common types of joins are INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

INNER JOIN Example

Using our employees and departments tables, we can retrieve a list of employees along with their department names using an INNER JOIN:

SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

LEFT JOIN Example

A LEFT JOIN will return all records from the left table and the matched records from the right table. If there is no match, NULL values will be returned for columns from the right table:

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

Best Practices and Common Mistakes

Always use aliases (like e and d in the examples) for better readability, especially in complex queries.

Avoid using subqueries unnecessarily, as they can lead to performance issues. Use joins whenever possible for better efficiency.

Ensure you understand the difference between INNER JOIN and LEFT JOIN to avoid missing records you expect to see.

Summary

• Subqueries allow you to nest queries within queries for complex data retrieval.
• Joins are essential for combining data from multiple tables based on related columns.
• Always use aliases for clarity in your SQL statements.
• Be cautious with subqueries for performance reasons; prefer joins when feasible.
• Understand the types of joins to use them effectively in your queries.

Exercise 1: Subquery Practice

Write a query to find all departments that have an average salary greater than 60000.

Exercise 2: INNER JOIN Practice

Write a query to list all employees along with their department names, ensuring to include only those employees who belong to the 'Engineering' department.

Exercise 3: LEFT JOIN Practice

Write a query to list all employees and their department names, including those employees who do not belong to any department.

• Subqueries enable complex data retrieval by nesting queries.
• Joins combine data from multiple tables based on related columns.
• Use aliases for better readability in SQL queries.
• Prefer joins over subqueries for performance optimization.
• Understand different join types to retrieve the desired data accurately.