SQL Subqueries

🎓 Top 15 Udemy Courses (80-90% Discount): My Udemy Courses - Ramesh Fadatare — All my Udemy courses are real-time and project oriented courses.

▶️ Subscribe to My YouTube Channel (178K+ subscribers): Java Guides on YouTube

▶️ For AI, ChatGPT, Web, Tech, and Generative AI, subscribe to another channel: Ramesh Fadatare on YouTube

Introduction

In this chapter, we will focus on subqueries in SQL. A subquery, also known as an inner query or nested query, is a query within another SQL query. Subqueries can be used to perform complex queries and return data that can be used in the main query. This chapter will cover the definition, types, syntax, and provide examples to help you understand how to use subqueries effectively.

What is a Subquery?

A subquery is a query nested inside another query, such as a SELECT, INSERT, UPDATE, or DELETE statement. The result of the subquery is used by the main query.

Types of Subqueries

1. Single-Row Subquery: Returns a single row.
2. Multiple-Row Subquery: Returns multiple rows.
3. Multiple-Column Subquery: Returns multiple columns.
4. Correlated Subquery: References columns from the outer query.
5. Non-Correlated Subquery: Independent of the outer query.

Syntax for Subqueries

Basic Syntax

SELECT column1, column2, ...
FROM table1
WHERE column_name operator (SELECT column_name FROM table2 WHERE condition);

Single-Row Subquery

SELECT first_name, last_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'HR');

Multiple-Row Subquery

SELECT first_name, last_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1);

Multiple-Column Subquery

SELECT first_name, last_name
FROM employees
WHERE (department_id, job_id) IN (SELECT department_id, job_id FROM job_history WHERE end_date IS NULL);

Correlated Subquery

SELECT first_name, last_name
FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d WHERE e.department_id = d.department_id AND d.location_id = 1);

Non-Correlated Subquery

SELECT first_name, last_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Finance');

Step-by-Step Example

Sample Tables

First, let's create sample tables named employees and departments.

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50),
    location_id INT
);

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Inserting Sample Data

INSERT INTO departments (department_id, department_name, location_id)
VALUES
(1, 'HR', 1),
(2, 'Finance', 1),
(3, 'IT', 2);

INSERT INTO employees (first_name, last_name, email, department_id, salary)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', 1, 50000),
('Sita', 'Patel', 'sita.patel@example.com', 2, 60000),
('Arjun', 'Singh', 'arjun.singh@example.com', 1, 55000),
('Priya', 'Sharma', 'priya.sharma@example.com', 2, 62000),
('Ravi', 'Verma', 'ravi.verma@example.com', 3, 50000);

Single-Row Subquery Example

To find employees in the HR department:

SELECT first_name, last_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'HR');

Output

first_name	last_name
Ramesh	Kumar
Arjun	Singh

Multiple-Row Subquery Example

To find employees in departments located in location_id 1:

SELECT first_name, last_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1);

Output

first_name	last_name
Ramesh	Kumar
Sita	Patel
Arjun	Singh
Priya	Sharma

Correlated Subquery Example

To find employees whose department is in location_id 1 using a correlated subquery:

SELECT first_name, last_name
FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d WHERE e.department_id = d.department_id AND d.location_id = 1);

Output

first_name	last_name
Ramesh	Kumar
Sita	Patel
Arjun	Singh
Priya	Sharma

Non-Correlated Subquery Example

To find employees in the Finance department:

SELECT first_name, last_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Finance');

Output

first_name	last_name
Sita	Patel
Priya	Sharma

Conclusion

Subqueries are used in SQL for performing complex queries and retrieving specific data sets. This chapter covered the different types of subqueries, their syntax, and provided examples to illustrate their use. Understanding how to use subqueries effectively will greatly enhance your ability to query and manipulate data in a relational database.

My Top and Bestseller Udemy Courses. The sale is going on with a 70 - 80% discount. The discount coupon has been added to each course below:

Build REST APIs with Spring Boot 4, Spring Security 7, and JWT

🆕 High-Demand 80–90% OFF

[NEW] Learn Apache Maven with IntelliJ IDEA and Java 25

🆕 High-Demand 80–90% OFF

ChatGPT + Generative AI + Prompt Engineering for Beginners

🚀 Trending Now 80–90% OFF

Spring 7 and Spring Boot 4 for Beginners (Includes 8 Projects)

🔥 Bestseller 80–90% OFF
Available in Udemy for Business

Building Real-Time REST APIs with Spring Boot - Blog App

🔥 Bestseller 80–90% OFF
Available in Udemy for Business

Building Microservices with Spring Boot and Spring Cloud

🌟 Top Rated 80–90% OFF
Available in Udemy for Business

Java Full-Stack Developer Course with Spring Boot and React JS

🔥 Bestseller 80–90% OFF
Available in Udemy for Business

Build 5 Spring Boot Projects with Java: Line-by-Line Coding

🌟 Top Rated 80–90% OFF

Testing Spring Boot Application with JUnit and Mockito

🔥 Bestseller 80–90% OFF
Available in Udemy for Business

Spring Boot Thymeleaf Real-Time Web Application - Blog App

🔥 Bestseller 80–90% OFF
Available in Udemy for Business

Master Spring Data JPA with Hibernate

🔥 Bestseller 80–90% OFF
Available in Udemy for Business

Spring Boot + Apache Kafka Course - The Practical Guide

🎓 Student Favorite 80–90% OFF
Available in Udemy for Business

Java Testing: Mastering JUnit 5 Framework

🆕 New 80–90% OFF

Reactive Programming in Java: Spring WebFlux and Testing

🌟 Top Rated 80–90% OFF

Spring Boot + RabbitMQ Course - The Practical Guide

🌟 Top Rated 80–90% OFF
Available in Udemy for Business

Functional Programming in Java (Includes Java Collections)

🎓 Student Favorite 80–90% OFF

ChatGPT for Java Developers: Boost Your Productivity with AI

🚀 Trending Now 80–90% OFF