You can retrieve records from two or more tables by executing a join query. There are five types of join queries you can execute in the Structured Query Language, but the SQLite engine only support those discussed below. Further more, these tables will be used to demonstrate SQL’s join queries.
An INNER JOIN query returns rows when there is at least one match in both tables. A join condition determines whether a match is made. An INNER JOIN query answers the question, “What rows in the left table match rows in the right table for the join condition?”
Here is the INNER JOIN query and Venn diagram to fetch rows from the authors and books table. As you can see, you don’t have to put the INNER keyword in the query.
This diagram shows how above INNER JOIN query work.
The join condition evaluates the left table’s primary key (authorID) against the right table’s foreign key (authorID), for each row in the right table (books). If both keys match, a record is placed in a temporary table. In above diagram, red lines show the join conditions. Notice that the query returned no record for the primary key number 4, that’s because there’s no matching foreign key in the right table for that key.
If you understand how the INNER JOIN query work, then you should have no problem understanding how the next two join queries presented below work.
LEFT OUTER JOIN
A LEFT OUTER JOIN query fetch all rows from the left table (authors), even if there are no match in the right table (books). Here is the syntax and Venn diagram of the LEFT OUTER JOIN query. As you can see, you don’t have to put the OUTER keyword in the query.
This diagram shows how aove LEFT OUTER JOIN query work.
The CROSS JOIN or Cartesian join return rows from both tables, which is the number of rows in the left table multiplied by the number of rows in the right table, if no WHERE clause is used in a CROSS JOIN query.
You should avoid executing a Cartesian join query because it may run for a long time, if the database tables contain a huge amount of records. Besides, records returned from a Cartesian join query is not very useful to the user of your application. If the WHERE clause is used in a CROSS JOIN query, it functions like an INNER JOIN query.
Here is the syntax and Venn diagram of the LEFT OUTER JOIN query. You don’t have to put the CROSS keyword in the query.
Above CROSS JOIN query will return 36 rows, as shown in the image below. That’s because each row in the authors table is multiplied by each row in the books table (4 x 9 = 36).
That’s pretty much it for this week’s workshop. Next week, you’ll learn how to implement above JOIN queries in the SqliteDatabase application.