SQL Joins: INNER JOIN, LEFT JOIN, and RIGHT JOIN

SQL joins: INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN 

SQL joins are essential tools for retrieving related data from multiple tables. Understanding how to use joins effectively can significantly enhance your database queries. In this post, we’ll cover three commonly used joins: INNER JOIN, LEFT JOIN, and RIGHT JOIN.

1. INNER JOIN

What it Does:

The INNER JOIN returns rows that have matching values in both tables. If no match is found, the row is excluded from the result.

Syntax:

SELECT column1, column2
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

Example:

Given two tables:

Customers

CustomerID	Name	City
1	Alice	New York
2	Bob	Chicago
3	Charlie	Boston

Orders

OrderID	CustomerID	OrderAmount
101	1	200
102	2	150
103	4	300

Query:

SELECT Customers.Name, Orders.OrderAmount
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

Result:

Name	OrderAmount
Alice	200
Bob	150

2. LEFT JOIN (or LEFT OUTER JOIN)

What it Does:

The LEFT JOIN returns all rows from the left table and the matching rows from the right table. If no match is found, NULL is returned for columns from the right table.

Syntax:

SELECT column1, column2
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;

Example:

Using the same tables:

Query:

SELECT Customers.Name, Orders.OrderAmount
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

Result:

Name	OrderAmount
Alice	200
Bob	150
Charlie	NULL

3. RIGHT JOIN (or RIGHT OUTER JOIN)

What it Does:

The RIGHT JOIN returns all rows from the right table and the matching rows from the left table. If no match is found, NULL is returned for columns from the left table.

Syntax:

SELECT column1, column2
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;

Example:

Query:

SELECT Customers.Name, Orders.OrderAmount
FROM Customers
RIGHT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

Result:

Name	OrderAmount
Alice	200
Bob	150
NULL	300

Quick Comparison:

Join Type	Rows Returned
INNER JOIN	Only matching rows from both tables
LEFT JOIN	All rows from the left table + matching rows from right
RIGHT JOIN	All rows from the right table + matching rows from left

Final Thoughts:

Understanding how these joins work can help you retrieve data effectively and avoid unexpected results. Practice using these joins with different datasets to master them!

If you have any questions or need further clarification, feel free to ask in the comments below.