How to use Joins In SQL
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:
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:
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.