SQL: INNER JOIN vs LEFT JOIN
INNER JOIN keeps only matched rows from both tables. LEFT JOIN keeps every row from the left table and fills NULL where the right table has no match.
INNER JOIN returns only the rows where a match exists in both specified tables. It filters out records from either table that lack a corresponding key in the other. Use this when you only need data that is definitively linked across all joined datasets.
LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table, regardless of matches in the right table. If a row in the left table has no match in the right table, the columns from the right table will contain NULL values. Use this when you must retain all records from the primary (left) table and wish to append related data if available.
Consider users (left) and orders (right):
INNER JOIN: shows only users who have placed orders.LEFT JOIN: shows all users, with order details filled in when present; users with no orders getNULLin thetotalcolumn.
SELECT u.id, u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;