SQL Subqueries

A subquery is a SQL query nested inside another query. Subqueries are incredibly versatile and allow developers to create more dynamic and efficient queries. This tutorial covers:

• Converting a JOIN query into a subquery.
• Using a subquery within an INSERT statement for dynamic seeding.

Constructing a Subquery

Subqueries are typically used to generate values that can be referenced in the main query. A subquery runs first, and its results are used by the outer query. Here's the syntax:

SELECT 
FROM 
WHERE   (
    SELECT 
    FROM 
);
Steps to construct a subquery:

        
Write and test the subquery independently.
        
Wrap the subquery in parentheses and integrate it into the outer query.
    
Use Case: Converting JOIN Queries

        Subqueries can often simplify complex JOIN queries. They may also improve performance in cases where filtering results early is beneficial.
    
Example:
Question: Which cat owns the toy named "Scratcher"?
Using JOIN:
SELECT cats.name
FROM cats
JOIN toys ON toys.cat_id = cats.id
WHERE toys.name = 'Scratcher';
Using Subquery:
SELECT name
FROM cats
WHERE id IN (
    SELECT cat_id
    FROM toys
    WHERE name = 'Scratcher'
);

        In this example, the subquery retrieves the IDs of all cats that own the toy named "Scratcher." The outer query then fetches the names of those cats.
    
Why Use IN?

        The IN operator is more appropriate than = in this case because the subquery might return multiple results. IN handles this scenario efficiently.
    
Use Case: Data Injection

        Subqueries are invaluable for dynamically seeding data, especially when foreign key relationships need to be established. This reduces the risk of errors and makes scripts more robust and flexible.
    
Example 1: Single ID
Insert a new toy for a specific cat:
INSERT INTO toys (name, cat_id) 
VALUES (
    'New Toy',
    (
        SELECT id
        FROM cats
        WHERE name = 'Molly'
    )
);
Example 2: Multiple Rows
Insert toys for multiple cats in one statement:
INSERT INTO toys (name, cat_id)
SELECT 'New Toy', id
FROM cats
WHERE name = 'Lucky' OR name = 'Garfield';
Example 3: Table Backup
Create a backup of the orders table:
INSERT INTO orders_backup
SELECT * FROM orders;

        This ensures a snapshot of the data is preserved before performing complex updates or deletions.
    
Subquery Requirements

        Follow these guidelines to avoid errors when using subqueries:
    

        
Always enclose subqueries in parentheses (unless copying a table).
        
Avoid ORDER BY in subqueries; use it in the outermost query instead.
        
Place the subquery on the right side of operators like IN, =, >, or <.
        
Ensure subqueries used with single-row operators (=, <, >) return only one result.
    
What You Learned

        In this lesson, you learned:
    

        
How to construct and use subqueries to replace JOIN queries.
        
How to dynamically seed data into a table using subqueries with INSERT statements.
        
Best practices and requirements for writing subqueries effectively.
    
Real-World Applications

        Subqueries are powerful tools in SQL that are widely used for:
    

        
Dynamic Data Management: Automating the insertion of related data.
        
Complex Filtering: Simplifying queries with multiple filtering conditions.
        
Data Backup: Preserving data before performing risky operations.
    

        By mastering subqueries, you can write more efficient and maintainable SQL queries for diverse use cases.