Learning about joins

Let’s address what a join is, how many types of joins exist, and what they are used for. We can think of a join as a combination of rows from two or more tables.

For example, the following query returns all the combinations from the rows of the category table and the rows of the posts table:

forumdb=>  select c.pk,c.title,p.pk,p.category,p.title from categories c,posts p;
 pk |         title         | pk | category |        title   
----+-----------------------+----+----------+------------
  1 | Database              |  1 |        1 | Indexing PostgreSQL
  2 | Unix                  |  1 |        1 | Indexing PostgreSQL
  3 | Programming Languages |  1 |        1 | Indexing PostgreSQL
  4 | New Category          |  1 |        1 | Indexing PostgreSQL
  5 | Database              |  1 |        1 | Indexing PostgreSQL
  1 | Database              |  2 |        1 | Indexing Mysql
  2 | Unix                  |  2 |        1 | Indexing Mysql
  3 | Programming...