SQLite | Joining Data | Natural Joins (NATURAL Keyword)
Inner and outer joins normally specify columns in a join condition. The NATURAL keyword instead matches columns that share the same name in both tables. This article explains natural joins.
Using Natural Joins
Add the NATURAL keyword to an inner or outer join. The syntax is as follows.
For an inner join
SELECT table_name.column_name, ... FROM table1
NATURAL INNER JOIN table2;
For an outer join
SELECT table_name.column_name, ... FROM table1
NATURAL LEFT OUTER JOIN table2;
A natural join automatically compares same-named columns, so no explicit join condition is required. Otherwise it behaves like the corresponding inner or outer join.
–
Create an employee table and a dept table. Both contain a dept_id column. Then insert sample 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);
sqlite> create table employee (id integer, name text, dept_id integer);
sqlite>
sqlite> insert into employee values (1, 'dekuma', 1);
sqlite> insert into employee values (2, 'kimkc', 3);
sqlite> insert into employee values (3, 'araikuma', 1);
sqlite> insert into employee values (4, 'happykuma', 2);
sqlite> insert into employee values (5, 'mykuma', 4);
sqlite> insert into employee values (6, 'raccoon', 3);
sqlite>
create table dept(dept_id integer, deptname text);
insert into dept values(1, 'Sales');
insert into dept values(2, 'Manage');
insert into dept values(3, 'Dev');
sqlite> create table dept(dept_id integer, deptname text);
sqlite>
sqlite> insert into dept values(1, 'Sales');
sqlite> insert into dept values(2, 'Manage');
sqlite> insert into dept values(3, 'Dev');
sqlite>
First, run a natural inner join. Since both tables have dept_id, SQLite uses that column for the join.
select * from employee natural inner join dept;
sqlite> select * from employee natural inner join dept;
id name dept_id deptname
---------- ---------- ---------- ----------
1 dekuma 1 Sales
2 kimkc 3 Dev
3 araikuma 1 Sales
4 happykuma 2 Manage
6 raccoon 3 Dev
sqlite>
With NATURAL, a same-named column appears only once in the result.
This is equivalent to an inner join with an explicit ON condition.
select * from employee inner join dept on employee.dept_id = dept.dept_id;
sqlite> select * from employee inner join dept on employee.dept_id = dept.dept_id;
id name dept_id dept_id deptname
---------- ---------- ---------- ---------- ----------
1 dekuma 1 1 Sales
2 kimkc 3 3 Dev
3 araikuma 1 1 Sales
4 happykuma 2 2 Manage
6 raccoon 3 3 Dev
sqlite>
Next, run a natural outer join. Again, SQLite joins the tables using their shared dept_id column.
select * from employee natural left outer join employee;
sqlite> select * from employee natural left outer join employee;
id name dept_id
---------- ---------- ----------
1 dekuma 1
2 kimkc 3
3 araikuma 1
4 happykuma 2
5 mykuma 4
6 raccoon 3
sqlite>
This is equivalent to an outer join with an explicit ON condition.
select * from employee left outer join dept on employee.dept_id = dept.dept_id;
sqlite> select * from employee left outer join dept on employee.dept_id = dept.dept_id;
id name dept_id dept_id deptname
---------- ---------- ---------- ---------- ----------
1 dekuma 1 1 Sales
2 kimkc 3 3 Dev
3 araikuma 1 1 Sales
4 happykuma 2 2 Manage
5 mykuma 4
6 raccoon 3 3 Dev
sqlite>