SQL Subquery with SELECT 🎯

Welcome to our in-depth tutorial on SQL Subqueries with SELECT! 📝 Here, we'll dive into the fascinating world of subqueries, exploring how they can help you retrieve complex data with ease.

What are Subqueries? 💡

Subqueries are a powerful feature in SQL that allows you to nest one SELECT statement inside another. They can be used to perform operations such as filtering data, aggregating results, and even joining tables dynamically.

Simple Subquery 📝

Let's start with a simple subquery. In its simplest form, a subquery looks like this:

sql

SELECT column_name
FROM (subquery)

The subquery inside the parentheses (subquery) is executed first, and its result is then used as a table in the main query.

Practical Example 💡

Imagine we have a table employees and we want to find the department with the highest average salary. Here's how we can do it using a subquery:

sql

SELECT department
FROM employees
WHERE salary = (
    SELECT AVG(salary)
    FROM employees
);

In this example, we're finding the average salary of all employees first, and then we're filtering the employees table to find the departments where the salary matches the average.

Advanced Subqueries 💡

Subqueries can also be used in the FROM, WHERE, and JOIN clauses. Let's explore these uses with examples.

Subqueries in the FROM Clause 💡

A subquery in the FROM clause is used to return a table that can be used in the main query.

sql

SELECT employee_id, department
FROM (
    SELECT employee_id, department, salary
    FROM employees
    ORDER BY salary DESC
) AS top_salaries
WHERE ROWNUM = 1;

In this example, we're creating a temporary table top_salaries containing the employee ID, department, and salary of the highest-paid employee in each department. We then select the employee ID and department of the highest-paid employee overall.

Subqueries in the WHERE Clause 💡

A subquery in the WHERE clause is used to filter the main query based on a condition that depends on the subquery's result.

sql

SELECT *
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM departments
    WHERE department_name = 'IT'
);

In this example, we're filtering the employees table to only include employees from the 'IT' department.

Subqueries in the JOIN Clause 💡

A subquery in the JOIN clause is used to dynamically generate a join condition.

sql

SELECT e1.employee_name, d.department_name
FROM employees AS e1
JOIN (
    SELECT department_id, department_name
    FROM departments
    WHERE budget > 100000
) AS d
ON e1.department_id = d.department_id;

In this example, we're joining the employees and departments tables, but only for departments with a budget over 100,000.

Quiz Time! 🎯

Keep exploring and mastering SQL subqueries! 🚀