In SQL Server, a LEFT JOIN is a type of join that returns all the records from the left table and only the matching records from the right table. This means that even if there are no matches in the right table, the left table records will still be returned.
To perform a LEFT JOIN in SQL Server, you use the JOIN keyword followed by the LEFT OUTER JOIN keyword, and then specify the tables you want to join and the columns you want to retrieve.
Syntax
The basic syntax for a LEFT JOIN in SQL Server is as follows:
SELECT column1, column2, ...
FROM table1
LEFT OUTER JOIN table2
ON table1.column = table2.column;
In this syntax, the SELECT statement specifies the columns you want to retrieve from the tables, while the FROM clause specifies the left table, which is table1 in this case. The LEFT OUTER JOIN keyword specifies the type of join to use, and the ON clause specifies the join condition, which is the column to join on.
Example
For example, let's say you have two tables, orders and customers, with the following data:
Orders table
| order_id | customer_id | order_date |
|---|---|---|
| 1 | 101 | 2022-01-01 |
| 2 | 102 | 2022-01-02 |
| 3 | 103 | 2022-01-03 |
| 4 | 101 | 2022-01-04 |
Customers able
| CUSTOMER_ID | CUSTOMER_NAME |
|---|---|
| 101 | John Smith |
| 102 | Jane Doe |
To retrieve all the orders and the corresponding customer names (if available), you can use a LEFT JOIN like this:
SELECT orders.order_id, customers.customer_name
FROM orders
LEFT OUTER JOIN customers
ON orders.customer_id = customers.customer_id;
Result:
This query will return the following result:
| order_id | customer_name |
|---|---|
| 1 | John Smith |
| 2 | Jane Doe |
| 3 | NULL |
| 4 | John Smith |
As you can see, the LEFT JOIN returns all the orders from the orders table, and the matching customer names from the customers table. However, since there is no customer with customer_id 103 in the customers table, the corresponding customer_name column is NULL in the result set.
Overall, the LEFT JOIN in SQL Server is a useful tool for combining data from multiple tables, and it ensures that all the records from the left table are included in the result set, even if there are no matches in the right table.
