What types of JOIN do you use in your practice?

In my practice, I use the following types of JOIN:

• INNER JOIN (or simply JOIN): Returns rows where there are matches in both tables.
• LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and matching rows from the right. If there are no matches in the right table, the right columns will contain NULL.
• RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and matching rows from the left. If there are no matches in the left table, the left columns will contain NULL.
• FULL JOIN (or FULL OUTER JOIN): Returns all rows where there is a match in one of the tables. The result will be a union of LEFT JOIN and RIGHT JOIN results. Non-matching rows will have NULL in the columns of the other table.

Example of a query using INNER JOIN in Rails Active Record:

// Returns users who have at least one order
User.joins(:orders)

Example of a query using LEFT JOIN:

// Returns all users and their associated orders (if any)
User.left_joins(:orders)

In complex cases, I can use Arel to build more flexible and specific JOIN queries.

// Example of Arel for LEFT JOIN
users_table = User.arel_table
orders_table = Order.arel_table

User.joins(users_table.join(orders_table, Arel::Nodes::OuterJoin).on(users_table[:id].eq(orders_table[:user_id])).outer)