SQLite | Joins | INNER JOIN
An inner join returns only rows whose join-column values match in both tables.
INNER JOIN syntax
SELECT table1.column, table2.column
FROM table1 INNER JOIN table2
ON table1.column1 = table2.column2;
Create employee and department data:
create table employee (id integer, name text, dept_id integer);
insert into employee values (1, 'dekuma', 1);
insert into employee values (2, 'kimkc', 3);
insert into employee values (3, 'araikuma', 1);
insert into employee values (4, 'happykuma', 2);
insert into employee values (5, 'mykuma', 4);
insert into employee values (6, 'raccoon', 3);
create table dept (id integer, name text);
insert into dept values (1, 'Sales');
insert into dept values (2, 'Manage');
insert into dept values (3, 'Dev');
Join employee.dept_id to dept.id:
select * from employee inner join dept
on employee.dept_id = dept.id;
Employees in departments 1–3 are returned; mykuma, whose department 4 has no match, is omitted. Reversing table order returns the same matched data in a different column order.
Specify result columns
Qualify duplicate column names with their table names:
select employee.id, employee.name, dept.name from employee
inner join dept on employee.dept_id = dept.id;
An unambiguous column such as dept_id may omit the table prefix. Selecting an ambiguous name without a prefix fails:
select name from employee inner join dept
on employee.dept_id = dept.id;
-- Error: ambiguous column name: name