The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

Subqueries

Next ►
►

Subqueries, also known as inner queries or nested queries, are queries embedded within another SQL query. They are powerful tools that allow you to perform more complex data retrieval operations by using the result of one query as an input for another. In this section, we will explore the concept of subqueries, their types, and how to use them effectively in BigQuery.

What is a Subquery?

A subquery is a query nested inside another query. The outer query is called the main query, and the inner query is called the subquery. Subqueries can be used in various parts of a SQL statement, including the SELECT, FROM, WHERE, and HAVING clauses.

Types of Subqueries

  1. Single-Row Subqueries: Return a single row and single column.
  2. Multiple-Row Subqueries: Return multiple rows and a single column.
  3. Multiple-Column Subqueries: Return multiple rows and multiple columns.
  4. Correlated Subqueries: Refer to columns in the outer query and are evaluated once for each row processed by the outer query.

Basic Subquery Syntax

Here is the basic syntax for a subquery:

SELECT column1, column2
FROM table1
WHERE column3 = (SELECT column4 FROM table2 WHERE condition);

In this example, the subquery (SELECT column4 FROM table2 WHERE condition) is executed first, and its result is used by the outer query.

Practical Examples

Example 1: Single-Row Subquery

Find the name of the employee with the highest salary.

SELECT name
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

Explanation:

  • The subquery SELECT MAX(salary) FROM employees returns the highest salary.
  • The outer query selects the name of the employee with that salary.

Example 2: Multiple-Row Subquery

Find the names of employees who work in the same department as 'John Doe'.

SELECT name
FROM employees
WHERE department_id = (SELECT department_id FROM employees WHERE name = 'John Doe');

Explanation:

  • The subquery SELECT department_id FROM employees WHERE name = 'John Doe' returns the department ID of 'John Doe'.
  • The outer query selects the names of all employees in that department.

Example 3: Correlated Subquery

Find employees who earn more than the average salary in their department.

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

Explanation:

  • The subquery SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id calculates the average salary for each department.
  • The outer query selects employees whose salary is greater than the average salary of their department.

Exercises

Exercise 1: Single-Row Subquery

Find the name of the department with the highest budget.

SELECT department_name
FROM departments
WHERE budget = (SELECT MAX(budget) FROM departments);

Exercise 2: Multiple-Row Subquery

List the names of employees who have the same job title as 'Jane Smith'.

SELECT name
FROM employees
WHERE job_title = (SELECT job_title FROM employees WHERE name = 'Jane Smith');

Exercise 3: Correlated Subquery

Find the names of employees who have a higher salary than the average salary of their department.

SELECT name
FROM employees e1
WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id);

Common Mistakes and Tips

  • Mistake: Using a subquery that returns multiple rows where a single row is expected.

    • Solution: Ensure the subquery returns a single value when used with operators like = or <.

  • Mistake: Forgetting to correlate the subquery with the outer query in correlated subqueries.

    • Solution: Always reference the outer query's columns in the subquery where necessary.

  • Tip: Use subqueries to break down complex queries into simpler, more manageable parts.

Conclusion

Subqueries are a powerful feature in SQL that allow you to perform complex queries by nesting one query inside another. By understanding and using subqueries effectively, you can enhance your data retrieval capabilities in BigQuery. Practice the provided exercises to reinforce your understanding and prepare for more advanced SQL topics.

◄ Previous
◄
Next ►
►
The Project | About Us | Contribute | Donations | License
© Copyright 2024. All rights reserved