What is a left join in SQL?

A LEFT JOIN (or LEFT OUTER JOIN) in SQL returns all rows from the "left" table and the matching rows from the "right" table. If there is no match in the right table, the columns from the right table will contain NULL.

• Syntax:

  SELECT column_list
  FROM table1  -- Left table
  LEFT JOIN table2 ON table1.column = table2.column;
  

• Result:

  • All rows from table1.
  • Matching rows from table2.
  • For rows in table1 with no match in table2, the table2 columns will contain NULL.

• Example:

  Suppose we have two tables: Orders and Customers. We want to get a list of all orders and the customer information who made them. If an order has no customer (for example, deleted), we still want to see that order.

  -- Orders table
  -- OrderID | CustomerID | OrderDate
  -- 1       | 101        | 2023-10-26
  -- 2       | 102        | 2023-10-26
  -- 3       | NULL       | 2023-10-27
  
  -- Customers table
  -- CustomerID | CustomerName
  -- 101        | Alice
  -- 102        | Bob
  
  SELECT
      O.OrderID,
      C.CustomerName
  FROM
      Orders AS O
  LEFT JOIN
      Customers AS C ON O.CustomerID = C.CustomerID;
  

  OrderID	CustomerName
  1	Alice
  2	Bob
  3	NULL