How to ‘Join’ in PostgreSQL

How we can accessed multiple table at a time? that were join queries came in handy. Queries that access multiple tables (or multiple instances of the same table) at one time are called join queries. They combine rows from one table with rows from a second table, with an expression specifying which rows are to be paired. 

there are so many join command in postgresql, such as:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN

---

1. INNER JOIN

An INNER JOIN only includes records in which the key is in both tables. You can see here that the id field matches for values of 1 and 4 only. With inner joins we look for matches in the right table corresponding to all entries in the key field in the left table.

SELECT cities.name AS city, countries.name AS country, region  
FROM cities
  INNER JOIN countries 
    ON cities.country_code = countries.code;

---

2. LEFT JOIN

The LEFT JOIN clause starts selecting data from the left table. For each row in the left table.

The following Venn diagram illustrates the LEFT JOIN operation:

the example :

SELECT
	film.film_id,
	film.title,
	inventory_id
FROM
	film
LEFT JOIN inventory 
   ON inventory.film_id = film.film_id
WHERE inventory.film_id IS NULL
ORDER BY title;

result :

the LEFT JOIN compares the value in the film.film_id column with the value of each row in the inventory.film_id column in the right table.

If these values are equal, the left join clause creates a new row that contains columns that appear in the SELECT clause and adds this row to the result set.

In case these values are not equal, the left join clause also creates a new row that contains columns that appear in the SELECT clause. In addition, it fills the columns that come from the right table with NULL.

If both tables have the same column name used in the ON clause, you can use the USING syntax like this:

SELECT
	f.film_id,
	title,
	inventory_id
FROM
	film f
LEFT JOIN inventory i USING (film_id)
WHERE i.film_id IS NULL
ORDER BY title;

Note that the LEFT JOIN is also referred to as LEFT OUTER JOIN.

---

3. RIGHT JOIN

the RIGHT JOIN selects all rows from the right table whether or not they have matching rows from the left table. the following table will show how the right join works.

suppose we have two table, the films and film_reviews tables:

SELECT * FROM films;

SELECT * FROM film_reviews;

The film id 1 has two reviews. The film id 2 has 1 review. The film id 3 has no reviews. The review id 4 does not associate with any film.

SELECT 
   review, 
   title
FROM 
   films
RIGHT JOIN film_reviews 
   ON film_reviews.film_id = films.film_id;

the result:

In this statement, films is the left table and film_reviews is the right table.

The RIGHT JOIN clause starts selecting data from the right table (film_reviews).

For each row from the right table (film_reviews), it checks if the value in the film_id column of the film_reviews table equals the value in the film_id column of every row from the left table (films).

If they are equal, the RIGHT JOIN creates a new row that contains columns from both tables specified in the SELECT clause and includes this new row in the result set.

Otherwise, the RIGHT JOIN still creates a new row that contains columns from both tables and includes this new row in the result set. However, it fills the columns from the left table (films) with NULL.

In other words, the RIGHT JOIN selects all rows from the right table whether or not they have matching rows from the left table.

Based on the data from the films and film_reviews tables:

• The review with id 1 matches with the film id 1.
• The review with id 2 matches with film id 2.
• The review with id 3 matches with the film id 2.
• The review with id 4 doesn’t match any film therefore the title column is filled with NULL.

Note that the RIGHT OUTER JOIN is the same as RIGHT JOIN. The OUTER keyword is optional

---

4. FULL JOIN

Suppose that you want to perform a full outer join of two tables: A and B. The following illustrates the syntax of the FULL OUTER JOIN:

SELECT * FROM A
FULL [OUTER] JOIN B on A.id = B.id;

In this syntax, the OUTER keyword is optional.

The full outer join combines the results of both left join and right join.

If the rows in the joined table do not match, the full outer join sets NULL values for every column of the table that does not have the matching row.

If a row from one table matches a row in another table, the result row will contain columns populated from columns of rows from both tables.

The following Venn diagram illustrates the FULL OUTER JOIN operation:

FULL JOIN example:

Suppose we have two tables, the departments and employees tables:

SELECT * FROM departments;

SELECT * FROM employees;

now we will perform the FULL OUTER JOIN to query data from both employees and departments tables.

SELECT
	employee_name,
	department_name
FROM
	employees e
FULL OUTER JOIN departments d 
        ON d.department_id = e.department_id;

The result set includes every employee who belongs to a department and every department which have an employee. In addition, it includes every employee who does not belong to a department and every department that does not have an employee.