Left join in sql server

The LEFT JOIN clause allows you to query data from multiple tables. It returns all rows from the left table and the matching rows from the right table. If no matching rows found in the right table, NULL are used.
The following illustrates how to join two tables T1 and T2 using the LEFT JOIN clause:
In this syntax, T1 and T2 are the left and right tables respectively.
For each row from the T1 table, the query compares it with all the rows from the T2 table. If a pair of rows causes the join predicate to evaluate to TRUE, the column values from these rows will be combined to form a new row which then included in the result set.
If a row from the left table (T1) does not have any matching row from the T2 table, the query combines column values of the row from the left table with NULL for each column values from the right table.
In short, the LEFT JOIN clause returns all rows from the left table (T1) and matching rows or NULL values from the right table (T2).
The following illustrates the LEFT JOIN of two tables T1(1, 2, 3) and T2(A, B, C). The LEFT JOIN will match rows from the T1 table with the rows from T2 table using patterns:
In this illustration, no row from T2 table matches the row 1 from the T1 table, therefore, NULL is used. Rows 2 and 3 from the T1 table match with rows A and B from the T2 table respectively.

SQL Server LEFT JOIN example

See the following products and order_items tables:
The order_items & products Tables
Each sales order item includes one product. The link between the order_items and the products tables is the product_id column.
The following statement uses the LEFT JOIN clause to query data from the products and order_items tables:
SQL Server Left Join example
As you see clearly from the result set, a list of NULL in the order_id column indicates that the corresponding products have not sold to any customer yet.
It is possible to use the WHERE clause to limit the result set. The following query returns the products that do not appear in any sales order:
SQL Server Left Join find unmatching rows
As always, SQL Server processes the WHERE clause after the LEFT JOIN clause.
The following example shows how to join three tables: production.productssales.orders, and sales.order_items using the LEFT JOIN clauses:
Here is the output:
SQL Server LEFT JOIN: conditions in ON vs. WHERE clause
The following query finds the products that belong to the order id 100:
Let’s move the condition order_id = 100 to the ON clause:
The query returned all products but only the order with id 100 has the associated product’s information.
Note that for the INNER JOIN clause, the condition in the ON clause is functionally equivalent if it is placed in the WHERE clause.
In this tutorial, you have learned how to use the SQL Server LEFT JOIN clause to retrieve data from multiple related tables.

Post a Comment