SQL 5
Nested Queries in SQL
Introduction to Nested Queries
A nested query involves using multiple select statements to retrieve specific information. As we delve into more advanced query
writing, understanding nested queries becomes essential as they allow us to retrieve precise data by using the results from one select
statement to inform another.
Example 1: Finding Employees with High Sales
Let's begin with an example. Consider the scenario where we need to find the names of all employees who have sold over $30,000
worth of products to a single client. We have a "works_with" table containing total sales, but it lacks employees' first and last names.
We can use a nested query to retrieve this information.
Step 1: Get Employee IDs with Sales over $30,000
SELECT employee_id
FROM works_with
WHERE total_sales > 30000;
This query retrieves all the employee IDs with sales over $30,000 to any client.
Step 2: Get Names of Employees with High Sales
SELECT employee.first_name, employee.last_name
FROM employee
WHERE employee_id IN (
SELECT employee_id
FROM works_with
WHERE total_sales > 30000
);
By nesting the first query inside the second, we can now obtain the first and last names of employees who meet the sales criteria. For
instance, Michael Scott and Stanley Hudson are employees with high sales.
Example 2: Clients Managed by Michael Scott
Next, let's explore another example. We want to find all clients who are handled by the branch managed by Michael Scott. To do this,
we need to retrieve the branch ID managed by Michael and then find the clients associated with that branch.
Step 1: Get Branch ID Managed by Michael Scott
SELECT branch_id
FROM branch
WHERE manager_id = 102;
This query finds the branch ID that Michael Scott manages. In this case, it returns the ID for the Scranton branch.
Step 2: Get Clients Handled by Michael's Branch
SELECT client_name
FROM client
WHERE branch_id = (
SELECT branch_id
FROM branch
WHERE manager_id = 102
)
LIMIT 1;
By using a nested query, we match the clients associated with the branch managed by Michael Scott. The "LIMIT 1" ensures we only
get one result, making it suitable for cases when the manager may handle multiple branches.
Using MySQL NOT IN Operator
The NOT IN operator is used to filter rows where a column's value does not match any value in a specified list.
SELECT column1, column2
FROM table_name
WHERE column_name NOT IN (value1, value2, ...);
SELECT *
FROM products
WHERE category NOT IN ('Electronics', 'Appliances');
Using MySQL IS NULL Operator
The IS NULL operator is used to filter rows where a column's value is NULL.
SELECT column1, column2
FROM table_name
WHERE column_name IS NULL;
SELECT *
FROM orders
WHERE payment_status IS NULL;
Using MySQL IS NOT NULL Operator
The IS NOT NULL operator is used to filter rows where a column's value is not NULL.
SELECT column1, column2
FROM table_name
WHERE column_name IS NOT NULL;
SELECT *
FROM customers
WHERE email IS NOT NULL;
Using MySQL BETWEEN
The BETWEEN operator is used to filter rows based on a range of values for a column.
SELECT column1, column2
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
SELECT *
FROM employees
WHERE salary BETWEEN 30000 AND 50000;
MySQL Conditional Functions
MySQL IF() Function
The IF() function in MySQL allows us to perform conditional logic within our queries. It evaluates a specified condition and returns one
value if the condition is true, and another value if the condition is false.
Syntax:
IF(condition, value_if_true, value_if_false)
Example: Using IF() to Classify Employees
Let's say we want to categorize employees based on their salary as "High" or "Low". Here's how we can achieve that using the IF()
function:
SELECT Employee_Name, Salary,
IF(Salary > 50000, 'High', 'Low') AS SalaryCategory
FROM Employees;
MySQL IFNULL() Function
The IFNULL() function in MySQL is used to handle NULL values. It returns the first nonNULL expression from the provided arguments.
Syntax:
IFNULL(expression, value_if_null)
Example: Handling NULL Values in Salary
Suppose we want to display a default value for employees with NULL salaries. We can use the IFNULL() function for this purpose:
SELECT Employee_Name, IFNULL(Salary, 0) AS AdjustedSalary
FROM Employees;
MySQL NULLIF() Function
The NULLIF() function in MySQL is used to compare two expressions. If the two expressions are equal, it returns NULL; otherwise, it
returns the first expression.
Syntax:
NULLIF(expression, value)
Example: Preventing Division by Zero
To avoid division by zero, we can use the NULLIF() function. If the divisor is zero, the function will return NULL:
SELECT Value1, Value2,
Value1 / NULLIF(Value2, 0) AS SafeDivision
FROM SomeTable;
Case Statement
In this lesson, we will explore SQL Case Statements and their use cases. A Case Statement allows you to specify conditions and
determine what values to return when those conditions are met. We will be using the "Employee Demographics" table for our
examples.
A Case Statement in SQL follows the syntax:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE default_result
END
It allows you to categorize or label data based on specified conditions.
Example 1: Categorizing Age
Let's start with a simple example to understand Case Statements. Assume we want to categorize employees based on their age as
"Young" or "Old."
SELECT first_name, last_name, age
FROM employee_demographics
WHERE age IS NOT NULL
ORDER BY age;
Using Case Statement to categorize age
SELECT first_name, last_name, age,
CASE
WHEN age > 30 THEN 'Old'
ELSE 'Young'
END AS age_category
FROM employee_demographics
WHERE age IS NOT NULL
ORDER BY age;
In this example, we create a new column "age_category" that categorizes employees as "Old" if their age is greater than 30 and
"Young" otherwise.
Example 2: Calculating Salary Raises
Now, let's dive into a more complex use case. Assume we have an "Employee Salaries" table and we want to calculate the salary after
giving different raises to different job titles.
SELECT first_name, last_name, job_title, salary,
CASE
WHEN job_title = 'Salesman' THEN salary + (salary * 0.10)
WHEN job_title = 'Accountant' THEN salary + (salary * 0.05)
WHEN job_title = 'HR' THEN salary + (salary * 0.01)
ELSE salary + (salary * 0.03)
END AS salary_after_raise
FROM employee_salaries
ORDER BY salary_after_raise DESC;
In this example, we use a Case Statement to calculate the salary_after_raise for each employee based on their job title. Salesmen get a
10% raise, Accountants a 5% raise, HR a 1% raise, and all others a 3% raise.
MySQL Window Functions
Window functions are advanced analytical functions in MySQL that allow you to perform calculations across a set of table rows related
to the current row. They are particularly useful for tasks that involve ranking, aggregation, and calculation of running totals without
affecting the overall query result. In this lesson, we'll explore several essential window functions in MySQL.
OVER() Function
The OVER() function defines the window or subset of rows for which a window function operates. It can include an ORDER BY clause
to specify the order of rows within the window.
Understanding the Partition By Clause in SQL
Group By: The Group By statement reduces the number of rows in the output by grouping them based on specified columns.
Aggregate functions, such as SUM or COUNT, are then applied to each group.
Partition By: On the other hand, Partition By doesn't reduce the number of rows returned in the output. Instead, it divides the result
set into partitions and affects how window functions are calculated within each partition.
The syntax for using the Partition By clause is as follows:
SELECT column1, column2, ..., window_function(column)
OVER (PARTITION BY partition_column)
FROM table_name;
Let's break down the components of this syntax:
column1, column2, ...: Columns you want to select in the output.
window_function(column): The window function you want to apply, such as COUNT or SUM.
PARTITION BY partition_column: Specifies the column by which the result set is partitioned.
Practical Example: Analyzing Employee Data
For this lesson, we'll work with two tables: employee and employee_demographics. The goal is to use the Partition By clause to
analyze the data and compare it with a Group By approach.
Let's start by examining the tables and the data they contain:
Now, let's use the Partition By clause to analyze the data. We want to determine the count of male and female employees in each row
of the result set.
SELECT
first_name,
last_name,
gender,
salary,
COUNT(gender) OVER (PARTITION BY gender) AS total_gender
FROM employee;
Comparison with Group By
To highlight the difference, let's rewrite the query using the Group By statement:
SELECT
gender,
COUNT(gender) AS total_gender
FROM employee
GROUP BY gender;
RANK() Function
The RANK() function assigns a unique rank to each distinct value in a result set, similar to DENSE_RANK(). However, it does not skip
ranks in case of ties.
Example: Ranking Students by Exam Scores
Rank students by their exam scores in ascending order:
SELECT Student_ID, ExamScore,
RANK() OVER (ORDER BY ExamScore ASC) AS ExamRank
FROM StudentScores;
DENSE_RANK() Function
The DENSE_RANK() function assigns a unique rank to each distinct value in a result set. It handles ties by skipping ranks, ensuring that
consecutive ranks are not skipped.
Example: Ranking Employees by Sales
Rank employees by their sales in descending order, considering ties:
SELECT Employee_ID, Sales,
DENSE_RANK() OVER (ORDER BY Sales DESC) AS SalesRank
FROM EmployeeSales;
PERCENT_RANK() Function
The PERCENT_RANK() function calculates the relative rank of a row within a result set as a percentage.
Example: Calculating Percentile Rank
Determine the percentile rank of employees based on their sales:
SELECT Employee_ID, Sales,
PERCENT_RANK() OVER (ORDER BY Sales) AS PercentileRank
FROM EmployeeSales;
ROW_NUMBER() Function
The ROW_NUMBER() function assigns a unique sequential integer to each row within a result set.
Example: Assigning Row Numbers
Assign row numbers to products in alphabetical order:
SELECT Product_ID, ProductName,
ROW_NUMBER() OVER (ORDER BY ProductName) AS RowNumber
FROM Products;
EXISTS and NOT EXISTS in MySQL
These operators are used to determine whether a subquery returns any rows (EXISTS) or doesn't return any rows (NOT EXISTS).
They're particularly handy for performing complex queries involving multiple tables.
Understanding the EXISTS Operator
The EXISTS operator checks if a subquery returns any rows. If the subquery has at least one result, the EXISTS condition evaluates to
true.
Example: Using EXISTS to Find Matching Records
Let's say we want to find all employees who are assigned to at least one project. We can use the EXISTS operator in a query like this:
SELECT EmployeeName
FROM Employees
WHERE EXISTS (
SELECT *
FROM Assignments
WHERE Assignments.EmployeeID = Employees.EmployeeID
);
Understanding the NOT EXISTS Operator
The NOT EXISTS operator checks if a subquery returns no rows. If the subquery has no results, the NOT EXISTS condition evaluates to
true.
Example: Using NOT EXISTS to Find Unmatched Records
Imagine we want to find all clients who haven't made any orders. We can use the NOT EXISTS operator in a query like this:
SELECT ClientName
FROM Clients
WHERE NOT EXISTS (
SELECT *
FROM Orders
WHERE Orders.ClientID = Clients.ClientID
);
