Previous

SQL INNER JOIN

The INNER JOIN clause in SQL is used to combine rows from two or more tables based on a related column between them. It returns only the rows that have matching values in both tables. If there is no match, those rows are excluded from the result.

 

Syntax of INNER JOIN

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
  • table1: First table in the join.
  • table2: Second table in the join.
  • column: The column that links the two tables.

 

Example

Consider the following two tables:

Customers Table

customer_id first_name last_name
1 John Doe
2 Jane Smith
3 Mike Johnson

Orders Table

order_id customer_id order_date
101 1 2024-01-01
102 2 2024-02-15
103 1 2024-03-10

To get a list of customers and their respective orders, we can use an INNER JOIN on the customer_id:

SELECT customers.first_name, customers.last_name, orders.order_id, orders.order_date
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;

Output:

first_name last_name order_id order_date
John Doe 101 2024-01-01
Jane Smith 102 2024-02-15
John Doe 103 2024-03-10

In this example:

  • The INNER JOIN combines rows from the customers and orders tables where the customer_id matches in both tables.
  • Only customers who have orders are included in the result. Customers without orders are excluded.

 

INNER JOIN with Multiple Tables

You can join more than two tables using multiple INNER JOINs. For example, joining a products table to the previous query:

SELECT customers.first_name, customers.last_name, orders.order_id, products.product_name
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
INNER JOIN products ON orders.order_id = products.order_id;

 

INNER JOIN with WHERE Clause

You can also filter the results using a WHERE clause after performing an INNER JOIN. For example:

SELECT customers.first_name, customers.last_name, orders.order_id
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.order_date >= '2024-02-01';

This query retrieves customers who made orders after February 1, 2024.

 

Key Points

  • INNER JOIN only returns rows with matching values in both tables.
  • It is the most common type of join and is used when you need to combine related data from different tables.
  • You can join multiple tables using multiple INNER JOIN clauses.
  • Filters can be applied to the results after the join using WHERE.

 

Summary

The SQL INNER JOIN is a powerful tool for combining related data from two or more tables. It ensures that only matching rows from both tables are included in the result. This join is commonly used in database queries to retrieve related information, such as customer orders or employee details from different tables.

Previous