SQLite Join: Natural Left Outer, Inner, Cross with Tables

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.

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 SQLite JOIN tables examples, you have to run the sqlite3.exe and open a connection to the sample database as flowing:

Step 1) In this step, open My Computer and navigate to the following directory “C:\sqlite” and 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 eliminates all other rows that do not 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 is 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 does not 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 do not 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 did not 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 do not 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 do not need to write a join condition with column names (like we did in INNER JOIN). We did not 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 than 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.

SQLite LEFT OUTER JOIN

The SQL standard defines three types of OUTER JOINs: LEFT, RIGHT, and FULL, but SQLite supports only the natural 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 does not 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

• SQLite 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 natural LEFT JOIN is the right table.
• The OUTER clause is optional; LEFT natural 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 fourth and the last student, Jena and George, have departmentIds that do not 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 does not have a departmentName that matches their departmentId value.

Let us give the previous query using the left join a deeper explanation using Venn diagrams:

The LEFT JOIN will give all the students names from the students table even if the student has a department id that does not exist in the departments table. So, the query will not 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 do not need to specify a join condition, because SQLite does not need it for the CROSS JOIN.

The SQLite will result in a 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 values (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 SQLite select from multiple tables, we just selected two columns “studentname” from the students table and the “departmentName” from the departments table.
• For the cross join, we did not 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.

FAQs