SQLite NATURAL JOIN

Last update on August 19 2022 21:50:40 (UTC/GMT +8 hours)

What is natural join in SQLite?

In SQLite, the NATURAL JOIN is such a join that performs the same task as an INNER or LEFT JOIN, in which the ON or USING clause refers to all columns that the tables to be joined have in common.

A natural join joins two tables by their common column names. Thus, using the natural join you can get the inner join without having to add the join condition.

The natural join automatically detects the common column names in participating tables and links them together.

The SQLite NATURAL JOIN is structured in such a way that, columns with the same name of associate tables will appear once only.

Natural Join: Guidelines

- The associated tables have one or more pairs of identically named columns.
- The columns must be the same data type.
- Don’t use ON clause in a NATURAL JOIN

Syntax:

SELECT * 
FROM table1 NATURAL JOIN table2;

Pictorial representation:

Sqlite natural join image

Here is the sample table:

table - table_a
id          des1        des2
----------  ----------  --------
100         desc11      desc12
101         desc21      desc22
102         desc31      desc32

table - table_b
id          des3        des4
----------  ----------  --------
101         desc41      desc42
103         desc51      desc52
105         desc61      desc62

Example: SQLite NATURAL JOIN

In the following example, the id is the common column for both the table and matched rows based on that common column from both the table have appeared.

SELECT * FROM table_a 
NATURAL JOIN table_b;

Relational Algebra Expression:

Relational Algebra Tree:

The INNER JOIN using ON clause do the same job. Here is the following -

SELECT *
FROM table_a
INNER JOIN table_b
ON table_a.id=table_b.id;

Relational Algebra Expression:

Relational Algebra Tree:

Output :

id          des1        des2        id          des3        des4
----------  ----------  ----------  ----------  ----------  -------
101         desc21      desc22      101         desc41      desc42

Example: SQLite NATURAL JOIN with WHERE clause

Here is sample tables.

table - doctors
doctor_id   doctor_name     degree
----------  --------------  ----------
210         Dr. John Linga  MD
211         Dr. Peter Hall  MBBS
212         Dr. Ke Gee      MD
213         Dr. Pat Fay     MD

table - speciality
spl_id      spl_descrip  doctor_id
----------  -----------  ----------
1           CARDIO       211
2           NEURO        213
3           ARTHO        212
4           GYNO         210

table - visits
doctor_id   patient_name  vdate
----------  ------------  ----------
210         Julia Nayer   2013-10-15
214         TJ Olson      2013-10-14
215         John Seo      2013-10-15
212         James Marlow  2013-10-16
212         Jason Mallin  2013-10-12

Here is the example

SELECT doctor_id,doctor_name,degree,patient_name,vdate
FROM doctors 
NATURAL JOIN visits 
WHERE doctors.degree="MD";

Relational Algebra Expression:

Relational Algebra Tree:

Output :

doctor_id   doctor_name     degree      patient_name  vdate
----------  --------------  ----------  ------------  ----------
210         Dr. John Linga  MD          Julia Nayer   2013-10-15
212         Dr. Ke Gee      MD          James Marlow  2013-10-16
212         Dr. Ke Gee      MD          Jason Mallin  2013-10-12

Example: SQLite NATURAL JOIN using three tables

Here is the example.

SELECT doctor_id,doctor_name,degree,spl_descrip,patient_name,vdate
FROM doctors
NATURAL JOIN speciality
NATURAL JOIN visits
WHERE doctors.degree='MD';

Relational Algebra Expression:

Relational Algebra Tree:

Output :

doctor_id   doctor_name  degree      spl_descrip  patient_name  vdate
----------  -----------  ----------  -----------  ------------  ----------
212         Dr. Ke Gee   MD          ARTHO        James Marlow  2013-10-16
212         Dr. Ke Gee   MD          ARTHO        Jason Mallin  2013-10-12
210         Dr. John Li  MD          GYNO         Julia Nayer   2013-10-15

NATURAL JOINS: SQL and other Relational Databases

Previous: CROSS JOIN
Next: Subqueries