SQL 2: Advanced Queries and Joins

30.12.23

Programming

Lesson 2: Advanced Queries and Joins

Introduction 

Welcome to Lesson 2 of our SQL course. Now that you have a solid understanding of the basics, it's time to elevate your SQL skills. In this lesson, we'll explore advanced SELECT statements, delve into the power of JOIN operations, and introduce the concept of subqueries. These topics are fundamental to querying complex databases and extracting meaningful insights. Let's dive in!

Advanced SELECT Statements 

**DISTINCT Keyword:**
The DISTINCT keyword is used to retrieve unique values from a specified column. It eliminates duplicate rows in the result set.

-- Example: Retrieve unique product categories from a 'products' table
SELECT DISTINCT category FROM products;

**Aggregate Functions:**
SQL provides powerful aggregate functions to perform calculations on data. Common ones include SUM, AVG, COUNT, MAX, and MIN.

-- Example: Calculate the total sales from an 'orders' table
SELECT SUM(total_sales) FROM orders;

Joins 

**INNER JOIN:**
The INNER JOIN retrieves rows from both tables where there is a match based on the specified condition.

-- Example: Retrieve customer names and their orders using INNER JOIN
SELECT customers.name, orders.order_id
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;

**LEFT JOIN:**
The LEFT JOIN returns all rows from the left table and matched rows from the right table. If no match is found, NULL values are returned for right table columns.

-- Example: Retrieve all employees and their assigned projects using LEFT JOIN
SELECT employees.name, projects.project_name
FROM employees
LEFT JOIN projects ON employees.employee_id = projects.employee_id;

**RIGHT JOIN:**
The RIGHT JOIN is similar to the LEFT JOIN but returns all rows from the right table and matched rows from the left table.

-- Example: Retrieve all products and their reviews using RIGHT JOIN
SELECT products.product_name, reviews.review_text
FROM products
RIGHT JOIN reviews ON products.product_id = reviews.product_id;

**FULL JOIN:**
The FULL JOIN returns all rows when there is a match in either the left or right table. If no match is found, NULL values are returned for the non-matching side.

-- Example: Retrieve all customers and their orders using FULL JOIN
SELECT customers.name, orders.order_id
FROM customers
FULL JOIN orders ON customers.customer_id = orders.customer_id;

**Using Aliases:**
Aliases provide shorthand for table or column names, making queries more concise.

-- Example: Using aliases to shorten table names in a query
SELECT c.name, o.order_id
FROM customers AS c
INNER JOIN orders AS o ON c.customer_id = o.customer_id;

Subqueries 

**Definition and Usage:**
A subquery is a query nested within another query. It can be used in SELECT, WHERE, or FROM clauses.

-- Example: Using a subquery to find employees with the highest salary
SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

**Subqueries in SELECT, WHERE, and FROM Clauses:**
Subqueries can be employed to perform various tasks, such as calculating aggregates or filtering data based on conditions.

-- Example: Using a subquery in the SELECT clause to calculate average product price
SELECT category, (SELECT AVG(price) FROM products WHERE category = p.category) AS avg_price
FROM products AS p;

Examples 

Let's reinforce these concepts with practical examples:

**Example 1: Using DISTINCT and Aggregate Functions:**
Consider a 'sales' table with columns: product_id, quantity, and price.

-- Retrieve the total sales for each product category
SELECT category, SUM(quantity * price) AS total_sales
FROM sales
GROUP BY category;

**Example 2: Performing Different Types of Joins:**
Assume a 'employees' table and a 'departments' table.

-- Retrieve employees and their assigned departments using LEFT JOIN
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

**Example 3: Utilizing Subqueries in Various Scenarios:**
Imagine a 'invoices' table with columns: invoice_id, customer_id, and total_amount.

-- Retrieve customers who have made invoices with amounts greater than the average
SELECT customer_id
FROM invoices
WHERE total_amount > (SELECT AVG(total_amount) FROM invoices);

In this lesson, you've explored advanced SQL queries, including DISTINCT, aggregate functions, various types of joins, and the use of subqueries. As you continue to progress, these skills will empower you to handle complex data scenarios efficiently. In the next lesson, we'll focus on data modification and transactions, allowing you to not only query data but also modify it with precision.

Back