SQLite Join Tables: Inner, Natural, Left Outer, Cross (Examples)

SQLite supports different types of SQL Joins, like INNER JOIN, LEFT OUTER JOIN, and CROSS JOIN. Each type of JOIN is used for a different situation as we will see in this tutorial.

In this tutorial, you will learn-

• Introduction to SQLite JOIN Clause
• INNER JOIN
• JOIN … USING
• NATURAL JOIN
• LEFT OUTER JOIN
• CROSS JOIN

Introduction to SQLite JOIN Clause

When you are working on a database with multiple tables, you often need to get data from these multiple tables.

With the JOIN clause, you can link two or more tables or subqueries by joining them. Also, you can define by which column you need to link the tables and by which conditions.

Any JOIN clause must have the following syntax:

Each join clause contains:

• A table or a subquery which is the left table; the table or the subquery before the join clause (on the left of it).
• JOIN operator – specify the join type (either INNER JOIN, LEFT OUTER JOIN, or CROSS JOIN).
• JOIN-constraint – after you specified the tables or subqueries to join, you need to specify a join constraint, which will be a condition on which the matching rows that match that condition will be selected depending on the join type.

Note that, for all the following examples, you have to run the sqlite3.exe and open a connection to the sample database as flowing:

Step 1) In this step,

1. Open My Computer and navigate to the following directory "C:\sqlite" and
2. Then open "sqlite3.exe":

Step 2) Open the database "TutorialsSampleDB.db" by the following command:

Now you are ready to run any type of query on the database.

SQLite INNER JOIN

The INNER JOIN returns only the rows that match the join condition and eliminate all other rows that don't match the join condition.

Example

In the following example, we will join the two tables "Students" and "Departments" with DepartmentId to get the department name for each student, as follows:

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Explanation of code:

The INNER JOIN works as following:

• In the Select clause, you can select whatever columns you want to select from the two referenced tables.
• The INNER JOIN clause is written after the first table referenced with "From" clause.
• Then the join condition is specified with ON.
• Aliases can be specified for referenced tables.
• The INNER word is optional, you can just write JOIN.

Output:

• The INNER JOIN produces the records from both - the students and the department's tables that match the condition which is "Students.DepartmentId = Departments.DepartmentId ". The unmatched rows will be ignored and not included in the result.
• That's why only 8 students from 10 students were returned from this query with IT, math, and physics departments. Whereas the students "Jena" and "George" were not included, because they have a null department Id, which doesn't match the departmentId column from the departments table. As following:

  

SQLite JOIN … USING

The INNER JOIN can be written using the "USING" clause to avoid redundancy, so instead of writing "ON Students.DepartmentId = Departments.DepartmentId", you can just write "USING(DepartmentID)".

You can use "JOIN .. USING" whenever the columns you will compare in the join condition are the same name. In such cases, there is no need to repeat them using the on condition and just state the column names and SQLite will detect that.

The Difference between the INNER JOIN and JOIN .. USING:

With "JOIN … USING" you don't write a join condition, you just write the join column which is in common between the two joined table, instead of writing table1 "INNER JOIN table2 ON table1.cola = table2.cola" we write it like "table1 JOIN table2 USING(cola)".

Example

In the following example, we will join the two tables "Students" and "Departments" with DepartmentId to get the department name for each student, as follows:

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
INNER JOIN Departments USING(DepartmentId);

Explanation

• Unlike the previous example, we didn't write "ON Students.DepartmentId = Departments.DepartmentId". We just wrote "USING(DepartmentId)".
• SQLite infers the join condition automatically and compares the DepartmentId from both the tables - Students and Departments.
• You can use this syntax whenever the two columns you are comparing are with the same name.

Output

• This will give you the same exact result as the previous example:

SQLite NATURAL JOIN

A NATURAL JOIN is similar to a JOIN...USING, the difference is that it automatically tests for equality between the values of every column that exists in both tables.

The difference between INNER JOIN and a NATURAL JOIN:

• In INNER JOIN, you have to specify a join condition which the inner join uses to join the two tables. Whereas in the natural join, you don't write a join condition. You just write the two tables' names without any condition. Then the natural join will automatically test for equality between the values for every column exists in both tables. Natural join infers the join condition automatically.
• In the NATURAL JOIN, all the columns from both tables with the same name will be matched against each other. For example, if we have two tables with two column names in common (the two columns exists with the same name in the two tables), then the natural join will join the two tables by comparing the values of both columns and not just from one column.

