How are table joins implemented in SQL and how to use them?

SQL joins are used to combine rows from two or more tables based on a related column between them.

Main types of JOIN:

• INNER JOIN — returns only the records that match in both tables.
• LEFT JOIN (or LEFT OUTER JOIN) — returns all records from the left table and the matching records from the right table; if there is no match, the right side will contain NULL.
• RIGHT JOIN — the opposite, returns all from the right and matching from the left.
• FULL JOIN — combines LEFT and RIGHT, returning all records from both tables.

Example in C# using an SQL query:

string query = @"
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID";

// Here we join the Orders and Customers tables on the CustomerID field to get the customer name for each order.

Using JOIN allows efficient retrieval of related data from multiple tables in a single query.