w3resource

SQLite INNER JOIN

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';

Relational Algebra Expression:

Relational Algebra Expression: SQLite INNER JOIN.

Relational Algebra Tree:

Relational Algebra Tree: SQLite INNER JOIN.

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;

Relational Algebra Expression:

Relational Algebra Expression: SQLite inner join with alias.

Relational Algebra Tree:

Relational Algebra Tree: SQLite inner join with alias.

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';

Relational Algebra Expression:

Relational Algebra Expression: SQLite INNER JOIN using three tables.

Relational Algebra Tree:

Relational Algebra Tree: SQLite INNER JOIN using three tables.

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;

Relational Algebra Expression:

Relational Algebra Expression: SQLite INNER JOIN using three tables.

Relational Algebra Tree:

Relational Algebra Tree: SQLite INNER JOIN using three tables.

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;

Relational Algebra Expression:

Relational Algebra Expression: SQLite INNER JOIN using three tables.

Relational Algebra Tree:

Relational Algebra Tree: SQLite INNER JOIN using three tables.

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';

Relational Algebra Expression:

Relational Algebra Expression: SQLite INNER JOIN using three tables.

Relational Algebra Tree:

Relational Algebra Tree: SQLite INNER JOIN using three tables.

example inner join

INNER JOINS: SQL and other Relational Databases

Previous: Create, Drop views
Next: LEFT OUTER JOIN



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/sqlite/sqlite-inner-join.php