Blog

Subquery in SELECT Statement in SQL

Ever found yourself puzzled by the phrase “subquery in SELECT statement in SQL”? Don’t worry, you’re not alone. Many budding coders find this concept tricky at first. But imagine how powerful your SQL queries could become with this tool in your arsenal! A subquery, simply put, allows you to nest queries and gather more specific data. It’s a game-changer for crafting refined, efficient queries. So, let’s dive in and unravel the mystery behind subqueries, making your coding journey smoother and more enjoyable.

Exploring SQL Subqueries 

sql
SELECT 
    employee_id, 
    first_name, 
    last_name, 
    (SELECT department_name 
     FROM departments 
     WHERE departments.department_id = employees.department_id) AS department 
FROM 
    employees;

Explanation of the Code
In this SQL query, the code retrieves information about employees and their respective departments. Here’s a breakdown in an ordered list:

  1. The `SELECT` statement specifies the columns we want from the `employees` table: `employee_id`, `first_name`, and `last_name`, providing basic info on each employee.
  2. The tricky part is the subquery within the `SELECT` statement, which is `(SELECT department_name FROM departments WHERE departments.department_id = employees.department_id) AS department`. This part fetches the `department_name` from the `departments` table.
  3. This subquery operates by finding `department_id` in the `departments` table that matches the `department_id` from the `employees` table, effectively linking the two tables.
  4. Lastly, the `AS department` gives a meaningful alias to the result, appearing neatly alongside other employee details.

So, this query efficiently combines data from different tables, offering a cohesive view of employees and their departments. Pretty handy, isn’t it?

Output 

| employee_id | first_name | last_name | department      |
|-------------|------------|-----------|-----------------|
| 1           | John       | Doe       | Sales           |
| 2           | Jane       | Smith     | Marketing       |
| 3           | Peter      | Johnson   | IT              |
| 4           | Emma       | Brown     | Human Resources |
| 5           | Emily      | Davis     | Finance         |

Real-Life Uses of Subqueries in SQL SELECT Statements

You might be thinking, “Great, but how on earth does this relate to real-world scenarios?” Let’s break it down into some real-life examples:

  1. Financial Analysis: Imagine a bank needing to identify accounts with transactions exceeding the average daily volume. A subquery establishes the day’s average, while the main query pulls all accounts above that threshold, ensuring compliance with monitoring requirements.
  2. Retail Inventory Management: A retailer is interested in listing products priced higher than the average price in their category. A quick subquery does the number-crunching for the category average, with the outer query snagging all the pricier items. This helps streamline inventory or pricing strategies.

SQL Subquery Quiz

Go on, have a crack at these quiz questions designed to quiz your knowledge of the subquery in a SELECT statement in SQL. An essential gap-filler for your programming skills:

  1. What is a subquery?
    • A query within a query
    • A standalone SQL statement
    • A type of database key
  2. Where can subqueries be used?
    • In SELECT, WHERE, and FROM clauses
    • In comments
    • Just in table joins
  3. Which clause typically contains correlated subqueries?
    • WHERE clause
    • GROUP BY clause
    • ORDER BY clause
  4. Can a subquery return multiple values?
    • Yes, often
    • No, never
    • Sometimes, depending on context
  5. What keyword is often used with subqueries for conditions?
    • ALL
    • IF
    • HINT

Looking for a tool that makes coding in SQL a breeze? Our AI-powered SQL online compiler lets you write, run, and test your SQL code immediately. No need to worry about local setups—just jump in and let the AI assist you in real-time coding tasks!

Conclusion

In conclusion, mastering the ‘subquery in SELECT statement in SQL’ enhances your data manipulation skills, offering you a powerful way to sharpen your SQL abilities. It’s a rewarding experience that can boost your confidence as a coder. So why wait? Give it a try and discover the magic of subqueries yourself. For more insights into programming languages, check out Newtum. Happy coding!

Edited and Compiled by

This article was compiled and edited by @rasikadeshpande, who has over 4 years of experience in writing. She’s passionate about helping beginners understand technical topics in a more interactive way.

About The Author

Rasika Deshpande

Rasika Deshpande, a passionate writer with over 4 years of experience in the EdTech industry. At Newtum, she writes, edit, and compile engaging blog and other content. Writing is not just a profession for her; it’s a passion that fuels creativity and dedication to the field of writing.

See author's posts