SQLite JOIN TABLES


 

SQLite: Joins

This SQLite tutorial explains how to use SQLite JOINS (inner and outer) with syntax, visual illustrations, and examples.

 

Description:

SQLite JOINS are used to retrieve data from multiple tables. A SQLite JOIN is performed whenever two or more tables are joined in a SQLite statement.

There are different types of SQLite joins:

  • SQLite INNER JOIN (or sometimes called simple join)
  • SQLite LEFT OUTER JOIN (or sometimes called LEFT JOIN)

Note: RIGHT OUTER JOIN and FULL OUTER JOIN are not supported in SQLite.

 

INNER JOIN (simple join)

SQLite INNER JOINS return all rows from multiple tables where the join condition is met. It is the most common type of join.

 

Syntax:

 
Note: In short, the INNER JOIN keywords are replaced with JOIN.

The SQLite INNER JOIN would return the records where table1 and table2 intersect.
LEFT OUTER JOIN

SQLite LEFT OUTER JOIN returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).

 

Syntax:

 
Note: In short, the LEFT OUTER JOIN keywords are replaced with LEFT JOIN.

The SQLite LEFT OUTER JOIN would return the all records from table1 and only those records from table2 that intersect with table1.
Examples:

INNER JOIN Example:

  • The following SQLite statement will return all employee with salary:

    name salary
    chiru 30000
    arunesh 60000
    naga 40000
    nithya 35000
    jaya 35000
    uma 5000
    mathu 5000

     

LEFT JOIN Example:

  • The following SQLite statement will return all employee with and with out salary:

    name salary
    chiru 30000
    arunesh 60000
    naga 40000
    nithya 35000
    jaya 35000
    uma 5000
    mathu 5000
    anitha
    kannan
    deepak