This is tricky to understand, Let me try to explain in simpler way.
Here two tables are joining based on type attribute.
if type = 1 query will join with private table else it will join company table.
LEFT JOIN will help you to filter only records from left table ie., users
SELECT *, IF (users.type = 1, p.name, c.name) AS name FROM users LEFT JOIN private AS p ON (users.type = 1 AND users.id = p.user_id) LEFT JOIN company AS c ON (users.type != 1 AND users.id = c.user_id)
We can extend this query to join ‘n’ number of tables .