SQLite Tutorial SQLite Advanced SQLite Database SQLite References

SQLite JOIN Keyword



The SQLite JOIN keyword is used to combine rows of two or more tables based on common column between them. There are four types of JOINs in SQLite:

  • INNER JOIN: It is sometimes called simple JOIN. It returns records based on matching rows in both tables.
  • LEFT JOIN: It is sometimes called LEFT OUTER JOIN. It returns records which contains all rows from left table and matching rows from right tables.
  • CROSS JOIN: Returns records which contains all rows from both tables. It is sometimes called CARTESIAN JOIN because in the absence of a WHERE condition it behaves like a CARTESIAN PRODUCT i.e., the number of rows in the result-set is the product of the number of rows of the two tables.
SQLite JOINs

Example:

Consider a database containing tables called Employee and Contact_Info with the following records:

Table 1: Employee table

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

Table 2: Contact_Info table

Phone_NumberEmpIDAddressGender
+1-80540980002Brooklyn, New York, USAF
+33-1479961013Grenelle, Paris, FranceM
+31-2011503194Geuzenveld, Amsterdam, NetherlandsF
+86-10997324586Yizhuangzhen, Beijing, ChinaM
+65-672348247Yishun, SingaporeM
+81-3577990728Koto City, Tokyo, JapanM

  • The JOIN keyword is used with Employee and Contact_Info tables based on common column EmpID. It returns Name, Age and Address columns based on match in both tables. The query is given below:

    SELECT Employee.Name, Employee.Age, Contact_Info.Address 
    FROM Employee
    JOIN Contact_Info
    ON Employee.EmpID = Contact_Info.EmpID;
    

    This will produce the result as shown below:

    NameAgeAddress
    Marry24Brooklyn, New York, USA
    Jo27Grenelle, Paris, France
    Kim30Geuzenveld, Amsterdam, Netherlands
    Huang28Yizhuangzhen, Beijing, China

❮ SQLite Keywords