IN Tutorial 🎯Welcome to our SQL Subquery with IN tutorial! In this lesson, we'll explore how to use Subqueries with the IN keyword in SQL. This powerful tool will help you query and manipulate data in a more efficient and versatile way. By the end of this tutorial, you'll be able to confidently apply subqueries with IN to your own projects. 📝 Note: This tutorial is designed for both beginners and intermediate learners, so let's dive in!
A Subquery is a query within another query. It allows you to embed one query inside another, which can be used to retrieve data based on the results of the inner query. This is extremely useful for filtering, joining, and manipulating data in complex ways.
IN keyword? 📝The IN keyword is a SQL keyword used to check if a value is present in a list of values. It can be a powerful tool when used in conjunction with subqueries.
IN Example 💡Let's start with a simple example to help you understand how IN works with subqueries. Imagine we have two tables: authors and books.
-- Authors table
CREATE TABLE authors (
id INT PRIMARY KEY,
name VARCHAR(255) NOT NULL
);
-- Books table
CREATE TABLE books (
id INT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
author_id INT,
FOREIGN KEY (author_id) REFERENCES authors (id)
);Now, let's insert some data into these tables:
INSERT INTO authors (name) VALUES ('John Doe'), ('Jane Smith'), ('Alice Johnson');
INSERT INTO books (title, author_id) VALUES
('The Power of Habit', 1),
('Clean Code', 1),
('Clean Architecture', 1),
('To Kill a Mockingbird', 2),
('Pride and Prejudice', 3);Now, let's write a subquery using the IN keyword to find all books written by 'John Doe':
SELECT title
FROM books
WHERE author_id IN (
SELECT id
FROM authors
WHERE name = 'John Doe'
);Breaking down the above query:
SELECT title FROM books WHERE author_id IN (...): This is the main query, and it selects the title column from the books table where the author_id is in the list provided by the subquery.
SELECT id FROM authors WHERE name = 'John Doe': This is the subquery, which selects the id column from the authors table where the name is 'John Doe'.
The subquery's result (the list of ids for 'John Doe') is passed to the main query as IN (...). This means the main query will only return rows where the author_id is in the list provided by the subquery.
Let's take our example a step further by finding books written by authors who have also written books with more than 100 pages.
SELECT title
FROM books
WHERE author_id IN (
SELECT id
FROM authors
WHERE id IN (
SELECT author_id
FROM books
WHERE page_count > 100
GROUP BY author_id
HAVING COUNT(*) > 1
)
);Breaking down the advanced example:
SELECT title FROM books WHERE author_id IN (...): This is the main query, and it selects the title column from the books table where the author_id is in the list provided by the subquery.
SELECT author_id FROM books WHERE page_count > 100 GROUP BY author_id HAVING COUNT(*) > 1: This subquery finds authors who have written books with more than 100 pages. It groups the data by author_id and returns only those authors who have written more than one book with more than 100 pages.
The result of the subquery (the list of ids for authors who have written books with more than 100 pages) is passed to the main query as IN (...). This means the main query will only return rows where the author_id is in the list provided by the subquery.
What is a Subquery in SQL?
What does the `IN` keyword do in SQL?
That's it for today! In the next lesson, we'll dive deeper into subqueries and explore more advanced examples and techniques. Stay tuned! 📝 Note: Remember to practice these concepts with your own data to fully understand how they work. Happy coding! 🎉