Example

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
Natural JOIN Departments;

Explanation

• We don't need to write a join condition with column names (like we did in INNER JOIN). We didn't even need to write the column name once (like we did in JOIN USING).
• The natural join will scan both the columns from the two tables. It will detect that the condition should be composed of comparing DepartmentId from both the two tables Students and Departments.

Output

• The Natural JOIN will give you the same exact output as the output we got from the INNER JOIN and the JOIN USING examples. Because in our example all three queries are equivalent. But in some cases, the output will be different from inner join then in a natural join. For example, if there are more tables with the same names, then the natural join will match all the columns against each other. However, the inner join will match only the columns in the join condition (more details on the next section; the difference between the inner join and natural join).

SQLite LEFT OUTER JOIN

The SQL standard defines three types of OUTER JOINs: LEFT, RIGHT, and FULL but SQLite supports only the LEFT OUTER JOIN.

In LEFT OUTER JOIN, all the values of the columns you select from the left table will be included in the result of the query, so regardless of the value matches the join condition or not, it will be included in the result.

So if the left table has 'n' rows, the results of the query will have 'n' rows. However, for the values of the columns coming from the right table, if any value that doesn't match the join condition it will contain a "null" value.

So, you will get a number of rows equivalent to the number of rows in the left join. So that you will get the matching rows from both tables (like the INNER JOIN results), plus the un-matching rows from the left table.

Example

In the following example, we will try the "LEFT JOIN" to join the two tables "Students" and "Departments":

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students             -- this is the left table
LEFT JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Explanation

• LEFT JOIN syntax is the same as INNER JOIN; you write the LEFT JOIN between the two tables, and then the join condition comes after the ON clause.
• The first table after the from clause is the left table. Whereas the second table specified after the left join is the right table.
• The OUTER clause is optional; LEFT OUTER JOIN is the same as LEFT JOIN.

Output

• As you can see all the rows from the students table are included which are 10 students in total. Even if the forth and the last student, Jena, and George departmentIds doesn't exist in the Departments table, they are included as well.
• And in these cases, the departmentName value for both Jena and George will be "null" because the departments table doesn't have a departmentName that match their departmentId value.

Let's give the previous query using the left join a deeper explanation using Van diagrams:

The LEFT JOIN will give all the students names from the students table even if the student has a department id that doesn't exist in the departments table. So, the query won't give you only the matching rows as the INNER JOIN, but will give you the extra part which have the unmatching rows from the left table which is the students table.

Note that any student name that has no matching department will have a "null" value for department name, because there is no matching value for it, and those values are the values in the un-matching rows.

SQLite CROSS JOIN

A CROSS JOIN gives the Cartesian product for the selected columns of the two joined tables, by matching all the values from the first table with all the values from the second table.

So, for every value in the first table, you will get 'n' matches from the second table where n is the number of second table rows.

Unlike INNER JOIN and LEFT OUTER JOIN, with CROSS JOIN, you don't need to specify a join condition, because SQLite doesn't need it for the CROSS JOIN.

The SQLite will result in logical results set by combining all the values from the first table with all the values from the second table.

For example, if you selected a column from the first table (colA) and another column from the second table (colB). The colA contains two value (1,2) and the colB also contains two values (3,4).

Then the result of the CROSS JOIN will be four rows:

• Two rows by combining the first value from colA which is 1 with the two values of the colB (3,4) which will be (1,3), (1,4).
• Likewise, two rows by combining the second value from colA which is 2 with the two values of the colB (3,4) which are (2,3), (2,4).

Example

In the following query we will try CROSS JOIN between the Students and Departments tables:

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
CROSS JOIN Departments;

Explanation

• In the select clause, we just selected two columns "studentname" from the students table and the "departmentName" from the departments table.
• For the cross join, we didn't specify any join condition just the two tables combined with CROSS JOIN in the middle of them.

Output:

As you can see, the result is 40 rows; 10 values from the students table matched against the 4 departments from the departments table. As following:

• Four values for the four departments from the departments table matched with the first student Michel.
• Four values for the Four departments from the departments table matched with the second student John.
• Four values for the Four departments from the departments table matched with the third student Jack.

  .. and so on.

Summary

Using SQLite JOINs, you can link one or more table or subquery together to select columns from both of the tables or subqueries.