SQL JOINS in Data Analytics
“Data” is the new “oil.” No matter what type of business we have, we end up producing an immense amount of data that reaches up to the petabyte level. Data cannot simply be stored in the traditional row and column form. The type of data produced can differ in many ways, including text, image, video, files, and others. Data must therefore be stored in either relational database systems where the data is structured or non-relational database systems where the data is unstructured. In this blog, we will cover the topic of joins only in structured data.
In any organization, structured data that is table data exists in many tables. Similarly to how, in an organization, there are various departments such as the finance department, the operations department, the sales department, and the marketing department etc. Every department needs to share some data with other departments but not all data so as to avoid data leakage. The customer data cannot be leaked to other organizations who can use it against you. Similarly, the data related to finance generated by the sales data does not need to be shared with operations as there will be no data leakage. Example of two tables in the e-commerce company's sales department is 'orders' and 'customers'.
But then, how do we extract information from these individual tables? As analyzing the information contained within these two tables may not give us the entire story. Here comes the part of SQL joins. is basically a method that allows you to combine data from two tables based on some common field or join condition. In this case, we may use the common field like customer_id. Both the tables have this column, thus declaring it to be the common column. However, in one table this column becomes the primary key whereas in the other table it becomes the foreign key. Some of the features of the primary key column are that it should have the unique and non-null values. Unique values mean that they should not be duplicated and non-null values mean that they should not be empty. In the foreign key, however, this rule does not apply as it is the common column that is being used for joining the two tables.
There are different types of SQL joins through which you can perform specific actions on the table.
Types of SQL Joins With Examples
Now, we will discuss different SQL joins with an example of a fictional e-commerce company named UrbanCart. This organization uses two tables, namely "Customers," which contains details of its customers, and "Orders," which contains order details.
1. INNER JOIN
In case of an "INNER JOIN," only those records, which contain similar values in both the tables, would be retrieved. If any customer is not having any order in the system, then that customer will not be retrieved in the output.
SELECT c.customer_name, o.order_id, o.amount
FROM Customers c
INNER JOIN Orders o
ON c.customer_id = o.customer_id;
For instance, if Aditi has made two orders, both of her orders will be returned along with her name. However, those customers who don't have any order will not be returned.
When to use it: Purchases analysis for known customers or generating sales reports.
2. LEFT JOIN
With a "LEFT JOIN" operation, all rows from the left table and the matching rows from the right table are retrieved. In case there is no match, then NULL values are retrieved for the right table columns.
SELECT c.customer_name, o.order_id, o.amount
FROM Customers c
LEFT JOIN Orders o
ON c.customer_id = o.customer_id;
The query retrieves not only the orders placed by customers but also those who have never ordered anything. Such rows display NULL values in the order column.
Use cases: Determining non-buyers, analyzing the customer coverage, and identifying users who have registered but have never made any purchase.
3. RIGHT JOIN
RIGHT JOIN displays all the rows from the right table and only the matched rows from the left table. In cases where there is no match, the column values of the left table will have NULL values.
SELECT c.customer_name, o.order_id, o.amount
FROM Customers c
RIGHT JOIN Orders o
ON c.customer_id = o.customer_id;
In case there is an order for a customer who is not present in the Customers table, then such an order would be returned in the output with the customer name as NULL.
Usage: Auditing transactions and locating orders which do not have corresponding customers.
4. FULL OUTER JOIN
"FULL OUTER JOIN" gives us all matching records along with the unmatched records from both sides of the table. The NULL value represents the missing values on either side of the table.
SELECT c.customer_name, o.order_id, o.amount
FROM Customers c
FULL OUTER JOIN Orders o
ON c.customer_id = o.customer_id;
The above code will generate customers that haven’t yet made any orders, orders that have no corresponding customers, and the matched pairs.
It’s used when you require to reconcile two datasets or need to compare records from two different datasets.
Note: “FULL OUTER JOIN” is not available in MySQL. In order to obtain similar results in MySQL, we can use combination of LEFT and RIGHT joins using UNION ALL.
5. CROSS JOIN
Cross join is used when all possible combinations of rows from both tables are required. Unlike the joins mentioned above, it does not need a common key or "ON" clause.
SELECT c.customer_name, o.order_id
FROM Customers c
CROSS JOIN Orders o;
In case the Customers table has 5 rows and the Orders table has 5 rows, the output will be a total of 25 rows.
Usage: When creating possible combinations of products with months or forecasting.
6. SELF JOIN
"A Self Join occurs where a table is joined with itself."
Self join is applied where the rows in a table are related to some other rows in the same table as an example employee and his/her manager.
We will consider a table called “Employees” having “employee_id”, “employee_name”, and “manager_id”.
SELECT
e.employee_name AS employee,
m.employee_name AS manager
FROM Employees e
LEFT JOIN Employees m
ON e.manager_id = m.employee_id;
In this case, we are referring the table two times using the alias "e" and "m". First "e" refers to the employee whereas "m" refers to the manager. Employees having no manager will show NULL in the manager column.
Where to use? To analyze the hierarchy of the organization.
Author:
Agarwaal Akkshhey
Related Links:
Resume Tips For Software Developers
Do visit our channel to know more: SevenMentor
Agarwaal Akkshhey
Expert trainer and consultant at SevenMentor with years of industry experience. Passionate about sharing knowledge and empowering the next generation of tech leaders.