What is the inner join operator in SQL and when should it be used?

INNER JOIN in SQL is used to combine rows from two or more tables based on a related column between them. It returns only those rows for which the join condition is true in both tables.

Use cases:

• When you need to get data from related tables where each record in one table has a corresponding record in another.
• When you want to select common records between two data sets.
• Data filtering when you need to exclude records that do not have a match in another table.

Example:

Suppose there are two tables: Users (id, username) and Orders (id, user_id, amount). To get a list of users and their orders, where users have actually placed orders, INNER JOIN is used:

SELECT
    u.username,
    o.amount
FROM
    Users u
INNER JOIN
    Orders o ON u.id = o.user_id;

Table illustrating the result:

username	amount
user1	100
user2	50
user1	200

If user3 had no orders in the Orders table, he would not appear in the INNER JOIN result.