When you're diving into databases, you'll quickly notice that data isn't usually crammed into a single table. Take an e-commerce site, for instance, you'd typically find one table for customers, another for orders, a separate one for products and yet another for payments.
What are SQL Joins
A SQL join is a tool that lets you combine rows from two or more tables based on a shared column between them.
For example, suppose we have these two tables:
Customers
Orders
The customer_id column connects the two tables. Instead of looking at customers and orders separately, we can use a join to find out who placed each order.
The result would be:
Types of SQL Joins
INNER JOIN
An INNER JOIN returns only records that have a match in both tables. It should be used when you only want records where a relationship exists.
For example, if you are generating a report showing customers who have actually placed orders, an INNER JOIN makes sense. Customers who have never placed an order will not appear.
LEFT JOIN
A LEFT JOIN returns all records from the left table, even when there is no matching record in the right table.
This becomes particularly useful when you want to identify customers who have not placed any orders.
RIGHT JOIN
A RIGHT JOIN works similarly to a LEFT JOIN, except that all records from the right table are returned.
In practice, RIGHT JOIN is used less frequently because the same result can usually be achieved by reversing the order of the tables and using a LEFT JOIN.
FULL OUTER JOIN
A FULL OUTER JOIN returns all records from both tables. Where a matching record doesn't exist, SQL returns NULL for the missing data.
This can be useful when comparing two datasets and you want to identify both matching and unmatched records.
For example, a company could use it to compare customer records from two different systems and find customers that exist in one system but not the other.
Conclusion
SQL joins may seem confusing when you first encounter them, but the basic idea is straightforward which is joins allow us to connect related data stored in different tables.
The key is understanding what information you want in the final result.
Once you understand joins, working with real-world databases becomes much easier. Whether you are building an e-commerce application, analyzing business data or creating reports in tools such as Power BI, joins are a skill you will use constantly.
Top comments (0)