Joins

Imagine a situation where we need records from the database but these records are in different tables. For example, some of the employee records are in one table and other details are in other tables. For example, we need to get the customer details and the names of orders they made and the date of ordering.

From the above, the details needed are in two different tables ie. customer details are in the customers table and the order details needed in the orders table. This is where JOINS come into play.

A join is a query that joins rows from two or more tables, views, or materialized views into a single result set. JOINS help in getting records from multiple tables. When using joins, we specify all the table we want to get data with a JOIN keyword in between. We must also have a common column in both tables that we use to join the tables. We finally specify the relationship between the tables using the common column. This relationship could be done with in the WHERE clause (old way) or the JOIN (USING) clause (new way—ISO/ANSI SQL1999 standard). For our case, we shall stick to the new format because of its simplicity and it is recommended.

Syntax

SELECT column, column, ... FROM table1 JOIN table2 USING (common_column);

Using our previous example, let us get a query to help us in answering the question (we need to get the customer details and the names of orders they made and the date of ordering). Remember the steps above. The tables we need are orders and customers tables. The common column in both tables is order_id. After getting that, its time for the query.

SELECT customer_id, customer_name, order_id, order_name, order_date FROM customers JOIN orders USING (order_id);

customer_id	customer_name	order_id	order_name	order_date
20	John Peter	1	MacBook Pro 16	21-02-2020
21	Resty Joy	2	iPhone 13 Pro Max	20-01-2021

After understanding how we can JOINS, let us see the types of joins we have in Oracle.

Types of Joins

There are different types of joins like left join, right join, inner join, cartesian join, natural join, among others. For our case, we shall focus only on the four types i.e. inner join and outer join (left join, right join and full join). For a full view of all join types, you can

Inner Join

This kind of join returns records from table A which has a matching row in table B i.e. it returns matched rows. For example, lets say we’re using the previous example of customers and orders. With the INNER join, only customers who placed orders will be returned. We use the keyword INNER JOIN or just JOIN to join the two tables.

Lets get an example using our sample hr database. We need to see employees names with their departments. We have the employees table and the departments table. Employee records in the employee table do not have department names. Department names are in the department table.

From the above, lets get a common column we shall use to join the table i.e. department_id column. Therefore, we can use the query below to get the desired results.

SELECT first_name, last_name, phone_number, department_name FROM employees INNER JOIN departments USING (department_id);

From the output above, only employees belonging to a department are ones returned. Remember, with the INNER join, records in table 1 must have a corresponding record in table 2. ?

We can also get the same output without using the INNER keyword when joining both tables.

SELECT first_name, last_name, phone_number, department_name FROM employees JOIN departments USING (department_id);

Outer Join

Sometimes, we might wish to see data from one table even if there is no corresponding entry in the joining table. We use outer joins for this. The results of an outer join depend on the inner join condition, as well as any unmatched rows from one or both tables.

Left Join

This kind of join returns all records from table A regardless whether there is a matching record in table B. The first table in the query is the table whose records are all returned. Lets use the previous example of customers and orders. With the LEFT join, all customer records are returned regardless whether they have any order placed or not. We use the keyword LEFT JOIN to join the two tables.

Using the sample hr database, lets get all countries and their cities regardless whether country has a city in the database or not. We are going to use the countries and locations tables. Country names are stored in the countries table and cities are in the locations table. Both tables have country_id column as the common column.

SELECT country_id, country_name, city FROM countries LEFT JOIN locations USING (country_id);

Note: The table whose records are all needed should be the first to be mentioned in the query. LEFT keyword is not optional here.

Right Join

This kind of join returns all records from table B regardless whether there is a matching record in table A. The last table in the query is the table whose records are all returned. Lets use the previous example of customers and orders. With the RIGHT JOIN, all order records are returned regardless whether they have a customer associated with them or not. We use the keyword RIGHT JOIN to join the two tables.

Using the sample hr database, lets get all departments and their employees regardless whether a department has employees or not. We are going to use the employees and departments tables. Country names are stored in the countries table and cities are in the locations table. Both tables have country_id column as the common column.

SELECT first_name, last_name, department_name FROM employees right JOIN departments USING (department_id);

Full Join

This kind of join returns all records from both tables regardless whether there is a matching row in either tables or not. Lets use the previous example of customers and orders. With the full join, all order records are returned regardless whether they have a customer associated with them or not and vice-versa. We use the keyword FULL JOIN to join the two tables.

Lets get an example using our sample hr database. We need to see employees names with their departments regardless whether an employee is attached to a department or any department has an employee. We have the employees table and the departments table. Let us use the common column i.e. department_id to join both tables.

SELECT first_name, last_name, department_name FROM employees FULL JOIN departments USING (department_id);

Joins are not only used to get records from two tables. We can get data from multiple tables with the help of joins. We add the JOIN keyword after the first JOIN condition. For example, We need to see country names, cities and the region in which the country belongs. These three records are available in three tables i.e. countries, regions, and locations table. We have common columns i.e. country_id (locations and countries table) and region_id (countries and regions table). Let us see how the query looks like.

SELECT country_name, city, region_name FROM countries JOIN locations USING (country_id) JOIN regions USING (region_id);

We can also use use all other constructs of a query like WHERE, ORDER BY, GROUP BY. For example, lets modify the previous query to return only countries from Asia in ascending order.

SELECT country_name, city, region_name FROM countries JOIN locations USING (country_id) JOIN regions USING (region_id) WHERE region_name = 'Asia' ORDER BY 1;

Summary

1. Joins are used to get records from multiple tables.

2. Inner join returns records from table A which has a matching row in table B

3. Right join returns all records from table B regardless whether there is a matching record in table A.

4. Left join returns all records from table A regardless whether there is a matching record in table B.

5. Full join returns all records from both tables regardless whether there is a matching row in either tables or not.

6. Joins are not only used to get records from two tables. We can get data from multiple tables with the help of joins.