SQL JOINS

That's a great topic! SQL JOINs are essential for working with relational databases, as they allow you to combine related data that is stored across multiple, separate tables.

---

What are SQL JOINs? 🔗

In a relational database, data is normalized—meaning it's split into smaller, logical tables to reduce redundancy. For example, a table of Orders might only store a CustomerID number, while the actual customer details (Name, Address, etc.) are in a separate Customers table.

A JOIN operation combines rows from two or more tables based on a related column between them (often a Foreign Key in one table referencing a Primary Key in another).

The basic syntax for most joins is:

SQL

SELECT columns
FROM TableA
JOIN_TYPE TableB
ON TableA.ColumnName = TableB.ColumnName;

---

Primary Types of SQL JOINs 🤝

There are four main types of JOINs, defined by how they treat rows that do not have a match in the other table.

1. INNER JOIN (The most common type)

• Goal: Returns only the rows that have matching values in both tables.

• Result: Rows without a match in the opposite table are excluded from the result set.

2. LEFT JOIN (or LEFT OUTER JOIN)

• Goal: Returns all rows from the left table (TableA), and the matched rows from the right table (TableB).

• Result: If there is no match in the right table, the columns from the right table will contain NULL. The left table's data is always included.

3. RIGHT JOIN (or RIGHT OUTER JOIN)

• Goal: Returns all rows from the right table (TableB), and the matched rows from the left table (TableA).

• Result: If there is no match in the left table, the columns from the left table will contain NULL. The right table's data is always included.

4. FULL JOIN (or FULL OUTER JOIN)

• Goal: Returns all rows when there is a match in either the left or the right table.

• Result: Rows are returned from both tables, with NULL values placed in columns for the table that does not have a match.

---

Practical Example

Imagine we have two tables:

1. Customers: Contains CustomerID and CustomerName.

2. Orders: Contains OrderID, CustomerID, and OrderDate.

We want a list of all customers and any orders they have placed.

Using a LEFT JOIN

If you use a LEFT JOIN:

SQL

SELECT
    C.CustomerName,
    O.OrderID,
    O.OrderDate
FROM
    Customers C  -- This is the Left Table
LEFT JOIN
    Orders O     -- This is the Right Table
ON
    C.CustomerID = O.CustomerID;

Result: This query would return every customer (even those who have placed zero orders). For customers with no orders, the OrderID and OrderDate columns would show NULL.

Do you have a specific scenario or set of tables you'd like to try joining?