Can you explain the concept of semi join and how it differs from other types of joins in databases?

Semi join is a type of join in databases where only the rows from the left table are selected for which there exists at least one matching row in the right table, but only columns from the left table are returned in the result set.

Differences from other joins:

• Inner join returns rows from both tables where there is a match based on the condition.
• Left join returns all rows from the left table and the matching rows from the right table, if any; otherwise, NULLs are used.
• Semi join returns only the rows from the left table that have a match in the right table, without adding columns from the right table.

Example: if we have a customers table and an orders table, semi join allows us to get a list of customers who have made at least one order, without adding information about the orders themselves.