How to Retrieve Data from Multiple Tables in SQL?

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.

How to Retrieve Data From Multiple Tables in SQL

Using SQL JOIN operations in SQL, you can extract data from multiple tables by combining rows based on the related columns. By using JOIN operations, you can easily extract data from different tables, providing a complete approach to data retrieval in SQL programming.

SQL offers various methods to fetch data from multiple tables efficiently. The common approaches are as follows:

1. Using Joins
2. Using Subqueries

Let’s begin with the Joins, and we’ll go over the explanation syntax and an example to help you understand:

1. Using Joins

One of the most common approaches to retrieve data from multiple tables in SQL is by utilizing JOIN clauses to combine data from different tables based on specified conditions.

SELECT t1.column1, t2.column2
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id;

Here,

• SELECT: Specifies the columns to retrieve.
• FROM: Specifies the tables from which to retrieve data.
• table1 t1, table2 t2: Aliases for the tables, improving readability.
• JOIN: Combines rows from two or more tables.
• ON: Specifies the join condition, determining how rows are matched.

Example1: Suppose we have two tables: employees and departments.

The employees table contains information about employees, including their names and department IDs. The departments table contains information about departments, including their names and department IDs.

-- Create employees table
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    department_id INT
);

-- Create departments table
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

-- Insert sample data into employees table
INSERT INTO employees (employee_id, employee_name, department_id)
VALUES
    (1, 'John Doe', 1),
    (2, 'Jane Smith', 2),
    (3, 'Michael Johnson', 1),
    (4, 'Emily Brown', 3);

-- Insert sample data into departments table
INSERT INTO departments (department_id, department_name)
VALUES
    (1, 'Engineering'),
    (2, 'Marketing'),
    (3, 'Finance');

The tables look like the following:

Example1 Tables

Now, we want to retrieve employee names along with their department names. This can be achieved by executing the following query:

SELECT e.employee_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

Output:

Output of Example1

Using Subqueries

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:

Example2 Table

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:

Output of Example2

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

Conclusion

In conclusion, we understand that advanced techniques for retrieving data from multiple tables in SQL offer developers the flexibility to construct complex queries and extract valuable insights from interconnected datasets. By mastering subqueries, correlated queries, and other advanced SQL functionalities, developers can optimize query performance, enhance data retrieval processes, and gain deeper insights into database relationships.