§2023-04-01

• PostgreSQL Joins

PostgreSQL join is used to combine columns from one (self-join) or more tables based on the values of the common columns between related tables. The common columns are typically the primary key columns of the first table and foreign key columns of the second table.

PostgreSQL supports inner join, left join, right join, full outer join, cross join, natural join, and a special kind of join called self-join.

&set up simple database

DROP DATABASE IF EXISTS join_demo_db;
CREATE DATABASE join_demo_db;

--- if you use psql, please do \c join_demo_db
DROP SCHEMA IF EXISTS join_demo;
CREATE SCHEMA join_demo;

SET SEARCH_PATH = join_demo;
SHOW SEARCH_PATH;

--- CRETAE TABLE and SEED it

CREATE TABLE basket_a (
    a INT PRIMARY KEY,
    fruit_a VARCHAR (100) NOT NULL
);

CREATE TABLE basket_b (
    b INT PRIMARY KEY,
    fruit_b VARCHAR (100) NOT NULL
);

INSERT INTO basket_a (a, fruit_a)
VALUES
    (1, 'Apple'),
    (2, 'Orange'),
    (3, 'Banana'),
    (4, 'Cucumber');

INSERT INTO basket_b (b, fruit_b)
VALUES
    (1, 'Orange'),
    (2, 'Apple'),
    (3, 'Watermelon'),
    (4, 'Pear');

• INNER JOIN or just JOIN

SELECT  a, fruit_a, b, fruit_b
FROM  basket_a INNER JOIN basket_b
ON fruit_a = fruit_b;

 a | fruit_a | b | fruit_b 
---+---------+---+---------
 1 | Apple   | 2 | Apple
 2 | Orange  | 1 | Orange
(2 rows)

• The left join starts selecting data from the left table. It compares values in the fruit_a column with the values in the fruit_b column in the basket_b table.
  • If these values are equal, the left join creates a new row that contains columns of both tables and adds this new row to the result set. (see the row #1 and #2 in the result set).
  • In case the values do not equal, the left join also creates a new row that contains columns from both tables and adds it to the result set. However, it fills the columns of the right table (basket_b) with null.

SELECT a, fruit_a, b, fruit_b
FROM basket_a LEFT JOIN basket_b 
ON fruit_a = fruit_b;

 a | fruit_a  | b | fruit_b 
---+----------+---+---------
 1 | Apple    | 2 | Apple
 2 | Orange   | 1 | Orange
 3 | Banana   |   | 
 4 | Cucumber |   | 
(4 rows)

• To select rows from the left table that do not have matching rows in the right table, you use the left join with a WHERE clause. For example:

SELECT a, fruit_a, b, fruit_b
FROM basket_a LEFT JOIN basket_b 
ON fruit_a = fruit_b
WHERE b IS NULL;

a | fruit_a  | b | fruit_b 
---+----------+---+---------
 3 | Banana   |   | 
 4 | Cucumber |   | 
(2 rows)

Note that the LEFT JOIN is the same as the LEFT OUTER JOIN so you can use them interchangeably.

SELECT a, fruit_a, b, fruit_b
FROM basket_a LEFT OUTER JOIN basket_b 
ON fruit_a = fruit_b;
 a | fruit_a  | b | fruit_b 
---+----------+---+---------
 1 | Apple    | 2 | Apple
 2 | Orange   | 1 | Orange
 3 | Banana   |   | 
 4 | Cucumber |   | 
(4 rows)

SELECT a, fruit_a, b, fruit_b
FROM basket_a LEFT OUTER JOIN basket_b 
ON fruit_a = fruit_b
WHERE b IS NULL;
 a | fruit_a  | b | fruit_b 
---+----------+---+---------
 3 | Banana   |   | 
 4 | Cucumber |   | 
(2 rows)

• PostgreSQL right join
  • The right join is a reversed version of the left join.

FROM basket_a RIGHT JOIN basket_b 
ON fruit_a = fruit_b;
 a | fruit_a | b |  fruit_b   
---+---------+---+------------
 2 | Orange  | 1 | Orange
 1 | Apple   | 2 | Apple
   |         | 3 | Watermelon
   |         | 4 | Pear
(4 rows)

SELECT a, fruit_a, b, fruit_b
FROM basket_a RIGHT JOIN basket_b 
ON fruit_a = fruit_b
WHERE a IS NULL;

 a | fruit_a | b |  fruit_b   
---+---------+---+------------
   |         | 3 | Watermelon
   |         | 4 | Pear
(2 rows)

The RIGHT JOIN and RIGHT OUTER JOIN are the same therefore you can use them interchangeably.

• PostgreSQL full outer join
  • The full outer join or full join returns a result set that contains all rows from both left and right tables, with the matching rows from both sides if available. In case there is no match, the columns of the table will be filled with NULL.

SELECT a, fruit_a, b, fruit_b
FROM basket_a FULL OUTER JOIN basket_b 
ON fruit_a = fruit_b;

 a | fruit_a  | b |  fruit_b   
---+----------+---+------------
 1 | Apple    | 2 | Apple
 2 | Orange   | 1 | Orange
 3 | Banana   |   | 
 4 | Cucumber |   | 
   |          | 3 | Watermelon
   |          | 4 | Pear
(6 rows)

• To return rows in a table that do not have matching rows in the other, you use the full join with a WHERE clause like this:

SELECT a, fruit_a, b, fruit_b
FROM basket_a FULL OUTER JOIN basket_b 
ON fruit_a = fruit_b
WHERE a IS NULL OR b IS NULL;

a | fruit_a  | b |  fruit_b   
---+----------+---+------------
 3 | Banana   |   | 
 4 | Cucumber |   | 
   |          | 3 | Watermelon
   |          | 4 | Pear
(4 rows)