SQL JOINS

Relational databases typically store information in various tables that are related by a set of keys (columns) that are common across those tables. This kind of storage infrastructure has many benefits, among them, ensuring data integrity at scale by chunking processes and storage across multiple tables. A fairly common task is to join rows of data from one table to another to use for some analysis.

There are few common JOINS we use in SQL:

• LEFT JOIN
• INNER JOIN
• RIGHT JOIN
• OUTER JOIN

Summary of JOINS

I have found it useful in the past to use this single venn diagram to remember left, right, inner and outer joins. You may find other representations but this one worked for me. We will explore individual join type.

LEFT JOIN

LEFT JOINS are probably the most commonly used JOINS you will see out. In this section we cover LEFT JOINS and how to implement them in SQL queries. Before we do that, it is useful to visualize the left join using a venn diagram.

A LEFT JOIN will return all rows from the first(left or TABLE A) table with matched to the second(right or TABLE B) irrespective of whether the join match exists. If a match exists, the query will return values from the right table. If not, the query will still return rows but they will contain null values.

LEFT JOIN in Action

Suppose we want to know how many customers from a particular city paid by credit card. And we have the following two tables to answer this question.

orders table

SELECT 
    order_id, 
    customer_id, 
    order_date, 
    total_amount, 
payment_method 
FROM orders
LIMIT 5;

 order_id | customer_id | order_date | total_amount | payment_method
----------+-------------+------------+--------------+----------------
        1 |           1 | 2022-02-10 |      1249.98 | Credit Card
        2 |           2 | 2022-02-15 |      2499.99 | PayPal
        3 |           3 | 2022-02-20 |       599.99 | Debit Card
        4 |           4 | 2022-02-25 |      1799.99 | Credit Card
        5 |           5 | 2022-03-05 |       349.99 | Apple Pay
(5 rows)

customers table

SELECT 
    customer_id, 
    city, 
    state 
FROM customers 
LIMIT 5;

 customer_id |    city     | state
-------------+-------------+-------
           1 | New York    | NY
           2 | Los Angeles | CA
           3 | Chicago     | IL
           4 | Houston     | TX
           5 | Phoenix     | AZ
(5 rows)

It is clear from the dataset that we need to join the two tables. In this case, this is easy, we do this using the customer_id

To implement the JOIN we simply do:

SELECT 
    a.customer_id,
    a.order_id,
    a.order_date,
    a.total_amount,
    a.payment_method,
    b.city,
    b.state        
FROM orders a 
LEFT JOIN customers b 
ON a.customer_id = b.customer_id
WHERE a.customer_id IS NOT NULL
LIMIT 5;

 customer_id  | order_id | order_date | total_amount | payment_method |    city     | state
--------------+----------+------------+--------------+----------------+-------------+-------
            1 |        1 | 2022-02-10 |      1249.98 | Credit Card    | New York    | NY
            2 |        2 | 2022-02-15 |      2499.99 | PayPal         | Los Angeles | CA
            3 |        3 | 2022-02-20 |       599.99 | Debit Card     | Chicago     | IL
            4 |        4 | 2022-02-25 |      1799.99 | Credit Card    | Houston     | TX
            5 |        5 | 2022-03-05 |       349.99 | Apple Pay      | Phoenix     | AZ
(5 rows)

Now we have a dataset that we can actually check the number of customers from a particularly city that used a specific payment method. For completeness, we can implement the query:

WITH customer_orders AS (SELECT 
        a.customer_id,
        a.order_id,
        a.order_date,
        a.total_amount,
        a.payment_method,
        b.city,
        b.state        
    FROM orders a 
    LEFT JOIN customers b 
    ON a.customer_id = b.customer_id
    WHERE a.customer_id IS NOT NULL)

SELECT customer_id, order_id, total_amount
FROM customer_orders
WHERE state = 'NY' AND payment_method = 'Credit Card';

 customer_id | order_id | total_amount
-------------+----------+--------------
           1 |        1 |      1249.98
(1 row)