sqlite
  1. sqlite-inner-join
PreviousNext

SQLite Inner Join

Inner Join is a type of join that combines rows from two or more tables based on a related column between them. It returns only those rows where the join condition is true. SQLite supports Inner Join for tables having a relationship based on a foreign key or a common column.

Syntax

The syntax of the Inner Join statement is as follows:

SELECT column_names
FROM table_1
INNER JOIN table_2
ON table_1.column_name = table_2.column_name;

Here, column_names are the column(s) to select from the tables, table_1 and table_2 are the tables to join, and column_name is the common column on which the join is based.

Example

Suppose we have two tables, employees and departments, and we want to combine their data based on the department ID column.

-- Creating employees table
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department_id INTEGER NOT NULL
);

-- Creating departments table
CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

-- Inserting data into employees table
INSERT INTO employees (id, name, department_id) VALUES
(1, 'John', 1),
(2, 'Jane', 2),
(3, 'Bob', 1);

-- Inserting data into departments table
INSERT INTO departments (id, name) VALUES
(1, 'Sales'),
(2, 'Marketing'),
(3, 'Human Resources');

We can then use an Inner Join to combine the data from both tables based on the department ID column.

SELECT employees.name, departments.name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;

Output

The output of the above query would be:

John | Sales
Jane | Marketing
Bob  | Sales

Explanation

In the example above, we create two tables employees and departments, and insert some data into them. We then use an Inner Join to combine the data from both tables based on the department ID column, using the ON keyword to specify the column on which to join the tables.

The resulting output shows the names of employees and the departments they work in.

Use

Inner Join is useful in scenarios where we need to combine data from two or more tables based on a related column. It is commonly used in relational databases to extract information that is spread across different tables.

Important Points

  • Inner Join only returns the rows that have matching values in both tables being joined.
  • When joining tables, make sure to specify a condition on which to join the tables.
  • You can join more than two tables at a time by using multiple Inner Join statements.
  • Inner Join can be used in conjunction with other SQL statements, such as WHERE and GROUP BY, to further refine the result set.

Summary

In this tutorial, we learned about Inner Join in SQLite and how to use it to combine data from two or more tables based on a related column. We saw examples of how to join tables and retrieve data from them using Inner Join. Inner Join is a powerful tool for querying related data from multiple tables in a relational database.

JoinsOuter Join
Published on: