SQL INNER JOIN 🎯

Welcome to our SQL INNER JOIN tutorial! In this lesson, we'll explore one of the most powerful features in SQL - the INNER JOIN. This will allow us to combine rows from two or more tables, based on a related column between them. Let's dive in!

What is SQL INNER JOIN? 📝

In simple terms, INNER JOIN is used to retrieve records that have matching values in both tables. It returns records where the joined columns are not null in either table.

Basic Syntax 💡

The basic syntax for an INNER JOIN is as follows:

sql

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

Let's take an example to understand this better.

Practical Example 💡

Suppose we have two tables: Customers and Orders.

sql

-- Customers table
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(50),
    Country VARCHAR(50)
);

-- Orders table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    Product VARCHAR(50)
);

To find the names of customers who have placed orders, we can use the following INNER JOIN query:

sql

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

This query will return a combined table with the names of customers who have placed orders, along with the products they've ordered.

📝 Note:

The ON clause defines the joining condition.
Inner joins will only return records where matching records exist in both tables.
If there are no matching records, the result will be an empty set.

Advanced INNER JOIN Examples 💡

Multiple Tables INNER JOIN

sql

SELECT Customers.Name, Orders.Product, OrderDetails.Quantity
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID;

This query combines data from three tables: Customers, Orders, and OrderDetails. It will return the names of customers, the products they've ordered, and the quantity of each product.

Self-Join

A self-join occurs when a table is joined with itself. It can be useful when you have a table with a reference to itself, like a hierarchy or a many-to-many relationship.

sql

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    ManagerID INT,
    Name VARCHAR(50)
);

To find all employees and their managers, we can use a self-join:

sql

SELECT e1.Name AS Employee, e2.Name AS Manager
FROM Employees e1
LEFT JOIN Employees e2
ON e1.ManagerID = e2.EmployeeID;

This query will return a list of employees and their managers. If an employee has no manager (like the CEO), their Manager field will be null.

Quiz 💡

That's it for our SQL INNER JOIN tutorial! As you've seen, INNER JOIN is a powerful tool for working with multiple tables in SQL. With practice, you'll be joining tables like a pro in no time!

Happy coding! 💻💻💻