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.
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 Tree:
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 Tree:
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 Tree:
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 Tree:
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 Tree:
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 Tree:
INNER JOINS: SQL and other Relational Databases
Previous: Create, Drop views
Next: LEFT OUTER JOIN
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics