YSQL Tutorial: JOINS

In this tutorial you’ll learn how to use YugaByte DB’s PostgreSQL-compatible YSQL interface to query data from the Northwind sample database using a variety of JOINs. For detailed information concerning JOINs, please refer to the official PostgreSQL documentation.

To start, make sure you have all the necessary prerequisites and have logged into the YSQL shell.fetch

Note: These instructions last tested with YugaByte DB 1.3

Let's begin!

INNER JOIN

SELECT with an INNER JOIN

In this exercise we will query the employees table using an INNER JOIN with the orders table.

SELECT DISTINCT employees.employee_id,
       employees.last_name,
       employees.first_name,
       employees.title
FROM employees
INNER JOIN orders ON employees.employee_id = orders.employee_id;

The query should return 9 rows.

SELECT with an INNER JOIN and ORDER BY

In this exercise we will query the employees table by using an INNER JOIN with the orders table and order the results by product_id in a descending order.

SELECT DISTINCT products.product_id,
                products.product_name,
                order_details.unit_price
FROM products
INNER JOIN order_details ON products.product_id = order_details.product_id
ORDER BY products.product_id DESC;

The query should return 156 rows.

SELECT with an INNER JOIN and WHERE clause

In this exercise we will query the products table by using an INNER JOIN with the order_details table where the product_id equals 2.

SELECT DISTINCT products.product_id,
                products.product_name,
                order_details.order_id
FROM products
INNER JOIN order_details ON products.product_id = order_details.product_id
WHERE products.product_id = 2;

The query should return 44 rows.

SELECT with an INNER JOIN and three tables

In this exercise we will query the orders table by using an INNER JOIN with the customers and _employees_table.

SELECT customers.company_name,
       employees.first_name,
       orders.order_id
FROM orders
INNER JOIN customers ON customers.customer_id = orders.customer_id
INNER JOIN employees ON employees.employee_id = orders.employee_id;

The query should return 830 rows.

OUTER JOIN

SELECT with a LEFT OUTER JOIN

In this exercise we will query the customers table using a LEFT JOIN with the orders table.

SELECT DISTINCT customers.company_name,
                customers.contact_name,
                orders.ship_region
FROM customers
LEFT JOIN orders ON customers.region = orders.ship_region
ORDER BY company_name DESC;

The query should return 91 rows.

SELECT with a LEFT OUTER JOIN with rows only from the left table

In this exercise we will query the customers table XXX.

TBD

The query should return XX rows.

SELECT with a RIGHT OUTER JOIN

In this exercise we will query the customers table using a RIGHT JOIN with the orders table.

SELECT DISTINCT customers.company_name,
                customers.contact_name,
                orders.ship_region
FROM customers
RIGHT JOIN orders ON customers.region = orders.ship_region
ORDER BY company_name DESC;

The query should return 33 rows.

SELECT with FULL RIGHT OUTER JOIN

In this exercise we will query xxx

xxx

The query should return xx rows.

SELECT with FULL OUTER JOIN

In this exercise we will query the customers table using a FULL OUTER JOIN with the orders table.

SELECT DISTINCT customers.company_name,
                customers.contact_name,
                orders.ship_region
FROM customers
FULL OUTER JOIN orders ON customers.region = orders.ship_region
ORDER BY company_name DESC;

The query should return 93 rows.

SELECT with FULL OUTER JOIN with only unique rows in both tables

In this exercise we will query the employees table using a FULL OUTER JOIN on the orders table using only the unique rows in each table.

SELECT DISTINCT employees.employee_id,
                employees.last_name,
                orders.customer_id
FROM employees
FULL OUTER JOIN orders ON employees.employee_id = orders.employee_id;

The query should return 464 rows.

SELF JOIN

SELECT with a self JOIN

In this exercise we will query the orders table using a self JOIN.

SELECT a.employee_id AS employee_id_1,
       b.employee_id AS employee_id_2,
       a.customer_id
FROM orders a,
     orders b
WHERE a.employee_id <> b.employee_id
  AND a.customer_id=b.customer_id
ORDER BY a.employee_id;

The query should return 8,704 rows.

CROSS JOIN

SELECT with a CROSS JOIN

In this exercise we will query the customers table using a CROSS JOIN with the suppliers table.

SELECT customers.customer_id,
       customers.contact_name,
       suppliers.company_name,
       suppliers.supplier_id
FROM customers
CROSS JOIN suppliers;

The query should return 2,639 rows.

NATURAL JOINs

SELECT with a NATURAL INNER JOIN

In this exercise we will query the products table using a NATURAL INNER JOIN with the order_details table.

SELECT DISTINCT products.product_id,
                products.product_name,
                order_details.unit_price
FROM products
NATURAL INNER JOIN order_details
ORDER BY products.product_id;

The query should return 76 rows.

SELECT with a NATURAL LEFT JOIN

In this exercise we will query the customers table using a NATURAL LEFT JOIN with the orders table.

SELECT DISTINCT customers.customer_id,
                customers.contact_name,
                orders.ship_region
FROM customers NATURAL
LEFT JOIN orders
ORDER BY customers.customer_id DESC;

The query should return 91 rows.

SELECT with a NATURAL RIGHT JOIN

In this exercise we will query the customers table using a NATURAL RIGHT JOIN with the orders table.

SELECT DISTINCT customers.company_name,
                customers.contact_name,
                orders.ship_region
FROM customers NATURAL
RIGHT JOIN orders
ORDER BY company_name DESC;

The query should return 89 rows.