How to Query Multiple Tables in SQL

SQL (Structured Query Language) is a powerful tool for managing and querying relational databases. One of its most valuable features is the ability to query multiple tables simultaneously, allowing us to retrieve and integrate related data efficiently.

In this article, we will explain how to query multiple tables in SQL with examples, using a step-by-step approach. By the end, we will be able to write queries to combine data from different tables in a well-structured database.

How to Query Multiple Tables in SQL

For this example, we will create a database named geeks, define two tables (department and employee), populate the tables with sample data, and execute queries to extract data from both tables using different methods. Each step will be explained in detail.

Department Table

The department table will store department-related information, such as ID, salary, name, and department ID. Use the following SQL statement to create it:

Create Table department(
   ID int,
   SALARY int,
   NAME Varchar(20),
   DEPT_ID Varchar(255));

INSERT INTO department VALUES (1,'Neha','F','1994-06-03');
INSERT INTO department VALUES (2,'Harsh','M','1996-03-12');
INSERT INTO department VALUES (3,'Harsh','M','1995-05-01');
INSERT INTO department VALUES (4,'Rupali','F',1996-11-11');
INSERT INTO department VALUES (5,'Rohan','M','1992-03-08');

Output

Employee Table

Now create another table called employee that will store employee-specific details, including ID, email, and city. This table will relate to the department table via the ID column. Create the table using below query.

CREATE TABLE employee(
  ID int,
  Email Varchar(255),
  City Varchar(20) );

INSERT INTO employee VALUES (1, "[email protected]", "Noida");
INSERT INTO employee VALUES (2, "[email protected]", "Jaipur");
INSERT INTO employee VALUES (3, "[email protected]", "Noida");
INSERT INTO employee VALUES (4, "[email protected]", "Jaipur");
INSERT INTO employee VALUES (5, "[email protected]", "Noida");

Output

Example 1: Using a Simple SELECT Statement

The most common way to query multiple tables is with a simple SELECT expression. To integrate results from different tables, use the FROM clause to name more than one table. Here's how it works in practice:

Syntax

SELECT table1name.column1name, table2name.column2name
FROM table1name, table2name
WHERE table1name.column1name = table2name.column1name;

This query retrieves combined data from both tables based on the matching ID column. It is simple yet effective for basic requirements.

Query:

SELECT department.ID, department.NAME, employee.Email, employee.City
FROM department, employee
WHERE department.ID = employee.ID;

Output

Example 2: Using JOINs

SQL JOINs provide a more structured and readable way to query multiple tables. They are particularly useful for complex queries.

Syntax:

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 JOIN table2
ON table1.matching_column = table2.matching_column;

Join the department and employee tables to fetch combined data: This query retrieves the same result as the previous example but uses the JOIN clause, which is more efficient and easier to read in complex queries.

Query:

SELECT department.ID, department.NAME, employee.Email, employee.City  
FROM department JOIN employee ON department.ID = employee.ID;

Output

Understanding the Use Case

When working with relational databases, data is often stored across multiple tables to maintain normalization and reduce redundancy. Querying multiple tables allows us to:

• Combine related data stored in separate tables, providing a unified view.
• Generate meaningful reports by correlating data from different entities.
• Maintain scalability and structure in large databases by avoiding data duplication.

Conclusion

Querying multiple tables in SQL is a fundamental skill for working with relational databases. Whether we use a simple SELECT statement or structured JOIN operations, understanding these techniques is essential for extracting meaningful insights from our data. By mastering these methods, we can effectively combine and analyze data across complex datasets. With practice, we'll be able to create robust queries that enhance data driven decision making and streamline database management tasks.