w3resource logo


>SQLite inner join on multiple tables

SQLite INNER JOIN

Secondary Nav

How inner joins works in SQLite?

In SQLite, the INNER JOIN selects all rows from both participating tables to appear in the result if and only if both tables meet the conditions specified in the ON clause. JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents. In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.

We have three types of INNER JOINS: INNER JOIN, NATURAL INNER JOIN, and CROSS INNER JOIN. The INNER keyword can be omitted.

Pictorial Presentation

Assume that, we have two tables table-A and table-B. The unique columns for two tables are ID. The value in ID column for table-A are 210,211,212 and 213 and the values for table-B are 210,214,215,212 and 212. Here is the pictorial presentation.

inner join pictorial presentation

Example : SQLite INNER JOIN

When combining records from more than one tables, an user needs to indicate, how the records in a table can be matched to records in the other. As the both of tables have an ID (doctor_id) column, we can match using that column. The ON clause is used to match records in two tables, based on the value of doctor_id column. Usage of INNER JOIN combines the tables. An INNER JOIN allows rows from either table to appear in the result if and only if both tables meet the conditions specified in the ON clause.

Here are 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 - 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

In this example, the ON clause specifies that the doctor_id column of both doctors and visits table must match. If the ID (doctor_id) does not appear in both of the tables, the row will not appear in the result because the condition in the ON clause fails. Only those doctors will participate in the JOIN whose degree id MD.

SELECT doctors.doctor_id,doctors.doctor_name,visits.patient_name           
FROM doctors
INNER JOIN visits        
ON doctors.doctor_id=visits.doctor_id
WHERE doctors.degree='MD';

Output :

doctor_id   doctor_name     patient_name
----------  --------------  ------------
210         Dr. John Linga  Julia Nayer
212         Dr. Ke Gee      James Marlow
212         Dr. Ke Gee      Jason Mallin

Example : SQLite inner join with alias

The following SQLite statement joins doctors ID, doctors name, doctors degree and the name of the patient for that doctor who is holding the degree MD and not hold the registered ID 210. Notice that aliases have been used to refer the column names. An INNER JOIN is performed based upon the condition that a doctor_id in doctors table must exist in visits table also.

SELECT doc.doctor_id,doc.doctor_name,doc.degree,vis.patient_name 
FROM doctors AS doc
INNER JOIN visits AS vis  ON doc.doctor_id=vis.doctor_id  AND
doc.degree="MD" AND doc.doctor_id<>210;

Here is the result.

doctor_id   doctor_name  degree      patient_name
----------  -----------  ----------  ------------
212         Dr. Ke Gee   MD          James Marlow
212         Dr. Ke Gee   MD          Jason Mallin

SQLite INNER JOIN using three tables

Here are the 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
s
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

The above tables are related to each other. In doctors, speciality and visits tables the doctor_id and spl_id are the primary key for doctors and speciality table consecutively. The doctor_id in speciality table and visits tables are a foreign key, which is reference to primary key doctor_id of doctors table.

If we want all records for a doctor who are specialized in special1 and seat in his chamber on wednesday (WED) in his schedule time, the following SQL can be used-

SELECT a.doctor_id,a.doctor_name,
       b.spl_descrip,c.patient_name,c.vdate
  FROM doctors a 
    INNER JOIN speciality b
     ON a.doctor_id=b.doctor_id
    INNER JOIN visits c
     ON a.doctor_id=c.doctor_id
        WHERE a.doctor_id=212 AND c.vdate='2013-10-16';

Output :

doctor_id   doctor_name  spl_descrip  patient_name  vdate
----------  -----------  -----------  ------------  ----------
212         Dr. Ke Gee   ARTHO        James Marlow  2013-10-16

Explanation

step-1

SELECT a.doctor_id,a.doctor_name,b.spl_descrip
FROM doctors a 
INNER JOIN speciality b
ON a.doctor_id=b.doctor_id;

example inner join

step-2

SELECT a.doctor_id,a.doctor_name,
b.spl_descrip,c.patient_name,c.vdate
FROM doctors a 
INNER JOIN speciality b
ON a.doctor_id=b.doctor_id
INNER JOIN visits c
ON a.doctor_id=c.doctor_id;

example inner join

step-3

SELECT a.doctor_id,a.doctor_name,
b.spl_descrip,c.patient_name,c.vdate
FROM doctors a 
INNER JOIN speciality b
ON a.doctor_id=b.doctor_id
INNER JOIN visits c
ON a.doctor_id=c.doctor_id
WHERE a.doctor_id=212 AND c.vdate='2013-10-16';

example inner join

INNER JOINS: SQL and other Relational Databases



Join our Question Answer community to learn and share your programming knowledge.

Help the community:

Python: Fizzbuzz

C++: Decimal to binary conversion

JavaScript: Need Help in JavaScript

Python: Help me with this program