The data available does not come from a single table in real life. While customer information could be present within one table, their order details will be kept separate in another. The JOIN operation brings together the contents of multiple tables to help with analysis. You'll learn it at an introductory level when you start studying SQL at your Data Science Training Institute in Mumbai. Let us discuss each type.

The Example Tables

customers

orders

Ravi has no orders, and order 104 belongs to a customer who is not in the customers table.

INNER JOIN: Only the Matches

INNER JOIN returns rows that have a match in both tables.

SELECT c.name, o.amount

FROM customers c

INNER JOIN orders o ON c.id = o.customer_id;

Output: Asha (500), Asha (700), Meera (300).

However, Ravi and Order No. 104 are excluded since there is no matching record for these two entities. Use this technique whenever only complete pairs of data entries will do.

LEFT JOIN: Keep Everything From the Left Table

LEFT JOIN returns all records from the left table, along with those records which match in the right table. If no match exists in the right table, you get NULL values.

SELECT c.name, o.amount

FROM customers c

LEFT JOIN orders o ON c.id = o.customer_id;

Output: Asha (500), Asha (700), Ravi (NULL), Meera (300).

Ravi comes next with a zero value. It helps identify missing values, such as customers who have never placed orders. Use WHERE o.id IS NULL in order to retrieve just those customers.

RIGHT JOIN: Keep Everything From the Right Table

RIGHT JOIN works exactly opposite. All rows from the right table appear in results together with matching rows from the left.

SELECT c.name, o.amount

FROM customers c

RIGHT JOIN orders o ON c.id = o.customer_id;

Output: Asha (500), Asha (700), Meera (300), NULL (900).

Order number 104 does not have any customer name associated with it. Most of the analysts tend to avoid using RIGHT JOIN since reversing the tables and applying LEFT JOIN accomplishes the task.

Conclusion

And it all comes down to just one thing – what kind of unmatched rows do you wish to retain. Go for an INNER JOIN if you only require matching rows; use a LEFT JOIN if you would like to retain everything from the first table; or choose a RIGHT JOIN for retaining elements from the second table.

Work your way through small tables first until you feel confident about your predictions. You can consider taking up a Data Science Training Course in Noida where mentoring on actual data sets will allow you to learn both SQL and Python, along with statistics and machine learning.