PostgreSQL Joins: A Visual Explanation of PostgreSQL Joins, pic from there:

• Joins combine the same or different tables based on the values of a common column (usually primary key of the main table and foreign key in the other)

• Postgres supports a lot of different joins

• If the column name is in both tables and “is ambiguous”, you can specify the table name:

  • select * from mondiall.city inner join mondiall.country on city.name=country.capital limit 10;

• Inner join:

  • intersection of two sets, all rows where the key with a value if that value exists in both

• Left join:

  • intersection plus part of the left table that’s not in the right one - so basically left table with info from right if available
  • same as left outer join

• Right join - same as left join but opposite, where it starts with values from the right table

• Full outer join - basically a union

• postgres=# select * from mondiall.city left join mondiall.country on city.name=country.capital where country.name is NULL order by city.name;