SQL Correlated Subqueries with example
A correlated subquery is a subquery that references one or more columns from the outer query. The subquery is evaluated once for each row processed by the outer query. This is in contrast to a non-correlated subquery, where the subquery is independent of the outer query.
Here’s an example to illustrate a correlated subquery. Suppose you have two tables: employees and salaries. The employees table contains information about employees, and the salaries table contains information about their salaries. You want to find employees whose salary is greater than the average salary for their department.
— Create tables and insert sample data
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(100),
department_id INT
)
CREATE TABLE salaries (
emp_id INT,
salary INT,
FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
)
INSERT INTO employees VALUES (1, ‘John Doe’, 101);
INSERT INTO employees VALUES (2, ‘Jane Smith’, 101);
INSERT INTO employees VALUES (3, ‘Bob Johnson’, 102);
INSERT INTO salaries VALUES (1, 60000);
INSERT INTO salaries VALUES (2, 70000);
INSERT INTO salaries VALUES (3, 80000);
— Correlated subquery to find employees with salary greater than the average for their department
SELECT emp_id, emp_name, department_id, salary
FROM employees e
JOIN salaries s ON e.emp_id = s.emp_id
WHERE salary > (
SELECT AVG(salary)
FROM salaries
WHERE emp_id IN (
SELECT emp_id
FROM employees
WHERE department_id = e.department_id
)
In this example, the correlated subquery is:
SELECT AVG(salary)
FROM salaries
WHERE emp_id IN (
SELECT emp_id
FROM employees
WHERE department_id = e.department_id
)
This subquery is correlated because it references the e.department_id from the outer query. It calculates the average salary for the employees in the same department as the current employee in the outer query. The outer query then selects employees whose salary is greater than this calculated average.
Correlated subqueries are useful when you need to perform row-by-row comparisons or calculations based on the values in the outer query.
what is SQL Nested Queries with example
In SQL, a nested query (also known as a subquery) is a query that is embedded within another query. The result of the nested query is used by the outer query to perform further processing. Nested queries are often employed when you need to retrieve data based on the results of another query.
Here’s a simple example to illustrate the concept of a nested query:
Let’s consider two tables, Orders and Customers. The Orders table contains information about customer orders, and the Customers table contains information about customers.
— Customers table
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(255)
);
— Orders table
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
Amount DECIMAL(10, 2),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
— Insert some sample data
INSERT INTO Customers VALUES (1, ‘John Doe’);
INSERT INTO Customers VALUES (2, ‘Jane Smith’);
INSERT INTO Orders VALUES (101, 1, ‘2023-01-01’, 100.00);
INSERT INTO Orders VALUES (102, 1, ‘2023-02-01’, 150.00);
INSERT INTO Orders VALUES (103, 2, ‘2023-03-01’, 200.00);
Now, suppose you want to find the names of customers who have placed orders. You can use a nested query for this:
— Nested query to find customers who have placed orders
SELECT CustomerName
FROM Customers
WHERE CustomerID IN (
SELECT CustomerID
FROM Orders
);
In this example, the inner query retrieves the CustomerID values from the Orders table for customers who have placed orders. The outer query then uses these CustomerID values to retrieve the corresponding CustomerName from the Customers table.
Keep in mind that this is a simple example, and nested queries can be used in more complex scenarios, such as with aggregate functions, joins, and multiple levels of nesting.
SQL Advanced functions with example
SQL provides a variety of advanced functions that go beyond basic data retrieval. Here are some commonly used advanced functions with examples:
Aggregate Functions:
COUNT(): Counts the number of rows.
SELECT COUNT(*) FROM Orders;
SUM(): Calculates the sum of a numeric column.
SELECT SUM(Amount) FROM Orders;
AVG(): Calculates the average of a numeric column.
SELECT AVG(Amount) FROM Orders;
MIN() and MAX(): Retrieve the minimum and maximum values from a column.
SELECT MIN(OrderDate), MAX(OrderDate) FROM Orders;
GROUP BY:
Groups rows based on the values in one or more columns.
SELECT CustomerID, COUNT(*) as OrderCount
FROM Orders
GROUP BY CustomerID;
HAVING:
Filters the results of a GROUP BY query.
SELECT CustomerID, COUNT(*) as OrderCount
FROM Orders
GROUP BY CustomerID
HAVING COUNT(*) > 1;
JOIN:
Combines rows from two or more tables based on a related column between them.
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
SUBQUERIES:
Using a SELECT statement inside another SELECT, INSERT, UPDATE, or DELETE statement.
SELECT CustomerName
FROM Customers
WHERE CustomerID IN (
SELECT CustomerID
FROM Orders
WHERE Amount > 100
);
CASE Statements:
Allows conditional logic within a query.
SELECT OrderID, Amount,
CASE
WHEN Amount > 100 THEN ‘High Value’
WHEN Amount > 50 THEN ‘Medium Value’
ELSE ‘Low Value’
END AS ValueCategory
FROM Orders;
UNION and UNION ALL:
Combines results of two or more SELECT statements.
SELECT CustomerID FROM Orders
UNION
SELECT CustomerID FROM Customers;
WINDOW Functions:
Perform calculations across a set of table rows related to the current row.
SELECT OrderID, Amount,
SUM(Amount) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS RunningTotal
FROM Orders;
These are just a few examples of advanced SQL functions and techniques. Depending on the specific database management system (DBMS) you are using, there may be additional functions or variations in syntax.
SQL sub Queries in from Clause
Subqueries in the FROM clause, also known as derived tables or inline views, are queries that are used to retrieve data and create a temporary result set within the FROM clause of another query. These subqueries can be used to simplify complex queries and make them more readable.
Here’s an example of using a subquery in the FROM clause:
Suppose you have a database with a Orders table and a Customers table, and you want to find the total amount of orders for each customer:
SELECT CustomerID, CustomerName, TotalAmount
FROM Customers
JOIN (
SELECT CustomerID, SUM(Amount) AS TotalAmount
FROM Orders
GROUP BY CustomerID
) AS OrderTotals
ON Customers.CustomerID = OrderTotals.CustomerID;
In this example, the subquery in the FROM clause calculates the total order amount for each customer using the SUM and GROUP BY functions. The main query then joins the result of this subquery with the Customers table based on the CustomerID. The final result includes the CustomerID, CustomerName, and the TotalAmount for each customer.
Key points about the subquery in the FROM clause:
It is enclosed within parentheses.
It must have an alias (AS OrderTotals) so that it can be referred to in the main query.
The fields selected in the subquery are used in the main query’s JOIN condition.
Subqueries in the FROM clause are useful when you need to perform complex calculations or aggregations before joining with other tables. They allow you to break down a problem into smaller, more manageable steps.