Using Five Types of JOIN in MySQL

An important component of any relational database system is the relationship between tables. This relationship enables one to tie data in one table to data in another table through SQL joins. The relationship is particularly useful when you want to query related data from more than one table and retrieve a meaningful result. Sometimes, this relationship may not be obvious but through SQL they can be combined in a variety of ways and conclusions can be drawn based upon conditions and clauses you provide. The article gives a quick introduction to joins and their types using SQL in the MySQL database.

MySQL JOIN Overview

The ability to query on the database that includes more than one table is referred to as joining of tables. A JOIN therefore 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. There can be n-number of tables joined but practically, a fewer number of tables generally result in a more efficient and manageable query.

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

CUSTOMERS ( cust_id [PK] , cust_fname, cust_lname, contact_person, phone, address, city,state, pin_code, country, sales_representive, credit_limit);

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

EMPLOYEES( emp_id [PK] , emp_fname, emp_lname, email, job_title);

You can join the table using an SQL query to obtain the list of customers who have placed one or more orders with the list of dates the order has been placed:

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

Note that, a join is specified by an SQL SELECT statement with conditions in the FROM clause. The join operations can vary in form based upon the JOIN clauses you apply. This directly effects the output produces by the query. For example, you can retrieve query results by including only those rows that match or unmatch the rows from left, right or both the tables. This idea of using left, right and both the tables determines the structure of the SQL query and the type of join used. They can be termed as -inner join, left-outer join, right-outer join, full-outer join.

A quick introduction to five of them: inner join, outer join, cross join, union, intersection are as follows:

1. Inner join in MySQL

This is the simplest type of join where each row in one table is matched with all other rows in another table. If the join condition evaluates to true a row is returned otherwise it simply ignores the row. This means that rows from customers and orders are combined and the combination of row is returned only when the join condition evaluates to TRUE as you can see in the query below. The same logic applies if more than two tables are joined.

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
ABC Co.	123456	2005-06-20
ABC Co.	147893	2006-12-14
...	...	...
XYZ Ltd.	258963	2010-05-06

Interestingly, you can also write an inner join SQL query using WHERE clause to have the same effect as follows.

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

In this case the join is done implicitly by the database optimizers. This type of query though functionally equivalent is highly discouraged due to its misleading nature as a simple SELECT query.

The INNER JOIN is the default join operation in MySQL databases; therefore, the keyword INNER is optional.

2. Outer join in MySQL

This is similar to INNER JOIN, but here you retrieve all the rows from the left, right or both of the tables regardless of matching rows in another table. Therefore, there are three types of outer joins:

A. Left-outer join in MySQL

This join returns all matched rows from the right table and all matched as well as unmatched rows from the left table. The left and right are signified by the table mentioned at the left and right of the JOIN keyword. However, NULL values are returned for the columns in the right table for the rows that do not match the JOIN condition.

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

B. Right-outer join in MySQL

This join returns all matched rows from the left table and all matched as well as unmatched rows from the right table. The left and right are signified by the table mentioned at the left and right of the JOIN keyword. However, 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 OUTER JOIN orders 
ON customer.cust_id = orders.cust_id;

C. Full-outer join in MySQL

This join returns all matched and unmatched rows from both the table concatenated together. For the columns from the unmatching join condition, NULL values are returned.

Note that the full-outer join is not supported by MySQL although you can emulate one by combining left and right-outer join with UNION set operation. Oracle and SQL Server do support the full-outer join.

3. Cross product in MySQL

A cross product between two table returns 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 product will consist of M x N rows. There is no direct support to create cross product between tables in MySQL but you can use SELECT statement as follows to create one.

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

Since, there are no join conditions all rows from customers table is matched with the rows from the orders table. This simple statement creates cross-product between two tables. Same logic applies if cross-join with more than two table.

4. Union in MySQL

This is the same as the set union operation. It joins all the rows in both tables without the duplicate rows. With the UNION operator you can combine the results of multiple SELECT queries into a single result. This essentially combines rows from one query with rows from another. The significant difference of UNION with typical JOIN operation is that join add columns obtained from multiple tables side by side whereas UNION adds rows to the end of the result set. It should be obvious that in order to apply UNION operation, there must be a compatible datatype and a number of columns produced by the SELECT statement.

SELECT 
 customers.cust_fname, customers.cust_lname
FROM 
 customers

UNION

SELECT 
 employees.emp_fname, employees.emp_lname
FROM 
 employees

5. Intersection in MySQL

It is same as the set intersection operation. Similar to UNION operator, it compares the out of two or more SELECT queries and return the rows common to both the queries. MySQL has no support for INTERSECTION operation. But you can emulate one as follows:

SELECT 
 DISTINCT customers.cust_id
FROM 
 customers
WHERE
 customers.cust_id 
IN (SELECT cust_id FROM orders)

or you can use INNER JOIN as follows to obtain the same result:

SELECT 
 DISTINCT customers.cust_id
FROM 
 customers
INNER JOIN
 orders USING(cust_id);

Conclusion

According to the combination how JOINs of tables are linked, they can be represented as a left-deep, right-deep or bushy tree. This defines the order of execution and intermediated joins. Typically, JOINs are used to establish master-detail relationship where one base table is used to create a relationship with one or more other sub-tables.

This is just a glimpse of the JOIN operations performed with SQL in MySQL database. A quick grasp on these would help one to dive deep on the topic. Happy learning!