How to use Subqueries In SQL

Another approach to fetching data from multiple tables in SQL involves using subqueries to retrieve data from one table based on the results of another table.

Syntax:

SELECT column1, column2
FROM table1
WHERE id IN (SELECT id FROM table2 WHERE condition);Here,
SELECT: Specifies the columns to retrieve.

Here,

• SELECT: Specifies the columns to retrieve.
• column1, column2: The specific columns to be selected from table1.
• FROM: Specifies the table from which to retrieve data.
• table1: The table from which data is being retrieved.
• WHERE: Filters the rows based on a condition.
• id: The column being used as a reference for filtering.
• IN: Specifies that the values returned by the subquery are to be compared with the main query.
• (SELECT id FROM table2 WHERE condition): The subquery that retrieves id values from table2 based on a certain condition.

Example1: Let’s say we have two tables called “customers” and “orders” with the following data:

“We can create the ‘orders‘ and ‘customers‘ tables using the following SQL code, which defines the table structure with columns such as order_id, order_date and customer_name of orders TABLE and customer_id and customer_name of customers TABLE.

-- Create orders table
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT
);

-- Create customers table
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50)
);

-- Insert sample data into customers table
INSERT INTO customers (customer_id, customer_name)
VALUES
    (1, 'Alice Johnson'),
    (2, 'Bob Smith'),
    (3, 'Charlie Brown');

-- Insert sample data into orders table
INSERT INTO orders (order_id, order_date, customer_id)
VALUES
    (101, '2024-03-01', 1),
    (102, '2024-03-05', 2),
    (103, '2024-03-10', 1),
    (104, '2024-03-12', 3);

After inserting the data into orders and customers, tables look like the following:

Now we want to retrieve order_id and order_date from orders for the customer named Alice Johnson. We can achieve this using a query as shown below:

SELECT order_id, order_date
FROM orders
WHERE customer_id IN (SELECT customer_id FROM customers WHERE customer_name = 'Alice Johnson');

Output:

We can observe that we have retrieved order_id and order_date from orders for the customer named Alice Johnson.

In SQL, Retrieving data from multiple tables is a common requirement in database operations. In this article, we will explore multiple approaches to retrieving data from multiple tables in SQL. We will provide an introduction to the topic, explain two distinct approaches with their respective syntax, present detailed examples for each approach with output explanations, etc.