Advanced Querying Techniques

This page covers several powerful SQL clauses and operators that allow for more complex subqueries and data manipulation: ANY, ALL, EXISTS, SELECT INTO, and INSERT INTO SELECT.

Database Preparation

Run this block to set up the tables used in the examples below.

DROP TABLE IF EXISTS Products;
CREATE TABLE Products (
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2)
);
INSERT INTO Products (ProductName, Price)
VALUES ('Laptop', 1200), ('Mouse', 25), ('Keyboard', 75);

DROP TABLE IF EXISTS ArchivedProducts;
CREATE TABLE ArchivedProducts (
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2)
);
INSERT INTO ArchivedProducts (ProductName, Price)
VALUES ('Old Mouse', 20), ('Old Keyboard', 50);

DROP TABLE IF EXISTS Customers;
CREATE TABLE Customers (
    CustomerID INTEGER PRIMARY KEY,
    CustomerName VARCHAR(100)
);
INSERT INTO Customers (CustomerID, CustomerName)
VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');

DROP TABLE IF EXISTS Orders;
CREATE TABLE Orders (
    OrderID INTEGER PRIMARY KEY,
    CustomerID INTEGER,
    Amount DECIMAL(10, 2)
);
INSERT INTO Orders (OrderID, CustomerID, Amount)
VALUES (101, 2, 50.00), (102, 1, 75.00), (103, 2, 25.00);

DROP TABLE IF EXISTS Books;
CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    Title VARCHAR(255),
    Genre VARCHAR(50),
    PublishedYear INTEGER
);
INSERT INTO Books (BookID, Title, Genre, PublishedYear)
VALUES (1, 'The Hobbit', 'Fantasy', 1937),
       (2, '1984', 'Dystopian', 1949),
       (3, 'The Fellowship of the Ring', 'Fantasy', 1954),
       (4, 'Dune', 'Sci-Fi', 1965);

SELECT 'Database Ready' AS Status;

The ANY and ALL Operators

ANY Operator

Query:

SELECT ProductName, Price
FROM Products
WHERE Price > ANY (SELECT Price FROM ArchivedProducts);

ALL Operator

Query:

SELECT ProductName, Price
FROM Products
WHERE Price > ALL (SELECT Price FROM ArchivedProducts);

The EXISTS Operator

Query:

SELECT CustomerName
FROM Customers
WHERE EXISTS (
    SELECT 1
    FROM Orders
    WHERE Orders.CustomerID = Customers.CustomerID
);

Copying Data: SELECT INTO and INSERT INTO SELECT

SELECT INTO (Non-Standard SQL)

Query (SQL Server Syntax):

-- Note: SQLite does not support SELECT INTO. Use CREATE TABLE ... AS SELECT ...
DROP TABLE IF EXISTS Books_Backup;
CREATE TABLE Books_Backup AS
SELECT BookID, Title, Genre
FROM Books
WHERE PublishedYear < 1950;

-- Check the result
SELECT * FROM Books_Backup;

INSERT INTO SELECT (Standard SQL)

First, we need an existing archive table:

DROP TABLE IF EXISTS ArchivedBooks;
CREATE TABLE ArchivedBooks (
    BookID INT,
    Title VARCHAR(255),
    ArchivedDate DATE
);

Query:

INSERT INTO ArchivedBooks (BookID, Title, ArchivedDate)
SELECT BookID, Title, '2025-09-24'
FROM Books
WHERE PublishedYear < 1950;

-- Check the result
SELECT * FROM ArchivedBooks;

Result:

ALL Operator

ALL returns TRUE only if the comparison is true for all of the values in the result set.

Operation: Find all current products that are more expensive than all products in the archive.

Query:

SELECT ProductName, Price
FROM Products
WHERE Price > ALL (SELECT Price FROM ArchivedProducts);

Result:

The EXISTS Operator

The EXISTS operator is used to test for the existence of any record in a subquery. It returns TRUE if the subquery returns one or more records, otherwise it returns FALSE. EXISTS is often used in correlated subqueries, where the inner query depends on data from the outer query.

Let's use our Customers and Orders tables.

Customers Table:

Orders Table:

Operation: Find the names of all customers who have placed at least one order.

Query:

SELECT CustomerName
FROM Customers
WHERE EXISTS (
    SELECT 1
    FROM Orders
    WHERE Orders.CustomerID = Customers.CustomerID
);

Explanation: This is a correlated subquery. For each Customer row being considered by the outer query, the inner query runs:

1. For Alice (ID 1): The subquery becomes SELECT 1 FROM Orders WHERE Orders.CustomerID = 1. This finds a matching order (OrderID 102), so it returns a row. EXISTS evaluates to TRUE. Alice is included.
2. For Bob (ID 2): The subquery becomes SELECT 1 FROM Orders WHERE Orders.CustomerID = 2. This finds two matching orders. EXISTS evaluates to TRUE. Bob is included.
3. For Charlie (ID 3): The subquery becomes SELECT 1 FROM Orders WHERE Orders.CustomerID = 3. This finds no matching orders, so it returns no rows. EXISTS evaluates to FALSE. Charlie is excluded.

(Note: SELECT 1 is a common convention. The actual value selected in the subquery doesn't matter; EXISTS only cares if any rows are returned at all.)

Result:

EXISTS is often more efficient than IN or JOIN for this type of check because the database can stop searching as soon as it finds the first matching record in the subquery.

Copying Data: SELECT INTO and INSERT INTO SELECT

SELECT INTO (Non-Standard SQL)

The SELECT INTO statement copies data from one table into a new table. This syntax is primarily used by Microsoft SQL Server.

Operation: Create a new backup table called Books_Backup containing all books published before 1950.

Query (SQL Server Syntax):

-- Note: SQLite does not support SELECT INTO. Use CREATE TABLE ... AS SELECT ...
DROP TABLE IF EXISTS Books_Backup;
CREATE TABLE Books_Backup AS
SELECT BookID, Title, Genre
FROM Books
WHERE PublishedYear < 1950;

-- Check the result
SELECT * FROM Books_Backup;

Result: A new table named Books_Backup is created and populated.

INSERT INTO SELECT (Standard SQL)

The INSERT INTO SELECT statement copies data from one or more tables and inserts it into an existing table.

Operation: Archive all books published before 1950 into an existing ArchivedBooks table.

First, we need an existing archive table:

DROP TABLE IF EXISTS ArchivedBooks;
CREATE TABLE ArchivedBooks (
    BookID INT,
    Title VARCHAR(255),
    ArchivedDate DATE
);

Query:

INSERT INTO ArchivedBooks (BookID, Title, ArchivedDate)
SELECT BookID, Title, '2025-09-24'
FROM Books
WHERE PublishedYear < 1950;

-- Check the result
SELECT * FROM ArchivedBooks;

Result: The ArchivedBooks table is now populated with the selected data.

Hands-on Exercise

Task: Use EXISTS to find all products from the Products table that have never been archived (i.e., their ProductName does not exist in the ArchivedProducts table).

Your Query:

-- Write your query here

Expected Results: