---
description: SQLite supports different types of SQL Joins, like INNER JOIN, LEFT OUTER JOIN, and CROSS JOIN. Each type of JOIN is used for the different situation as we will see in this tutorial. In this tutorial,
title: SQLite Join: Natural Left Outer, Inner, Cross with Tables
image: https://www.guru99.com/images/sqlite-join-1.png
---

 

[Skip to content](#main) 

**⚡ Smart Summary**

SQLite JOIN clauses combine rows from two or more tables using INNER JOIN, JOIN USING, NATURAL JOIN, LEFT OUTER JOIN, and CROSS JOIN, letting you match related records by shared columns and read data across a normalized database.

* 🔗 **Join Clause:** The JOIN clause links two or more tables or subqueries on a shared column, defined with an ON or USING condition.
* 🎯 **INNER JOIN:** INNER JOIN returns only the rows where the join condition matches in both tables, discarding unmatched rows.
* 🧩 **USING and NATURAL:** JOIN USING names one shared column, while NATURAL JOIN matches every identically named column automatically.
* ↩️ **LEFT OUTER JOIN:** LEFT OUTER JOIN keeps every left-table row and fills unmatched right-table columns with NULL values.
* ✖️ **CROSS JOIN:** CROSS JOIN returns the Cartesian product, pairing every left-table row with every right-table row.
* 🤖 **AI Assistance:** AI text-to-SQL tools and GitHub Copilot generate SQLite JOIN queries from plain-English prompts.

[ Read More ](javascript:void%280%29;) 

![SQLite Join]()

SQLite supports different types of [SQL](https://www.guru99.com/sql.html) 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:

[](https://www.guru99.com/images/SQLite/012116%5F1153%5FSQLiteJoinI1.jpg)

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“:

[](https://www.guru99.com/images/SQLite/012116%5F1153%5FSQLiteJoinI2.jpg)

**Step 2)** Open the database “TutorialsSampleDB.db” by the following command:

[](https://www.guru99.com/images/SQLite/012116%5F1153%5FSQLiteJoinI3.jpg)

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

## SQLite INNER JOIN

[](https://www.guru99.com/images/SQLite/012116%5F1153%5FSQLiteJoinI4.png)

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.

[](https://www.guru99.com/images/SQLite/012116%5F1153%5FSQLiteJoinI5.jpg)

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:

[](https://www.guru99.com/images/SQLite/012116%5F1153%5FSQLiteJoinI6.jpg)

### RELATED ARTICLES

* [How to Download & Install SQLite on Windows ](https://www.guru99.com/download-install-sqlite.html "How to Download & Install SQLite on Windows")
* [SQLite Create, Alter, Drop Table with Examples ](https://www.guru99.com/sqlite-create-alter-drop-table.html "SQLite Create, Alter, Drop Table with Examples")
* [SQLite Tutorial PDF Book for Beginners ](https://www.guru99.com/sqlite-pdf.html "SQLite Tutorial PDF Book for Beginners")
* [Top 20 SQLite Interview Questions and Answers (2026) ](https://www.guru99.com/sqlite-interview-questions.html "Top 20 SQLite Interview Questions and Answers (2026)")

## 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:

[](https://www.guru99.com/images/SQLite/012116%5F1153%5FSQLiteJoinI7.jpg)

## 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.

[](https://www.guru99.com/images/SQLite/012116%5F1153%5FSQLiteJoinI8.jpg)

## 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.

[](https://www.guru99.com/images/SQLite/012116%5F1153%5FSQLiteJoinI9.jpg)

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

[](https://www.guru99.com/images/SQLite/012116%5F1153%5FSQLiteJoinI10.png)

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.

[](https://www.guru99.com/images/SQLite/012116%5F1153%5FSQLiteJoinI11.jpg)

## FAQs

🔀 Does SQLite support RIGHT JOIN and FULL OUTER JOIN?

SQLite added RIGHT JOIN and FULL OUTER JOIN support in version 3.39.0, released in 2022\. On older builds you emulate a RIGHT JOIN by swapping the tables in a LEFT JOIN, and a FULL OUTER JOIN by combining two LEFT JOINs with UNION.

🔁 What is a self join in SQLite?

A self join joins a table to itself using table aliases, so one copy acts as the left table and another as the right. It is useful for comparing rows within the same table, such as matching employees to their managers.

🧮 Can you join more than two tables in one SQLite query?

Yes. You chain several JOIN clauses in a single SELECT, each with its own ON or USING condition, for example FROM A JOIN B ON … JOIN C ON …. SQLite joins the tables left to right into one combined result set.

🎯 Which join type does SQLite use when you write only JOIN?

Writing JOIN alone is the same as INNER JOIN in SQLite. Both keep only the rows that satisfy the ON or USING condition, so unmatched rows are dropped. The INNER keyword is optional, making JOIN and INNER JOIN interchangeable.

⚡ How do indexes affect SQLite JOIN performance?

Creating an index on the columns used in the join condition lets SQLite match rows without scanning entire tables, which speeds up joins on large datasets. Indexing foreign-key columns and running ANALYZE to refresh statistics further improve join query performance.

🔍 What is the difference between INNER JOIN and LEFT OUTER JOIN?

An INNER JOIN returns only rows that match in both tables. A LEFT OUTER JOIN returns every row from the left table plus matching right-table rows, filling unmatched right columns with NULL. So a LEFT JOIN never drops left-table rows.

🤖 Can AI text-to-SQL tools write SQLite JOIN queries?

Yes. AI text-to-SQL assistants turn plain-English requests into SQLite INNER, LEFT, NATURAL, and CROSS JOIN statements. Providing your table names, column names, and relationships improves accuracy, and every generated join should be reviewed and tested before running on real data.

🧠 Can GitHub Copilot generate SQLite JOIN statements?

[GitHub Copilot](https://github.com/features/copilot) suggests SQLite JOIN queries inline in editors like VS Code, completing INNER JOIN, LEFT JOIN, and ON or USING clauses. It reads nearby schema and comments, so its suggestions reuse your real table and column names.

#### Summarize this post with:

ChatGPT Perplexity Grok Google AI 

**Stay Updated on AI** **Get Weekly AI Skills, Trends, Actionable Advice.** 

##### Sign up for the newsletter

Subscribe for Free 

You have successfully subscribed.  
Please check your inbox. 

![AI-Newsletter]() Chosen by over **350,000+** professionals 

[Scroll to top ](#wrapper)Scroll to top 

× 

Toggle Menu Close 

Search for: 

Search

```json
{"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://www.guru99.com/#organization","name":"Guru99","sameAs":["https://www.facebook.com/Guru99Official","https://twitter.com/guru99com"],"logo":{"@type":"ImageObject","@id":"https://www.guru99.com/#logo","url":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","contentUrl":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","caption":"Guru99","inLanguage":"en-US"}},{"@type":"WebSite","@id":"https://www.guru99.com/#website","url":"https://www.guru99.com","name":"Guru99","publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US"},{"@type":"ImageObject","@id":"https://www.guru99.com/images/sqlite-join-1.png","url":"https://www.guru99.com/images/sqlite-join-1.png","width":"700","height":"250","caption":"SQLite Join","inLanguage":"en-US"},{"@type":"BreadcrumbList","@id":"https://www.guru99.com/sqlite-join.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":"1","item":{"@id":"https://www.guru99.com","name":"Home"}},{"@type":"ListItem","position":"2","item":{"@id":"https://www.guru99.com/sqlite","name":"SQLite"}},{"@type":"ListItem","position":"3","item":{"@id":"https://www.guru99.com/sqlite-join.html","name":"SQLite Join: Natural Left Outer, Inner, Cross with Tables"}}]},{"@type":"WebPage","@id":"https://www.guru99.com/sqlite-join.html#webpage","url":"https://www.guru99.com/sqlite-join.html","name":"SQLite Join: Natural Left Outer, Inner, Cross with Tables","dateModified":"2026-07-13T12:25:13+05:30","isPartOf":{"@id":"https://www.guru99.com/#website"},"primaryImageOfPage":{"@id":"https://www.guru99.com/images/sqlite-join-1.png"},"inLanguage":"en-US","breadcrumb":{"@id":"https://www.guru99.com/sqlite-join.html#breadcrumb"}},{"@type":"Person","@id":"https://www.guru99.com/author/marcus","name":"Marcus Allen","description":"I'm Marcus Allen, an SQL and Data Warehousing Consultant with over a decade of experience in designing and optimizing large-scale data solutions.","url":"https://www.guru99.com/author/marcus","image":{"@type":"ImageObject","@id":"https://www.guru99.com/images/marcus-allen-author.png","url":"https://www.guru99.com/images/marcus-allen-author.png","caption":"Marcus Allen","inLanguage":"en-US"},"worksFor":{"@id":"https://www.guru99.com/#organization"}},{"articleSection":"SQLite","headline":"SQLite Join: Natural Left Outer, Inner, Cross with Tables","description":"SQLite supports different types of SQL Joins, like INNER JOIN, LEFT OUTER JOIN, and CROSS JOIN. Each type of JOIN is used for the different situation as we will see in this tutorial. In this tutorial,","keywords":"sqlite","speakable":{"@type":"SpeakableSpecification","cssSelector":[".entry-title",".summary"]},"@type":"Article","author":{"@id":"https://www.guru99.com/author/marcus","name":"Marcus Allen"},"dateModified":"2026-07-13T12:25:13+05:30","image":{"@id":"https://www.guru99.com/images/sqlite-join-1.png"},"copyrightYear":"2026","name":"SQLite Join: Natural Left Outer, Inner, Cross with Tables","subjectOf":[{"@type":"FAQPage","mainEntity":[{"@type":"Question","name":"Does SQLite support RIGHT JOIN and FULL OUTER JOIN?","acceptedAnswer":{"@type":"Answer","text":"SQLite added RIGHT JOIN and FULL OUTER JOIN support in version 3.39.0, released in 2022. On older builds you emulate a RIGHT JOIN by swapping the tables in a LEFT JOIN, and a FULL OUTER JOIN by combining two LEFT JOINs with UNION."}},{"@type":"Question","name":"What is a self join in SQLite?","acceptedAnswer":{"@type":"Answer","text":"A self join joins a table to itself using table aliases, so one copy acts as the left table and another as the right. It is useful for comparing rows within the same table, such as matching employees to their managers."}},{"@type":"Question","name":"Can you join more than two tables in one SQLite query?","acceptedAnswer":{"@type":"Answer","text":"Yes. You chain several JOIN clauses in a single SELECT, each with its own ON or USING condition, for example FROM A JOIN B ON \u2026 JOIN C ON \u2026. SQLite joins the tables left to right into one combined result set."}},{"@type":"Question","name":"Which join type does SQLite use when you write only JOIN?","acceptedAnswer":{"@type":"Answer","text":"Writing JOIN alone is the same as INNER JOIN in SQLite. Both keep only the rows that satisfy the ON or USING condition, so unmatched rows are dropped. The INNER keyword is optional, making JOIN and INNER JOIN interchangeable."}},{"@type":"Question","name":"How do indexes affect SQLite JOIN performance?","acceptedAnswer":{"@type":"Answer","text":"Creating an index on the columns used in the join condition lets SQLite match rows without scanning entire tables, which speeds up joins on large datasets. Indexing foreign-key columns and running ANALYZE to refresh statistics further improve join query performance."}},{"@type":"Question","name":"What is the difference between INNER JOIN and LEFT OUTER JOIN?","acceptedAnswer":{"@type":"Answer","text":"An INNER JOIN returns only rows that match in both tables. A LEFT OUTER JOIN returns every row from the left table plus matching right-table rows, filling unmatched right columns with NULL. So a LEFT JOIN never drops left-table rows."}},{"@type":"Question","name":"Can AI text-to-SQL tools write SQLite JOIN queries?","acceptedAnswer":{"@type":"Answer","text":"Yes. AI text-to-SQL assistants turn plain-English requests into SQLite INNER, LEFT, NATURAL, and CROSS JOIN statements. Providing your table names, column names, and relationships improves accuracy, and every generated join should be reviewed and tested before running on real data."}},{"@type":"Question","name":"Can GitHub Copilot generate SQLite JOIN statements?","acceptedAnswer":{"@type":"Answer","text":"GitHub Copilot suggests SQLite JOIN queries inline in editors like VS Code, completing INNER JOIN, LEFT JOIN, and ON or USING clauses. It reads nearby schema and comments, so its suggestions reuse your real table and column names."}}]}],"@id":"https://www.guru99.com/sqlite-join.html#schema-1142735","isPartOf":{"@id":"https://www.guru99.com/sqlite-join.html#webpage"},"publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US","mainEntityOfPage":{"@id":"https://www.guru99.com/sqlite-join.html#webpage"}}]}
```