Types of JOINs in SQL

A quick overview of JOIN commands that Relational Databases have to offer

An important component of any relational database system is the relationship between tables. This relationship facilitates connecting two tables based on data that they ‘have in common’. This connection is accomplished using a JOIN, which is an operation that matches rows from one table to the rows in another. The matching is done in such a manner that the columns from both the tables placed side by side although they may have come from separate tables. It must be said though, the fewer tables being joined is the faster the query will operate. This article gives an introduction to joins and their uses in the relational database engine.

Let’s create tables: CUSTOMERS and ORDERS as follows:

End-to-End Database Development Courses

CUSTOMERS ( cust_id [PK] , cust_fname, cust_lname, phone, address, country);

ORDERS ( order_id [PK] , order_date, reqd_date, ship_date, cust_id [FK] );

INNER JOIN

Scenario: We need to see a list of customers and details of their respective orders.

This is the simplest type of join where each row in one table is matched with all other rows in another table. Only if the join condition evaluates to true a row is. The data in common, must be true for both tables involved in the JOIN.

SELECT 
	customers.cust_fname, orders.order_id, orders.order_date 
FROM 
	customers INNER JOIN orders ON customer.cust_id = orders.cust_id; 

Result:

cust_name	order_id	order_date
John	789654	2015-06-10
Betty	147893	2016-12-04

Interestingly enough (doesn’t work for all engines), you can also write an INNER JOIN query with a WHERE condition.

SELECT 
	customers.cust_fname, orders.order_id, orders.order_date 
FROM 
	customers, orders  
WHERE  
	customers.cust_id = orders.cust_id;

LEFT JOIN

This JOIN retrieves all records in the table that is on the (literally) LEFT side of the join condition AND…any columns that match from the table on the RIGHT side of the condition. So a so customer with no orders will be returned from the customer table, but NULL values are returned for the columns in the orders table for the rows that do not match the JOIN condition.

SELECT 
	customers.cust_fname, orders.order_id, orders.order_date 
FROM 
	customers LEFT JOIN orders 
ON customer.cust_id = orders.cust_id;

cust_name	order_id	order_date
John	789654	2015-06-10
Betty	NULL	NULL

RIGHT JOIN

This JOIN does the inverse of the LEFT JOIN. It returns all records from the table on the right, with any rows that might match the condition from the left table. In the same way, NULL values are returned for the columns in the left table for the rows that do not match the JOIN condition.

SELECT 
	customers.cust_fname, orders.order_id, orders.order_date 
FROM 
	customers RIGHT JOIN orders 
ON customer.cust_id = orders.cust_id;

The LEFT and RIGHT JOIN statements are sometimes penned with an optional OUTER keyword:

LEFT OUTER JOIN
RIGHT OUTER JOIN

End-to-End Database Development Courses

CROSS JOIN

A cross join returns as many combinations of all the rows contained in the joined tables. That is,each row of the left table concatenated with every row in the right table. Therefore, if there are total M rows in Table A and there are N rows in Table B, a cross join will produce M x N rows. Two ways to accomplish this CROSS JOIN are as follows:

CROSS JOIN

SELECT 
	customers.cust_fname, orders.order_id, orders.order_date 
FROM customers CROSS JOIN orders;

Select Statement

SELECT 
	customers.cust_fname, orders.order_id, orders.order_date 
FROM 
	customers, orders;

FULL JOIN

A FULL JOIN allows you to join two tables completely, bringing back full rows where the JOIN condition is met and NULL values on the side with no matching rows.

SELECT 
	customers.cust_fname, orders.order_id, orders.order_date 
FROM 
	customers FULL JOIN orders 
ON customer.cust_id = orders.cust_id;

This query will attempt a LEFT and RIGHT JOIN in one, bring back full rows for customers with orders and NULLs where there is a customer, with no order…and then also bring back every single order, and NULLs for Orders that have no matching customer (a good query to audit your Point of Sales System maybe?). The FULL JOIN is also sometimes written with the OUTER keyword.

Conclusion

Typically, JOINs are used to establish a master-detail kind of data representation and are essential for pulling meaningful data from multiple related tables. Multiple tables can be joined in a singular select statement, but remember that the query will slow down for each table and more so, the number of records being returned.