SQL Joins Made Easy: INNER, OUTER, LEFT, RIGHT Joins

#learning #database #sql #developer

What Are Joins?

Joins in SQL allow you to combine rows from two or more tables based on a related column. They are a powerful tool for retrieving meaningful, interconnected data from multiple tables in your database.

Types of Joins

INNER JOIN:

Retrieves rows that have matching values in both tables.

SELECT users.Name, orders.OrderID
FROM users
INNER JOIN orders ON users.ID = orders.UserID;

This query retrieves the names of users and their corresponding order IDs by matching users.ID with orders.UserID.

LEFT JOIN (or LEFT OUTER JOIN)

Retrieves all rows from the left table and the matching rows from the right table. If there’s no match, NULL values are returned for the right table's columns.

RIGHT JOIN (or RIGHT OUTER JOIN)

Opposite of LEFT JOIN—it retrieves all rows from the right table and the matching rows from the left table. NULL values appear for non-matching rows in the left table.

FULL OUTER JOIN:

Combines the results of LEFT JOIN and RIGHT JOIN, returning all rows from both tables. Where there is no match, NULL values will fill in for the missing data on either side.